Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to drop the 1TB form in the mysql library". The editor shows you the operation process through an actual case. The method of operation is simple and fast, and it is practical. I hope that this article "how to drop the 1TB form in the mysql library" can help you solve the problem.

1. Clear the Buffer Pool buffer

During drop table, the innodb engine cleans up the corresponding data block pages of the table in each buffer pool instance. In order to avoid the impact on the system, the cleanup operation here is not a real flush, but the pages involved are removed from the flush queue. However, during the removal process, the deletion process holds the global lock for each buffer pool, and then searches the corresponding page in the buffer pool to delete it from the flush list. If too many pages need to be searched and deleted in buffer pool, the traversal time increases, which results in other transaction operations being blocked and, in severe cases, database locking.

(recommended course: MySQL tutorial)

There is one more thing to pay attention to here. If the buffer pool of the database is set large, it will lead to longer traversal time to clean up the buffer pool, including cleaning the data contained in the AHI. The function of AHI is not to say much here, mainly 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 according to a certain search condition and skip the step of layer-by-layer positioning. Secondly, AHI will occupy the size of the buffer pool of 1Compact 16. If the data of the online table is not very large and is not ultra-concurrency, it is not recommended to enable AHI. You can consider turning off the AHI feature.

Mysql > SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index' +-- +-+ | Variable_name | Value | +-+-+ | innodb_adaptive_hash_index | ON | +- -+-+ 1 row in set (0.01sec) 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.01sec) 2, Delete the corresponding disk data file ibd

When deleting a data file, if the data file is too large, the deletion process will generate a large amount of IO and take more time, resulting in a surge in disk IO overhead and high CPU load, affecting the operation of other programs. A good friend of mine once deleted a 1TB-sized table in the online library. As a result, the database did not respond for 20 minutes. Finally, the library crashed and restarted.

Now that you know that drop table has done two things, optimize for the above two things.

On clearing the Buffer Pool buffer, in order to reduce the size of the buffer pool, we can set the innodb_buffer_pool_instances parameter reasonably, reduce the scanning time of buffer pool block list, and turn off the AHI function at the same time.

In step 2, you can skillfully take advantage of linux's hard connection feature to delay the deletion of real physical files.

When multiple filenames point to the same INODE at the same time, the number of references to the INODE is N > 1, and deleting any one filename will be quick. Because its direct physical file block has not been deleted. Only a pointer has been deleted; when the reference number of INODE is Number1, deleting a file needs to clear all data blocks related to the file, so it will 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 will actually delete a pointer to the physical file, so the response speed of the delete operation will be very fast, about 1 second.

Let's demonstrate the specific operation.

First create a hard link to the table file ln t_test.ibd t_test.ibd.bak delete the table drop table t_test

Finally, to really delete the physical files and free the disk space occupied by the files, then the problem is: if you delete the physical files gracefully, here we recommend the truncate command in the coreutils toolset.

Of course, you need to install the relevant software package first.

Wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.29.tar.xz decompresses tar with non-root-xvJf coreutils-8.29.tar.xzcd coreutils-8.29./configuremake uses root for make install

Once installed, you can write a script that elegantly distributes and deletes large files. ${I} G means delete 10g at a time.

#! / bin/bashTRUNCATE=/usr/local/bin/truncatefor I in `seq 2194-10 10`; do sleep 2$ TRUNCATE-s ${I} G / data/mysql/t_test.ibd.hdlk donerm-rf / data/mysql/t_test.ibd.hdlk. That's all for "how to drop the 1TB form in the mysql library". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.