Use hard links and truncate to reduce the impact of drop table on the online environment

As we all know, drop table will seriously consume server I/O performance. If the size of the table to be dropped is large, it may even affect the normal online operation.

First, let's take a look at why a table with a large drop capacity affects online services.

Run drop table directly. mysql deletes all table definitions and table data, including physical files on the disk and memory data in the buffer pool.

This is done in two steps. The first step is to delete the buffer pool, which involves the lock of table_cache. If the lock of table_cache is held, other queries cannot be executed. This is especially serious before innodb_per_table is available. In addition, the lazy drop table function is added after mysql5.5.23 to solve the problem of mutex on the LRU list. The central idea is to lock, find the page to be deleted, delete 1024 pages, release the lock, let other threads work, and then loop. Percona's lazy drop process is more elegant. It locks the page to be deleted, marks the page, releases the lock, and deletes the page slowly in the background.

The next step is the second step. This step consumes more time in large-capacity tables, that is, deleting physical files on the OS. As we all know, rm a gb file on ext3 is very time-consuming. This is caused by the Data Structure Stored in ext3. If a large file is stored, the I _block of ext3 cannot be directly stored, multi-layer Nesting is required for full storage. In this case, many ing layers and multi-layer ing won't be stored sequentially, resulting in a large random IO, this results in a very slow process of deleting physical files. In this case, it is recommended to upgrade to ext4, because ext4 uses extent to allocate storage space than ext3, and its biggest advantage is sequential storage.

Ext3:

Ext4:

 

1. Create a hard link.

 table.ibd table.idb.hdlk

    tablename；

truncate -s ** filename

 

In fact, you don't need to talk about hard links and drop tables. After a hard link is established, mysql will think that after the rm hard link file is established, the operation is complete, it does not actually Delete physical files, thus improving the speed. However, for the server, the actual physical files are still there. If you run the rm manually, there will still be a lot of io impact. At this time, the truncate tool is used. This tool will be gradually deleted based on the specified size, which will minimize the impact on IO.

 options are mandatory  -c, --no-create        -o, --io--r, --reference=-s, --size=SIZE        set or adjust the ----, K , MB *, M *, and so on + reduce by, `</-