What Does The Universal Scalability Law Reveal About MySQL?
Posted in Databases on Nov 12, 2015
In the last couple of weeks, there have been a few blog posts about benchmarks comparing the performance of various versions of MySQL and variants such as MariaDB. There’s also been some analysis of the results using formal models such as Neil Gunther’s Universal Scalability Law.
What can the Universal Scalability Law (USL) teach us about the performance characteristics of these systems, as revealed by the benchmarks? To find out, I’ll examine one particular benchmark, MariaDB 10.1 and MySQL 5.7 performance on commodity hardware.
The context for this benchmark, in a nutshell, is that MySQL 5.7 was just released as GA, and the published performance results are spectacular, from the point of view of throughput on large servers. Although this is good, showing that MySQL can scale to do lots of work on large servers, the MariaDB benchmarkers wanted to understand how a simple benchmark would run on a typical commodity server.
The benchmark compares several versions of MySQL. Neil tweeted an analysis of the benchmark with the USL:
Be careful with that chart, because the horizontal axis is log-scaled, not linear. What can we learn from that? First of all, if you’re familiar with the USL at all you’ll instantly recognize that the system isn’t behaving as the USL predicts. A much better way to model this is to use only the first few datapoints to predict what might happen if the system didn’t encounter the limitation we can see beginning at 8 connections. Neil did this too:
Neil’s commentary, which I’ll paraphrase for clarity, is *If you can remove the bottleneck at 118k queries per second, the USL predicts that the system will hit a ceiling around 250k queries per second. Can MySQL or MariaDB get there? If not, why not? With the USL, it’s no longer OK to just measure, you have to EXPLAIN the data.*
The explanation is simple. The server has 4 cores and 8 threads:
Intel Xeon E5 E5–1630v3 4/8t
3,7 / 3,8 GHz 64 GB RAM
DDR4 ECC 2133 MHz 2 ×2TB SOFT
This server can run only 8 queries simultaneously. After that, adding more connections trying to run queries is not going to improve things.
The USL doesn’t include parameters that model the fixed size of underlying hardware and other resources, and as a result, it’s very common to find some type of resource saturation that creates a ceiling it doesn’t predict. This is a perfect example.
As I discuss in my new 50-page ebook on the Universal Scalability Law, it’s rather pointless to try to use the USL to assess behavior beyond a saturation point such as the one you can see in this benchmark. That’s why fitting the USL to the data up to and including 8 connections is the right approach.
Beyond the saturation point, all you can see is whether, under increased queue length, the server stays efficient or wastes effort. Most servers lose efficiency. To find out exactly what causes this, please read the ebook I linked above.
The charts above are all of several versions of the server analyzed together. I think it’s a better idea to analyze only a single version of the server. Let’s look at the results for MySQL 5.6.27 only, because on this benchmark it beat the other versions:
clients qps 1 24456 2 45314 4 78024 8 126892 16 129029 32 127780 64 125526 128 124158 256 116337
And the USL analysis of this data up to 8 clients:
There’s such a small kappa coefficient that it should be ignored, however, sigma is about 7.4%, which is quite significant and limits performance very seriously. If you’re familiar with the USL this provides concrete ideas about what needs to be changed, and defines how far you can scale this server on this type of configuration. When I was at Percona in the 2009-2011 timeframe we made a business out of finding and alleviating those kinds of bottlenecks, which is what led to XtraDB and eventually to Percona Server.