MySQL Performance: MySQL => How to speed up DELETE with innodb table

Members

Help

Home

Home » Performance » MySQL » How to speed up DELETE with innodb table

Show: Today's Messages :: Show Polls :: Message Navigator

How to speed up DELETE with innodb table [message #3721]

Wed, 19 November 2008 06:36

TommyDIY
Messages: 7
Registered: January 2008

Junior Member

Hi

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
RAM: 4GB

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.
Cheers
Tom

Attachment: info.txt
(Size: 8.24KB, Downloaded 58 time(s))

Report message to a moderator

Re: How to speed up DELETE with innodb table [message #3724 is a reply to message #3721 ]

Thu, 20 November 2008 09:20

TommyDIY
Messages: 7
Registered: January 2008

Junior Member

Out of curiosity and as a half baked idea towards a solution I also tried the following.

Copy all the the data I want to keep into an identical table and once that is complete drop the old log table and rename the new one

mysql> INSERT INTO log_temp SELECT * from log where DATE_SUB(CURDATE(), INTERVAL 31 DAY) <= dt;
Query OK, 9740800 rows affected (1 hour 8 min 39.67 sec)
Records: 9740800 Duplicates: 0 Warnings: 0

Obviously this is not the solution either as it takes over a hour to run. But it is amazing that it takes nearly as long to copy 9,740,800 rows to a new table as it does to delete 250,000.

My brain is melting here..........

Report message to a moderator

Re: How to speed up DELETE with innodb table [message #3729 is a reply to message #3724 ]

Mon, 24 November 2008 09:06

TommyDIY
Messages: 7
Registered: January 2008

Junior Member

Well I dropped 3 of the indexes on this table only leaving the index on the dt column and got the desired result I was looking for.

mysql> delete FROM log WHERE dt < DATE_SUB(CURDATE(), INTERVAL 31 DAY);
Query OK, 259200 rows affected (6.45 sec)

It would seem that I will have to revisit the usefulness of indexes when it comes to the various queries that are run on this table. As it would seem that 4 is 2 many for the table of this size.

Hope this info is useful to some other sucker

Report message to a moderator

Re: How to speed up DELETE with innodb table [message #3736 is a reply to message #3721 ]

Tue, 25 November 2008 10:51

bstjean
Messages: 2
Registered: December 2006
Location: Canada

Junior Member

Have you tried deleting in "chunks", not all at once, by repeating your delete statement as in :

DELETE FROM log WHERE DATEDIFF(current_date(),dt)>31 LIMIT 5000

*Sometimes* this gives better results. Besides, you ensure your application is somehow responsive all the time (as long as the number of records is not too big)...

One other thing, you might be better with a constant instead of a function call in your WHERE clause.

So something like (assuming we're '2008-11-25':

DELETE FROM log WHERE dt < '2008-10-25'

can use an index on column dt

Report message to a moderator

Previous Topic:	Random user search
Next Topic:	Need help with under-performing query: using where, temporary and filesort

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 16:57:10 EDT 2009

Total time taken to generate the page: 0.01057 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.