Is MySQL 5.6 slower than MySQL 5.5? No, it's faster

MySQL 5.6 is believed to be the best MySQL release ever. In order to find out if it is true or not I ran some benchmarks and I am going to share the results with you in this tutorial. Please note that I only tested with default settings (in both cases) and using sysbench as a benchmarking tool, so its a pretty basic test. I highly recommend that you test on your particular workload before upgrading and share your results.

I used instructions explained at the following links to download, compile, and install MySQL 5.5 (used 5.5.30 for benchmarks) and MySQL 5.6.

MySQL 5.6 comes with performance schema turned on by defaults, so I turned performance schema OFF for both instances since even with new improvements in 5.6 it still runs with some overhead that would obviously be unfair for 5.6. Other than that I only used default configuration options at the command line except the following (should not have any impact on performance).

--no-defaults  --port=<port no> --datadir=<path to datadir> 
--socket=<path to socket file> --basedir=<mysql installation directory path> 
--character-set-server=utf8 
--collation-server=utf8_general_ci  
--performance_schema=OFF --log-error=err.txt 
--pid-file=mysqld.pid

Environment:

I ran these tests on Red Hat Enterprise Linux Server release 6.2 (SELinux disabled). The tests were executed when no other user processes were running on the server and only one instance was allowed to run at any time.

Hardware:

I ran the tests on a machine with 2GB RAM and an Intel Xeon processor with 4 cores ie. 4xIntel(R) Xeon(R) CPU E5335 @ 2.00GHz

Tests:

The tests were run with 4 threads on an Innodb table with 3 million records. Below is the sysbench command I used to populate the table.

sysbench --test=oltp  
--oltp-table-size=3000000 
--mysql-socket=<path to socket file> --mysql-user=root 
--mysql-db=sbtest --mysql-table-engine=innodb 
--mysql-engine-trx=yes prepare

Simple read-only sysbench test:

I used the following sysbench options for this test:

--test=oltp --oltp-test-mode=simple 
--oltp-table-size=3000000 --mysql-socket=<path to socket file> 
--mysql-user=root --num-threads=4 
--init-rng=on --max-requests=0 --max-time=300 
--mysql-db=sbtest --mysql-table-engine=innodb 
--oltp-read-only=on --mysql-engine-trx=yes

Results:

MySQL 5.6 did better than MySQL 5.5 on this test running 1,021,237 more queries in 300 seconds.

MySQL 5.6 ran 13450923 (44836.38 per sec.) transactions/queries.
MySQL 5.5 ran 12429686 (41432.26 per sec.) transactions/queries.

Complex read/write sysbench test:

I used the following sysbench options for this test:

--test=oltp --oltp-test-mode=complex 
--oltp-table-size=3000000 
--mysql-socket=<path to socket file> --mysql-user=root 
--num-threads=4 --init-rng=on --max-requests=0 
--max-time=300 --mysql-db=sbtest --mysql-table-engine=innodb 
--oltp-read-only=off 
--mysql-engine-trx=yes

Results:

MySQL 5.6 did better than MySQL 5.5 on this test too running 15,312 more complex read/write transactions in 300 seconds.

MySQL 5.6 ran 101259 (337.52 per sec.) transactions.
MySQL 5.5 ran 85947  (286.48 per sec.) transactions.

Queries performed for this test:
    queries performed (on 5.6.10):
       read:                            1417626
       write:                           506295
       other:                           202518
       total:                           2126439

    queries performed (on 5.5.30):
       read:                            1203258
       write:                           429735
       other:                           171894
       total:                           1804887

There is another important thing that I must mention here and that is "features". MySQL 5.6 contains some really awesome features that MySQL 5.5 did not have, so if you have a need to use any of those features or you think that your application might benefit from them then you should use 5.6 (thorough testing is required). You can find out about new features in MySQL 5.6 here.

In a nutshell, answer to the question "Is MySQL 5.6 slower than MySQL 5.5 ?" is "No, it's faster" based on the results mentioned above.

Please feel free to use the comments form below if you have any questions or need more explanation on anything. I recommend thoroughy testing on a production-like test system first before moving to production. Use the icons below to share this tutorial with your friends.

tags cloud

optimization sysbench benchmark mysql5.6 compare mysql5.5 performance

popular searches

Comments (write a comment):

since when performance_schema=OFF is a default setting? Posted by: Oracle on Feb 22, 2013

@above: the reason is explained in tutorial already. Posted by: shahryar on Feb 23, 2013

Using the default settings is hardly fair as some of the key defaults change in 5.6. Using the sysbench vars from http://www.mysqldbahelp.com/2012/12/first-sysbench-results-comparing-amazon.html with 10 billion rows I found that I was able to realize ~600tps with mysql 5.5.28ga, ~1000tps with mysql 5.5.28 percona, and ~1200tps with mysql 5.6.9. I did cheat a bit with percona and 5.6 using a 8g total log pool size. Posted by: Rolf on Feb 23, 2013

leave a comment