Delete many rows from a large percona mysql db

Question

I need a fresh opinion on the case. Any thoughts are appreciated.

Input: we have a huge percona mysql (5.5) database that takes a couple of Tb (terabytes). Tables on innodb engine. More than a half (2/3) of that size should be deleted as quick as possible. Also we have master-slave configuration.

As the quickest way to achieve that I am considering the following solution: Execute for each table on the slave server (to avoid production downtime) :

1. Stop replication
2. Select the rows NOT to be deleted into an empty new table that has the same structure as the original table
3. Rename original table to "table_old", new table - to correct name
4. Drop the original table "table_old"
5. Start replication

The problem is that we have a lot of FK constraints. Also I am afraid to break the replication during this process.

Questions: 1) What the potential problems can be with FK constraints in this solution? 2) How do not break replication? 3) Opinions? Alternative solutions?

Thank you in advance.

Answer 1

Your procedure is excellent if you have the disk space.

Disable foreign keys while doing such. (And hope that no one creates an FK violation that won't be caught.)

Do the two renames in a single statement: RENAME TABLE real TO old, new TO real;.

Replication should not be an issue -- except that a single SELECT to do the copy will be cause a big replication delay.

If you don't have the disk space for that, you are in deep weeds. No table structure ever shrinks.

More discussion of big deletes.

That discusses "chunking", which could be adapted to the big SELECT, so that it has less impact on replication.

Or the chunking could be done to do the deletes, without all the rest of the stuff. This also avoids the FK question and the replication delays. However, it does not shrink the resulting table size; it leaves lots of free space. You could then use pt-online-schema-change to merely copy the table over, thereby shrinking the table. Hmmm... I guess this is how to do the deletes and free up disk space without needing extra space. But it won't help if you have innodb_file_per_table=OFF.

Answer 2

Like pt-online-schame-change's steps, you can manually do it:

• Create new table table_new with same schema to table table (the original one).
• Create trigger on table table for INSERT, UPDATE, and DELETE, to write to table_new one.
• Copy data and use Primary Key to limit row number, to avoid long-time lock.
• Once copy finished, lock these two tables and all FK related tables.
• Drop FK constraints.
• Rename table to table_old, and rename table_new to table.
• Add FK constraints back.
• Drop trigger on table_old.
• You can drop table_old once you confirm everything is okay.

This will reduce production downtime to minimal. I have done this on a 100GB table before (but without Foreign Keys) and it worked very well.