How to drop the 1TB form in the mysql library

If you ask how you can empty a table from MySQL database, many people will probably say, quite simply, just use drop table t_test statement. This is really the newborn calf is not afraid of tigers, you really think so simple, go to the online business library drop a 1TB size table, resulting in a long time of business can not access the database, more serious, resulting in database crash, downtime is possible.

Let's talk about what's behind the drop table statement, and after the statement is executed, there are two main things to do

1, clear the Buffer Pool buffer

At drop table the innodb engine cleans up the block pages that correspond to the table in each buffer pool instance, and to avoid the impact on the system, the cleanup operation here is not the real flush but rather the pages involved are removed from the flush queue. H owever, during the removal process, the deletion process holds the global lock for each buffer pool pool and then searches for the corresponding page in the buffer pool to remove it from flush list If there are too many pages in buffer pool that need to be searched and deleted, the traversal time increases, causing other transaction operations to be blocked and, in severe cases, the database to lock.

(Recommended course: MySQL tutorial.) )

One thing to note here is that if the buffer pool is set up very well, it will cause the traversal time to grow Clean up the buffer buffer pool which also includes cleaning up the data that AHI contains for this table, AHI functionality is not much to say here, mainly when the hierarchy of b+tree becomes higher, in order to avoid b+tree layer-by-layer search, AHI can query the corresponding data page directly according to a retrieval condition, skipping the step of layer-by-layer positioning. Second, THE AHI TAKES UP 1/16 OF THE SIZE OF buffer pool AND IF THE ONLINE TABLE DATA IS NOT PARTICULARLY LARGE AND NOT SUPER-HIGH CONCURRON, IT IS NOT RECOMMENDED TO TURN ON AHI, CONSIDER TURNING OFF AHI FEATURE

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 data files, if the data files are too large, the deletion process produces a large number of IO and takes more time, causing disk IO overhead to soar and CPU loads to be too high, affecting the operation of other programs. One of my good mates deleted a 1TB table in the online library, and for 20 minutes, the database was unresponsive, and finally the library crashed and restarted.

Now that you know drop drop table has done 2 things, optimize for these 2 things

On Buffer Pool the Buffer Pool buffer, to reduce the size of the buffer pool you can reasonably set innodb_buffer_pool_instances parameter, reduce the buffer pool block list scan time, and turn off AHI feature

In Step 2, you can cleverly take advantage of linux hard connection features to delay deleting real physical files.

First look at the hard-link diagram of the linux system

When multiple file names point to the same INODE at the same time, the reference number of this INODE N >1, and deleting any one of the file names will be quick. B ecause its direct physical file block has not been deleted. J ust deleted a pointer; When the number of references to INODE is N-1, deleting the file requires that all blocks associated with the file be purged, so it can be time-consuming;

If you create a hard link to the .ibd file of the database table, when you delete the table, when you delete the physical file, you actually delete a pointer to the physical file, so the deletion will respond very quickly, about 1 second

Let's show you what to do

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

Finally, to really delete the physical files, free up the disk space occupied by the files, then the problem is, if the elegant deletion of physical files, here is recommended for everyone coreutils tool set of truncate commands

Of course, you need to install the relevant packages first

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

Once installed, you can write a script that is very elegantly distributed to delete large files, represented by ${i}G 10G at a time

#!/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 ;

Finally, give you a suggestion, do not do drop table operations during peak business hours, be sure to do in the low peak period of business.

(Recommended micro-class: MySQL micro-course.) )

Source: www.toutiao.com/a6863864032139411975/

These are W3Cschool编程狮 about how to drop the 1TB form in the mysql library, I hope to help you.