0

I'm working with MySQL 5.7 on Windows.

I've got several tables to clean data from. Largest table's ibd file is 300GB in size and it has almost 1.5 billion lines. I need to leave about 290 million rows in it. So a very large chunk needs to be removed.

From reading the docs there are 2 ways

  1. DELETE statement and OPTIMIZE TABLE after that
  2. copying data to a new table, dropping the old one and renaming the new one.

Second option seems much better in this case, but it there any potential issues to look our for? Downtime is not really an issue.

Another question, I saw that delete statement on big tables (deleting 1 million+ rows) can get stuck and then cause ibdata to grow (hundreds of MB), even though innodb_file_per_table option is on.

I assume that has to do with temporary tables somehow, but can't find an explanation. Any ideas?

2 Answers 2

1

The best way to have done that is to create a table with the 290M rows, then use RENAME TABLE and DROP the old 1500M-row table. This is like your choice 2, except that it renames before dropping. This rearrangement avoids the slight chance of losing the data.

Your choice 1 is very bad because it will save the 1210M rows for possible recovery or ROLLBACK. Very costly, very slow, uses lots of disk (somewhere).

I discuss that, and other options, in http://mysql.rjweb.org/doc.php/deletebig

That also show ways to "chunk" a DELETE (or UPDATE) so that it does not bloat the undo/redo log.

0

Similar to the answer from Rick James, a method that I often use is Dump⇢Truncate⇢Insert:

  1. Disable foreign key checks (if applicable):
    SET FOREIGN_KEY_CHECKS=0;
    
  2. Dump the table using a WHERE clause:
    mysqldump -u {username} -p {dbname} {tablename} --where="created_at >= '2021-01-01 00:00:00'" > tablename.sql
    
  3. Truncate the table:
    TRUNCATE TABLE {tablename};
    
  4. Reload the data:
    mysql -u {username} -p {dbname} < tablename.sql
    
  5. Re-enable the foreign key checks (if applicable):
    SET FOREIGN_KEY_CHECKS=1;
    

The caveat here is that there is no simple way to back-populate a little bit more data from the previous incarnation of the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.