Slow MySQL Performance & Sleep queries

Question

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?

Answer 1

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.