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.