MySQL Bugs: #64594: Performance regression in InnoDB table truncation

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:	None
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

Description:
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:
https://groups.google.com/forum/?fromgroups#!topic/ruby-capybara/8LLxs4BGV-E
http://forums.mysql.com/read.php?24,518634,518634#msg-518634

How to repeat:
Truncate an empty innodb table on the latest linux with the latest mysql. It will take ~50ms.

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 ..

Thank you for status update. Please, re-open this bug in case of similar problem with 5.5.23+ on your new hardware.