On Weibo, we will discuss how MySQL can reduce MySQL hang time When deleting a large table engine = innodb (30G +). Here is a brief summary:
When buffer_pool is large (30 GB +), because the table is deleted, the entire buffer pool will be traversed to clean up data, resulting in MySQL hang. The solution is as follows:
1. When innodb_file_per_table = 0, the above is not a problem because the space occupied by the table will not be deleted when the table is shared, and the related pages in the buffer pool will not be discard.
2. When innodb_file_per_table = 1 and buffer_pool is large, traversing the entire buffer pool takes a lot of time (table_cache will be locked and all DML operations will be blocked ).
Misunderstanding:
Changing the innodb table to the myisam table does not work. This operation deletes the old table, creates a new table, and traverses the entire buffer_pool.
Solution:
1. Batch delete some records in the form of scripts
2. You can perform operations on the slave to perform master-slave switchover (high cost)
3. Percona 5.1.58 and later versions support innodb_lazy_drop_table (lots of bugs, please use them with caution)
4. It has nothing to do with the buffer pool, but it can speed up data file deletion and reduce the time for MySQL hang. That is, establish a hard link to the data file (dependency principle: OS HARD LINK when multiple file names point to the same INODE at the same time, the reference number of this INODE N> 1, deleting any file name only deletes a pointer and does not delete the data file. When INODE references N = 1, it takes a long time to delete all data blocks related to this file)