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

Symptoms

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

Lesson

  1. When troubleshooting always check slaves too.
  2. Close unused connections especially for masters.
  3. Keep your query optimized and keep track on data distribution changes because this can cause former good queries to behave worse.

 

You might like these too

MySQL Benchmark – updates by primary vs seco... (Note: when I'm talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.) I've he...
How to store boolean in MySQL I always hear newer and newer exotic way to store different types of data in MySQL. People are trying to solve the problem of storing complex and not ...
Full text search in MySQL Full text is a critical point when it comes to mysql. It used to have that feature in MyISAM but that's not really maintained anymore nor it is advise...

About charlesnagy

I'm out of many things mostly automation expert, database specialist, system engineer and software architect with passion towards data, searching it, analyze it, learn from it. I learn by experimenting and this blog is a result of these experiments and some other random thought I have time to time.
Bookmark the permalink.