Automatic Kill slow Query

in a production environment, DB servers are often hung by concurrent slow queries, so it is important to do SQL audits beforehand to avoid bad SQL. Wan accidentally slow SQL or run to the line, and concurrency is not small, this is the DBA will definitely receive an alarm. DBA on-line processing the first time is to locate and kill slow query, avoid slow query other normal transactions. This paper focuses on kill deployment , and introduces several related timeout parameters and implementation mechanism.

Kill command

The syntax for Kill is as follows: Kill [Connection|query] thread_id, kill a query or kill a connection by using the KILL command. In general, Each user can only view and terminate their users ' connections and queries, and if the user has process permissions, they will be able to view all threads with super privileges to view and terminate connections and queries for all users. Assuming that two sessions A and b,a thread_id execute queries for xxx,a sessions, the B session executes kill query XXX and kill xxx respectively, session a receives 1317 and 2013 errors respectively.

Mysql> Select Count(*) fromTest_slowwhere 1=1; ERROR1317(70100): Query execution was interruptedmysql> Select Count(*) fromTest_slowwhere 1=1; ERROR -(HY000): Lost connection toMySQL Server during query 

through the MySQL client operation, in addition to sending the KILL command, you can also use CTRL + C to end themselves. The principle here is very simple, the MySQL client has a signal capture thread, after hearing Sigint, create a connection, and then send the KILL command to the server, the end of their own. When debugging through GDB, in order to avoid the effect of GDB on the signal, the command can be set as follows:

Handle SIGINT nostop Print Pass

Kill implementation Principle

mysqld When the KILL command is received, the corresponding thread instance THD is set to the kill state, and if it is kill_connection, the socket is actively disconnected. Therefore, for the kill connection operation, the thread will kill immediately, but for the kill query, the query may not end immediately because the query may be in the process of running. Therefore, the key node in the code of MYSQLD will call the trx_is_interrupted function to determine whether its state is killed, if so, the error returns, terminates execution. exactly where the mysqld will be inspected, here is an official document stating the following:

Combinatorial SELECT, ORDER by and GROUP by loops, the flag was checked after reading a block of rows. If the KILL flag is set, the statement is aborted.

during ALTER table,the kill flag is checked before each block of rows was read from the originaltable. If the KILL flag was set, the statement are aborted and the temporarytable is deleted.

during Updateor DELETE operations, the kill flag is checked after each block read and Aftereach updated or deleted row. If the KILL flag is set, the statement is aborted. Note that if you is not using transactions, the changes is not rolledback.

Kill automation

if each query requires the DBA to manually trigger, then when encountering a problem, may not be handled so timely, if you can automate the kill slow query can minimize the impact. This is done through the interface statement.setquerytimeout (int) in JDBC . This principle is in fact similar to the MySQL client through Ctrl+ckill queries, except that this is done through timeouts. Set a timer, there is a thread timed to determine whether the timer event has triggered, reached the trigger point, and then send the kill query to the server, to achieve the purpose of kill query. Although this way can be automated, but this interface call control is still in the hands of development, and generally, development must consider their own query is not a problem, certainly will not time out, so this interface is not controlled by the call. the db will still hang after the problem is encountered, or it needs to be handled manually by the DBA. So a similar set of functions is also done in the Alimysql kernel, with the parameter max_statement_time to control the current session and the time-out of all sessions. Whenever the timeout is exceeded, MySQL internally invokes the interface awake interface to set the corresponding THD state to killed, which is the purpose of kill query or kill connection.

Several timeout parameters

In addition to the usual timeout parameters in the Statementtimeout,jdbc we mentioned above, there are connecttimeout and socket timeout.

ConnectTimeout: Time-out, in milliseconds, when establishing a socket connection with the database server.

Socket Timeout:socket operation (read-write) timeout

Specifically, JDBC uses the properties of the socket to achieve timeout purposes, and different JDBC drivers are configured differently. Timeout at Socket connection: set by Socket.connect (socketaddressendpoint, int timeout) ; Timeout for socket read and write: via socket.setsotimeout (int timeout) setting, with blocking IO model, if no sockettimeout or connect timeout is set, In most cases, network errors cannot be detected. Therefore, when a network error occurs, the application waits indefinitely until the connection is reconnected successfully or the data is successfully received. Of course, There will also be a socket timeout setting at the operating system level. The relevant configuration parameters are as follows:

TCP_KEEPALIVE_TIME:TCP connection after idle time, the kernel only initiates probe

Tcp_keepalive_probes:tcp send keepalive probe to determine the number of times that the connection has been disconnected

TCP_KEEPALIVE_INTVL: Detecting how often messages are sent

Therefore, after probing (tcp_keepalive_time + TCP_KEEPALIVE_INTVL * tcp_keepalive_probes) time, if not yet connected, then disconnect.

These parameters can be modified:

/proc/sys/net/ipv4/tcp_keepalive_time
/proc/sys/net/ipv4/tcp_keepalive_intvl
/proc/sys/net/ipv4/tcp_keepalive_probes

Reference documents

http://astar.baidu.com/forum/forum.php?mod=viewthread&tid=363

Http://dev.mysql.com/doc/refman/5.1/en/kill.html

http://m.blog.csdn.net/blog/xieyuooo/39898449

Http://blog.sina.com.cn/s/blog_a2d4803001013hrk.html

Automatic Kill slow Query