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.
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: [url]http://mysqlyogi.blogspot.in/2013/04/is-mysql-56-slower-than-55.html[/url]
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(R) Xeon(R) 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.
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.
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!!!