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

Difference between DISTINCT and GROUP BY Today we had an interesting situation where the same query was executed significantly slower when it was written with GROUP BY instead of DISTINCT and...
How to troubleshoot MySQL replication issues? In MySQL a big portion of the problems you're facing is different replication delays so there's no surprise this is one of the most common interview q...
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.