MySQL using show processlist to view the SQL statements being executed

Today to work routine to check the health of the server, the Discovery Server Special card, is mysqld this process consumes CPU to 99% caused.

More curious is that the program in the use of MySQL caused the CPU so high, through the show processlist command to view the currently executing SQL statement, so as to locate the corresponding program, found that there is a dead loop in the code of the query caused the CPU consumption 99%, The reason to find the problem is solved.

Here is a simple record of the use of processlist:

The output of the Processlist command shows which threads are running and can help identify problematic query statements, using this command in two ways.

1. Enter Mysqladmin processlist into the Mysql/bin directory;

2. Start mysql and enter show Processlist;

If you have SUPER privileges, you can see all the threads, otherwise you will only see the thread that you initiated (this is the thread that the current MySQL account is running on).

The data is obtained in the form of the following (only three interception):

Mysql> show Processlist;

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+-------- --

| Id | User | Host | db | Command | time| State | Info

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+-------- --

|207|root |192.168.0.20:51718 |mytest | Sleep |          5 | | Null

|208|root |192.168.0.20:51719 |mytest | Sleep |          5 | | Null

|220|root |192.168.0.20:51731 |mytest | Query | 84 | Locked |

Select Bookname,culture,value,type from book where id=001

First of all, the meaning and purpose of each column, the first column, ID, needless to say, an identity, you want to kill a statement when it is useful. The user column shows the single-user, if not root, this command displays only the SQL statements within the scope of your permission. The host column that shows which IP port this statement was issued from. Oh, can be used to track the problem of the user statement. The DB column that shows which database the process is currently connected to. Command column, which displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect). Time column, which is the duration of this state, in seconds. The State column, which shows the status of the SQL statement using the current connection, the very important column, followed by a description of all States, note that State is only one of the states in the statement execution, an SQL statement that has been queried as an example, may need to go through copying to TMP table, Sort ing result, sending data and other states can be completed, the info column, display this SQL statement, because the length is limited, so the long SQL statement is not complete, but a judgment question statement important basis.

When MySQL is busy running show processlist, it will find that there are many lines of output, each of which corresponds to a MySQL connection. How do I diagnose which process is initiating a connection? What is it doing now?

First, MySQL needs to be connected via a TCP socket instead of a UNIX socket, so that there is a source port number in the output of show processlist. As follows

Mysql> show Processlist;
+--–+--–+ ————— –+--+ ——— +--+ ——-+ —————— +
| Id | User | Host | db | Command | Time | State | Info |
+--–+--–+ ————— –+--+ ——— +--+ ——-+ —————— +
| 277801 | Mydbuser | localhost:35558 | MyDB | Sleep | 1 | | NULL |
| 277804 | Mydbuser | localhost:35561 | MyDB | Sleep | 1 | | NULL |
| 277805 | Mydbuser | localhost:35562 | MyDB | Sleep | 0 | | NULL |
+--–+--–+ ————— –+--+ ——— +--+ ——-+ —————— +

In the host column there is the source IP and port number, and then we see from the connection machine who opened the port number,

[Email protected] ~]# NETSTAT-NTP | grep 35558
... 124.115.0.68:35558 established 18783/HTTPD

It is known that process 18783 originated the MySQL connection source port is 35558, then you can use Strace to observe the process. If it is an Apache PHP script, you can also use the Proctitle module (http://pecl.php.net/package/proctitle/) to set the state information of the script.

Lsof can also display the process number according to the port number, please refer to the manual for details.

MySQL using show processlist to view the SQL statements being executed