MySQL Analytics data Run status "SHOW processlist"

This blog post, will be just a simple record, our database operation and use, add index not up, analysis of the process, is actually relatively simple, is to see whether the connection process is still in the Operation table. If there is, stop it (without impacting the business scenario).

The main characters today are:

SHOW [Full] Processlist

The official documentation is described below:

SHOW processlist shows you which threads is running. You can also get this information from Theinformation_schema processlist table or the mysqladmin processlist command. If you havethe PROCESS privilege, you can see all threads. Otherwise, you can see only your own threads (which is,threads associated with the MySQL account, which is using). If you don't use the full keyword, onlythe First is characters of each statement is shown in the Info field.

This means that the above instructions are used to see which threads are running, and you can also get this information from information_schema processlist This table, or through mysqladmin processlist instructions. If you have process permissions, you can view all the threads. Otherwise, you can only view the threads of your current account. If you don't use the full keyword, you can only see the first 100 characters in the Info field in each record .

Specific operation:

Mysql> show Processlist; ERROR 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID:    46661Current database:advsql+-------+-----------+-------------------+--------+---------+----- --+-------+------------------+| Id    | User      | Host              | db     | Command | Time  | State | Info             |+-------+-----------+-------------------+--------+---------+-------+-------+------------------+| 46586 | Tkcssuser | 10.90.13.61:55838 | Tkcss  | Sleep   |    |       | NULL             | | 46660 | tkcssuser | 10.90.13.8:52008  | tkcss  | Sleep   | 11041 |       | NULL             | | 46661 | root      | localhost         | advsql | Query   |     0 | Init  | show processlist |+-------+-----------+-------------------+--------+---------+-------+-------+------- -----------+3 rows in Set (0.01 sec)

By querying Information_schema's processlist table:

 mysql> SELECT * from information_schema.processlist;+-------+-----------+-------------------+--------+---- -----+-------+-----------+----------------------------------------------+| ID | USER | HOST | DB | COMMAND | Time | State | INFO |+-------+-----------+-------------------+--------+---------+-------+-------- ---+----------------------------------------------+| 46661 | Root | localhost | Advsql |     Query | 0 | Executing | SELECT * FROM Information_schema.processlist | | 46586 | Tkcssuser | 10.90.13.61:55838 | Tkcss |   Sleep |           279 | | NULL | | 46660 | Tkcssuser | 10.90.13.8:52008 | Tkcss | Sleep |           11578 | | NULL |+-------+-----------+-------------------+--------+---------+-------+-------- ---+----------------------------------------------+3 rows in Set (0.00 sec) 

or mysqladmin processlist directive:

[Email protected] ~]# mysqladmin processlist-u root-p Enter Password: +-------+-----------+-------------------+------ --+---------+-------+-------+------------------+| Id    | User      | Host              | db     | Command | Time  | State | Info             |+-------+-----------+-------------------+--------+---------+-------+-------+------------------+| 46586 | Tkcssuser | 10.90.13.61:55838 | Tkcss  | Sleep   | 138 | | |                  | 46660 | tkcssuser | 10.90.13.8:52008  | tkcss  | Sleep   | 12037 | | |                  | 46661 | root      | localhost         | advsql | Sleep   | 459   | | |                  | 46662 | root      | localhost         |        | Query   | 0     | init  | show processlist |+-------+-----------+-------------------+--------+---------+--- ----+-------+------------------+

Regarding the value of this order, the official introduction is as follows:

The SHOW processlist statement is very useful if you get the 'too many connections' error Messageand want to find Going on. MySQL reserves one extra connection to being used by accounts thathave the SUPER privilege, to ensure that administrators sho Uld always is able to connect and check thesystem (assuming, that is not giving this privilege to all your users).

Threads can killed with the KILL statement.

A running thread that can be killed by a kill command. This information is very important.

Here, you can see the output of the show processlist directive, there are several fields, explained below, only to understand the meaning of these fields, it is valuable for our actual project problem analysis:

• Id:

  The connection identifier. The same type of value displayed in the ID column of the
  Information_schema. Processlist table, the processlist_id column of the performance
  Schema threads table, and returned by the connection_id () function.

• User:

  The MySQL user who issued the statement. If This is the system user, it refers to a nonclient thread
  Spawned by the server to handle tasks internally. This could is the I/O or SQL thread used on replication
  Slaves or a delayed-row handler. Unauthenticated user refers to a thread this has become
  Associated with a client connection but for which authentication of the client user have not yet been done.
  Event_scheduler refers to the thread that monitors scheduled events. For system user, there is no
  Host specified in the host column.

• Host:

  The host name of the client issuing the statement (Except for system user where there is no host).
  SHOW Processlist reports the host name for TCP/IP connections in Host_name:client_port
  Format to make it easier to determine which client are doing what.

• Db:

The default database, if one is selected, otherwise NULL.

• Command:

The type of command the thread is executing. For example, in the example above, Sleep, or query

• Time:

  The time in seconds, the thread has a been in it current state. For a Slave SQL thread, the value is
  The number of seconds between the timestamp of the last replicated event and the real time of the slave
  Machine.

• State:

  Most states correspond to very quick operations. If a thread stays in a given the state for many seconds,
  There might be a problem this needs to be investigated.

An action, event, or state, which indicates what's the thread is doing.

• Info:

  The statement the thread is executing, or NULL if it isn't executing any statement. The statement might
  Be the one sent to the server, or a innermost statement if the statement executes other statements. For
  example, if a call statement executes a stored procedure which is executing a SELECT statement, the
  Info value shows the SELECT statement.

In the interpretation of the above information, the Id,host,time,state and Info fields are particularly important for us to analyze the problem. In these few, the first thing we can see is the Time,state and info fields. If we want to see a more detailed info field, please use the show full processlist instruction . Note The previous command explains the content.

One problem on our line is that the add index does not add up, we will apply to stop or add up, through the show processlist instructions, found that there are many threads command in the query state. The longest time field displays up to 230172seconds and translates into hours, all 63 hours.

To resolve the problem, call the KILL command immediately, as mentioned earlier.

KILL [CONNECTION | QUERY] processlist_id

Official Introduction:

Each connection to mysqld runs in a separate thread. You can kill a thread with the KILLPROCESSLIST_ID statement.

The processlist_id in the parameter, derived from the ID field in the Show Processlist results list.

The KILL command supports two optional parameters, connection, and query.

CONNECTION QUERY Terminates the statement the connection is currently executing, but leaves the connectionitself intact.

Very simple,connection option, kill the time, the connection will also be broken, and the query option, the kill process just kill the command, the connection remains. When the kill instruction does not specify the connection or query option, the default is connection.

MySQL Analytics data Run status "SHOW processlist"