Tuesday, March 23, 2010

Checking running queries on MySQL server

You are administering a MySQL server and suddenly your users are not so very happy with what is going on. Basically, they are getting errors like "too many connections" or the content they are accessing is basically crawling at all. In this situations, one can monitor "what is going on" on a loaded MySQL server to find out what is causing hiccups/bogs or what queries are eating CPU cycles available to the a system.

Upon logging in to the mysql database's interactive shell, we can use the "show processlist;" command. This will list the running querries currently to the database of the logged in user.

One one of the interactive shell, we somehow run for example.
mysql> select sleep(100);

mysql> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 23 | root | localhost | NULL | Query | 2 | executing | select sleep(100)|
| 24 | root | localhost | NULL | Query | 0 | NULL | show processlist |
2 rows in set (0.00 sec)

We can somehow kill hte process if we elect to, using the "kill 23" (23 being the process id).

No comments:

Post a Comment