11

We have a mysql table that at any given time has about 12 million rows. We need to delete old data to keep the size of the table somewhat manageable.

Right now we are running this query daily, at midnight, using a cron job:

DELETE FROM table WHERE endTime < '1393632001'

The last time the query ran it examined 4,602,400, took over 3 minutes and CPU went through the roof.

CPU spiking at midnight

What can we do to keep the CPU, synchronous db connections, disk cue depth etc from spiking unreasonably while still clearing old data?

PS: You will notice that the query is actually happening at a fairly inopportune time in our usage cycle. Assume that we have already shifted the query's timing to occur at the lowest point of usage each day. Also, there is no index on "endTime" and I would prefer to keep it that way if possible because there is a ton of data being inserted very regularly, and not much lookup.

11
  • maybe Use cron jobs to delete every 10 minutes and 100k per round or every 5 minutes 50k per round
    – Genus
    Mar 4, 2014 at 0:50
  • smaller chunks on a more regular basis?
    – Dagon
    Mar 4, 2014 at 0:50
  • ok, but it seems like that might just cripple our user experience for longer periods of time :) anything we can do query / design wise?
    – user417669
    Mar 4, 2014 at 0:52
  • 1
    186k users, no dedicated db guy?
    – Dagon
    Mar 4, 2014 at 1:09
  • 1
    You will get better answers on "Database Administrators" Mar 4, 2014 at 1:18

1 Answer 1

14

The solution to your problem is a MySQL capability called "partitioning". The documentation is here.

What partitioning does is store a single table in separate "partitions". These are defined by a particular expression, usually a column value or range. In your case, this would probably be based on endTime -- assuming that it is known when a record is created and it doesn't change.

You would store a day's worth of endTime in each partition. Then the deletion step would be truncating a partition rather than deleting a bunch of rows in a big table. The partition truncation would be a much faster method.

2
  • wow, that was incredibly helpful, and seems like a perfect solution. Time to read up on partitioning! Thanks!
    – user417669
    Mar 4, 2014 at 1:02
  • Although partitioning might be a good solution beware of overhead - it may slow down your queries significantly. Besides truncate table is not instant either. I would consider pt-archiver . You can solve your problems with spikes and keep your table as simple as it is now
    – akuzminsky
    Mar 4, 2014 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.