Mysql – Delete many rows from a large percona thesql db

deleteMySQLmysql-5.5perconareplication

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.

Best Answer

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.

Related Question