I did some profiling when I was working at one of the big online travel sites. We were running about 300 or so servers with a 50-connection pool each. After testing, we determined that at no time, ever, was more than one connection in simultaneous use across the server farm. We could have reduced it to a single shared connection per server and maintained the same QoS.
Why is that Oracle instance using a max thread pool size of 2048 on a 12-core host? That seems like the real problem. Why not configure a smaller thread pool like cores * 2, and do more queuing on the server?<p>It seems wrong IMO to expect <i>clients</i> to tune connection pools based on how much concurrency they think a server can productively handle. The client doesn't necessarily know how many cores the server has, or more importantly, how many other clients are connected.<p>In HTTP/2, clients can configure a maximum number of streams (SETTINGS_MAX_CONCURRENT_STREAMS), and the RFC recommends setting it "no smaller than 100". If servers worked on all pending requests concurrently, a value of 100 would be unreasonably large. But the implicit expectation is that servers will decide for themselves how many requests to process concurrently, and queue the rest. In other words, SETTINGS_MAX_CONCURRENT_STREAMS is intended to limit the server's queue size, not its thread pool size.
The other half of this is correctly using the connection pool in the application layer to begin with.<p>One of the most common performance errors I see in random http applications is grabbing db connections for too long, like in middleware, or too aggressively in parallel queries.
Alternatively, and specifically for PostgreSQL, if you can live with pgbouncer's constraints then you should be using it. It effectively self-manages an optimal shared connection pool for all of the application processes that connect to it.
See also [1]. The top comment there links to the article (2017).<p>[1] <a href="https://news.ycombinator.com/item?id=18220906" rel="nofollow">https://news.ycombinator.com/item?id=18220906</a>
Or you could use an async. db connection: <a href="https://github.com/tinspin/rupy/wiki/Fuse" rel="nofollow">https://github.com/tinspin/rupy/wiki/Fuse</a>
This article would be better if it was called "connection pool sizing for MySQL". If you had a better database, you would have to worry way less about configuring pool size on the clients.<p>The proper way to do this would be for the server (i.e. database) itself to have maximum active transaction limits and ways to setup quotas for different use cases, especially as your company gets large, and you have many different use cases mixed in the same database. Basically the queue should exist mostly on the server, and clients shouldn't have to worry about overwhelming the server. If server queue gets large, server should start rejecting requests faster, and clients would do a backoff and retry with a delay based on that instead. This also makes sure server can't be easily overworked if you have one misconfigured and misbehaving client.<p>Lot of issues with many idle connections in MySQL are specific to MySQL itself and its implementation. In MySQL the perf drops not only when you have many active transaction, but even when you have many just many connected idle sessions. This is why there are tons of different random "MySQL Connection Proxy" projects that exist in the open source.