Deleting huge chunks of data from mysql innodb

Question

I need to delete a huge chunk of my data in my production database, which runs about 100GB in size. If possible, i would like to minimize my downtime.

My selection criteria for deleting is likely to be

DELETE * FROM POSTING WHERE USER.ID=5 AND UPDATED_AT<100

What is the best way to delete it?

• Build an index?
• Write a sequential script that deletes via paginating through the rows 1000 at a time?

Answer 1

You can try to use method mentioned in mysql doc:

1. Select the rows not to be deleted into an empty table that has the same structure as the original table:

   INSERT INTO t_copy SELECT * FROM t WHERE ... ;

2. Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:

   RENAME TABLE t TO t_old, t_copy TO t;

3. Drop the original table:

   DROP TABLE t_old;

Answer 2

If at all possible use row level binary logging rather than statement level binary logging (it reduces the number of locks) at least during this operation. Perform your deletes in batches (1000 is a decent size). Use the primary key as a criteria to delete each batch and order by the primary key (so that you delete rows that are physically close to each other).