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 5.6 mysql_install_db script problem We're always testing the latest versions of MySQL with most of the environments to make sure that we can find the critical issues before it goes to pr...
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...
Corrupted InnoDB dictionary Last week one of my collagues came to me with the following problem. He was not able to create an innodb table. MySQL returned ERROR 1005: Can't creat...