DimitriK's (dim) Weblog

MySQL Performance: 1M QPS on mixed OLTP_RO with MySQL 5.7 GA

This article is following the MySQL 5.7 Performance series started from 1.6M QPS details post

Let's focus now on the Sysbench "mixed" OLTP_RO workload (so, not only Point-Selects, but all RO queries as it was initially designed in OLTP_RO scenario in Sysbench), which is composed of :

• x10 point-selects
• x1 simple-ranges
• x1 order-ranges
• x1 sum-ranges
• x1 distinct-ranges

Note about test conditions :

• in this and in the previous article we're seeking for MySQL & InnoDB scalability limits ;-)
• so, yes, the client and server are running locally on the same host (we're not testing here network stuff vendors)
• the load is not IO-bound either, very quickly the whole dataset is reaching BP and remains in memory (we're not testing IO vendors here)
• again, all the focus here is on MySQL scalability limits, nothing else..

Note about tested workloads :

• why we're paying so many attention to these "simple" queries from Sysbench ?..
• well, just consider it like the "entry ticket" for any database engine ;-)
• these simple tests in fact are extremely aggressive, and bombarding all engine internals very heavily..
• the point-select, for ex., is extremely fast and crossing on every pass the whole engine stack from entry to end point & back..
• while the distinct-ranges is extremely aggressive on malloc() scalability + in-memory temp tables management..
• etc. etc. etc..
• so, if your production workload is based on much more complex queries, and you're suspecting scalability issues -- rather to stay negative, send us your test workload and share your observations -- we cannot improve everything within a one single shot, right? -- there is still so many things to do yet..
• but I just may say you one thing : without improved scalability on "simple" loads any "complex" load scalability is just impossible.. -- so, stay tuned, we're coming ;-)

Note about the tested HW :

• I was told several times during the MySQL Central conference and other user meeting that 72cores config is waaay toooo big..
• guys, is it really big ?? ;-)
• just think that this is the same 4CPU sockets server as we used before..
• just that today's Intel chip has 18cores per socket -vs- 10cores we saw before, and the CPU chip itself was also greatly improved..
• well, if 4CPU is still toooo biiiig for you, will be 2CPU is "good enough" ?..
• but even with 2CPU you're having 36cores-HT here ;-)
• and this 2CPU 36cores-HT config is already showing a way better QPS than we observed on 4CPU 40cores-HT before !!
• what to do with this then ?? ;-)
• keeping in mind that the next Intel chip will give you 48cores-HT (24cores-HT per CPU socket)..
• so, you want it, or not -- scalability is THE MUST today to give MySQL users a full power of their HW ;-))

Well, a long story short, let's go directly to the test results.

Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

Observations :

• MySQL 5.7 is reaching 1M QPS on OLTP_RO !! (while seems like there is still a room for progress)..
• on the second position is MySQL 5.6, and in max-to-max comparison MySQL 5.7 is doing x2.5 times better than 5.6
• Persona Server 5.6 is taking #3position
• and, surprisingly MariaDB 10.1 here the worse, except MySQL 5.5
• Note that except MySQL 5.7, the difference between MySQL 5.5 result and other engines is not that big..
• this is because QPS gain here is coming for them mostly from "mixed" ranges queries, which was already not that bad in 5.5
• (and if you payed attention to the previous article, you may see that on point-selects MySQL 5.5 is reaching the same max QPS as all other engines, except 5.7)..
• so, yes, the huge gain you're seeing here for MySQL 5.7 is coming due all this heavy remastering started from TRX-list re-design 2 years ago and continued on all levels till 5.7 GA release..
• indeed, this was not easy, but yes, we done it !!! ;-))

What about scalability now?

MySQL 5.7 Scalability @Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

MySQL 5.6 Scalability @Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

MySQL 5.5 Scalability @Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

Percona Server 5.6 Scalability @Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

MariaDB 10.1 Scalability @Sysbench OLTP_RO 1M x 8-tables 72cores-HT :

Observations :

• only MySQL 5.7 is really scaling here up to 72cores-HT
• except MySQL 5.5, all other engines are having better QPS on 32cores-HT -vs- 18cores-HT
• however, this gain is not that big.. - but MySQL 5.6 is doing better than others
• MariaDB 10.1 is the worse again, just better than the oldest MySQL 5.5..

Now the same workload, but using only a single 10M rows table.

Sysbench OLTP_RO 10M x 1-table 72cores-HT :

Observations :

• MySQL 5.7 is the best again, but not yet reaching 1M QPS, a room for improvement ;-)
• MySQL 5.6 and Percona Server 5.6 on the #2 position
• and MariaDB 10.1 closing the group, just before MySQL 5.5

And scalability?

MySQL 5.7 Scalability @Sysbench OLTP_RO 10M x 1-table 72cores-HT :

MySQL 5.6 Scalability @Sysbench OLTP_RO 10M x 1-table 72cores-HT :

MySQL 5.5 Scalability @Sysbench OLTP_RO 10M x 1-table 72cores-HT :

Percona Server 5.6 Scalability @Sysbench OLTP_RO 10M x 1-table 72cores-HT :

MariaDB 10.1 Scalability @Sysbench OLTP_RO 10M x 1-table 72cores-HT :

Observations :

• the result is very similar to 8-tables results, except that Percona Server 5.6 is very close with MySQL 5.6 here
• while MySQL 5.7 is just the best ;-)

And, similar to the previous article, here are the results reflecting the evolution of tested HW and CPU power :

MySQL 5.7 Scalability @Sysbench OLTP_RO 1M x 8-tables :

MySQL 5.7 Scalability @Sysbench OLTP_RO 10M x 1-table :

Indeed, the difference is very impressive ;-)

Over last 2 weeks in all discussions I've got many times the same question : do we really need to upgrade our HW to get the best from MySQL Performance ?...

Let me show you then just few following graphs :

here is HW evolution related to MySQL 5.5 on OLTP_RO the presented workload :

Observations :

• as you can see, 5.5 was the best on 12cores-HT config on the old HW
• it was not able to show anything better on 32 or 40cores-HT configuration..
• however, moved to the newer CPU chip, even MySQL 5.5 is going x2 times faster here! ;-)
• and as you can see from the 5.5 scalability graphs, 1CPU socket is just enough here..
• and, unfortunately, 1CPU will be also your limit for MySQL 5.5 Performance ;-)

now the same for MySQL 5.6 :

Observations :

• MySQL 5.6 is scaling better, and already able to show over 50% better performance on an old, but bigger HW
• but on the newer CPU it's doing yet x2 times better (even without scaling well)..

And, of course, once you'll move to MySQL 5.7, you'll be able to go yet more far on these new 2CPU or 4CPU sockets :

Observations :

• no comments ;-))

And to finish, few more details about the test conditions :

my.conf -- used exactly the same config settings as in the previous article.

The Sysbench command used to run OLTP_RO test via IP port (starting 8 processes in parallel):

$ LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench --num-threads=$1  \
        --test=oltp --oltp-table-size=1000000 \
        --oltp-dist-type=uniform --oltp-table-name=sbtest_1M_$n \
        --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql  --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &

Should I say once more again MySQL 5.7 rocks !!! ;-))

Rgds,
-Dimitri