Bug #78176 5.6.20 is almost twice as slow as 5.0.96
Submitted: 23 Aug 2015 13:36 Modified: 17 May 2018 12:45
Reporter: teo teo Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.20 OS:Debian
Assigned to: CPU Architecture:Any
Tags: performance

[23 Aug 2015 13:36] teo teo
Description:
On a Debian server which runs a vBulletin forum with about 20k visits per day, we have upgraded MySQL from 5.0.96 to 5.6.20.

Under the same load and with the exact same PHP scripts running, doing the exact same queries, we have observed that the average response time of MySQL, as measured by NewRelic, has almost doubled.

From typically around 15ms, with sporadical 30ms peaks, to around 30-40ms with 70ms peaks, comparing the same hours of a same weekday (and also we saw the change immediately).

Performance should improve (or remain unchanged) in newer versions, not degrade.

How to repeat:
On a Debian server which runs a vBulletin forum with about 20k visits per day, we have upgraded MySQL from 5.0.96 to 5.6.20.

Under the same load and with the exact same PHP scripts running, doing the exact same queries, we have observed that the average response time of MySQL, as measured by NewRelic, has almost doubled.

From typically around 15ms, with sporadical 30ms peaks, to around 30-40ms with 70ms peaks, comparing the same hours of a same weekday (and also we saw the change immediately).

Performance should improve (or remain unchanged) in newer versions, not degrade.
[23 Aug 2015 14:15] Peter Laursen
See also https://bugs.mysql.com/bug.php?id=69236

In my understanding a load originating from a single PHP application as mentioned is a "single-threaded workload". Much development in recent server versions hasbeen  about utilizing a highly multicore'd CPU architecture (what older versions very bad at). But with a "single-threaded load" that is irrelevant, (again in my understanding). 

-- Peter 
-- not a MySQL/Oracle person
[23 Aug 2015 14:39] Peter Laursen
.. so maybe the problem is equally much that the vBulletin Forums software (and PHP as such?) is designed for dated hardware systems as much as it is a problem with MySQL? 

Anyway .. there has been so extreme focus on performance with multithreaded load on highly multi-cored systems (like systems with 8 pcs. of 4-core CPU's and similar) in MySQL 5.5 and 5.6 development.  Focus of development has simply shifted from "the poor man's database server" to "high-end web2-users' database server". And it does not look like the cost for the 1st category of users listed were taken into consideration. It was probably completely overlooked in the process.

And unfortunately I don't think any configuration option available will make much difference with MySQL 5.6 in this case.
[23 Aug 2015 14:50] MySQL Verification Team
Hi Teo,

Generally comparing 5.0 to 5.6 is simply not fair.  

For example,  one can say "create temporary table t1(a int)" and is much slower in 5.6.  Not realizing the default storage engine changed from myisam to innodb ..  Furthermore,  creating innodb tables in 5.6 might be slower than 5.0 due to persistent table stats and innodb-file-per-table being enabled by default.

Optimizer plans might have changed for queries.. Those need comparison of EXPLAIN on both versions.

There are many possibilities, and you have to analyze more, then provide the evidence here.  We required more detailed information or specific cases.
[23 Aug 2015 14:51] teo teo
What you mean by single-threaded workload?

We do have 24 cores, and there are multiple httpd processes serving several http requests in parallel, each one querying the mysql server in parallel.

So, I think the scenario is as multi-threaded (multi-process, actually) as it can possibly get.

Or do you mean one is supposed to parallelize the execution of multiple queries from every single PHP script execution? How is that even possible?
Are you implying that PHP is supposed to be obsolete in itself?

Anyway, I understand that a focus in improving performance for a certain kind of scenarios may yield no improvements in other scenarios, but if you get a performance *decrease* in ANY given scenario, something is badly wrong.
[23 Aug 2015 15:02] teo teo
> For example,  one can say "create temporary table t1(a int)" 
> and is much slower in 5.6.  Not realizing the default storage engine 
> changed from myisam to innodb ..  

We were using Innodb as the storage engine already before the upgrade

> Optimizer plans might have changed for queries.. Those need comparison 
> of EXPLAIN on both versions.

Whatever. Optimizing things is the job of the database server. If one has to figure out the best combination of a huge lot of settings in order to not get a performance decrease, the system is badly designed.

> There are many possibilities, and you have to analyze more, then 
> provide the evidence here.  We required more detailed information 
> or specific cases.

Tell me what tests I can run and what output you need and I'll be happy to provide it.

Regarding "analyzing more", it's Oracle's job, not mine, because if upgrading the server while maintaining the same data and the same workload yields a performance decrease, there's a regression. 

I can run tests and provide detailed data, but I'm not supposed to either figure out which tests to run or analyze the results.
[23 Aug 2015 15:24] Peter Laursen
Basically I just wanted to point attention to Mark Callaghan's (verified) report as I think it could be related. If you SHOW FULL PROCESLIST how many client process do you see at the same time on the MySQL instance? If there is mostly only one or two you will not benefit from the improved multithreading support in recent MySQL versions. On the opposite you may suffer from reduced single-threaded performance. This s at least what I can understand.

Another reason could be non-persistent nature of PHP<->MySQL connections and  more time could be spent in connection phase with 5.6 than with 5.0. It should be possible to profile this. 

(and I will not go into further controversy about PHP)
[23 Aug 2015 16:24] teo teo
> If you SHOW FULL PROCESLIST how many client 
> process do you see at the same time on the MySQL instance?

Anywhere between 2 and 12. Usually 5 or 6
[23 Aug 2015 16:25] teo teo
(now that the workload is small compared to a typical business day)
[17 Apr 2018 12:45] MySQL Verification Team
Hi,

First of all, let me inform you that we do not support 5.0 any more. The earliest version that we support is MySQL-5.5. Hence, we can only work with performance comparisons between the versions 5.5, 5.6, 5.7 and 8.0.

Next, If you have 24 cores and 12 intensive processes in total, you are simply back in the single-query slowdown, which is a well known phenomenon with newer MySQL versions. There are several articles written on the subject and several bugs that are already verified. Simply, the more features you add and the more work you invest in scaling MySQL server, the slower will single-thread performance be.

I would also like to agree with the comments of my colleagues that pointed to this report as a known phenomena.

We can try to find a bug that already covers the area of the performance degradation between 5.5 and later versions, but for that we need more data.  We would welcome the reports in optimiser plans that might have changed for queries.. Those need comparison of EXPLAIN in all mentioned versions.

There are many possibilities, and you have to analyze more, then provide the evidence here.  We required more detailed information or specific cases.

Thank you in advance.
[17 Apr 2018 14:10] Mark Callaghan
Many more results elsewhere to match what you see. Single-query perf is degrading. But that isn't a surprise to me as that happens with lots of system software because more features == more code path. The question for me is whether the degradation is larger than expected.

http://smalldatum.blogspot.com/2017/12/myrocks-innodb-and-tokudb-summary.html
[18 May 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".