Detailed explanation of the execution principle of MySQL kill command

There are two ways to write the kill command: "kill query + thread id" and "kill connection (optional) + thread id". They respectively indicate closing the statement being executed by the specified thread and disconnecting the client connected to the specified thread (if there is an operation being executed, the operation will be stopped before the connection is closed). But in some cases, after using kill query and then using show processlist to view the Command column, it is shown as killed (which means it is waiting for the recycling thread to be recycled but has not been recycled yet). Why is this?

Before answering this question, you need to know how the thread that processes requests on the server is executed and how the kill command works.

Kill instruction execution principle

Instruction execution characteristics

1. There are multiple "buried points" during the execution of a statement. The thread status is judged at these "buried points". If the thread status is found to be THD:KILL_QUERY, the statement termination logic is entered;

2. If it is in a waiting state, it must be a waiting state that can be awakened, otherwise it will not be executed to the "buried point" at all;

3. There is a process from the beginning of the statement entering the termination logic to the completion of the termination logic.

Kill query execution principle

kill query mainly performs two steps:

1. Change the running state of the thread to THD::KILL_QUERY (assign the variable killed to THD::KILL_QUERY);

2. Send a signal to the execution thread of the session to exit the blocking state and process this state.

Kill Connection Implementation Principle

1. Set the status of thread 12 to KILL_CONNECTION;

2. Turn off the network connection of thread 12.

Can it be interrupted?

1. Generally, after executing kill query, the status of normally executed statements will be changed from killed to KILL_QUERY, and then the execution will be interrupted when it reaches the "embedded point".

2. If it is a blocked statement, you need to check whether the current blocked waiting state can be awakened. If it can be awakened, there is a chance to interrupt the current statement.

Scenarios that can be interrupted: normal execution or in a blocked waiting state that can be awakened.

Because the pthread_cond_timedwait function is used when waiting for the row lock, this waiting state can be awakened. It can be directly awakened by kill query to continue execution until the "burying point" judgment.

Scenarios that cannot be interrupted: blocked and cannot be awakened.

Example: Blocking due to running out of concurrent threads.

Set the parameter innodb_thread_concurrency (the number of concurrent threads for MySQL) to 2. Then do the following:

After session D executes kill query C, session C does not exit the blocking state.

• Question 1: Why does killing query not interrupt the blocking process?

Answer: Because this kind of blockage is not a blockage from a microscopic perspective, but a circular judgment. Every 10 milliseconds, it is determined whether Innodb can be entered for execution. If not, the nanosleep function is called to enter the sleep state. That is to say, although the thread status has been set to KILL_QUERY (THD::KILL_QUERY), the "buried point" is not executed during the loop waiting to enter InnoDB, and the thread status is not judged, so the termination logic stage will not be entered at all. So there will be no interruption.

• Question 2: If you use show processlist to view it, you will find that the Command is listed as killed. Why is this?

Answer: The kill query statement will set the thread state to KILL_QUERY. At this time, it will be judged as executing interrupt logic because of this state, so the Command value is killed.

• Question 3: Why can killing a connection interrupt the blocking process?

Answer: Because kill connection will directly close the thread's network connection and force it to close, so session C receives a prompt to disconnect at this time.

• Question 4: When can the blocking be interrupted if only kill query is used?

Answer: The program will not exit until a thread is assigned to the session and the execution reaches the "embedded point", and then the interrupt logic is executed. However, after a thread is assigned, it will not necessarily be interrupted. If the thread is released before the execution reaches the "buried point", it will wait again. MySQL threads are multiplexed.

other

1. In fact, in addition to using the kill command to terminate the blocking state, you can also use "ctrl+c" directly in the session to terminate the blocking. What is the principle behind this?

Answer: First of all, you need to know that the client operates the server by opening a thread on the client, letting this thread process, sending request data, transmitting it to the server through the network, and the server then assigns a thread to process it. "ctrl + c" tells the client to open another connection and send a kill query command. So although we seem to have interrupted the blocking, the server thread that processed the previous connection will not necessarily be interrupted.

2. Why is it so slow when connecting by specifying the library name? As shown below:

Answer: This is because the auto-completion function of MySQL is enabled by default (you can use tab to auto-complete when entering table names). Its implementation is to perform some more operations when connecting to the database:

1. Execute show databases;
2. Switch to the db1 database and execute show tables;
3. Use the results of these two commands to build a local hash table. (Most time consuming)

This feature can be disabled by adding -A to the command. It can also be disabled by using -quick. However, using -quick may reduce client performance. why is that? This brings us to the process of sending data between the server and the client.

Server thread execution flow

The client first verifies the username and password with the server, and after passing, a connection is formally established. The client then sends a request, and the server takes a thread from the thread pool to process it. Processing process:

1. Get a line and write it to net_buffer. The size of this memory is defined by the parameter net_buffer_length, which defaults to 16k.
2. Repeatedly fetch rows until net_buffer is full, and then call the network interface to send them out.
3. If the transmission is successful, clear the net_buffer, then continue to take the next line and write it to the net_buffer.
4. If the sending function returns EAGAIN or WSAEWOULDBLOCK, it means that the local network stack (socket send buffer) is full and enters the waiting state. Wait until the network stack becomes writable again before continuing to send.

From the above process, we can know that if the amount of data to be sent at one time exceeds the socket send buffer space, it will be split up and sent, and the "memory explosion" situation will not occur. From this we can know that MySQL reads and sends at the same time.

1. If the amount of data returned by the request is large, then when waiting for the return, use show processlist to check the value of the State column and it will be "Sending to client", indicating that the network stack on the server side is full.

This is because the value of the Sate column changes when the query request arrives and starts to be executed, and it will become "Sending data". If the network stack is full, it will switch to "Sending to client", which means "waiting for the client to receive the result". "Sending data" may be at any stage of the thread execution process, such as blocked by a lock.

2. If the State column of show processlist is always "Sending to Client", then you can

1) Check this SQL statement to see if it can be optimized to reduce the return value.

2) Set net_buffer_length to a larger value to avoid or reduce the time of sending blocking.

Client execution process

At the beginning, the client will create a thread to connect to the server, and then receive the data returned by the server. There are two ways for the client to receive the data returned by the server:

1. Local cache. Open a piece of memory locally and save the results first. If you develop using the API, the corresponding method is mysql_store_result. It is recommended to use local cache when the client processing volume is large. You can use mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file to save the returned data to the specified file.

2. No caching, read and process one at a time. If you develop using the API, the corresponding method is mysql_use_result.

Back to the question above, why using -quick may cause client performance to degrade? This is because the client uses the cache by default to receive, so when the client is processing other data, it can cache it first, and then read the cache directly later. Using quick will cause the client to receive data without using cache. If the client is performing other operations, the data will be blocked, and the corresponding thread on the server will not interrupt the transaction because it has not received feedback from the client. The resource locks involved in this transaction will not be released, causing concurrency problems and affecting efficiency. In addition, quick has three more effects.

1. As mentioned earlier, skip the automatic completion function of the table name.
2. The client receives data without caching. The mysql_store_result method needs to apply for local memory to cache query results. If the query results are too large, it will consume more local memory and may affect the performance of the client's local machine.
3. The executed commands will not be recorded in the local command history file.

The above is a detailed explanation of the execution principle of the MySQL kill command. For more information about the MySQL kill command, please pay attention to other related articles on 123WORDPRESS.COM!