MySQL solution for removing large tables for performance problems
- 2020-05-14 05:06:11
- OfStack
On weibo, we discuss how to reduce the time of MySQL hang when MySQL =innodb (30G+) is deleted. Here is a simple summary:
When buffer_pool is very large (30G+), MySQL hang will live because the entire buffer pool will be traversed to clean up the data when deleting the table. The solution is as follows:
1. When innodb_file_per_table=0, the above is not a problem, because when a Shared table space is used, the space occupied by the table will not be deleted, and the relevant pages in buffer pool will not be discard.
2. When innodb_file_per_table=1, and when buffer_pool is relatively large, it takes a lot of time to traverse the whole buffer pool (table_cache will be locked and all DML operations will be blocked).
Misunderstanding:
Changing the innodb table to myisam table has no effect. This operation will delete the old table, create a new table, and still traverse the entire buffer_pool.
Solutions:
1. Delete part of the records in batches in the form of script
2. It can be operated on slave to switch between main and backup (high cost)
3. Percona 5.1.58 and above all support innodb_lazy_drop_table (bug is not a few, use with caution)
4. It has nothing to do with buffer pool, but it can speed up the deletion of data files and reduce the time of MySQL hang. When multiple file names point to the same INODE at the same time, the reference number of INODE is N > 1, delete any one of the file name only deleted a pointer, will not delete the data file. When the reference number of INODE is N=1, deleting the file needs to clear all the data blocks related to this file, so it will be time-consuming.)
When buffer_pool is very large (30G+), MySQL hang will live because the entire buffer pool will be traversed to clean up the data when deleting the table. The solution is as follows:
1. When innodb_file_per_table=0, the above is not a problem, because when a Shared table space is used, the space occupied by the table will not be deleted, and the relevant pages in buffer pool will not be discard.
2. When innodb_file_per_table=1, and when buffer_pool is relatively large, it takes a lot of time to traverse the whole buffer pool (table_cache will be locked and all DML operations will be blocked).
Misunderstanding:
Changing the innodb table to myisam table has no effect. This operation will delete the old table, create a new table, and still traverse the entire buffer_pool.
Solutions:
1. Delete part of the records in batches in the form of script
2. It can be operated on slave to switch between main and backup (high cost)
3. Percona 5.1.58 and above all support innodb_lazy_drop_table (bug is not a few, use with caution)
4. It has nothing to do with buffer pool, but it can speed up the deletion of data files and reduce the time of MySQL hang. When multiple file names point to the same INODE at the same time, the reference number of INODE is N > 1, delete any one of the file name only deleted a pointer, will not delete the data file. When the reference number of INODE is N=1, deleting the file needs to clear all the data blocks related to this file, so it will be time-consuming.)