We have a logging table that contains a months worth of data (about 10Million records) and we run a clean up script everyday that deletes a days worth of records (about 250k) so that we only have the last 31 days worth of information retained. This is taking hours to run!!
OS: Ubuntu 32bit version
MySQL version: 5.0.51a-3ubuntu5.1-log
Some (hopefully) useful MySQL info has been included in the info.txt along with output from top, vmstat and iostat
We have tried various DELETE querys
DELETE FROM log WHERE DATEDIFF(current_date(),dt)>31;
and even added a AUTO INC index called myid to see if that speeds up things
SELECT @l_id:=MIN(myid) FROM log;
SELECT @m_id:=MAX(myid) FROM log WHERE DATEDIFF(current_date(),dt)>31;
DELETE FROM log WHERE myid between @l_id and @m_id;
A select works very fast
mysql> SELECT count() FROM cpelog WHERE myid between @l_id and @m_id;
| count() |
| 259200 |
1 row in set (0.19 sec)
What can we do to speed up the delete?
Do I have too many indexes as I see that the index size is is greater that the table data size and approaching the 4GB physcial limit to my RAM?I see that that my SWAP does not seems to be used though so sure if that is the problem.
Any help most appreciated.