5 ms·

About Database Connection Pool Sizing

194 points by zyngaro 8y ago

zrail 8y ago
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.
- dijit 8y ago
  I would like to dissent. If your application is long lived you will get more benefit using the pooling functionality provided by the postgresql client libraries for your platform/language. pgbouncer is an intimidatory, and thus another thing to break- it has it's own constraints (mainly things like prepared statements will block a connection/cannot be reused) and its application should be well understood. For instance; I use it on a local socket on my zabbix master so that it can connect using a TLS encrypted pgsql socket. (something zabbix doesn't support). But for my main applications written in C++, I use connection pooling with nothing but the C++ client libraries. If you're bringing up and tearing down connections very often, then you will get a lot of benefit from pgbouncer, but in my experience these cases are limited.
  - sb8244 8y ago
    Coming from ActiveRecord gem, pgbouncer has been great. I'd say the real advantage is transaction level pooling, which will free up connections when they're not actively in a transaction (vs request level isolation). I don't see how this is possible across a large number of isolated servers without a cooperating agent like pgbouncer. I would advocate for pgbouncer at the DB ingress level and your languages connection pooler at the application level.
bullen 8y ago
Or you could use an async. db connection: https://github.com/tinspin/rupy/wiki/Fuse https://github.com/tinspin/rupy/wiki/Fuse
commandlinefan 8y ago
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.
- nextweek2 8y ago
  I've always worked with a transaction per connection principle to saving having to manage transaction id's within a connection. Were you not using transactions or did you have some other approach?
thezviad 8y ago
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. 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. 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.
- tyingq 8y ago
  I see that Postgres has 3rd party projects related to pooling as well, like PgBouncer. Is MySQL's approach particularly worse than other databases?
  - thezviad 8y ago
    I am not as familiar with Postgres as with MySQL, but in this case they would most likely be similarly deficient. Databases like MySQL and Postgres have optimized on disk storage engines and query planners, but the other parts that are needed for true high availability, and stability at scale are definitely lacking in both.
- Thaxll 8y ago
  What other database do that out of the box without third party tool?
  - deleted 8y ago
    [deleted]
wild_preference 8y ago
The other half of this is correctly using the connection pool in the application layer to begin with. 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.
- sb8244 8y ago
  This is so true and easy to overlook until a post mortem. A great exercise is to audit how connections are maintained in your application, before it's too late.
  - macintux 8y ago
    I'm reminded of a systems failure described in, IIRC, the O'Reilly book "Release It!". Java ODBC exception swallowed resulted in eventually running out of connections. Very tough problem to troubleshoot without extensive monitoring.
    - fnord123 8y ago
      Release it! is Prag Press. Good book too!
dlubarov 8y ago
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? It seems wrong IMO to expect clients 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. 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.
- derefr 8y ago
  The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world states" of the DB engine: the one before the transaction began, and the way the world looks after the current statement's modifications—but not those of any other concurrently-executing transaction. Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state! Imagine an RDBMS instance shared between numerous clients built and run by different companies. This RDBMS is locked down in various ways to guarantee each company an SLA on their queries. And, of course, the most important part of that SLA is that a query that is ACKed by the server as having gone through, must never lose data. If you allow clients unlimited concurrency, they can execute enough transactions to fill memory and disk with MVCC world-states, causing potential data loss (since the full disk may have prevented the DB engine from writing down a committed world-state.) Thus, ACID-conformant SQL-speaking databases are always going to limit the number of concurrent transactions, and so the number of concurrent connections; and so—at least for SQL-speaking databases designed for instance-addressable rather than cluster-addressable deployment—the responsibility for deciding how much concurrency a client wants/needs is always going to be pushed to the client, so that an enterprise with a DB cluster can set up its less concurrency-intensive clients to self-limit their pool size. † SQL isn't a wire protocol per se; it's just a syntax and semantics of a formal language, without even a shared encoding. But the standard does specify per-connection and per-transaction state-machines the backend must have, and how these are affected by executing particular statements. So, in this way, SQL is an abstract contract for the semantics that any particular RDBMS's wire protocol must obey. A "meta-protocol", if you will.
exabrial 8y ago
Take time to peruse the rest of the Wiki; It's chock full of information of performance tuning techniques. Absolutely fascinating.
lixtra 8y ago
See also [1]. The top comment there links to the article (2017). [1] https://news.ycombinator.com/item?id=18220906 https://news.ycombinator.com/item?id=18220906

194 points by zyngaro 8y ago

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.

