3

Basically, we have a table with the following structure:

CREATE TABLE `reqs` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `reqid` BIGINT(20) NOT NULL, `from` BIGINT(20) NOT NULL, `to` BIGINT(20) NOT NULL, `rdate` DATETIME NOT NULL, `state` ENUM('sent','approved','accepted','rejected') NOT NULL, `type` INT(11) NOT NULL, `subtype` INT(11) NOT NULL, `clusterid` INT(11) NOT NULL, `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), INDEX `to_state_clusterid` (`to`, `state`, `clusterid`), INDEX `from_state_clusterid` (`from`, `state`, `clusterid`), INDEX `reqid_to` (`reqid`, `to`), INDEX `from_rdate_type` (`from`, `rdate`, `type`), INDEX `type` (`type`), INDEX `from_state_type_clusterid` (`from`, `state`, `type`, `clusterid`), INDEX `to_state_type_clusterid` (`to`, `state`, `type`, `clusterid`), INDEX `state` (`state`), INDEX `from_to_state_type_clusterid` (`from`, `to`, `state`, `type`, `clusterid`), INDEX `from_rdate_type_clusterid` (`from`, `rdate`, `type`, `clusterid`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB

It's currently around ~250M rows and we'd like to delete 200M+ of them. This is a production database and this table in particular gets dozens of INSERT/UPDATE/SELECT queries every second.

Finally, I don't know if it changes anything, but all SELECTs to this table have USE INDEX statements in them.

What would be the best way to do this with minimal impact to our application?

EDIT: There are 2 DELETE statements to be executed:

DELETE FROM `reqs` WHERE `state` = 'accepted' or `state` = 'rejected'; DELETE FROM `reqs` WHERE `rdate` < '2013-01-01 00:00:00';

These queries are expected to delete ~70M and ~130M rows respectively.

Improve this question
4
  • What would be the DELETE statement? Commented Sep 11, 2013 at 7:48
  • @ypercube I have updated the question. Commented Sep 11, 2013 at 8:12
  • If you have a time slot without activities on this table. Maybe,it will be quickier to rebuild the table/indexes without the uneeded rows. Commented Sep 11, 2013 at 10:23
  • Sadly, no. This table is very active 24/7. Commented Sep 11, 2013 at 10:36

1 Answer 1

Reset to default
3

Any data change action of this size if executed at once will cause a significant logging overhead and will require a lot of additional log space. That in turn will slow down the execution causing an even bigger impact on the rest of the system.

The best way to handle this is to break the delete down into smaller chunks. If you delete between 1000 and 10000 rows at a time and then wait for a few seconds the impact on other transactions should be small. Make sure each delete runs in a separate transaction so that all locks are released during the wait time. However, keep in mind that this will stretch out the delete over several hours or even days.

Also, even though it might sound counter intuitive, make sure there is an index supporting each delete so that MySQL does not have to execute a full table scan each time.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.