Deep analysis of the new feature of MySQL 8.0: crash safe DDL

Time:2021-1-22

preface

In versions before MySQL 8.0, because of the architecture, MySQL uses a unified frm file to store table metadata information in the server layer, which can be recognized by different storage engines. In fact, InnoDB also stores metadata information.

This brings a certain challenge to DDL, because this architecture can’t achieve the atomization of DDL. We can often see the temporary files left under the data directory, or errors such as the inconsistent number of columns in the server layer and InnoDB layer. Even some DDLS may leave metadata in InnoDB and lose frm, which makes it impossible to rebuild tables In order to solve this problem, we implement a function called drop table force to force cleaning .)

(all discussions below assume that InnoDB storage engine is used)

By version 8.0, we know that all metadata has been managed by InnoDB, which makes it possible to implement atomic DDL. Almost all operations on InnoDB tables, stored procedures, triggers, views or UDFs can be atomized

-Metadata modification, binlog and InnoDB operations are all in one transaction
-Added an internal hidden system table` mysql.innodb_ ddl_ Log '. DDL operations are recorded in this table. Note that the redo generated by the operation on this table will fsync to the disk instead of InnoDB_ flush_ log_ at_ trx_ The configuration of commit. When the crash restarts, it will decide whether to roll back or perform DDL operation through the records of this table according to whether the transaction is committed or not
-A post DDL stage has been added, which is also the last stage of DDL. It will go to: 1. Delete or rename the file physically; 2. Delete InnoDB_ ddl_ For some DDL operations, dynamic metadata information (stored in` mysql.innodb_ dynamic_ Metadata ', such as corrupt flag, auto_ Inc value, etc.)
-After a normal DDL is finished, its DDL log should also be cleaned up. If it crashes in the middle, it will try to replay during restart: 1. If it has reached the last DDL stage (after commit), it will replay the DDL log to complete DDL; 2. If it is in an intermediate state, it will roll back DDL

Due to the introduction of atomic DDL, the behavior of some DDL operations has changed

-Drop table: in previous versions, if multiple tables are deleted in a drop table statement, such as T1, T2 and T2, T1 will be deleted. However, in 8.0, T1 and T2 are not deleted, but an error is thrown. Therefore, we should pay attention to the replication problem of 5.7 - > 8.0 (drop view and create user have similar problems)
-Drop database: modifying metadata and DDL_ Log submits the transaction first, and the real physical deletion data file is put at the end. Therefore, if it crashes when deleting the file, it will be restarted according to DDL_ Log continues to run drop database

Test:

MySQL has added an option

innodb_print_ddl_logsAfter opening, we can see the corresponding DDL log from the error log. Let’s take a look at some typical DDL processes through this

[email protected](none) 11:12:19>SET GLOBAL innodb_print_ddl_logs = 1;                                                                                                                                    
Query OK, 0 rows affected (0.00 sec)

[email protected](none) 11:12:22>SET GLOBAL log_error_verbosity = 3;                                                                                                                                                       
Query OK, 0 rows affected (0.00 sec)

CREATE DATABASE

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.02 sec)

Create database statement does not write log_ DDL, you may think that this is not a high-frequency operation. If you fail in the process of creating a database, you may need to delete the directory manually after restarting.

CREATE TABLE

mysql> USE test;
Database changed
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT);
Query OK, 0 rows affected (0.06 sec)

[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=428, thread_id=7, space_id=76, old_file_path=./test/t1.ibd]
[InnoDB] DDL log delete : by id 428
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=429, thread_id=7, table_id=1102, new_file_path=test/t1]
[InnoDB] DDL log delete : by id 429
[InnoDB] DDL log insert : [DDL record: FREE, id=430, thread_id=7, space_id=76, index_id=190, page_no=4]
[InnoDB] DDL log delete : by id 430
[InnoDB] DDL log post ddl : begin for thread id : 7
InnoDB] DDL log post ddl : end for thread id : 7

According to the log, there are three types of operations, which actually describe the three reverse operations that need to be carried out if the operation fails: deleting the data file, releasing the data dictionary information in memory, and deleting the index BTREE. Before the table is created, the data is written to DDL_ After creating the table and committing, delete these records from DDL log.

In addition, there areDDL log deleteIn fact, each time a DDL log is written, a separate transaction is committed. However, after the commit, a delete operation will be performed using the current transaction, and it will not be committed until the end of the operation.

Instant

mysql> ALTER TABLE t1 ADD COLUMN c INT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0


