Question 40: How long will it take to kill the ongoing DDL?

ask

A DDL is running in MySQL. At this time, we kill this DDL. How long will this DDL be killed?

To discuss this issue, we need to split the issue: How long will DDL be killed = How long will DDL start kill + How long has the DDL rollback and finishing operation been performed

This experiment only discusses how to observe: how soon will DDL start to kill

experiment

Refer to Question 16, we set up a database and let it run in debug mode.

First, Kuanyou starts a database instance:

Change the start script and configure it to debug mode:

Restart the database and add the --debug parameter when starting:

Access the database and confirm that it is the debug version:

Create a simple table and insert 4 rows of data into it:

Now write a simple gdb script:

In this script, we set a breakpoint on the trx_is_interrupted function.

The trx_is_interrupted function is a function for InnoDB to check whether the current thread is killed. When this function is called, InnoDB will check whether there is a kill operation currently, and if so, it will perform the corresponding processing.

(If you want to know which function should be interrupted, you can seek help from developers)

When gdb runs to this breakpoint, we let gdb print the stack at the time, as well as a dividing line, so that we can see which process called this function

Then start gdb, here we use the script we wrote before:

No operation is required, the breakpoint has been added according to our script

Now trigger an alter in MySQL:

Find the gdb log file we specified:

You can see that the stack information is densely written inside. Don't panic at this time, let's copy it into a text editor and analyze it bit by bit:

The first stack:

We recognize a read clustered index, that is, in the process of reading the clustered index, MySQL will check whether the current thread is killed

The second stack:

We recognize BtrBulk, Btr is the abbreviation of B-tree, that is, in the process of bulk inserting B-tree, MySQL will check whether the current thread is killed

The third stack:

This stack is not easy to identify: this is the outer operation of the entire DDL, only knows to rebuild the index (the previous two stacks are also steps in rebuilding the index)

At the end of the line, I marked its code location as: row0merge.cc:4668, check the code:

Don't worry, we only know about flush, here is the last flush operation to rebuild the index, after flushing, check whether the current thread is killed

The fourth and fifth stacks:

The last two stacks are similar, both are checked when replaying the online DDL log to the new table

Based on the above experiments, we have reached a preliminary conclusion:

For the DDL in this experiment, MySQL checks whether the current thread is killed in the following places:

1. The process of reading the clustered index from the old table
2. The process of writing an index to a new table
3. When rebuilding the index, check after refreshing
4. The process of replaying online DDL log to a new table
5. If a kill occurs during these four processes, the DDL operation will soon begin to roll back.

But this conclusion still does not satisfy us. Which step in a process will check the kill?

Looking at the official documentation, the description of the kill behavior is as follows:

It can be seen that for a large number of data operations, MySQL will check whether the thread is killed after part of the data is processed.

In our experimental conclusions, the three processes 1/2/4 involve a large amount of data operations. MySQL divides them into several parts, and it is reasonable to check after each part is processed.

It should be noted that: Killing DDL does not always trigger in a reasonable time: for example, the processing of data becomes slower, or the flush becomes slower in stack 3. At this time, the check can only be performed until the checkpoint.

Tips

In this experiment, the DDL operation performed, the operation type is as shown in the figure:

For other types of DDL, you can explore on your own through experiments.

---

What else do you want to know about the technical content of MySQL? Hurry up and leave a message to tell the editor!