6

I am using MySQL database which is hosted on Google Compute Engine and is accessed from another compute engine via PHP.

Whenever i run

SHOW PROCESSLIST

There are about 5-6 sleep connections in the result which is OK with me as it does not downgrade the performance of the system. But whenever there is heavy traffic on the website, the sleep connection increases to 100+ thus causing the entire system to slow down.

I checked online and found that these sleep connections are open mysql connections waiting for command.

But i close my SQL connection after every query. Here is an example of how i am using PHP to access MySQL DB.

function db_connect()
{
    $host = "100.100.100.100"; // dummy
    $username = "abc";
    $pass = "1234";
    $db = "database";
    $connection = new mysqli($host, $username, $pass, $db);
    $connection->set_charset("utf8");
    return $connection;
}
function closeConnection($db)
{
    $db->close();
}
function runQuery($query)
{
    $db = db_connect();
    $sql = $db->query($query);
    closeConnection($db);
    return $sql;
}

I pass the SQL statement to the runQuery function which opens up a new connection and closes it after executing the query. I know opening a new connection for every query on the page will reduce the performance of the page but this way i am sure that i am not leaving any connection open.

Still, when the traffic is high, i am getting sleep connections.

I have attached a sample of SHOW PROCESSLIST below.

Sample SHOW PROCESSLIST result when traffic is high

Please help me resolve this issue.

Vitul Goyal
  • 611
  • 7
  • 19
  • You can reduce `wait_timeout` variable value if you have access to config files of your MySQL server. – Madhur Bhaiya Nov 23 '18 at 12:07
  • Having burnt with something like this before; Too many sleep connections is generally due to few query(s) being not efficient. Once such an inefficient query gets fired multiple times in concurrent manner, it is going to affect everything else. In case of heavy traffic, every other query starts hanging because previous queries are not yet finished . Enable `slow_query_log` and check for the bad queries. – Madhur Bhaiya Nov 23 '18 at 12:15
  • Does this answer your question? [MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?](https://stackoverflow.com/questions/2407732/mysql-proccesslist-filled-with-sleep-entries-leading-to-too-many-connections) – Dr. Gianluigi Zane Zanettini Sep 02 '22 at 14:06

0 Answers0