MySQL 5.5 Vs 5.6 Performance

MySQL 5.6 has huge performance gain over 5.5, it is more than 100% as per 5.6 performance white paper.

I did benchmarking using Sysbench on VMWare Instance and old physical machine with Opetron processor.

My sysbench results are very surprising, on both machine MySQL 5.5 have slight edge over 5.6.

I am using 1000000 rows for test cases and following sysbench command.

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=? run

Xeon® CPU X5670@2.93GHz 3 CPUVMWare Instance
MySQL 5.6.10 MySQL 5.5.28
Read R/W Read R/W
Thread Rows TPS TPS TPS TPS
4 1000000 2049 1036 2417 955
8 1000000 2071 1171 2457 1129
16 1000000 2077 1234 2509 1243
32 1000000 2013 1244 2437 1311
64 1000000 1990 1274 2424 1332
128 1000000 1900 1259 2416 1294
256 1000000 1763 1167 2416 1202
4 2000000 1980 853 2213 823
8 2000000 1968 1018 2213 945
16 2000000 1927 1052 2216 1080
32 2000000 1883 1099 2200 1198
64 2000000 1870 1152 2239 1221
128 2000000 1819 1069 2257 1255
256 2000000 1668 1021 2217 1209

Dual-Core AMD Opteron™ Processor 2216 4 CPU
MySQL 5.6.10 MySQL 5.5.28
Read R/W Read R/W
Thread Rows TPS TPS TPS TPS
4 1000000 1887 1026 2148 1063
8 1000000 1894 1043 2169 1128
16 1000000 1892 1067 2163 1154
32 1000000 1800 1041 2083 1143
64 1000000 1687 1009 1953 1103
128 1000000 1608 969 1845 1035
256 1000000 1572 898 1786 946
4 2000000 1718 700 1900 736
8 2000000 1735 791 1822 777
16 2000000 1701 786 1763 800
32 2000000 1623 780 1718 809
64 2000000 1381 722 1658 800
128 2000000 1210 503 1537 781
256 2000000 960 562 1459 732

Am I not using SysBench properly? Why the results are so discouraging?

The difference between MySQL 5.5 and 5.6 performance may depend on many factors like workload, hardware, exact MySQL version as well as whether exactly the same variables were used. Note that some variables defaults were changed in MySQL 5.6 and may affect the behaviour. For example Performance Schema overhead is lower in 5.6, hence it was decided to leave it enabled by default. But still some performance overhead exists.

You can find a deeper view into this problem in following articles:
http://www.mysqlperformanceblog.com/2013/02/18/is-mysql-5-6-slower-than-mysql-5-5/
http://dimitrik.free.fr/blog/archives/2013/02/mysql-performance-analyzing-benchmarks-part1-mysql-56-and-mariadb-55.html
http://bugs.mysql.com/bug.php?id=66473

You may also find this news interesting:
http://www.mysqlperformanceblog.com/2013/04/12/trx-descriptors-mysql-performance-improvements-in-percona-server-5-5-30-30-2/
Basically, latest Percona Server 5.5 has some important improvements for high concurrency workloads, so you may want to try it out.

Btw. the way you pasted your results make it unclear which ones are for 5.5 and which for 5.6. Can you re-format them?

I am using default parameter with both mysql 5.5 and 5.6.
What I was expecting from benchmark is mysql 5.6 performance should be more atleast 1-2 % than 5.5 with default installations if mysql white paper claiming 230% performance improvements in 5.6.
Here are my results: http://mysqlyogi.blogspot.in/2013/04/is-mysql-56-slower-than-55.html
Which particular parameter should I tune in 5.6 for better sysbench results?

Well, first, I would not trust any marketing white paper without an exact method to reproduce results. The 230% can be true, but for very particular workloads.
On my tests, P_S in 5.6 still have a 5-10% overhead, which is more or less the same that Percona and Facebook are getting for 5.6.10 (see links from przemek). P_S is disabled by default in 5.5.
We may have to wait a little more to get those issues solved. For now, you can already try 5.6.11 and see if something has changed, and follow the status of several of those MySQL issues.

