MySQL Bugs: #69738: Huge MySQL performance hit when running on CentOS 6x

Bug #69738	Huge MySQL performance hit when running on CentOS 6x
Submitted:	13 Jul 2013 14:48	Modified:	5 Aug 2013 15:20
Reporter:	Michael Reed	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.69, 5.5, and 5.6	OS:	Linux
Assigned to:	Paul Dubois	CPU Architecture:	Any

Description:
MySQL running on default installation of CentOS 6X results in 50 times slower performance than on CentOS 5X.

How to repeat:
Install CentOS 5.9 (or 5.8) using URL install on a physical (not VPS) computer (maybe this doesn't mater), and select all default options.
Make no server configuration changes.
Install MySQL 5.0.95 using yum install mysql-server which uses CentOS's native repository
Execute mysql_secure_installation.
Created a very simple script called test.sql which creates 40 tables and inserts 5 rows in each table (I will attach)
Execute time mysql -uroot -pmyPassword < test.sql.

My results were:
real 0m0.112s
user 0m0.008s
sys 0m0.005s

Do the same with CentOS 6.4 (or 6.3). Note that CentOS 6X's native repository will install MySQL 5.1.69. You can also install MySQL 5.5 using yum with IUS's repository as well as MySQL 5.6 using RPM directly from MySQL, however, had no change in performance.

My results were:
real 0m8.140s
user 0m0.012s
sys 0m0.009s

Similar complaints appear at:
http://bugs.mysql.com/bug.php?id=46959
http://planet.mysql.com/entry/?id=23027
http://michal.hrusecky.net/2009/11/barriers-ext4-mysql/
https://bugzilla.novell.com/show_bug.cgi?id=549534
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=706489

Suggested fix:
Fixes apply to Centos 6X. Suggested fix is to change filesystem. Note that this is not desired as barriers improve data integrity should the server go down.

filesystem: ext3, barrier=1
real 0m5.810s
user 0m0.007s
sys 0m0.018s

filesystem: ext4, barrier=0
real 0m0.455s
user 0m0.006s
sys 0m0.006s

filesystem: ext3, barrier=0
real 0m0.165s
user 0m0.008s
sys 0m0.007s

Simple test SQL file

Attachment: test.sql (application/octet-stream, text), 12.26 KiB.

Note that my server does NOT have a RAID hard drive.

Correct behavior syncing to disk is not a bug. It's unclear what is the point of this report?

The point of this report is that an out of the box CentOS 6X installation executes MySQL 70 times slower than CentOS 5X.  I am coming to realize this is probably not a deficiency of MySQL, but of CentOS (and other distributors), and apologize for the false alarm.  I do believe, however, that MySQL should better document this potential behavior.

Hi, thanks for your report. However, this is something probably best reported to CentOS so that it can be addressed at the source of the issue.