There have been a number reports/benchmarks showing MySQL 5.6 to be slower than MySQL 5.5 on variety of workloads. There are many possible reasons and I believe we will learn about many of them in the next few weeks and months as MySQL 5.6 is starting to get production battle-tested and there is inflow of real world production performance data hitting MySQL Support Team at Oracle. For a number of simple workloads I would expect to see some slowdown – MySQL 5.6 Optimizer got a lot smarter and the more plans have to be considered for the query the more time the query optimization is likely to take. I also would expect newer code to benefit from “polishing” and clean up to achieve the best performance possible. At Percona we surely will be doing fine tuning for our Percona Server for MySQL version 5.6 release.
Another thing to remember about MySQL 5.6 is – it comes with Performance Schema enabled by default. Even though Performance Schema overhead was reduced in MySQL 5.6 it is still not free and it will cause an overhead, yet hopefully having more information about system operation at your fingertips will help you to achieve gains higher than the slowdown it causes.
I decided to do my own quick check into the problem using SysBench. It is by no means reflection of any production workload but we found it is a good measure of the overhead, especially for simple queries. I ran small (1M rows) benchmark so data was well fitting in memory and Read Only one to simplify things and get more repeatable results without spending too much time on it. I used Ubuntu 12.04 on some old Dell PowerEdge 2850 server. The questions I tried to answer are how much slower 5.6 is for such simple workload ? How much of this slowness comes from Performance Schema ? Whenever we get difference higher or lower with high concurrency compared to single thread ?
Note Sysbench is simple enough it does not take advantage of any of new MySQL 5.6 optimizations so it is geared towards worse case scenario for MySQL 5.6 performance.
Here is Sysbench command I used:
1 | root@dpe01:/mnt/data/sandboxes/msb_5_6_10# sysbench --test=oltp --mysql-socket=/tmp/mysql_sandbox5610.sock --mysql-user=msandbox_rw --mysql-password=msandbox --oltp-table-size=1000000 --num-threads=1 --oltp-read-only=on --init-rng=on --max-requests=0 --max-time=300 run |
For single thread MySQL 5.5 is 11% faster than MySQL 5.6. If you disable Performance Schema in MySQL 5.6 comes to just about 3% showing Performance Schema itself is responsible for 7.5% overhead in this case. This is quite reasonable result and I do not think 3% difference will be noticed in most production cases.
For benchmark with 64 threads MySQL 5.5 is 26% faster compared to MySQL 5.6 and if you disable Performance Schema the difference is still 11% with 13% difference between MySQL 5.6 with Performance Schema enabled and Disabled. The difference of over 25% is rather substantial and if fair amount of workloads will see similar kind of overhead I expect a lot of grief.
It is also unfortunate to see the difference for this specific benchmark actually growing with increased contention even though MySQL 5.6 is positioned as improving scalability over MySQL 5.5. Also number of expected slowdowns such as more work done at optimizer phase should not increase with contention.
Granted I have not tuned MySQL 5.6 for best performance going with defaults outside of few variables MySQL Sandbox sets explicitly but yet this is likely course of action for many MySQL users out there, also was not change of defaults in MySQL 5.6 had more reasonable defaults as its goal ?
Summary: Well, to be frank I expected more from MySQL 5.6 release – more of cleaned up code and better out of the box performance. Yet I’m still very excited about all architecture changed implemented in MySQL 5.6 and new feature and I believe when we look at real workloads we’ll see more cases when MySQL 5.6 excels. I also expect some analyses and cleanup done over next few months which should help to improve MySQL 5.6 performance.
Hi Peter,
an how many cores are on your server?
did you use jemalloc?
Rgds,
-Dimitri
What’s the best way to install 5.6 on Ubuntu 12.04 (fresh install)?
Dimitry,
It is just 4 cores per box of model name : Intel(R) Xeon(TM) CPU 3.00GHz
Linux dpe01 3.2.0-37-generic #58-Ubuntu SMP Thu Jan 24 15:28:10 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
I just downloaded binaries:
-rw-rw-rw- 1 root root 186641309 Jan 16 21:04 mysql-5.5.30-linux2.6-x86_64.tar.gz
-rw-rw-rw- 1 root root 303263359 Jan 23 03:55 mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz
As I stated in the post this is not about testing the best tuned 5.5 vs 5.6 but rather to see how out of the box performance would look like.
Hi Peter.
According to my math, in the single thread case, 1-331/367 is 9.8 percent. You may round that up to 10%, but this is not 11%.
Likewise, for the 64 threads case, 1-709/891 is 20.41 percent, definitively not 26.
According to your math, taking 5.6 as a base, a 5.6 QPS which is the 2x from a 5.5 QPS (a 100% improvement in my book) would be counted as only 50 % improvement … humm.
Math and point of reference apart, a 20 or 26 percent overhead with the performance schema is inconsistent with our experience, hence my follow up question:
Could you post the my.cnf relevant config for the performance schema.
Claiming “with performance schema” is meaningless these days, considering how many different instrumentation may or may not be enabled. If the default out of the box configuration was used, please clarify that, or if something else was used, please indicate the details.
Thanks,
— Marc Alff.
Marc,
First about math. Might be wording is not aligned with this. What I did is 367/331= 1.10876 which I rounded to 11% yes… Perhaps I should say MySQL 5.5 is 11% faster than MySQL 5.6 rather than 5.6 is slower than 5.5…. Typically the point is to show how much faster something is growing from lower to higher numbers. There is no intent here to show worse numbers than they are 🙂
If MySQL 5.6 QPS would be 2x of 5.5 we could call it 5.6 is 100% faster or we can call it 5.5 is 50% slower both are relevant.
Now regarding performance schema. I used MySQL Sandbox set up with MySQL 5.6 and 5.5 in their default settings.
I did not do anything to tune performance schema comparing it out of the box. when I disabled it I did it by adding “skip-performance-schema” to my.cnf
I think you guys in development team grossly overestimate how much tuning the general user is going to do 🙂
Here is complete config file from sandbox:
[mysqld]
user = root
port = 5610
socket = /tmp/mysql_sandbox5610.sock
basedir = /mnt/nfs/dist/5.6.10
datadir = /mnt/data/sandboxes/msb_5_6_10/data
tmpdir = /mnt/data/sandboxes/msb_5_6_10/tmp
pid-file = /mnt/data/sandboxes/msb_5_6_10/data/mysql_sandbox5610.pid
#log-slow-queries = /mnt/data/sandboxes/msb_5_6_10/data/msandbox-slow.log
#log = /mnt/data/sandboxes/msb_5_6_10/data/msandbox.log
#
# additional options passed through ‘my_clause’
#
log-error=msandbox.err
#skip-performance-schema
Marc,
I have now updated the language to state it is 5.5 being X% faster not 5.6 being X% slower as this is how math was done.
This is disappointing. I (as I’m sure many others) am very excited about the changes in 5.6. Hopefully there’s some simple (a relative term obviously) changes to bring this back up to 5.5 standards – at least.
Peter, there should be really something wrong…
on MySQL 5.6 (PFS=off) with a single thread I’m getting:
– 380 TPS on 2.27Ghz CPU server..
– 550 TPS on 2.9Ghz CPU server..
so, seems pretty strange that you’re getting only 361 TPS on 3.0Ghz CPU server..
seems to me I have to investigate it in details..
Rgds,
-Dimitri
Dimitry,
You can’t just look at Ghz. as stated this is Dell PowerEdge 2850 which is about 5 years old box (or more). The per Ghz frequency has improved dramatically with the recent years.
Peter,
my servers are also 3-4 years old,
that’s why I have yet more reasons to be surprised ;-))
as I said, I’ll investigate it..
Rgds,
-Dimitri
Thanks Dimitri !
Here is exact CPU information for mine if you’re interested:
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Xeon(TM) CPU 3.00GHz
stepping : 3
microcode : 0x5
cpu MHz : 2992.776
cache size : 2048 KB
physical id : 3
siblings : 2
core id : 0
cpu cores : 1
apicid : 7
initial apicid : 7
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc pebs bts nopl pni dtes64 monitor ds_cpl cid cx16 xtpr
bogomips : 5985.40
clflush size : 64
cache_alignment : 128
address sizes : 36 bits physical, 48 bits virtual
power management:
There was one specific time few years ago when CPU architectures changed from P4 architecture to “Core” architectures which offered substantially better performance for lower clock frequency.
That appears to be a P4, the end of the Netburst architecture before core was introduced. Memory speed/bandwidth may have a big part in performance here.
I reopened a bug I opened for 5.6.6. There is still too much mutex contention on the MDL code for some workloads — http://bugs.mysql.com/bug.php?id=66473
I also created a bug for a long known behavior (see http://www.mysqlperformanceblog.com/2011/04/25/performance-schema-overhead/). The overhead from PS can be more than 10%. I think that is too much — http://bugs.mysql.com/bug.php?id=68413
A bit off-topic Peter, but will Percona be getting Handlersocket to compile with 5.6?
I am sure many would agree that Oracles “NoSQL” layer doesn’t even come close to what handlersocket has already achieved… not in terms of performance, or flexibility…
Cheers
//Steve
we’ve found the drag on performance from Performance Schema in a production environment can vary dramatically depending on your workload, and can be quite significant for high volume simple key read/writes on beefier hardware.. more so than these particular benchmarks (on 1 core/ 4 logical cpus I’m guessing) would indicate.
I’m still digging into what could be the problem here. One thing I noticed the official “General Linux” binaries are using different compilers. MySQL 5.5.30 is using GCC 4.3.4 and MySQL 5.6.10 is using GCC 4.1.2 (from 2008) This surely can be part of explanation. Anyone knows if there is a reason for this difference or if it is mistake ?
Well, now here’s something interesting:
MySQL 5.6.10 (PFS enabled): 666.95 TPS
MySQL 5.6.10 (PFS disabled): 622.77 TPS
MySQL 5.5.30: 842.47 TPS
processor : 7
vendor_id : GenuineIntel
cpu family : 6
model : 42
model name : Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz
stepping : 7
cpu MHz : 1600.000
cache size : 8192 KB
physical id : 0
siblings : 8
core id : 3
cpu cores : 4
apicid : 7
initial apicid : 7
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt aes xsave avx lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid
bogomips : 6784.40
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
The 5.6.10 results make no sense to me unless it’s just measurement error; I’m going to have to try this again from runlevel 1 without all the extra desktop stuff running.
Steve,
Thanks for interest. We have not decided on Handlersocket inclusion yet. It will depend on user and customer interest.
These are incredibly concerning figures.
I’ve been reading about how much faster 5.6 supposedly is with mutli-threading but from reading the above I am going to steer clear of MySQL 5.6 in production for now.
Quick question – how can i generate these QPS figures similar to the above? I have a large number of 12 & 16 core Gentoo servers with GCC 4.7 that i’d be keen to test source built (not precompiled) 5.5 vs 5.6 performance on.
Hi,
I found the same result but during some crash tests, with innodb_flush_log_at_trx_commit=2 and innodb_flush_method=O_DIRECT, there are uncommited data present into table in 5.5 release.
In 5.6, all uncommited data are rollbacked after a crash.
So, for me, 5.6 release is expected version to have performance AND data coherence in case of crash. Good alternative to Oracle database.
Hi Peter.
As Dimitri stated,it seams to be something whrong … with your build of mysql-5.6!
On 64bit Win7 host running 512 MB VirtulBox Ubuntu 12.04 guest(3.2.0-39-generic #62-Ubuntu SMP Thu Feb 28 00:28:53 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux) using VBoxHeadless.exe with 1 core
# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 23
model name : Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz
stepping : 10
cpu MHz : 2878.300
cache size : 6144 KB
fpu : yes
fpu_exception : yes
cpuid level : 5
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx lm constant_tsc up rep_good nopl pni monitor ssse3 lahf_lm
bogomips : 5756.60
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
I get this as result:
transactions: 148503 (495.00 per sec.)
transactions: 161630 (538.76 per sec.) # using skip-performance-schema
with standard mysql-5.6.10-debian6.0-x86_64.deb package !
You may consider rerun your tests.
Cheers.
Vladislav
Vladislav – thank you for the results. The PS reduces QPS by more than 8% for your tests. Did you enable anything special or was it collecting the default data? I think 8% is too much overhead.
I forgot to mention – the results from previous comment are for 1 thread.
Same VM with PS off
1 core, 1 thread
mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 564 PTS
mysql-5.6.10-debian6.0-x86_64.deb 538 PTS
1 core, 64 threads:
mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 575 PTS
mysql-5.6.10-debian6.0-x86_64.deb 572 PTS
2 cores, 64 threads:
mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 911 PTS
mysql-5.6.10-debian6.0-x86_64.deb 918 PTS
@Peter Zaitsev, in a reply you say this: “I did not do anything to tune performance schema comparing it out of the box. when I disabled it I did it by adding “skip-performance-schema” to my.cnf”. This confuses me, because when I read http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html it say that it should be: performance_schema=off
I am considering upgrading from 5.5 to 5.6 and disable performance schema, so which way is correct? Adding performance_schema=off or your skip-performance-schema to my.cnf?
Hi Peter,
I was just wondering whether there is a significant difference in partition select performance between Mysql 5.5 and Mysql 5.6 since select operations against partitioned tables are not implemented the same way in both versions. In Mysql 5.6, the statement mentions the partition name rather than the “partitioned_column=value” criteria. Technically speaking, does it mean that a select statement against a Mysql 5.6 partitioned table would be somewhat as fast as/equivalent to the “classic” form of the same select statement against a non-partitioned table holding the same data as the partition?
phd,
The feature you are talking about is for explicit partition elimination. Explicit partition elimination still applies.
For example, you could partition by year and have the following query:
select count(*) from some_table where year in (2001,2002,2003,2004)
For simplicity say that returns 4000 (exactly 1000 per year).
In MySQL 5.6 you could say:
select count(*) from some_table PARTITION(p2001,p2002) where year in (2001,2002,2003,2004)
This would return 2000 because you have “whitelisted” only p2001 and p2002 partitions.
Rows from only the p2003 and p2004 partitions would be included if the query said:
select count(*) from some_table PARTITION(p2003,p2004) where year in (2001,2002,2003,2004)
and the result would be 2000
and likewise:
select count(*) from some_table PARTITION(p2004) where year in (2001,2002,2003,2004)
would result in 1000
This new hint can also be used for examining only one partition of a hash partition for example, something that can not be done with implicit partition elimination.
Hi Peter.,
Is there any mentioned performance issue still exist in the current available version 5.6.21(wrt 5.5).Or Its been fixed.
Senthil
Hi Peter ,
I am using two different mysql version on two different servers :-
1)5.5.28-enterprise-commercial-advanced-log
2)5.6.22-71.0-log
I am seeing performance issue on 2nd i.e. 5.6.22-71.0-log
same function if executing in ms on 1st and seconds on 2nd one.Please suggest whhat sould i do ??
We have installed percona 5.5 on a cPanel enviroment and are wondering to upgrade to 5.6 but do not know if export more than 10 GB in databases sapce will worth it. We like to see more detailed benchmarks to take the decision. Any advice will be very aprreciated. Thanks.