Hi I am back again
Earlier i was doing tests on virtual machine and on low configuration machine, i thought low hardware might be reason of bad sysbench results.
Now I have new physical machine with Intel® Xeon® CPU E5-2667 0 @ 2.90GHz 24 core , 72 GB RAM with SAN partition mounted.
The results are again disappointing, MySQL 5.5 is again winner. this time it is MySQL 5.6.12 latest version.

Any views??

Did you try disabling Performance Schema in 5.6 and rerunning your tests?

As previously mentioned, MySQL 5.6 has quite a few default value changes / new “auto sizing” variables that could result in worse performance with specific work loads / configurations / etc. What I would do is get a list of the variables that did have their default values changed, and make sure to set them to the same thing in both 5.5 and 5.6 for your testing. If the results change at that point, then I would start to remove those default values one at a time until there is a noticeable change. That (potentially) will narrow down what could be affecting your results.

http://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

Hi

The default value changes / new “auto sizing” variables changed to default value in 5.5, here are the parameters set in my.cnf

performance_schema=OFF
max_connections=1100
back_log=50
binlog_checksum=NONE
host_cache_size=128
innodb_autoextend_increment=8
innodb_buffer_pool_instances=1
innodb_concurrency_tickets=500
innodb_log_file_size=5MB
innodb_old_blocks_time=0
innodb_stats_on_metadata=ON
join_buffer_size=128KB
secure_auth=OFF
table_open_cache=400
thread_cache_size=0
table_definition_cache=400
sync_relay_log_info=0
sync_relay_log=0
sync_master_info=0
query_cache_type=ON
max_allowed_packet=1MB
max_connect_errors=0
innodb_open_files=300

the sysbench results are disappointing compare to 5.6 with default parameter values.
[TABLE]
[TR]
[TD] [/TD]
[TD=“colspan: 2”]MySQL 5.5.30[/TD]
[TD=“colspan: 2”]5.6.10 default values[/TD]
[TD=“colspan: 4”]5.6.10 default value changes to5.5.30 default values[/TD]
[/TR]
[TR]
[TD]Thread[/TD]
[TD]RO TPS[/TD]
[TD]R/W TPS[/TD]
[TD]RO TPS[/TD]
[TD]R/W TPS[/TD]
[TD]RO TPS[/TD]
[TD]R/W TPS[/TD]
[TD=“colspan: 2”] [/TD]
[/TR]
[TR]
[TD=“align: right”]1[/TD]
[TD=“align: right”]616[/TD]
[TD=“align: right”]390[/TD]
[TD=“align: right”]497[/TD]
[TD=“align: right”]311[/TD]
[TD=“align: right”]432[/TD]
[TD=“align: right”]330[/TD]
[/TR]
[TR]
[TD=“align: right”]4[/TD]
[TD=“align: right”]2148[/TD]
[TD=“align: right”]1063[/TD]
[TD=“align: right”]1887[/TD]
[TD=“align: right”]1026[/TD]
[TD=“align: right”]735[/TD]
[TD=“align: right”]981[/TD]
[/TR]
[TR]
[TD=“align: right”]8[/TD]
[TD=“align: right”]2169[/TD]
[TD=“align: right”]1128[/TD]
[TD=“align: right”]1894[/TD]
[TD=“align: right”]1043[/TD]
[TD=“align: right”]961[/TD]
[TD=“align: right”]1028[/TD]
[/TR]
[TR]
[TD=“align: right”]16[/TD]
[TD=“align: right”]2163[/TD]
[TD=“align: right”]1154[/TD]
[TD=“align: right”]1892[/TD]
[TD=“align: right”]1067[/TD]
[TD=“align: right”]780[/TD]
[TD=“align: right”]1022[/TD]
[/TR]
[TR]
[TD=“align: right”]32[/TD]
[TD=“align: right”]2083[/TD]
[TD=“align: right”]1143[/TD]
[TD=“align: right”]1800[/TD]
[TD=“align: right”]1041[/TD]
[TD=“align: right”]676[/TD]
[TD=“align: right”]1026[/TD]
[/TR]
[TR]
[TD=“align: right”]64[/TD]
[TD=“align: right”]1953[/TD]
[TD=“align: right”]1103[/TD]
[TD=“align: right”]1687[/TD]
[TD=“align: right”]1009[/TD]
[TD=“align: right”]640[/TD]
[TD=“align: right”]1018[/TD]
[/TR]
[TR]
[TD=“align: right”]128[/TD]
[TD=“align: right”]1852[/TD]
[TD=“align: right”]1035[/TD]
[TD=“align: right”]1608[/TD]
[TD=“align: right”]969[/TD]
[TD=“align: right”]616[/TD]
[TD=“align: right”]980[/TD]
[/TR]
[TR]
[TD=“align: right”]256[/TD]
[TD=“align: right”]1843[/TD]
[TD=“align: right”]946[/TD]
[TD=“align: right”]1572[/TD]
[TD=“align: right”]898[/TD]
[TD=“align: right”]592[/TD]
[TD=“align: right”]896[/TD]
[/TR]
[TR]
[TD=“align: right”]512[/TD]
[TD=“align: right”]1832[/TD]
[TD=“align: right”]828[/TD]
[TD=“align: right”]1493[/TD]
[TD=“align: right”]823[/TD]
[TD=“align: right”]565[/TD]
[TD=“align: right”]531[/TD]
[/TR]
[TR]
[TD=“align: right”]1024[/TD]
[TD=“align: right”]1521[/TD]
[TD=“align: right”]601[/TD]
[TD=“align: right”]1541[/TD]
[TD=“align: right”]366[/TD]
[TD=“align: right”]545[/TD]
[TD=“align: right”]534[/TD]
[/TR]
[/TABLE]

