4 ms·

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 v

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

evanelias 8y ago
In my experience, many high-scale MySQL configurations use a global max_connections in the single-digit thousands (4000-5000 is common at social networks doing high-volume OLTP), and an aggressive wait_timeout (~10 seconds) to kill idle conns from misbehaving/stalled clients. I've never seen a max_connections configured anywhere near 100k. That would be an extreme edge-case, and is generally unwise unless there's some very specific unusual reason that you need that. My assumption would be something is very wrong at the application architecture level if this is needed. What "scan of all connected sessions" are you referring to? I've never heard anything about this, and never seen general performance issues purely related to high idle connection count, but I've also never configured max_connections to such an insanely high value. As for other databases, I don't see how Postgres would be able to handle 100k connections (without a proxy) either, given that means 100k OS processes in Postgres. > 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 Fire-and-forget (connection per web request) is somewhat common in MySQL, especially with languages like PHP. Establishing a connection is relatively low overhead on the server side in MySQL, since it just involves spawning a thread. Usually the bigger issue is network latency, especially if cross-region SSL connections are involved. In this case, a client-side connection pool or proxy certainly makes sense, and that is true regardless of what DB technology is used. It sounds like you have a lot of application servers maintaining connection pools to a single database. In this case it is generally beneficial to tune the connection pools to aggressively prune idle connections, or use proxies that multiplex connections (ProxySQL is great), and then set max_connections to a more sane value as a circuit-breaker.

evanelias 8y ago

In my experience, many high-scale MySQL configurations use a global max_connections in the single-digit thousands (4000-5000 is common at social networks doing high-volume OLTP), and an aggressive wait_timeout (~10 seconds) to kill idle conns from misbehaving/stalled clients. I've never seen a max_connections configured anywhere near 100k. That would be an extreme edge-case, and is generally unwise unless there's some very specific unusual reason that you need that. My assumption would be something is very wrong at the application architecture level if this is needed. What "scan of all connected sessions" are you referring to? I've never heard anything about this, and never seen general performance issues purely related to high idle connection count, but I've also never configured max_connections to such an insanely high value. As for other databases, I don't see how Postgres would be able to handle 100k connections (without a proxy) either, given that means 100k OS processes in Postgres. > 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 Fire-and-forget (connection per web request) is somewhat common in MySQL, especially with languages like PHP. Establishing a connection is relatively low overhead on the server side in MySQL, since it just involves spawning a thread. Usually the bigger issue is network latency, especially if cross-region SSL connections are involved. In this case, a client-side connection pool or proxy certainly makes sense, and that is true regardless of what DB technology is used. It sounds like you have a lot of application servers maintaining connection pools to a single database. In this case it is generally beneficial to tune the connection pools to aggressively prune idle connections, or use proxies that multiplex connections (ProxySQL is great), and then set max_connections to a more sane value as a circuit-breaker.