Shulou(Shulou.com)05/31 Report--

This article mainly explains the "solution to the MySQL big table deletion problem". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the solution to the MySQL big table deletion problem".

Summary of table deletion questions

(1), mysql how to delete a table

(2) how to optimize and solve the problem of deleting large tables

II. Principle and optimization of deleting tables

Delete table is divided into two parts in principle:

Buffer pool page cleanup process.

When you delete a table, Innodb clears the corresponding page of the file in buffer pool. For the page cleanup that deletes the table, you only need to delete the page from the flash queue, and you don't need to do flush operation to reduce the impact on the system.

Problem 1: if the buffer pool is very large, or if there are many pages in the buffer pool that need to be flush, then traversing the scanned page will take a long time, causing other transactions to be blocked when using the corresponding buffer pool instance, thus affecting the performance of the entire database.

Optimization: it is difficult to optimize because it involves source code

The process of deleting ibd disk files.

Problem 1: the table file is too large. Deleting directly will instantly take up a lot of IO and cause IO blocking.

Optimization: using hard chain

Principle: files on a disk can be referenced by files of multiple file systems, which are identical and all point to files on the same disk. When we delete any file, it will not affect the real file, but will subtract its reference data by 1. Only when the number of references becomes 1, the file will be deleted again and it will really be deleted. When deleting, the difference between the two cases is obvious, one is to reduce the number of references, and the other is to actually do IO to delete it.

Action:

Ln / data/mydata/my3306/testdb/table1.ibd / data/mydata/my3306/testdb/table1.ibd.hdlk

Ls-lh / data/mydata/my3306/testdb view file references (should be 2)

Question 2: after finishing the hard chain, the real big file deletion problem, direct rm deletion, will cause the instantaneous peak of IO.

Optimization: use the tool to delete a small number of times

Principle: use the truncate of the system file, and the script tool is slowrm

III. Slowrm3.1, demand

Relieve the instantaneous pressure of IO caused by large table deletion

3.2Function of slowrm

Delete large files at the specified speed to reduce the instantaneous pressure of IO

3.3.3.The implementation of slowrm 3.3.1, background description

First briefly introduce the background of slowrm tool development.

We have mentioned in the "linux_mysql_DROP_TABLE procedure" that for the deletion of large tables, hard chains should be established first, and then the table data files should be deleted after drop table.

For data files with large tables, it may reach 10G, 100G, or even larger. Under linux, when such a large file uses rm, it will undoubtedly cause IO resources to be forcibly occupied, as shown by the fact that the io_util of the hard disk is about 100%, which will block other IO operations. To make matters worse, the process of rm a single file is an atomic process, and you can't use kill or kill-9 to kill the rm process. You just have to wait for it to end.

If such a deletion is done on the machine where the busy online service is located, it is likely to have an impact on the online service. Therefore, there needs to be a scheme to smoothly delete large files. In the end, such a scheme has nothing to do with the database, but a general scheme.

3.3.2. Design ideas

For the operation of deleting a large file, the file is truncated a little at a time, truncated several times, and finally until the file size is truncated to 0.

As an example of the following procedure:

Block_size = 1024576bytes

While file_size > = block_size

Begin

Ftruncate (file, new_size)

(where (old_size-new_size = block_size)

Sleep 0.05 seconds

End

Ftruncate (file, 0)

Because since a file in rm is uninterruptible, we break up the deletion process by deleting one file several times, a little bit at a time. Fortunately, linux now provides an API,truncate/ftruncate function that supports this idea, which can truncate the file to any length (). After testing, it is found that one point is truncated each time, truncated several times, and finally until the file size is truncated to 0, so it takes more time to delete large files, but the impact on disk IO is greatly reduced. Therefore, it is feasible to adopt this idea.

3.4.Using slowrm

Slowrm [OPTIONS] FILES

Delete files in a similar way to rm.

3.4.1, options and parameters

The use of this tool mainly involves two parameters:

-b # size

The file size to be truncated each time is in bytes. The parameter value needs to be an integer. The default value is 4096 * 256Bytes (= 1MB).

-s # seconds

The time interval between every two truncation operations, in seconds, and the parameter value can be a decimal. These two parameters are used to control the deletion speed and the pressure on the disk IO during the deletion process. Default value 0.1 second

-h,-- help

View help information.

Enter slowrm directly, or enter slowrm-h or slowrm-- help to view help.

3.4.2. Delete speed

In fact, you can see that by # size * # seconds = # ioBPS Bytes/s

You can calculate the IO throughput (bytes) consumed by the operation per second, that is, approximately the number of bytes reduced by the file per second.

You can see that the default deletion speed is 4096 * 256Bytes / 0.1second= 10MB/s, and the deleted files can be reduced by 10MB per second.

Users can use the above two parameters to control the speed of deletion and the pressure on the disk IO.

3.5.Contrastive use of slowrm and rm

Slowrmrm comments prompt whether to delete

Rm-I is supported by default. The default slowrm asks the user whether to delete each specified file or delete a single file.

Slowrm filerm file same delete multiple files to display the specified file slowrm file1 file2 file3rm file1 file2 file3

Use the wildcard character slowrm file*rm file*

Delete hard chain number greater than 1 delete hard chain and rm delete hard chain actually execute is unlink hard chain number is equal to 1 low speed delete file full speed delete file is essentially the same logic in judging the number of hard chain, but the last hard chain deletion method is different from the forced deletion of files

Slowrm-f

Slowrm-forcerm-f

Rm-force same control deletion speed control slowrm-s 0.1-b 262144 file (2MB/s) does not support

Default speed slowrm file

The corresponding parameter-s 1048576-b 1048576 (10MB/s) generally uses the default parameter to delete other types of file directories. Soft chain does not support it.

Recursively delete directory

Rm-rf directory

It can be seen that, in general, you can delete it using rm.

You can use slowrm when you need a low IO load to delete large files.

Slowrm plays a complementary role to rm.

3.6, Note 3.6.1, delete file type

At present, the tool only supports deleting specific regular files, and cannot delete directories recursively, but it can delete multiple files, such as slowrm * .logmysql.err.2014*.

Soft links, files, etc., cannot be deleted.

3.6.2. Deletion of hard chain

If the number of hard links of the file to be deleted by slowrm is greater than 1, then the hard chain will be deleted directly

If the number of hard chains of the file to be deleted is equal to 1, the file will be truncated step by step in the way of smooth deletion.

3.7. Test use

Make large documents

Start deleting

Size situation

Pressure situation

Device: rrqm/s wrqm/s rUnip s wdeband s rMB/s wMB/s avgrq-sz avgqu-sz await svctm% util

3.8. Daily use

1. Open a screen

Screen-S slowrm_test

2. Start deleting files

Slowrm file

Or specify the speed, which is normal by default (10MB/s)

Thank you for your reading, the above is the content of "the solution to the MySQL table deletion problem". After the study of this article, I believe you have a deeper understanding of the solution to the MySQL table deletion problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.