CODEWITHSUNDEEP
phpmysqlsqldatabasecodeigniter
Nyxynyx
Nyxynyx

Reputation: 63697

Slow MySQL Performance & Sleep queries

Recently I've been noticing very slow loading of AJAX data on my website in the early hours of the day. Using the Web Developer feature in Chrome browser, the usual speeds of 300-500ms Wait and 100-300ms Receive can increase up to 5 seconds! The site uses jQuery, Codeigniter/PHP and MySQL.

I checked with the support desk of my VPS and they mentioned about having too many sleep queries. 

There are many sleep queries for the database 'databasename'.

| 4341 | mysqluser | 204.197.252.106:48669 | databasename | Sleep | 19 | | |
| 4373 | mysqluser | 204.197.252.106:59966 | databasename | Sleep | 78 | | |
| 4387 | mysqluser | 204.197.252.106:34807 | databasename | Sleep | 46 | | |
| 4388 | mysqluser | 204.197.252.106:35099 | databasename | Sleep | 6 | | |
| 4390 | mysqluser | 204.197.252.106:35452 | databasename | Sleep | 26 | | |
| 4391 | mysqluser | 204.197.252.106:35589 | databasename | Sleep | 7 | | |
| 4392 | mysqluser | 204.197.252.106:35680 | databasename | Sleep | 19 | | |
| 4393 | mysqluser | 204.197.252.106:36264 | databasename | Sleep | 12 | | |
| 4394 | mysqluser | 204.197.252.106:36327 | databasename | Sleep | 11 | | |

Is this the cause of the slowdowns in the morning? How can I find out what's causing the slowdown and get rid of it?

Upvotes: 2

Views: 14075

Answers (2)

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16086

If you want to kill sleeping connection you can do like this: login to Mysql as admin:

 mysql -uroot -ppassword;

And than run command:

mysql> show processlist;

You will get something like below :

+----+-------------+--------------------+----------+---------+------+-------+------------------+
| Id | User        | Host               | db       | Command | Time | State | Info             |
+----+-------------+--------------------+----------+---------+------+-------+------------------+
| 49 | application | 192.168.44.1:51718 | XXXXXXXX | Sleep   |  183 |       | NULL             ||
| 55 | application | 192.168.44.1:51769 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 56 | application | 192.168.44.1:51770 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 57 | application | 192.168.44.1:51771 | XXXXXXXX | Sleep   |  148 |       | NULL             |
| 58 | application | 192.168.44.1:51968 | XXXXXXXX | Sleep   |   11 |       | NULL             |
| 59 | root        | localhost          | NULL     | Query   |    0 | NULL  | show processlist |
+----+-------------+--------------------+----------+---------+------+-------+------------------+

You will see complete details of different connections. Now you can kill the sleeping connection as below:

mysql> kill 52;
Query OK, 0 rows affected (0.00 sec)

Upvotes: 0

Sahil Muthoo
Sahil Muthoo

Reputation: 12506

  1. These sleep queries are just open connections. I wouldn't be bothered by them unless they are in the hundreds. Most probably they're forming the connection pool your framework/ORM is using.

  2. To diagnose your problem with slow queries, you'll need to obtain the slow query log. In case slow query logging is switched off, immediately turn it on.

  3. Once you have a suitable log file, you'll want to analyze the slow running queries with explain. Explain will allow to see how Mysql's query planner and analyzer is executing your queries.

  4. Also paste the output of explain on the slowest running queries here along with the table schema, so that we can better diagnose your problem.

Upvotes: 9

Related Questions