Buy Percona ServicesBuy Now!

Delete many rows from a large percona mysql db

  • Filter
  • Time
  • Show
Clear All
new posts

  • Delete many rows from a large percona mysql db


    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) :

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

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

    1) What the potential problems can be with FK constraints in this solution? I do not want to disable FK checks.
    2) How do not break replication?
    3) Opinions? Alternative solutions?

    Thank you in advance.