"Go" MySQL process management

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 7 | Root | localhost | yy |  Sleep |       154 | | NULL |
| 8 | Root | localhost | NULL |    Query | 0 | NULL | Show Processlist |
+----+------+-----------+------+---------+------+-------+------------------+
2 rows in Set (0.00 sec)

Mysql> Kill 7
;

Mysql> show Processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 8 | Root | localhost | NULL |    Query | 0 | NULL | Show Processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in Set (0.00 sec)

-----

Each connection to the mysqld is run in a separate thread, and you can use the show Processlist statement to see which threads are running and terminate a thread with the kill thread_id statement.

Kill allows optional connection or query modifiers:

· Kill connection is the same as a kill without modifiers: it terminates the connection associated with a given thread_id .

· Kill query Terminates the statement that the connection is currently executing, but keeps the connection intact.

If you have process permissions, you can view all the threads. If you have super privileges, you can terminate all threads and statements. Otherwise, you can only view and terminate your own threads and statements.

You can also use the mysqladmin processlist and mysqladmin kill commands to check and terminate threads.

Note: You cannot use both the kill and embedded MySQL server libraries because the embedded server only runs the thread of the host application. It cannot create any of its own connection threads.

When you make a kill, a unique termination token is set on the thread. In most cases, thread termination can take some time, because the terminating token is only checked at a specific interval:

· In the Select, ORDER by and group by loops, the tag is checked after reading a set of rows. If a termination token is set, the statement is discarded.

· In the ALTER TABLE procedure, the terminating token is checked before each set of rows is read from the original table. If a termination token is set, the statement is discarded and the temporary table is deleted.

· During the update or delete run, after each group has been read and after each row that has been more or deleted, the termination token is checked. If the termination token is set, the statement is discarded. Note that if you are using transactions, the changes are not rolled back.

· Get_lock () discards and returns NULL.

· The insert delayed thread quickly refreshes (inserts) all of its rows in memory and then terminates.

· If the thread is in the table lock Manager (state: Locked), the table lock is quickly discarded.

· If the thread is waiting for free disk space in the write call, the write is discarded and accompanies the "Disk full" error message.

· Warning: Terminating a repair table or optimize table operation on MyISAM tables results in a corrupted, unused table. Any read or write to such a table will fail until you optimize or repair it again (without interruption).

Transfer from Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-index

MySQL selectively kill process


Show Processlist/show full processlist can see the current process information,
If you want to kill some process, you can only copy its ID (thread ID), then kill, and kill only one at a time.
If you want to kill all statements that run for more than 10 seconds, it's too much of a hassle.
Today introduces a simple method, mainly through the INFORMATION_SCHEMA database to achieve.
The Processlist table records the process information.


Identify the ID of the process that is running longer than 10 seconds.
SELECT ID from INFORMATION_SCHEMA. Processlist
WHERE COMMAND = ' Query ' and time > 10;
This seems too rash, some processes should not be killed, such as responsible for replication ...
Need to filter:


SELECT ID from INFORMATION_SCHEMA. Processlist
WHERE COMMAND = ' Query ' and time > 10
and ID! = connection_id ()
and USER! = ' root '
and COMMAND! = ' Binlog Dump '
and state not REGEXP ' (slave|relay|event) '
You can also add your own filter according to the actual conditions.


Then the tragedy is that kill can only accept an ID as a parameter.
Had to generate all the kill statements:


mysql> SELECT CONCAT (' KILL ', ID, '; ')
From INFORMATION_SCHEMA. Processlist
WHERE USER = ' webs '
and COMMAND = ' Query '
and Time > 10
Into OUTFILE '/tmp/kill_list.txt ';
And then import:


Note that the following line of statements is not terminated with a semicolon
mysql> \. /tmp/kill_list.txt
Query OK, 0 ROWS Affected (0.00 sec)
Although now more intelligent, but it is still more troublesome to make a return.
You can try Percona Toolkit inside the Pt-kill, wish you good luck

Transfer from http://blog.csdn.net/hewy0526/article/details/8240710

"Go" MySQL process management