At Percona, we monitor our users’ needs and try to accommodate them. One aspect we monitor is the MySQL version distribution/utilization. Observing that, we identified a very interesting trend: the lack of migration from 5.7 to 8.x, or better yet, the need of many to remain on 5.7.
That observation has triggered several actions from our side. The first one was to discuss with several 5.7 utilizers and discover why they had not migrated to 8.x. Consequently, we created the EOL program with extended support for 5.7 (https://www.percona.com/post-mysql-5-7-eol-support) to allow those who still need to count on professional support for 5.7, binaries, and code fixes.
At the same time, we have performed extensive testing of several MySQL versions with the intent of verifying any performance degradation. The testing is still ongoing; however, we have now enough data to start to draw a picture. This article is the first scratch, and its aim is to illustrate what is coming out of our testing.
Spoiler alert: the ones like me who love Sakila will not be happy.
The tests
Assumptions
There are many ways to run tests, and we know that results may vary depending on how you play with many factors, like the environment or the MySQL server settings. However, if we compare several versions of the same product on the same platform, it is logical to assume that all the versions will have the same “chance” to behave well or badly unless we change the MySQL server settings.
Because of this, I ran the tests ON DEFAULTS, with the clear assumption that if you release your product based on the defaults, that implies you had tested with them and consider them the safest for generic use.
I also applied some modifications and ran the tests again to see how optimization would impact performance.
What tests do we run?
High level, we run two sets of tests:
- Sysbench
- TPC-C (https://www.tpc.org/tpcc/) like
The full methodology and test details can be found here, while actual commands are available:
Results
While I have executed the whole set of tests as indicated on the page, and all the results are visible here, for brevity and because I want to keep this article at a high level, I will report and cover only the Read-Write tests and the TPC-C.
This is because, in my opinion, they offer an immediate and global view of how the server behaves. They also represent the most used scenario, while the other tests are more interesting to dig into problems.
The sysbench read/write tests reported below have a lower percentage of writes ~36% and ~64% reads, where reads are point selects and range selects. TPC-C instead has an even distribution of 50/50 % between read and write operations.
Sysbench read and write tests
Test using default configurations only MySQL in different versions.
Small dataset:
Optimized configuration only MySQL:
Large dataset using defaults:
Using optimization:
The first two graphs are interesting for several reasons, but one that jumps out is that we cannot count on DEFAULTS as a starting point. Or, to be correct, we can use them as the base from which we must identify better defaults; this is also corroborated by Oracle’s recent decision to modify many defaults in 8.4 (see article).
Given that I will focus on the results obtained with the optimized configs.
Now looking at the graphs above, we can see that:
- MySQL 5.7 is performing better in both cases just using defaults.
- Given bad defaults, MySQL 8.036 was not performing well in the first case; just making some adjustments allowed it to over-perform 8.4 and be closer to what 5.7 can do.
TPC-C tests
As indicated, TPC-C tests are supposed to be write-intensive, using transactions and more complex queries with join, grouping, and sorting.
I was testing the TPC-C using the most common isolation modes, Repeatable Reads, and Read Committed.
While we experienced several issues during the multiple runs, those were not consistent, mainly due to locking timeouts. Given that, while I am representing the issue presence with a blank in the graph, they are not to be considered to impact the execution trend but only represent a saturation limit.
Test using optimized configurations:
Test using optimized configurations:
In this test we can observe that MySQL 5.7 is better performing in comparison with the other MySQL versions.
What if we compare it with Percona Server for MySQL and MariaDB?
I will present only the optimized tests here for brevity because, as I saw before, we know defaults are not serving us well.
When comparing the MYSQL versions against Percona Server for MySQL 8.0.36 and MariaDB 11.3, we see how MySQL 8.4 is doing better only in relation to MariaDB; after that, it remains behind also compared to MySQL 8.0.36.
TPC-C
As expected, MySQL 8.4 is not acting well here either, and only MariaDB is performing worse. Note how Percona Server for MySQL 8.0.36 is the only one able to handle the increased contention.
What are these tests saying to us?
Frankly speaking, what we get here is what most of our users get as well, but on their own skin. MySQL performances are degrading with the increase of versions.
For sure, MySQL 8.x comes with interesting additions; however, if you consider performance as the first and most important topic, then MySQL 8.x is not any better.
Having said this, we must say that probably most of the ones still using MySQL 5.7 (and we have thousands of them) are right. Why embark on a very risky migration and then discover that you have lost a considerable percentage in performance?
Regarding this, if we analyze the data and convert the trends into transactions/sec, we can identify the following scenarios if we compare the tests done using TPC:
As we can see, the performance degradation can be significant in both tests, while the benefits (when present) are irrelevant.
In absolute numbers:
In this scenario, we need to ask ourselves, can my business deal with such a performance drop?
Considerations
When MySQL was sold to SUN Microsystems, I was in MySQL AB. I was not happy about that move at all, and when Oracle took over SUN, I was really concerned about Oracle’s possible decision to kill MySQL. I also decided to move on and join another company.
In the years after, I changed my mind, and I was supporting and promoting the Oracle/MySQL work. In many ways, I still am.
They did a great job rationalizing the development, and the code clean-up was significant. However, something did not progress with the rest of the code. The performance decrease we are seeing is the cost of this lack of progress; see also Peter’s article Is Oracle Finally Killing MySQL?.
On the other hand, we need to recognize that Oracle is investing a lot in performance and functionalities when we talk of the OCI/MySQL/Heatwave offer. Only those improvements are not reflected in the MySQL code, no matter if it is Community or Enterprise.
Once more, while I consider this extremely sad, I can also understand why.
Why should Oracle continue to optimize the MySQL code for free when cloud providers such as Google or AWS use that code, optimize it for their use, make billions, and not even share the code back?
We know this has been happening for many years now, and we know this is causing a significant and negative impact on the open source ecosystem.
MySQL is just another Lego block in a larger scenario in which cloud companies are cannibalizing the work of others for their own economic return.
What can be done? I can only hope we will see a different behavior soon. Opening the code and investing in projects that will help communities such as MySQL to quickly recover the lost ground.
In the meantime, we must acknowledge that many customers/users are on 5.7 for a good reason and that until we are able to fix that, they may decide not to migrate forever or, if they must, to migrate to something else, such as Postgres.
Then Sakila will slowly and painfully die as usual for the greed of the human being, nothing new in a way, yes, but not good.
Happy MySQL to all.
About the Author
Marco TusaMarco Tusa had his own international practice for the past twenty eight years. His experience and expertise are in a wide variety of information technology and information management fields, cover research, development, analysis, quality control, project management and team management. Marco is currently working at Percona as High Availability Practice Manager, previously working at Percona as manager of the Consulting Rapid Response Team on October 2013. He has being working as employee for the SUN Microsystems as MySQL Professional Service manager for South Europe., and previously in MySQL AB. He has worked with the Food and Agriculture Organization of the United Nation since 1994, leading the development of the Organization’s hyper textual environment.Team leader for the FAO corporate database support. For several years he has led the development group in the WAICENT/Faoinfo team. He has assisted in defining the Organization’s guidelines for the dissemination of information from the technology and the management point of view. He has participated in field missions in order to perform analysis, reviews and evaluation of the status of local projects, providing local support and advice. He had collaborates with MIT Media Lab (Massachusetts Institute of Technology laboratory) and FAO as Sustainable Information Technology for developing countries Specialist in relation with the FAO’s Special Program for Food Security for Senegal.
Hi Marco,
You said you used the defaults comparing the versions, but you certainly know that from 8.0 binary logs are enabled by default. This leads to more IOPS and reduced performance compared to not having them. Also, have you tried to enable innodb_dedicated_server?
Fred THANK YOU!!!
I forgot to specify that in the configuration description (in github).
Of course I do, this is why set skip-log-bin in all tests, also the ones based on defaults.
My attempt was to be as fair as possible while comparing. I know this is a diversion from the defaults defined in 8.0, but as you said the possible impact was justifying my choice.
No I did not use innodb_dedicatd_server, as it is not present in MariaDB 11.3 (https://mariadb.com/kb/en/system-variable-differences-between-mariadb-11-3-and-mysql-8-0/) so I would not be able to have comparable settings.
This can be the scope of other tests though.
Again thanks for raising this, I was given it for granted and forgot to add it.
Hi Marco.
These results should relate with the Enterprise version of MySQL, indicating a general problem with MySQL, or only with Community version, indicating some sort of “lack of interest” by Oracle in developing improvements?
Thank you
Hi Marco,
Could you confirm that the first chart in your article titled “write rw with select-range=100 small dataset” refers to line 3 and 49 of this file in your “benchmarktools” repo? https://github.com/Tusamarco/benchmarktools/blob/main/software/fill_sysbench_map.sh
If so, could you then confirm that the “small dataset” scenario is just using 1 table and 10K rows in it (sysbench defaults) for testing? And would that put in question some of the numbers in your results – e.g., operations per second, given the data set is so small?
Lastly, I think the skip_trx flag is also being used when calling sysbench. Does it mean MySQL was configured to use autocommit=true for the test?
Thanks!
all the methodology is described here https://github.com/Tusamarco/benchmarktools/blob/main/docs/plan.md
For sure I am not using a single table.
Hi Marco,
Could you share more details about the storage drive you used? Like its brand and model? Was it an SSD NVMe?
Thank you
all details can be found here https://github.com/Tusamarco/blogs/tree/master/sakila_where_are_you_going