Increases MySQL processor when I have a sleeping connection that remains open

Question

I have MySQL 5.6.27-0ubuntu0.14.04.1 that runs on an instance of Google Compute with 4 processors.

I noticed that if I have a connection that will sleep for a long time, then the server processor will increase linearly. I do not understand why? If I kill the dream, then the CPU will simply return to proper use.

So, to summarize, I have the following: I notice that the processor of my instance is increasing:

Then I check the list of processes 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 that I only have 2 connections that Sleep and one here, from 1300 seconds (because I have a process that got stuck when opening the connection)

So, I kill connection 85949 and the processor just crashes.

Can someone explain to me why one sleeping connection can affect my database as follows.

Thanks.

Answer 1

Some unblocked connections or long, slow requests may cause this behavior. You can limit unblocked connections by setting the wait_timeout global variable to a reasonable value, and set another associated interactive_timeout variable with maximum efficiency.

Installed state applications using the connection pool (Java, .NET, etc.) need to configure wait_timeout to match their connection pool settings. By default, 8 hours (wait_timeout = 28800) works well with properly configured connection pools.

Configure wait_timeout to be slightly longer than the expected connection lifetime. This is a good security check. Also profile queries to monitor the performance of the MySQL instance, you can avoid I / O bottlenecks .