Bug #64594 Performance regression in InnoDB table truncation
Submitted: 8 Mar 2012 16:21 Modified: 15 Apr 2012 14:02
Reporter: Bjørn Trondsen Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version:5.5.21 OS:Linux (Any)
Assigned to: CPU Architecture:Any
Tags: innodb truncate performance linux

[8 Mar 2012 16:21] Bjørn Trondsen
I was instructed to file a bug report after a brief discussion in the "Performance" -forum.

I use Arch Linux and after doing a full system upgrade where I upgraded the linux kernel from 3.0.something to 3.2.8, as well as updated all installed packages including mysql, the performance of innodb truncation dropped significantly. An Ubuntu user has reported the same thing (see link below).

Running "truncate table_name" on an empty table takes 50ms, it shouldnt take more than 1-3ms. This is a big problem for me because I truncate databases a lot when running automated test suites when programming. Truncating an almost empty database with 20 tables take 1 second on InnoDB. On MyISAM it takes 5ms. InnoDB is now 200 times slower than MyISAM so something must be wrong. 

I've tried downgrading to 5.5.14, 5.5.11 and 5.1.54 without seing any changes. I reckon the issue was caused by a change in the linux kernel or a core library.

The issue has been discussed here:

How to repeat:
Truncate an empty innodb table on the latest linux with the latest mysql. It will take ~50ms.
[13 Apr 2012 12:18] Bjørn Trondsen
I had to buy a new computer because the old one broke down. On the new machine the issue no longer occurs. The hardware is of course new, the kernel is upgraded to v3.3.1, mysql is upgraded to v5.5.23. It's hard to say what precisely solved the problem ..
[15 Apr 2012 14:02] Valeriy Kravchuk
Thank you for status update. Please, re-open this bug in case of similar problem with 5.5.23+ on your new hardware.