MySQL database system has a lot of things and its very powerful. If you bump into a load issue or misbehaving database server this article could help you.

We usually run a command ‘show processist’ to see the current queries that are running.

Sample results are:

mysql> show processlist; 
+---------+-------------+-----------------+----------+---------+-------+-----------------------------------+------------------+-----------+---------------+-----------+
|   Id    |      User   | Host            |  db      | Command | Time  |                State              | Info             | Rows_sent | Rows_examined | Rows_read |
+---------+-------------+-----------------+----------+---------+-------+-----------------------------------+------------------+-----------+---------------+-----------+
| 2266183 | site_user   | 127.0.0.1:38839 | sitedb   | Sleep   | 17483 |           						   | NULL             |       464 |             6 |         6 |
| 2266189 | site_user   | 127.0.0.1:38908 | sitedb   | Sleep   | 15249 |                                   | NULL             |       325 |             2 |         2 |
| 2266194 | site_user   | 127.0.0.1:38925 | sitedb   | Query   | 16462 | Waiting for table level lock      | NULL             |       162 |           162 |       162 |
| 2266196 | site_user   | 127.0.0.1:38927 | sitedb   | Query   | 18496 | Waiting for table level lock      | NULL             |       352 |           352 |       162 |
| 2266198 | site_user   | 127.0.0.1:38929 | sitedb   | Query   | 19061 | Waiting for table level lock      | NULL             |       232 |           232 |       162 |
| 2266199 | site_user   | 127.0.0.1:48550 | NULL     | Query   |     0 | NULL                              | show processlist |         0 |             0 |         1 |
+---------+-------------+-----------------+----------+---------+-------+-----------------------------------+------------------+-----------+---------------+-----------+
4 rows in set (0.03 sec)

Whenever you check the processes on database we should immediately look at the column of “Time”. The time in seconds that the thread has been in its current state. Imagine it has a few more processes that are waiting on queue than what we have on the screenshot the server load will pile up that could kill and crash the system.

So before it happens on the first email or sms alert you have received (assuming you have set some monitoring tools on the server) you need to check the processes immediately. If you see the same a above the way you can kill it is by doing these commands:

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

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

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

If there are so many Sleep query on the processlist that you can also clear them up by running a 1(one)-liner command like:

for i in $(mysql -uroot -p$(cat /etc/.mysqlpw) -e 'show processlist' | grep 'Sleep' | awk '{print $1}'); do  mysqladmin -uroot -p$(cat /etc/.mysqlpw) kill $i; done

Where in the mysql root password is at /etc/.mysqlpw file.