Speed of InnoDB DELETEs on large tables

[Jeremy Howard]

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?

[Steve McGregory]

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?

[Jeremy Howard]

"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?

[Steve McGregory]

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?