MySQL 5.6 proved to be low performance versions in all conditions than 5.5.30

query_cache_type plays important role in performance, with query_cache_type=ON ( default value in 5.6 is query_cache_type=OFF) reduces the performance by 50 %. In previous post the stats in last two column are when query_cache_type=ON that is why performance was low. However, in both cases performance is lower than 5.5.30

Sorry for the beginner question. I installed mysql 5.5 on windows, found sysbench was not available on that platform. so installed unbuntu under vitual box. Installed mysql and sysbench using the ubuntu installer (which gives no options).

I can run mysql and mysql admin, and can see I have a two databases:
information_schema
test.

When I try and run:

[COLOR=#252C2F]sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=1 run

I get the following error:

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=1 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
ALERT: Error: failed to determine table ‘sbtest’ type!
ALERT: MySQL error:
FATAL: failed to get database capabilities!

Anyone know what this error means? The schema is there, and is viewable by the user I am logged in as using mysql command.

If I do:
use test;
show tables;
I get: Empty Set.

Is this the problem? that I need to create the test database first? If so what tables/rows etc should I create?

Hi, all, I encounter the same issue, tested the same SQL on same physic machine, percona mysql 5.6 Vs MySQL 5.6 Vs MySQL 5.5.34,
same configuration, but MySQL 5.5 performance is the best, percona and MySQL 5.6 almost the same. any ideas? thanks.

We are getting ready to migrate from 5,5 to either MySQL Community Server 5.6 or Percona Server 5.6, but now we are concerned about the implications of this thread. WIll be monitoring this thread to see if more updates are coming soon in this regard, please do!

I migrated one of the highly used word press blog to MySQL 5.6 Community Edition recently in a hope for better performance specially with FULL TEXT SEARCH in InnoDB, within 15 minutes database server hangs and forced to reboot. after proper analysis i found FULL TEXT SEARCH ( InnoDB support full text searching in 5.6 ) queries piles up and consumed all 500 connections. reverting table engine back to MyISAM database was normal.
I repeat the same activity one more time and analyze the behavior for 1-2 hr, there was no performance gain. Finally i revery to MyISAM.

To be very honest FULL TEXT SEARCH in InnoDB kills!!!