How to quickly remove multiple rows from a large database in MySQL

As you know, all system administrators are divided into two categories. Those who are already doing backups and those who are not yet.

Like them, database administrators are also divided into two categories, those who have already started the deletion procedure on a large database with the InnoDB table type, and those who are yet to come.


')
Of course, in theory, everyone knows that due to the peculiarities of InnoDB, deletion may be a long one, but this knowledge is akin to the fact that "you need to make backups . " Many are aware of these simple truths, just stepping on a rake.

To understand, deleting 350M records in a table for 500M records may take more than two days . The second rake, which many attack, is an attempt to nail the request. As we all remember, InnoDB is a transactional engine, so if you try to nail a request, it will try to roll back the changes, which may take longer than the request was executed .

How to make it so that it was not painfully painful? Welcome under the cut!

1. If you ALREADY started the removal and now you want to estimate approximately how much has already been done and how much is left, use SHOW ENGINE INNODB STATUS; Get a long conclusion. Look for your team in it and see undo log entries, this will be the number of entries already processed.

---TRANSACTION 1 4141054098, ACTIVE 191816 sec, OS thread id 36004918272 updating or deleting, thread declared inside InnoDB 84 mysql tables in use 1, locked 1 686063 lock struct(s), heap size 88520688, undo log entries 229144332 MySQL thread id 56087872, query id 2202164550 1.1.1.2 database updating DELETE FROM table WHERE UNIX_TIMESTAMP(moment) < 1498712335 - 365 * 86400 AND UNIX_TIMESTAMP(moment) > 0 

Further, depending on how much has already been done, you decide whether to wait until the bitter end or interrupt the request.

2. If you are only planning to start removal, use LIMIT.
The number of entries is chosen empirically, say, on a not very powerful server, I used LIMIT 5000, on a more powerful LIMIT 50000. Start with small values ​​and increase them as necessary.

Solution Minuses:

a) you need to start the query with your hands the necessary number of times (well, or make a text file with the required number of lines with the query and run from it.
b) each subsequent request takes longer than the previous one

3. Use pt-archiver from the percona-tools suite.

I would recommend this method for several reasons:

a) he is fast ;
b) it can be interrupted at any time;
c) in it you can monitor the progress of the operation.

Example:

 pt-archiver --source h=127.0.0.1,D=build4,t=b_iblock_element \ --optimize s --purge --where 'TAGS LIKE "%%"' \ --limit 1000 --commit-each --progress 500 --charset "CP1251" 

In principle, the keys are pretty obvious, however, I will go over them:

--source - describes the connection. Host, base and table. If necessary, you can add a login and password (in the example I use the credits from ~ / .my.cnf);
--optimize - optimizes the source table, or the one to which the data is transferred. Since in this case I don’t transfer, but delete the data, I optimize the source (s) table. In principle, this is not necessary;
--purge - initially the utility is designed to transfer data to another table (or to a file). But you can just delete the line;
- where is the usual SQL condition by which rows will be selected for deletion;
--limit 1000 - process 1000 lines at a time (more is possible, depending on the performance of your server);
--commit-each - make a commit after the number of lines specified in --limit;
--progress 500 - output progress every 500 lines (again, it makes sense to select this parameter individually);
--charset - encoding. If only ASCII encoding is used, it is easier to specify --no-check-charset. Separately, I mention that it is necessary that the console locale matches the specified charset, otherwise the error will not be displayed, but the lines will not be processed.

I hope this small note seemed useful (or at least interesting) to you.
If you have something to add, correct, or just speak - write!

In a separate line, I want to thank LJ-user merkwurdig , who raised the discussion of this problem and made me remember how I ran along the same rake, LJ-user svetasmirnova , which always comes to the aid of difficulties with MySQL and the ru_root community, thanks to which this small article.