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

[13 Jul 2013 14:48] Michael Reed
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
[13 Jul 2013 14:49] Michael Reed
Simple test SQL file

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

[14 Jul 2013 16:30] Michael Reed
Note that my server does NOT have a RAID hard drive.
[16 Jul 2013 23:27] Jeremy Cole
Correct behavior syncing to disk is not a bug. It's unclear what is the point of this report?
[17 Jul 2013 13:57] Michael Reed
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.
[5 Aug 2013 15:20] Paul Dubois
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.