InnoDB in MySQL How to quickly delete large tables of 2T

Small comics

Come on, take a look at the little comic book to edify sentiment

OK, here's the point. Suppose that you have a table ERP if you directly make the following command
At this point all the MySQL related processes will stop until the drop is over and MySQL resumes execution. The reason for this is because, in the drop table, InnoDB maintains a global lock, and the drop is released when the lock is complete.

This means that if during the day, when the traffic is very large, if you do not do any processing measures, the execution of the order to delete the big table, the entire MySQL hanging there, during the deletion of the table, the QPS will seriously decline, and then the product manager came to you for tea. So there is a scene in the comic, you can 12 o'clock in the evening, the dead of night and then deleted.

Of course, some people do not obey, may say: "You can write a delete table stored procedures, in the evening when no amount of traffic when the time to run the line." "

I was startled, think about it, can only say: "We still do not contradicting, or listen to my talk about the industry general practice." "

A hypothesis

First of all, here is a premise, MySQL opened a separate table space, MySQL5.6.7 after the default open.

That is, in the my.cnf, there is such a configuration (these are MySQL optimization knowledge, later to introduce to you)

To view the table space status, use the following command

If the value of innodb_file_per_table is off, it represents a shared table space.

If the value of innodb_file_per_table is on, it is represented by a stand-alone table space.

So, you're going to ask me, what's the difference between a standalone tablespace and a shared table space?

Shared tablespace: All the table data for a database, the index file is all in one file, the file path of the shared tablespace is in the data directory by default. The default file name is: ibdata1 (this file can be expanded into multiple). Note that in this way, operation is super inconvenient. You see, all the data in a file, to the single table maintenance, very inconvenient. In addition, when you do a delete operation, the file will leave a lot of gaps, ibdata1 file will not automatically shrink. In other words, using shared tablespace to store data, you encounter a problem where the space cannot be freed after the drop table.

Stand-alone tablespace: Each table is deployed in a standalone manner, with one. frm table description file and one. ibd file.

. frm file: The metadata for each table is saved, including the definition of the table structure, and the file is not related to the database engine.

. ibd file: A file that holds data and indexes for each table.

Note that in this way, each table has its own independent table space, which makes it easy to move between the different databases in a single table. In addition, the table space can be automatically reclaimed when the drop table operation is performed. After you perform a delete operation, you can defragment the fragment by executing the ALTER TABLE TableName ENGINE=INNODB statement, which reclaims some of the tablespace.

The DataDir in PS:MY.CNF is used to set the data store directory

Well, there's a whole bunch of balabala up here, and I just want to say one thing:

InnoDB in MySQL How to quickly delete large tables of 2T
So, the premise that I mentioned at the beginning, MySQL needs to open a separate table space. This hypothesis, hundred 90 of the case is set up. If you really meet, your company's MySQL is a shared table space situation, you and your family's operations to talk about the heart, ask why use shared table space.

Correct posture

Suppose we have datadir =/data/mysql/, in addition, we have a database, named MyTest. In the database mytest, there is a table, called ERP, that executes the following commands

Get the following output (I filtered it)

The role of FRM and IBD has been described above. Now the erp.ibd file is too large, so the deletion is stuck.

How to solve this problem?

You need to use the knowledge of hard links in Linux for quick deletion. Let me have some of the contents of "Bird's private Cuisine",

Soft links In fact, we can understand the analogy as a shortcut in Windows, not much introduction, mainly about the hard link.

As for this hard link, I simply say, do not want to post a lot of words come over, looks too tired.

For a file that is actually stored, there is an inode index pointing to the storage file

And then there is a file name that points to the Inode Index

So the so-called hard link is that more than one file name points to the Inode index, with several filenames pointing to the Inode index.

Suppose, this would have another file name pointing to the Inode Index above, which

This time, you did delete the file name (1) operation, the Linux system detected, there is a file name (2) to the Inode Index, so does not really delete the file, but the filename (1) To delete the reference, this step is very fast, after all, just delete the reference. And so the picture becomes

Next, you do delete the file name (2) operation, the Linux system detects that no other file name points to the Inode Index, it will delete the real storage file, this step is to delete the real file, so it is relatively slow.

OK, we are using the above principle.

First, create a hard link for erp.ibd, using the ln command

At this point, the file directory is as follows

You will find that there is one more Erp.ibd.hdlk file, and the Innode of both ERP.IBD and Erp.ibd.hdlk are 2.

At this point, you perform a drop table operation

You'll notice that it's deleted in less than 1 seconds. Because, at this point, there are two file names (ERP.IBD and Erp.ibd.hdlk), and point to a innode. This time, the delete operation is done, just the reference is deleted, so very fast.

So, at this point, the delete has removed the table from MySQL. But the disk space is not released, because there is one file Erp.ibd.hdlk left.

How to delete the Erp.ibd.hdlk correctly?

If you have no experience, you will be sure to answer me, using RM command to delete. It should be explained here that, in production environment, the direct use of the RM command to delete large files, will cause disk IO overhead soar, CPU load is too high, it will affect other programs running.

Then, this time, you should use the truncate command to delete. It should be stated that the TRUNCATE command is in the Coreutils toolset and needs to be installed separately.

Details, we can go to Baidu installation tutorial. In addition, there are articles circulating on the Internet, these articles on the RM and TRUNCATE commands specially tested, truncate command on the disk IO,CPU load almost no impact.

Delete the script as follows

Starting at 2194G, each time you reduce 10G, stop for 2 seconds, continue until the file is only 10G, and then use the RM command to remove the remaining portions.

Other conditions

This refers to what to do if the database is deployed on Windows. This question, I come to answer, is actually not professional enough. Because I have never come across a production environment, MySQL is part of Windows. Assuming that there is a real encounter, Windows has a tool called Mklink, which can create hard links under windows and should be able to do similar functions.

Summarize

The first MySQL article, try the water, this article a little partial operation, the content, small and medium-sized company's research and development is relatively easy to meet. Because small and medium-sized companies do not have a professional DBA, they have to do anything to develop children's shoes. I hope you have something to gain.

InnoDB in MySQL How to quickly delete large tables of 2T