By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,813 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,813 IT Pros & Developers. It's quick & easy.

Speed of InnoDB DELETEs on large tables

P: n/a
I am finding delete queries on large InnoDB tables very slow - are
there ways to speed this up?

I have a table with about 100 million rows:

I am trying to delete just a few of these rows (the following select
takes a couple of seconds):
SELECT count(*) -> FROM UserSnap
-> WHERE LogDate<now() - INTERVAL 750 DAY;
+----------+
| count(*) |
+----------+
| 308969 |
+----------+
DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;
That delete query takes hours to run. The structure of the table is:
desc UserSnap;

+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| LogDate | datetime | | PRI | 0000-00-00 00:00:00 | |
| Period | tinyint(4) | | PRI | 0 | |
| UserName | varchar(50) | | PRI | | |
| RateType | varchar(50) | | PRI | default | |
| Rate | float | YES | | NULL | |
+----------+-------------+------+-----+---------------------+-------+

Any suggestions on why this is slow, and what to do about it?
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I too discovered that InnoDB tables are horrible when it comes to deletions.

I have found that increasing the key_buffer_size will speed up deletes on
very large tables. Also, the number of indexes on a table directly affects
the performance of the delete.

Another thing to be concerned about is data file fragmentation and slow
overall performance for InnoDB tables. If you have a table that
continuously grows and you want to delete the oldest records, oldest is
maintained by insertion time, then you can use an auto_increment column as
your table index. The count of records in such a table is very fast to
compute, and deletion by this index can be fast as well.
"Jeremy Howard" <j+******@howard.fm> wrote in message
news:27*************************@posting.google.co m...
I am finding delete queries on large InnoDB tables very slow - are
there ways to speed this up?

I have a table with about 100 million rows:

I am trying to delete just a few of these rows (the following select
takes a couple of seconds):
SELECT count(*)

-> FROM UserSnap
-> WHERE LogDate<now() - INTERVAL 750 DAY;
+----------+
| count(*) |
+----------+
| 308969 |
+----------+
DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;


That delete query takes hours to run. The structure of the table is:
desc UserSnap;

+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------------------+-------+
| LogDate | datetime | | PRI | 0000-00-00 00:00:00 | |
| Period | tinyint(4) | | PRI | 0 | |
| UserName | varchar(50) | | PRI | | |
| RateType | varchar(50) | | PRI | default | |
| Rate | float | YES | | NULL | |
+----------+-------------+------+-----+---------------------+-------+

Any suggestions on why this is slow, and what to do about it?

Jul 20 '05 #2

P: n/a
"Steve McGregory" <st************@yahoo.com> wrote in message news:<cB****************@newssvr22.news.prodigy.co m>...
I too discovered that InnoDB tables are horrible when it comes to deletions.

I have found that increasing the key_buffer_size will speed up deletes on
very large tables. Also, the number of indexes on a table directly affects
the performance of the delete.
OK I'll try that - thanks.
Another thing to be concerned about is data file fragmentation and slow
overall performance for InnoDB tables. If you have a table that
continuously grows and you want to delete the oldest records, oldest is
maintained by insertion time, then you can use an auto_increment column as
your table index. The count of records in such a table is very fast to
compute, and deletion by this index can be fast as well.
That's a great idea - exactly the situation I'm in. I'll give that a
try - something like 'SELECT @m_id=MAX(ID) FROM tbl WHERE
LastUpdate>@time; DELETE FROM tbl WHERE ID<@m_id;' I guess...
"Jeremy Howard" <j+******@howard.fm> wrote in message
news:27*************************@posting.google.co m...
I am finding delete queries on large InnoDB tables very slow - are
there ways to speed this up?

I have a table with about 100 million rows:

I am trying to delete just a few of these rows (the following select
takes a couple of seconds):
SELECT count(*)

-> FROM UserSnap
-> WHERE LogDate<now() - INTERVAL 750 DAY;
+----------+
| count(*) |

+----------+
| 308969 |
+----------+
DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;


That delete query takes hours to run. The structure of the table is:
desc UserSnap;

+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------------------+-------+
| LogDate | datetime | | PRI | 0000-00-00 00:00:00 | |
| Period | tinyint(4) | | PRI | 0 | |
| UserName | varchar(50) | | PRI | | |
| RateType | varchar(50) | | PRI | default | |
| Rate | float | YES | | NULL | |
+----------+-------------+------+-----+---------------------+-------+

Any suggestions on why this is slow, and what to do about it?

Jul 20 '05 #3

P: n/a
You got it, however MySQL is horrible with < than arguments on a query. I'd
recommend that you
use a between;

SELECT @l_id=MIN(ID) FROM tbl; SELECT @m_id=MAX(ID) FROM tbl WHERE
LastUpdate>@time; DELETE FROM tbl WHERE ID between @l_id and @m_id;

Also, when I'm deleting out of our system, usually in the order of many
millions at a time, I
split the deletion into chunks.

"Jeremy Howard" <j+******@howard.fm> wrote in message
news:27**************************@posting.google.c om...
"Steve McGregory" <st************@yahoo.com> wrote in message

news:<cB****************@newssvr22.news.prodigy.co m>...
I too discovered that InnoDB tables are horrible when it comes to deletions.
I have found that increasing the key_buffer_size will speed up deletes on very large tables. Also, the number of indexes on a table directly affects the performance of the delete.


OK I'll try that - thanks.
Another thing to be concerned about is data file fragmentation and slow
overall performance for InnoDB tables. If you have a table that
continuously grows and you want to delete the oldest records, oldest is
maintained by insertion time, then you can use an auto_increment column as your table index. The count of records in such a table is very fast to
compute, and deletion by this index can be fast as well.


That's a great idea - exactly the situation I'm in. I'll give that a
try - something like 'SELECT @m_id=MAX(ID) FROM tbl WHERE
LastUpdate>@time; DELETE FROM tbl WHERE ID<@m_id;' I guess...
"Jeremy Howard" <j+******@howard.fm> wrote in message
news:27*************************@posting.google.co m...
I am finding delete queries on large InnoDB tables very slow - are
there ways to speed this up?

I have a table with about 100 million rows:

I am trying to delete just a few of these rows (the following select
takes a couple of seconds):

> SELECT count(*)
-> FROM UserSnap
-> WHERE LogDate<now() - INTERVAL 750 DAY;
+----------+
| count(*) |

+----------+
| 308969 |
+----------+

> DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;

That delete query takes hours to run. The structure of the table is:

> desc UserSnap;

+----------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------------------+-------+
| LogDate | datetime | | PRI | 0000-00-00 00:00:00 | |
| Period | tinyint(4) | | PRI | 0 | |
| UserName | varchar(50) | | PRI | | |
| RateType | varchar(50) | | PRI | default | |
| Rate | float | YES | | NULL | |
+----------+-------------+------+-----+---------------------+-------+

Any suggestions on why this is slow, and what to do about it?

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.