Showing entries 1 to 4
Displaying posts with tag: connections (reset)
Track and Optimize Server Connection Methods

The MySQL server supports a variety of client connection methods. To summarize: you have TCP/IP (v4 and v6) on all OSes (with or without TLS/SSL encryption), Unix Domain Sockets on Unix/Linux, and Named Pipes and/or Shared Memory on Windows.

Each of these connection methods has its own set of pros and cons: speed, security, portability, and ease-of-use.…

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, …

[Read more]
Sleeping connections

Why is it so important to close connections to databases if there’s no explicit need to keep them open (which usually the case)?


  • Icinga reports high usage of allowed connections (>90%) on master MySQL server.
  • No running queries or any specific in processlist. All the connections are sleeping from the same servers.
  • The number of connections was 20 times more than the usual

Root cause

There was tremendous amount of queries doing “copying to tmp table” on the slaves. The connections were opened to both master and slaves and waiting for the slaves to answer the query the connections to master was kept opened. So the sleeping connections was piled up to the limit of max connections.


  1. When troubleshooting always check slaves too.
  2. Close unused connections especially for masters.
  3. Keep your query optimized and …
[Read more]
Multiple Connections Bringing MySQL to a Halt

Hi all,

I was asked this question in an e-mail — feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here’s the question:

Basically when we open more than one connection- we’re opening 5, and we do processing in mysql (innodb tables) the server cpu and memory max out and the processing grinds to a halt. One connection at a time is able to run well- even with lots of data. But once we try to process data under 5 concurrent connections, mysql gets bogged down to the point where it’s barely usable.

I’ve researched this and found a number of opinions, including whether this is just poor performance due to where the we are in the 5.1 life cycle (i.e. energies have gone into fixing big bugs not …

[Read more]
Showing entries 1 to 4