MySQL CPU increase when I have Sleeping connection that stay open

Question

I have a MySQL 5.6.27-0ubuntu0.14.04.1 that run on a Google Compute instance with 4 CPU.

I noticed that if I have a connection that Sleep for a long time, then the CPU of the server will increase in a linear way. I don't understand why? If I kill the Sleep connection then CPU just restore to a correct usage.

So to summary I have the following: I notice the CPU of my instance is increasing:

Then I check the processlist on my server

mysql> show processlist
-> ;
+-------+--------+-------------------+----------------+---------+------+-------+------------------+
| Id    | User   | Host              | db             | Command | Time | State | Info             |
+-------+--------+-------------------+----------------+---------+------+-------+------------------+
| 85949 | nafora | paper-eee-2:58461 | state_recorder | Sleep   | 1300 |       | NULL             |
| 85956 | nafora | paper-eee-2:58568 | state_recorder | Sleep   |   64 |       | NULL             |
| 85959 | root   | localhost         | NULL           | Query   |    0 | init  | show processlist |
+-------+--------+-------------------+----------------+---------+------+-------+------------------+

You can see I have just 2 connection that Sleep and one is here from 1300 seconds (because I have a process that is stuck with the connection open)

So I kill the connection 85949, and the CPU just fall down.

Can someone explain me why a single connection that is sleeping can impact my database like this.

Thanks.

Gyasi

Strange issue. Could you add a bit more info about the system? MySQL version? InnoDB or MyISAM maybe? In case of InnoDB output of ` SHOW ENGINE INNODB STATUS`? Enabling monitoring could be also useful: dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html . Some background on the connections? Did they read/write a lot before becoming idle. Are they autocommited? etc. – Hols

You should try to find out what that process is doing. Maybe this article helps: percona.com/blog/2007/02/08/… – Janessajanet

I've just come across the same symptom with a cloudSQL managed instance. CPU will sit at 50% for an extended period, processlist shows all processes in "sleep". However one process had a very high time. Killed that process and CPU dropped immediately. This question has given me a good start in trying to understand this issue. – Celt

Answer 1

Some non-closed connections or long running slow queries might cause this behavior. You could limit the non-closed connections by configuring the global variable wait_timeout as reasonable value and also set another related variable interactive_timeout as high as per best practice.

Stateful applications that use a connection pool (Java, .NET, etc.) will need to adjust wait_timeout to match their connection pool settings. The default 8 hours (wait_timeout = 28800) works well with properly configured connection pools.

Configure the wait_timeout to be slightly longer than the application connection pool’s expected connection lifetime. This is a good safety check. Also profile the queries accordingly to observe the performance of MySQL instance can help you to avoid I/O bottlenecks.

Triphthong