MySQL Bugs: #7150: InnoDB TRUNCATE can get very slow

Bug #7150	InnoDB TRUNCATE can get very slow
Submitted:	10 Dec 2004 0:18	Modified:	14 Jan 2005 13:52
Reporter:	B Jones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (Linux)
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
A performance observation rather than a bug:

Sometimes InnoDB can get very slow when truncating tables; the record is 9 
minutes 36 seconds on our entities table holding 20K records.  This is on a dual-CPU server with 3.4GHz CPUs and SCSI RAID HDDs.  For dropping such a small table, even accounting for indexes, this seems kind of slow.  Certainly 9 minutes is way to much.

mysql> truncate enquiries;
Query OK, 28074 rows affected (36.81 sec)

mysql> TRUNCATE accounts;
Query OK, 12127 rows affected (51.91 sec)

mysql> TRUNCATE credit_cards;
Query OK, 2794 rows affected (4.59 sec)

mysql> truncate entities;
Query OK, 20031 rows affected (1 min 41.31 sec)

mysql> truncate contacts;
Query OK, 64968 rows affected (1 min 23.58 sec)

mysql> truncate addresses;
Query OK, 96563 rows affected (3 min 43.94 sec)

How to repeat:
Try repeatedly creating, loading and dropping tables.  May be cummulative.

Suggested fix:
Workaround we're using is not to design databases that require TRUNCATEs.

Hi!

It is in Marko's TODO to do TRUNCATE as DROP + CREATE.

Now it is very slow, DELETE FROM tablename, internally.

Workaround: use DROP + CREATE.

Regards,

Heikki

Thanks for your quick response.

cheers
bj

I know this shouldn't be the case, but we're finding DELETE FROM <table> is much faster than TRUNCATE <table>.  The online MySQL manual says for InnoDB they're the same right?  Curious.

cheers
bj

Hi!

Maybe TRUNCATE is waiting for an exclusive table lock on the table? Do you have queries by other users running on the same table at the same time?

Please print

SHOW PROCESSLIST;

and

SHOW INNODB STATUS;

during such slow TRUNCATE.

Regards,

Heikki

I've now implemented fast TRUNCATE TABLE, which will hopefully be included in MySQL 5.0.3.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix has now been committed to the 5.0 tree and is likely to appear in MySQL 5.0.4. If you are using the innodb_file_per_table option, TRUNCATE TABLE will not yet truncate the *.ibd file. That part is still under development.

Sorry, I wrongly assumed that MySQL 5.0.3 would be very close to release. The fix is likely to be in 5.0.3 already.

After installing from 4.1.7 to 4.1.9 we found that DELETE is much faster; Down from over 16 minutes to 1 minute 29 seconds.
<p>
Aside: Be careful in testing; we were running these two versions simultaneously during testing on different ports, but the MySQL command line client ignores the port specified. That bug has apparently been separately reported.

Hmmm... Stand corrected.  Over time it deteriorated, and is now at 11 minutes.  (This is 4.1.9)

Now at 15 minutes 30 seconds,so apparently degrades markedly over time.

Technically, it would be trivial to backport my fix from MySQL 5.0.3 to the 4.1 tree. However, we do not want to introduce potential bugs in a stable release, unless there are very strong reasons for doing so. There already were a couple of bugs (now fixed) in the new TRUNCATE code, and the feature has not been widely tested by users yet.

> Technically, it would be trivial to backport my fix from MySQL 5.0.3 to the 4.1
> tree. However, we do not want to introduce potential bugs in a stable release,

Understood. Unfortunately we're running a very busy OLTP environment off 4.1,
so we're nervous about upgrading to 5.0 until it's declared stable.
Guess we'll have to wait.

cheers
bj

Now hit 30 minutes... Is it possible for us to apply the fix ourselves to 4.1.9?

cheers
bj

mysql> delete from purchases;
Query OK, 1425822 rows affected (30 min 40.44 sec)

You can obtain the patches from our source repository or http://mysql.bkbits.net/ and try to apply them to the 4.1 tree. If I remember correctly, there are a total of 3 patches (2 bugfixes to the initial patch). Some InnoDB functions have more parameters in 5.0 due to the row_format=compact table format, so you may need to manually resolve that.

I'm planning yet another patch regarding TRUNCATE, so that it would truncate *.ibd files when using innodb_file_per_table.

If you would like to have the feature in 4.1, maybe you should contact sales@innodb.com or the MySQL salesmen about this?

As a workaround, you could use DROP TABLE and CREATE TABLE. By the way, if the table you are truncating is being referenced by foreign key constraints, also the new TRUNCATE TABLE code will resort to DELETE row by row.