4 ms·

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 configuri

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]
virtualwhys 8y ago
> 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-Configuration https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Confi...
cwyers 8y ago
The article showcases Postgres benchmarks and quotes the Postgres documentation, and references material put out by Oracle for their flagship database product. MySQL is never mentioned once. Your opening sentence is deeply misleading as to the contents of this article.
evanelias 8y ago
What are you referring to in your 3rd paragraph? Idle connections in MySQL pose few issues. They just take up some memory for session-level buffers, and that amount of memory depends on what you've configured those buffer sizes to. They also take up a slot in terms of whatever you've configured max_connections to, but that's fully configurable as well. MySQL has long had some abilities to limit resources on a per-user basis, see https://dev.mysql.com/doc/refman/5.6/en/user-resources.html https://dev.mysql.com/doc/refman/5.6/en/user-resources.html for example. This includes the ability to set max simultaneous connections per user. MySQL's default connection model dynamically uses a thread per connection, which actually tends to handle high connection counts out-of-the-box better than process-per-conn approaches like Postgres's. In my experience, using a proxy like pgbouncer is much more common in Postgres than using a proxy is in MySQL. I'm not bashing Postgres overall, it's a great DB. But in terms of connection-handling your criticism of MySQL here feels substantially off-the-mark. (Source: have been using MySQL for 15 years, including at largest scale in the world)
- thezviad 8y ago
  Start of every new transaction does a scan of all connected sessions (including idle) ones. I am pretty sure this is true in 5.6 too, and most likely in newer versions too. So if the queries that you are issuing are large or the total connection count is <10k it is not going to be problem or even noticeable. But if you have lots of small and fast queries and have idle connections >100k you will definitely have significant perf issues because of the idle connections. 100k is a big number for sure, but it is definitely possible to hit those limits if the application layer is done in languages like node, ruby, python, when you need to run many application processes since each app process can't properly utilize more than single CPU core. Thus you end up having a lot of separate processes each with its own connection pool to the backing database. As for per-user limits, those resource limits aren't that useful for stability. Setting max simultaneous connections is not as useful, because you don't know how many of those are idle or active. You want limits on active sessions that are actually doing work, not how many idle sessions exist. Unless of course you plan on not doing any session reuse, and always make a new session for each transaction, which will lead to huge other set of performance issues because establishing new sessions is pretty expensive. As for the other "per hour" limits, they are also not useful to provide protections against burst traffic which is a common way a MySQL instance can enter into a bad feedback loop and slow down to crawl. (Example: there is a burst traffic from one use case, creating lots of new active transactions at the same time, because of that, MySQL perf slows down, so now you have even more active transactions because everything is slower, which leads to even more slow down, so even after the burst traffic is over, database is in a bad state since now you continuously have too many active transactions and it is unable to recover on its own to handle the same steady state traffic as it was able to handle before the burst).

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]

virtualwhys 8y ago

> 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-Configuration https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Confi...

cwyers 8y ago

The article showcases Postgres benchmarks and quotes the Postgres documentation, and references material put out by Oracle for their flagship database product. MySQL is never mentioned once. Your opening sentence is deeply misleading as to the contents of this article.

evanelias 8y ago

What are you referring to in your 3rd paragraph? Idle connections in MySQL pose few issues. They just take up some memory for session-level buffers, and that amount of memory depends on what you've configured those buffer sizes to. They also take up a slot in terms of whatever you've configured max_connections to, but that's fully configurable as well. MySQL has long had some abilities to limit resources on a per-user basis, see https://dev.mysql.com/doc/refman/5.6/en/user-resources.html https://dev.mysql.com/doc/refman/5.6/en/user-resources.html for example. This includes the ability to set max simultaneous connections per user. MySQL's default connection model dynamically uses a thread per connection, which actually tends to handle high connection counts out-of-the-box better than process-per-conn approaches like Postgres's. In my experience, using a proxy like pgbouncer is much more common in Postgres than using a proxy is in MySQL. I'm not bashing Postgres overall, it's a great DB. But in terms of connection-handling your criticism of MySQL here feels substantially off-the-mark. (Source: have been using MySQL for 15 years, including at largest scale in the world)

thezviad 8y ago
Start of every new transaction does a scan of all connected sessions (including idle) ones. I am pretty sure this is true in 5.6 too, and most likely in newer versions too. So if the queries that you are issuing are large or the total connection count is <10k it is not going to be problem or even noticeable. But if you have lots of small and fast queries and have idle connections >100k you will definitely have significant perf issues because of the idle connections. 100k is a big number for sure, but it is definitely possible to hit those limits if the application layer is done in languages like node, ruby, python, when you need to run many application processes since each app process can't properly utilize more than single CPU core. Thus you end up having a lot of separate processes each with its own connection pool to the backing database. As for per-user limits, those resource limits aren't that useful for stability. Setting max simultaneous connections is not as useful, because you don't know how many of those are idle or active. You want limits on active sessions that are actually doing work, not how many idle sessions exist. Unless of course you plan on not doing any session reuse, and always make a new session for each transaction, which will lead to huge other set of performance issues because establishing new sessions is pretty expensive. As for the other "per hour" limits, they are also not useful to provide protections against burst traffic which is a common way a MySQL instance can enter into a bad feedback loop and slow down to crawl. (Example: there is a burst traffic from one use case, creating lots of new active transactions at the same time, because of that, MySQL perf slows down, so now you have even more active transactions because everything is slower, which leads to even more slow down, so even after the burst traffic is over, database is in a bad state since now you continuously have too many active transactions and it is unable to recover on its own to handle the same steady state traffic as it was able to handle before the burst).