dijit 8y ago
I would like to dissent. If your application is long lived you will get more benefit using the pooling functionality provided by the postgresql client libraries for your platform/language. pgbouncer is an intimidatory, and thus another thing to break- it has it's own constraints (mainly things like prepared statements will block a connection/cannot be reused) and its application should be well understood. For instance; I use it on a local socket on my zabbix master so that it can connect using a TLS encrypted pgsql socket. (something zabbix doesn't support). But for my main applications written in C++, I use connection pooling with nothing but the C++ client libraries. If you're bringing up and tearing down connections very often, then you will get a lot of benefit from pgbouncer, but in my experience these cases are limited.
- sb8244 8y ago
  Coming from ActiveRecord gem, pgbouncer has been great. I'd say the real advantage is transaction level pooling, which will free up connections when they're not actively in a transaction (vs request level isolation). I don't see how this is possible across a large number of isolated servers without a cooperating agent like pgbouncer. I would advocate for pgbouncer at the DB ingress level and your languages connection pooler at the application level.

bullen 8y ago

Or you could use an async. db connection: https://github.com/tinspin/rupy/wiki/Fuse https://github.com/tinspin/rupy/wiki/Fuse

commandlinefan 8y ago

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.

nextweek2 8y ago
I've always worked with a transaction per connection principle to saving having to manage transaction id's within a connection. Were you not using transactions or did you have some other approach?

thezviad 8y ago

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. 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. 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.

tyingq 8y ago
I see that Postgres has 3rd party projects related to pooling as well, like PgBouncer. Is MySQL's approach particularly worse than other databases?
- thezviad 8y ago
  I am not as familiar with Postgres as with MySQL, but in this case they would most likely be similarly deficient. Databases like MySQL and Postgres have optimized on disk storage engines and query planners, but the other parts that are needed for true high availability, and stability at scale are definitely lacking in both.
Thaxll 8y ago
What other database do that out of the box without third party tool?
- deleted 8y ago
  [deleted]

wild_preference 8y ago

The other half of this is correctly using the connection pool in the application layer to begin with. 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.

sb8244 8y ago
This is so true and easy to overlook until a post mortem. A great exercise is to audit how connections are maintained in your application, before it's too late.
- macintux 8y ago
  I'm reminded of a systems failure described in, IIRC, the O'Reilly book "Release It!". Java ODBC exception swallowed resulted in eventually running out of connections. Very tough problem to troubleshoot without extensive monitoring.
  - fnord123 8y ago
    Release it! is Prag Press. Good book too!

dlubarov 8y ago

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? It seems wrong IMO to expect clients 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. 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.

derefr 8y ago
The SQL "protocol"† is a hairy one for a backend to deal with, state-wise. Each concurrent connection to an (ACID conformant) SQL-speaking server implies that that connection can have a transaction in progress. A transaction, in SQL, requires the ability for the client (or the backend) to roll it back at any time before it gets committed—and thus, a transaction in progress requires keeping around at least two "world states" of the DB engine: the one before the transaction began, and the way the world looks after the current statement's modifications—but not those of any other concurrently-executing transaction. Thus, a backend concurrent connection (i.e. transaction) cap of N, translates to O(N) concurrent MVCC "world states" that the DB engine has to keep track of. No matter how efficient the DB engine is at doing that, that's still a lot of state! Imagine an RDBMS instance shared between numerous clients built and run by different companies. This RDBMS is locked down in various ways to guarantee each company an SLA on their queries. And, of course, the most important part of that SLA is that a query that is ACKed by the server as having gone through, must never lose data. If you allow clients unlimited concurrency, they can execute enough transactions to fill memory and disk with MVCC world-states, causing potential data loss (since the full disk may have prevented the DB engine from writing down a committed world-state.) Thus, ACID-conformant SQL-speaking databases are always going to limit the number of concurrent transactions, and so the number of concurrent connections; and so—at least for SQL-speaking databases designed for instance-addressable rather than cluster-addressable deployment—the responsibility for deciding how much concurrency a client wants/needs is always going to be pushed to the client, so that an enterprise with a DB cluster can set up its less concurrency-intensive clients to self-limit their pool size. † SQL isn't a wire protocol per se; it's just a syntax and semantics of a formal language, without even a shared encoding. But the standard does specify per-connection and per-transaction state-machines the backend must have, and how these are affected by executing particular statements. So, in this way, SQL is an abstract contract for the semantics that any particular RDBMS's wire protocol must obey. A "meta-protocol", if you will.

exabrial 8y ago

Take time to peruse the rest of the Wiki; It's chock full of information of performance tuning techniques. Absolutely fascinating.

lixtra 8y ago

See also [1]. The top comment there links to the article (2017). [1] https://news.ycombinator.com/item?id=18220906 https://news.ycombinator.com/item?id=18220906