[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log post ddl : end for thread id : 7

Note that instant DDL is implemented here, which is a newly supported feature in 8.0.13. Adding columns can only modify metadata, so there is no need to record data from DDL log

Delete column

mysql> ALTER  TABLE t1 DROP COLUMN c;
Query OK, 0 rows affected (2.77 sec)
Records: 0  Duplicates: 0  Warnings: 0


[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=487, thread_id=7, space_id=83, old_file_path=./test/#sql-ib1108-1917598001.ibd]
[InnoDB] DDL log delete : by id 487
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=488, thread_id=7, table_id=1109, new_file_path=test/#sql-ib1108-1917598001]
[InnoDB] DDL log delete : by id 488
[InnoDB] DDL log insert : [DDL record: FREE, id=489, thread_id=7, space_id=83, index_id=200, page_no=4]
[InnoDB] DDL log delete : by id 489

[InnoDB] DDL log insert : [DDL record: DROP, id=490, thread_id=7, table_id=1108]
[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=491, thread_id=7, space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd, new_file_path=./test/t1.ibd]
[InnoDB] DDL log delete : by id 491
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=492, thread_id=7, table_id=1108, old_file_path=test/#sql-ib1109-1917598002, new_file_path=test/t1]
[InnoDB] DDL log delete : by id 492
[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=493, thread_id=7, space_id=83, old_file_path=./test/t1.ibd, new_file_path=./test/#sql-ib1108-1917598001.ibd]
[InnoDB] DDL log delete : by id 493
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=494, thread_id=7, table_id=1109, old_file_path=test/t1, new_file_path=test/#sql-ib1108-1917598001]
[InnoDB] DDL log delete : by id 494
[InnoDB] DDL log insert : [DDL record: DROP, id=495, thread_id=7, table_id=1108]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=496, thread_id=7, space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd]

[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=496, thread_id=7, space_id=82, old_file_path=./test/#sql-ib1109-1917598002.ibd]
[InnoDB] DDL log replay : [DDL record: DROP, id=495, thread_id=7, table_id=1108]
[InnoDB] DDL log replay : [DDL record: DROP, id=490, thread_id=7, table_id=1108]
[InnoDB] DDL log post ddl : end for thread id : 7

This is a typical three-stage DDL process: divided intoPrepare, perform and commitThere are three stages

  • Prepare:At this stage, we will modify the metadata and create a temporary IBD file # sql-ib1108-1917598001.ibd. If an abnormal crash occurs, we need to be able to delete the temporary file. Therefore, similar to create table, we also write three logs for the IDB: delete space, remove cache, and free BTREE

  • Perform:Execute the operation to copy the data to the above IBD file (while processing online dmllog), which does not involve log DDL operation

  • Commit:To update the data dictionary information and commit the transaction, several logs will be written here:

    DROP : table_id=1108

    Rename space: # sql-ib1109-1917598002.ibd file is renamed to t1.ibd

    Rename table: sql-ib1109-1917598002 is renamed as T1

    Rename space: t1.ibd is renamed as sql-ib1108-1917598001.ibd

    Rename table: T1 table is renamed as sql-ib1108-1917598001

    DROP TABLE: table_id=1108

    Delete space: delete sql-ib1109-1917598002.ibd

In fact, the DDL log written in this step describes the reverse process of the operation in the commit phase: change t1.ibd rename to sql-ib1109-1917598002, change sql-ib1108-1917598001 rename to T1 table, and finally delete the old table. The operation of deleting the old table is not executed here, but is executed in the post DDL stage

  • Post DDL: after the transaction is committed, perform the final operation: replay DDL log, delete old files, and clean up mysql.innodb_ dynamic_ Related information in metadata

    DELETE SPACE:

    sql-ib1109-1917598002.ibd

    DROP: table_id=1108

    DROP: table_id=1108

South Asia quotation

mysql> ALTER TABLE t1 ADD KEY(b);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0


[InnoDB] DDL log insert : [DDL record: FREE, id=431, thread_id=7, space_id=76, index_id=191, page_no=5]
[InnoDB] DDL log delete : by id 431

[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log post ddl : end for thread id : 7

The index is created by inplace. There is no temporary file. However, if an exception occurs, you still need to clean up the temporary index when an exception occurs. Therefore, a free log is added to delete the temporary index when an exception occurs.

TRUNCATE TABLE

mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.13 sec)


[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=439, thread_id=7, space_id=77, old_file_path=./test/#sql-ib1103-1917597994.ibd, new_file_path=./test/t1.ibd]
[InnoDB] DDL log delete : by id 439
[InnoDB] DDL log insert : [DDL record: DROP, id=440, thread_id=7, table_id=1103]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=441, thread_id=7, space_id=77, old_file_path=./test/#sql-ib1103-1917597994.ibd]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=442, thread_id=7, space_id=78, old_file_path=./test/t1.ibd]
[InnoDB] DDL log delete : by id 442
[InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=443, thread_id=7, table_id=1104, new_file_path=test/t1]
[InnoDB] DDL log delete : by id 443
[InnoDB] DDL log insert : [DDL record: FREE, id=444, thread_id=7, space_id=78, index_id=194, page_no=4]
[InnoDB] DDL log delete : by id 444
[InnoDB] DDL log insert : [DDL record: FREE, id=445, thread_id=7, space_id=78, index_id=195, page_no=5]
[InnoDB] DDL log delete : by id 445

[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=441, thread_id=7, space_id=77, old_file_path=./test/#sql-ib1103-1917597994.ibd]
[InnoDB] DDL log replay : [DDL record: DROP, id=440, thread_id=7, table_id=1103]
[InnoDB] DDL log post ddl : end for thread id : 7

Truncate table is an interesting topic. In earlier versions 5.6 and before, it was created by deleting the old table. After 5.7, in order to ensure atomicity, it was changed to the original truncate file and a truncate log file was added. If it crashes during the truncate process, you can use this file to truncate again during the crash recovery.

When it comes to version 8.0, it is restored to the way of deleting old tables and creating new ones. Unlike before, version 8.0 can roll back to old data when it crashes instead of executing again. Taking the above as an example, it mainly includes several steps:

  • Change the table t1.ibd rename to sql-ib1103-1917597994.ibd

  • Create a new file t1.ibd

  • Post DDL: delete the old file # sql-ib1103-1917597994.ibd

RENAME TABLE

mysql> RENAME TABLE t1 TO t2;
Query OK, 0 rows affected (0.06 sec)

DDL LOG:

[InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=450, thread_id=7, space_id=78, old_file_path=./test/t2.ibd, new_file_path=./test/t1.ibd]
[InnoDB] DDL log delete : by id 450
[InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=451, thread_id=7, table_id=1104, old_file_path=test/t2, new_file_path=test/t1]
[InnoDB] DDL log delete : by id 451

[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log post ddl : end for thread id : 7

This is relatively simple, just need to record the reverse operation of rename space and rename table. Post DDL does not need to do the actual operation

DROP TABLE

DROP TABLE t2
[InnoDB] DDL log insert : [DDL record: DROP, id=595, thread_id=7, table_id=1119]
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=596, thread_id=7, space_id=93, old_file_path=./test/t2.ibd]

[InnoDB] DDL log post ddl : begin for thread id : 7
[InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=596, thread_id=7, space_id=93, old_file_path=./test/t2.ibd]
[InnoDB] DDL log replay : [DDL record: DROP, id=595, thread_id=7, table_id=1119]
[InnoDB] DDL log post ddl : end for thread id : 7

First record the data to be deleted in DDL log, then submit it, and finally perform the real operation of deleting table objects and files in post DDL stage

code implementation

The main implementation code is concentrated in the file storage / innobase / log / log0 ddl.cc Contains the log_ DDL table insert records and replay logic.

Hidden InnoDB_ log_ DDL table structure is as follows

def->add_field(0, "id", "id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT");
  def->add_field(1, "thread_id", "thread_id BIGINT UNSIGNED NOT NULL");
  def->add_field(2, "type", "type INT UNSIGNED NOT NULL");
  def->add_field(3, "space_id", "space_id INT UNSIGNED");
  def->add_field(4, "page_no", "page_no INT UNSIGNED");
  def->add_field(5, "index_id", "index_id BIGINT UNSIGNED");
  def->add_field(6, "table_id", "table_id BIGINT UNSIGNED");
  def->add_field(7, "old_file_path",
                 "old_file_path VARCHAR(512) COLLATE UTF8_BIN");
  def->add_field(8, "new_file_path",
                 "new_file_path VARCHAR(512) COLLATE UTF8_BIN");
  def->add_index(0, "index_pk", "PRIMARY KEY(id)");
  def->add_index(1, "index_k_thread_id", "KEY(thread_id)");

Record type

According to different operation types, it can be divided into the following categories:

FREE_TREE_LOG

The purpose is to release the index BTREE

log_DDL::write_free_tree_log, called when creating an index and deleting a table.

For the delete index operation involved in drop table, the insert operation of log DDL is put into the parent transaction, which is either committed or rolled back together
For the case of creating index, log DDL needs to be submitted separately, and the parent transaction will delete the record mark, so that if DDL is rolled back later, the remaining index can also be deleted.

DELETE_SPACE_LOG

Entry function:

Log_DDL::write_delete_space_log

It is used to delete a table space. It can also be divided into two cases

  1. Drop table / tablespace. The written records are submitted with the parent transaction and replayed in the post DDL stage

  2. Create a tablespace. The written records are submitted separately and marked for deletion by the parent transaction. If the parent transaction rolls back, the participating tablespaces are deleted through replay

  3. RENAME_SPACE_LOG

Entry function:

Log_DDL::write_rename_space_log

It is used to record the rename operation. For example, if we rename table t1 to T2, the reverse operation T2 rename to T1 is recorded.

In functionFil_shard::space_rename()DDL log is always written before real rename operation. The process of writing log is also independent transaction commit, and parent transaction delete uncommitted

DROP_LOG

Entry function:Log_DDL::write_drop_log

It is used to record the operation of deleting table objects. There is no file level operation involved here. Writing DDL log is executed in the parent transaction

RENAME_TABLE_LOG

Entry function:

Log_DDL::write_rename_table_log

It is used to record the reverse operation of the rename table object. Similar to rename space, it is also an independent transaction that submits DDL log and the parent transaction is marked for deletion

REMOVE_CACHE_LOG

Entry function:

Log_DDL::write_remove_cache_log

It is used to clean up memory table objects, submit independent transactions, and delete parent transaction marks

ALTER_ENCRYPT_TABLESPACE_LOG

Entry function:

Log_DDL::write_alter_encrypt_space_log

It is used to record the modification of the encrypted attribute of the table space and submit the independent transaction. After writing the DDL log, modify the encryption mark in the table space Page0

To sum up, multiple transactions may be committed during DDL, which can be roughly divided into three categories:

  • If the parent transaction is committed, the DDL log is deleted. If the parent transaction is rolled back, the DDL log will be rolled back according to the DDL log

  • The independent transaction writes DDL log and commits (currently only alter)_ ENCRYPT_ TABLESPACE_ LOG)

  • Use the parent transaction to write DDL log and commit at the end of DDL. It needs to be processed in the post DDL phase

post_ddl

As mentioned above, some DDL logs are submitted with the parent transaction, while others are executed in the post DDL phase, and the post_ DDL occurs after the parent commits or rolls back: if the transaction is rolled back, the reverse operation is performed according to DDL log; if the transaction is committed, the last real irreversible operation is performed in post DDL phase (such as deleting files)

Entry function:Log_DDL::post_ddl -->Log_DDL::replay_by_thread_id

According to the thread ID of the thread executing DDL, through InnoDB_ log_ The secondary index on the DDL table, find the log ID, find the corresponding record item on the clustered index, and then replay these operations. After DDL is completed, clean up the corresponding record

Crash recovery

At the end of crash recovery, theha_post_recoverInterface function, and then call the function in InnoDBLog_DDL::recover(), replay the records in the same way, and delete the records after the end. But alter_ ENCRYPT_ TABLESPACE_ Log type is not deleted in this step, but added to an array_ encrypt_ ddl_ In records, after the callresume_alter_encrypt_tablespaceTo resume the operation.

Reference documents

  1. Official document:

    https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

  2. WL#9536: InnoDB_New_DD: Support crash-safe DDL

    https://dev.mysql.com/worklog/task/?spm=a2c4e.11153940.blogcont684418.13.7b5b4116dYdg9Y&id=9536

Pay attention, don’t get lost

OK, everyone, the above is the whole content of this article, you can see the people here, they are allpersonnel. As I said before, there are many technical points in PHP, but also because there are too many. We can’t write them down, and we won’t see too much when we write them down. So I’ve organized them into PDF and documents here, if necessary

Click to enter the code: blog Garden


More learning content can be accessedAs long as you can read it, your salary will go up a step

The above contents hope to help youA lot of PHPer always encounter some problems and bottlenecks when they are upgrading. They write too much business code and have no sense of direction. They don’t know where to start to improve. For this, I have sorted out some materials, including but not limited to:Distributed architecture, high scalability, high performance, high concurrency, server performance tuning, tp6, laravel, yii2, redis, spool, swoft, Kafka, MySQL optimization, shell script, docker, microservice, nginxAnd so on many knowledge points, advanced dry goods need can be free to share with you, need can join mePHP Technology Exchange Group953224940