About Database Connection Pool Sizing
github.com
About Database Connection Pool Sizing
1–10 of 32 posts
Re: About Database Connection Pool Sizing
#2Alternatively, 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.
Re: About Database Connection Pool Sizing
#3Or you could use an async. db connection: https://github.com/tinspin/rupy/wiki/Fuse
Re: About Database Connection Pool Sizing
#4I 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.
Re: About Database Connection Pool Sizing
#5This 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.
Re: About Database Connection Pool Sizing
#6This 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…
I see that Postgres has 3rd party projects related to pooling as well, like PgBouncer. Is MySQL's approach particularly worse than other databases?
Re: About Database Connection Pool Sizing
#7This 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…
I see that Postgres has 3rd party projects related to pooling as well, like PgBouncer. Is MySQL's approach particularly worse than other databases?
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.
Re: About Database Connection Pool Sizing
#8This 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…
What other database do that out of the box without third party tool?
Re: About Database Connection Pool Sizing
#9This 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…
> This article would be better if it was called "connection pool sizing for MySQL"
For the curious, HikariCP provides suggested JDBC config for MySQL[1] as well.
[1] https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Confi...
Re: About Database Connection Pool Sizing
#10Alternatively, 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.
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.