10

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.

|improve this question

migrated from stackoverflow.com Mar 4 '14 at 14:19

This question came from our site for professional and enthusiast programmers.

  • maybe Use cron jobs to delete every 10 minutes and 100k per round or every 5 minutes 50k per round – Genus Mar 4 '14 at 0:50
  • smaller chunks on a more regular basis? – Dagon Mar 4 '14 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 '14 at 0:52
  • 1
    186k users, no dedicated db guy? – Dagon Mar 4 '14 at 1:09
  • 1
    You will get better answers on "Database Administrators" – James Anderson Mar 4 '14 at 1:18
13

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.

|improve this answer
  • wow, that was incredibly helpful, and seems like a perfect solution. Time to read up on partitioning! Thanks! – user417669 Mar 4 '14 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 '14 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy