How to drop the 1TB form in the mysql library

1. Clear the Buffer Pool

When drop table, the innodb engine will clean up the table in each buffer pool instance For the corresponding data block pages in the middle, in order to avoid impact on the system, the clearing operation here is not really flush, but the involved pages are removed from the flush queue. But during the removal process, the deletion process will hold the global lock of each buffer pool, and then search for the corresponding page in this buffer pool in order to remove it from the flush list Delete in. If there are too many pages that need to be searched and deleted in buffer pool, the traversal time will increase, which will cause other transaction operations to be blocked, and in severe cases, the database may be locked.

(Recommended course: MySQL tutorial)

There is one more thing you need to pay attention to here. If the buffer pool of the database is set to a large size, the traversal time will change. long When cleaning buffer pool, it also includes cleaning AHI containing the data of this table. The function of AHI will not be discussed here, mainly when When the level of b tree becomes higher, in order to avoid b tree searching layer by layer, AHI can directly query the corresponding data page based on a certain search condition, skipping the layer-by-layer search. Layer positioning steps. Secondly, AHI will occupy 1/16 of the buffer pool size. If the online table data is not particularly large and the concurrency is not ultra-high, it is not recommended to turn on AHI. You may consider turning off the AHI function.

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.01 sec)


mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;
Query OK, 0 rows affected (0.00 sec)


mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name                   | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)

2. Delete the corresponding disk data file ibd

When deleting the data file, if the data file is too large, the deletion process will generate a large amount of IO And consume more time, causing disk IO overhead to soar, CPU load is too high, affecting the operation of other programs. A good friend of mine once deleted a 1TB table from the online database. As a result, the database became unresponsive for 20 minutes, and finally the database crashed and restarted.

Now that we know that drop table has done 2 things, then optimize the above 2 things

In clearing the Buffer Pool buffer, In order to reduce the size of a buffer pool, you can set the innodb_buffer_pool_instances parameter appropriately, reduce the buffer pool data block list scanning time, and turn off AHI## at the same time. #Function

In step 2, you can cleverly use the hard connection feature of

linux to delay deletion of real physical files.

When multiple file names point to the same

INODE at the same time, the number of references to this INODE is N>1, and deleting any one of the file names will be very fast. Because The direct physical file block has not been deleted. Just a pointer has been deleted; when the reference number of INODE is N=1, deleting the file requires clearing all the data blocks related to the file, so it will be compared. Time-consuming;

If you create a hard link for the

.ibd file of the database table, when the table is deleted, when the physical file is deleted, what is actually deleted is a pointer to the physical file, so the deletion The operation response speed will be very fast, about less than 1 second.

Let’s demonstrate the specific operation

先创建表文件的硬链接
ln t_test.ibd t_test.ibd.bak
删除表
drop table t_test;

The last step is to actually delete the physical file and release the disk space occupied by the file. , then the question is, if you delete physical files gracefully, here I recommend you

coreutilstruncatecommand in the tool set

Of course you need to install the relevant software first After the package

wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.29.tar.xz


使用非root进行解压
tar -xvJf coreutils-8.29.tar.xz
cd coreutils-8.29
./configure
make
使用root进行make install

is installed, you can write a script to delete large files very elegantly and distributedly.

${i}G means that each time you delete 10G

#!/bin/bash


TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `; 
do 
  sleep 2
  $TRUNCATE -s ${i}G /data/mysql/t_test.ibd.hdlk 
done
rm -rf /data/mysql/t_test.ibd.hdlk ;

The above is the detailed content of How to drop the 1TB form in the mysql library. For more information, please follow other related articles on the PHP Chinese website!