MySQL Bugs: #46959: Delay in simple create table on ext4

Bug #46959	Delay in simple create table on ext4
Submitted:	27 Aug 2009 16:52	Modified:	22 Oct 2009 23:10
Reporter:	shane adams (OCA)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.1.37	OS:	Linux (Centos 5.3)
Assigned to:		CPU Architecture:	Any
Tags:	ext4 ddl

Description:
Hi,

I tracked down a puzzling delay [sometimes taking over a second] on a simple create table.  I believe its due to ext4 and not mysql specifically but I thought I'd report it anyway just to make sure.

Specs:  hardware [adaptec] raid 10 scsi 15k rpm.  25 gig ram etc.  

ext4 on centos 5.3

# Query_time: 1.502743  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use db13122;
SET timestamp=1249596412;
CREATE TABLE `table13169_scrub` (id INTEGER NOT NULL AUTO_INCREMENT , col13173_scrub BLOB, col13172_scrub BLOB, col13170_scrub BLOB, col13171_scrub BLOB, problems TEXT, import_id INTEGER, PRIMARY KEY(id)) TYPE=InnoDB;

Moving my data files to an ext3 partition eliminated the problem.  I noticed this as it was taking half a minute to create an empty database schema on a pretty beefy system with no load or data.  

Random queries are getting logged as well:

# User@Host: admin[admin] @  [10.17.175.130]
# Query_time: 1.310168  Lock_time: 0.000030 Rows_sent: 0  Rows_examined: 0
use admindb;
SET timestamp=1249670932;
DELETE FROM SessionsJDBCStore WHERE id = '5AEABE404C1F2618C3CAA3410ED832E6.app2'  AND app = '/Catalina/localhost/MDB';

There were perhaps 10 rows in the table when this delete was issued.

How to repeat:
Run mysql on ext4.  Start creating some tables or inserting some data.  You will [at least I did] see occasional queries taking a 1+ seconds that normally would never take that much time.

Suggested fix:
I just wanted to let you guys know whats going on here given that it seems largely related to ext4 and not mysql.  Perhaps this report will be useful to someone.

I really think it's a hardware/linux/ext4 thing.

Thank you for the bug report. I had Fedora Core 11 with ext4 and I didn't noticed that behavior, anyway could you please print here some output sample you got and describe your hardware too. Thanks in advance.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have noticed something similar. 

1) I had Ubuntu 9.04 (ext3) with default mysql-server-5.1 (5.1.31)
2) Now, I installed Ubuntu 9.10 (ext4) with default mysql-server-5.1 (5.1.37)

I wanted to try ext4 but I got stuck with this issue, so I might have to revert to ext3.

To reproduce this issue just install fresh the two combos on same machine and try a performance benchmark for any medium mysql dump(structure + data).

Hardware info of the system

Attachment: hardinfo-ubuntu9.10_ext4.tar.gz (application/x-gzip, text), 8.94 KiB.

I disabled the write barrier mode on the ext4 partition and that fixed it

/dev/sda3 on / type ext4 (rw,errors=remount-ro,barrier=0)