MySQL Analytics data running state weapon "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, can see only your own threads (that  is  ,threads associated with the MySQL account that is  using ). If you do  100  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; ERROR2006(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 | the| | NULL | |46660| Tkcssuser |10.90.13.8:52008| Tkcss | Sleep |11041| | NULL | |46661| Root | localhost | Advsql | Query |0| init | Show Processlist |+-------+-----------+-------------------+--------+---------+-------+-------+------------------ +3Rowsinch Set(0.01sec)

By querying Information_schema's processlist table:

Mysql>Select* frominformation_schema.processlist;+-------+-----------+-------------------+--------+---------+-------+-----------+------------------------------- ---------------+| ID | USER | HOST | DB | COMMAND | Time | State | INFO |+-------+-----------+-------------------+--------+---------+-------+-------- ---+----------------------------------------------+|46661| Root | localhost | Advsql | Query |0| Executing |Select* fromInformation_schema.processlist | |46586| Tkcssuser |10.90.13.61:55838| Tkcss | Sleep |279| | NULL | |46660| Tkcssuser |10.90.13.8:52008| Tkcss | Sleep |11578| | NULL |+-------+-----------+-------------------+--------+---------+-------+-------- ---+----------------------------------------------+3Rowsinch Set(0.00Sec

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:

 is if Get the "too many connections   out  is   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 beenin 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 display reaches 230172seconds, converted into hours, all 63 hours, damn!!!! ( this place has questions )

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

KILL [CONNECTION | QUERY] processlist_id

Official Introduction:

inch 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  is  as  isQUERY  was 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.

To this end, the contents of the running connection state of the MySQL view data to be introduced today.

The last thing to add is that the " this place has a problem ", it is questionable, our application 3 servers, 6 tomcat applications, all kill process stopped, why finally show processlist inside there will be a running query, And time is more than 200,000 seconds, is a select to look for a few days? Such as:

Our application in 3 days, two nights, every day will kill the application, do version update operation, how to say, select will not appear more than 200,000 seconds it? Who can help analysis, my suspicion is that these are show processlist process, in the execution of query, MySQL internal deadlock caused, the data status shows that this query has been, no end, because the deadlock, if not kill the process, The time displayed will be longer ...

Also ask a master to help analyze or give some clues.

MySQL Analytics data running state weapon "SHOW processlist"