Category Archives: Connections

Preventing Max Connections Errors with InnoDB

Stop increasing max_connections every time there’s a 1040: Too Many Connections error. Every additional connection is another share to further divide the available memory.

Instead, while it would be best to manage the workload, it is also reasonable to properly utilize the available hardware with good server configuration.

There are three relevant server configuration options for managing connection counts as they relate to satisfying web requests.

  1. max_connections – the queue depth
  2. innodb_thread_concurrency – the count of queue consumers
  3. innodb_concurrency_tickets – the amount of work a consumer can do on a query before switching to the next query request

Correctly configuring these three variables, and controlling your workload of course, can prevent 1040 Too many connections errors, assuming, of course, that your server is not beyond its configurable capacity.

Taken together, these variables have a number of interesting behaviors.

The first typical issue, resource starvation due to large reporting queries, can be mitigated with proper innodb_concurrency_tickets when the hardware is too weak to increase the query consumer count (innodb_thread_concurrency). When innodb_concurrency_tickets equals 500, which is the default, then five hundred InnoDB rows may be touched before the InnoDB thread moves onto the next query in the queue. A ticket is consumed on any row touch, without regard to whether or not it’s a row-read, a uniqueness check, or a foreign-key lookup. Accordingly, setting innodb_concurrency_tickets to 1 means the InnoDB consumer threads will spend a large portion of their cycles context switching between queries, whereas setting innodb_concurrency_tickets to max will starve other potentially much cheaper queries of resources. Personally, the default value of innodb_concurrency_tickets works just fine for my workload and I’ve never moved it from the default value of 500. A few cpu resources will be spent context switching, but the fairness will be worthwhile for most web apps.

The second typical issue, stampedes of cheap queries causing 1040 errors, can be mitigated with an appropriately sized max_connection value when the hardware is too weak to increase the query consumer count. It’s not really an ideal solution, though. Every additional max_connection value means there’s a risk of another query concurrently doing joins, temporary tables, and the piles of complicated, memory-consuming behavior that leads to swap, and even potentially the OOM-Killer. Managing workload with good caching, potentially even proactive caching to manage the Thundering Herd problem, and throttled asynchronous application behavior is a positive solution that scales well.

The combination of these issues, as well as modern, multi-core hardware with powerful IO backends, pulls in the third system configuration option, innodb_thread_concurrency. The count of ‘query consumers,’ innodb_thread_concurrency will allow InnoDB to work on separate queries at the same time. By default, innodb_thread_concurrency is set to 8. Modern OS’s are just fine at scheduling, so I like to keep this value sufficiently high that I have at least twice as many consumers available as the number of possible concurrent, expensive queries. Having a large count of query consumers available keeps the cheap queries processed and back out of the queue, allowing the connection count to stay low.

So, what’s wrong with just opening up the max_connection and/or the innodb_thread_concurrency values to max? It doesn’t matter how powerful your hardware is, too many queries all simultaneously consuming an equal share of resources will prevent any of them from completing. Queries not completing and closing their connections will, of course, lead to a backlog of the cheap queries, and potentially a whole new batch of the cheap queries if the web requests retry the unreturned cheap queries. Eventually, the server goes unresponsive, customers get angry, the business crashes, and you’re looking for a new job.

Hopefully it’s clear: the right number of query consumers (innodb_thread_concurrency), the right amount of work per focus (innodb_concurrency_tickets), and the right amount of time for the consumers to close incoming query requests (max_connections), and you’ll be able to avoid 1040 Too many connections exceptions, business closings, and job hunts.