Sleep Query in MySQL Database

Sleep Query in MySQL Database can be seen when we use the SHOW FULL PROCESSLIST. It shows currently running queries on the database.
The question is why some sleeping queries are created, how long they wait the queries exist and their impact on the Server.

Now let’s see the problem

Sleep Query in MySQL Database

Sleep Query in MySQL Database

 

How MySQL Sleep queries are created?

  • The database connection is created when the session is also set on the MySQL database. As the connection is created that needs to be closed.
  • When we do SHOW FULL PROCESSLIST and it has a column “State” which indicates that what the thread is doing. In our case, it “Sleep”
  • Sleep query is the query that waits for the timeout to terminate.
  • Through PHP code we connect to MySQL but we don’t close the connection explicitly

The impact on the Server

  • It increases the CPU and Memory usage
  • Slowdowns the speed of the server
  • Until the thread dies it is kept in the buffer for 28800 seconds (8 hours) by default.

The solution to the problem:

  • One option can be /etc/my.cnf set following variables interactive_timeout=180, wait_timeout=180 but the problem with this is that it only closes new connection not the previous one
  • Fixing in the code for explicitly closing MySQL connection after MySQL query execution ends down with mysqli_close($con);
  • GRANT USAGE ON *.* TO ‘admin’@’localhost’ WITH MAX_USER_CONNECTIONS 10; to set max user connections.

Conclusion:

  • The best way is to close the connection when it is no longer required. It is the best developer practice.

Leave a Reply

Your email address will not be published. Required fields are marked *