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:
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:4.1 OS:Linux (Linux)
Assigned to: Marko Mäkelä CPU Architecture:Any

[10 Dec 2004 0:18] B Jones
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.
[10 Dec 2004 2:55] Heikki Tuuri

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.


[10 Dec 2004 3:33] B Jones
Thanks for your quick response.

[15 Dec 2004 0:33] B Jones
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.

[15 Dec 2004 15:10] Heikki Tuuri

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




during such slow TRUNCATE.


[11 Jan 2005 15:10] Marko Mäkelä
I've now implemented fast TRUNCATE TABLE, which will hopefully be included in MySQL 5.0.3.
[14 Jan 2005 13:52] Marko Mäkelä
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

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.
[17 Jan 2005 10:19] Marko Mäkelä
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.
[8 Feb 2005 23:09] B Jones
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.
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.
[9 Feb 2005 4:07] B Jones
Hmmm... Stand corrected.  Over time it deteriorated, and is now at 11 minutes.  (This is 4.1.9)
[13 Feb 2005 22:50] B Jones
Now at 15 minutes 30 seconds,so apparently degrades markedly over time.
[14 Feb 2005 9:57] Marko Mäkelä
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.
[16 Feb 2005 4:20] B Jones
> 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.

[16 Feb 2005 4:33] B Jones
Now hit 30 minutes... Is it possible for us to apply the fix ourselves to 4.1.9?


mysql> delete from purchases;
Query OK, 1425822 rows affected (30 min 40.44 sec)
[16 Feb 2005 8:26] Marko Mäkelä
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.