MySQL Optimization-DDL statements

Author: skate
Time: 2012/07/02

MySQL Optimization-DDL statements

When the drop table maintains the MySQL database, the entire system will be hang during the drop operation. The duration of this hang is related to the buffer pool size. The main reason is that when InnoDB drops the table, it will traverse the Buf pool LRU linked list two times in a row and lock the traversal process, resulting in the hang of the system.

 

The first time is used to release records in the adaptive hash:
0. Obtain the mutex of the current Buf pool to traverse the LRU list of the Buf pool.
1. Traverse from the end of the LRU linked list
2. For each page that belongs to the drop table, determine whether there is any entry in the page into the adaptive hash. If yes, collect the current page
3. After collecting 1024 such pages, release the Buf pool mutex and call the buf_lru_drop_page_hash_batch function to release the items of the page in the adaptive hash.
4. Obtain the Buf pool mutex again and traverse the LRU linked list of the Buf pool until the linked list Header
5. Finally, release the Buf pool mutex and exit.

 

The second traversal of the Buf pool LRU linked list releases all pages corresponding to the drop table
1. Get the Buf pool mutex
2. traverse the LRU linked list of the buffer pool
3. If dirty page is used, set dirty page to clean page and remove it from the flush list.
4. Remove the page from the LRU list and add it to the Free List.
5. Finally, release the Buf pool mutex.

 

Test:
Environment:
OS: redhat5.2
MySQL: 5.5.24-Log

Session1:
Mysql> insert into TT2 select @ A: = @ A + 1, name from TT2;
Query OK, 4194304 rows affected (1 min 11.52 Sec)
Records: 4194304 duplicates: 0 Warnings: 0

Start Drop TT2 and insert a record in session2.
Mysql> drop table TT2;
Query OK, 0 rows affected (6.42 Sec)

Mysql>

Session2:
It takes 0.03 s to insert a record normally.
Mysql> insert into T2 values (11, '22 ');
Query OK, 1 row affected (0.03 Sec)

When executing drop TT2, 4.17 S is required.
Mysql> insert into T2 values (11, '22 ');
Query OK, 1 row affected (4.17 Sec)

Mysql>

 

Summary:
1. The LRU linked list will be regularly released by the Buf pool mutex for the first time, so it has little impact on the system hang. The second time will always be held, which has a greater impact on the system hang.
2. When maintaining the drop operation, you must select the idle time because it will block all DDL and DML operations.

 

 

--- End ----