« Linux | Main | news »

Wednesday, 11 November, 2015

MySQL Performance: What is odd with 1M QPS claimed by MariaDB 10.1 ?..

This article is continuing the MySQL 5.7 Performance story, started from 1.6M QPS details post, and followed by 1M QPS OLTP_RO article. However, the current story will not be mostly about MySQL 5.7, but also about announced on the same time MariaDB 10.1 GA ;-)

So far, MySQL Team was proud to show 1.6M QPS on Point-Select (SQL) queries, and MariaDB 10.1 GA announce was also claiming an ability to reach 1M QPS, also on Point-Selects, but on POWER8 HW. And I may be only happy for MariaDB team for their progress on POWER systems, except just one small detail related to how their 1M QPS result was obtained..

But first of all, what are these Point-Selects and what is so special with this workload ?..

  • point-select is representing a single SQL query reading a row by its primary key (PK)
  • a workload based on such queries is very similar to any generic key-value store solution, but via SQL
  • usually a point-select is extremely fast and doing a round-trip from end-to-end in database engine
  • so any internals overhead in engine code, any contentions, any scalability limits are seen very quickly..
  • historically such kind of workload did not scale very well on MySQL/InnoDB tandem..
  • on MySQL Server side there was a huge amount of internal locks around every SQL query execution
  • while on the InnoDB side there was a huge general overhead related to transactions management + related locks..
  • the first step ahead was made here in MySQL 5.6
  • but the real changes came only with MySQL 5.7 ;-)

So, our 1.6M QPS on Point-Selects with MySQL 5.7 we were able to reach under the most heavy conditions :
  • each Sysbench "client" thread is executing a single point-select query per iteration
  • no transactions clauses used, no grouping..
  • single query round-trip is driving the response time latency
  • a fair simulation of a key-value store by SQL access

and the Sysbench execution command line is looking then as the following:
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-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
        --oltp-read-only=on run  > /tmp/test_$n.log &

Now, about 1M QPS result published on MariaDB 10.1 :
  • if you'll look in details published in their article, instead of --oltp-point-selects=1 the --oltp-point-selects=1000 was used
  • and these 1K point-selects then grouped and executed within a single transaction!.. (--oltp-skip-trx=off is used (default))

Adapting these test conditions to my Sysbench command line this will then give the following :
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-socket=/SSD_raid0/mysql.sock \
        --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
        --mysql-table-engine=INNODB  --db-driver=mysql \
        --oltp-point-selects=1000 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
        --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=off \
        --oltp-read-only=on run  > /tmp/test_$n.log &

How does this change the initial workload test case ?
  • in one word : completely ;-)
  • by grouping 1000 queries within a single transaction we're lowering a lot the whole related transaction locks..
  • and as the result, an overall QPS numbers are growing! ;-)
  • but did you see many real cases when a single MySQL session is grouping 1000 SELECT queries within a single transaction ?..
  • I may still imagine a need to run several SELECTs within a single transaction to guarantee a read consistency...
  • but such kind of queries are usually not short ones, so not really good to show a high QPS ;-)
  • but well, even they are really short as point-selects.. -- what kind of application will run them by 1000 in a single shot ?..
  • such kind of a "workaround" we proposed yet to our users when started to ship MySQL 5.6, but always mentioned that this is as a workaround, not a final solution (as this could help when you're really have many fast SELECTs and you can really group then within a single transaction without breaking your apps logic).. -- but as soon as potential amount of SELECTs to group is small, the transaction overhead is quickly eating your gain..
  • so, the real solution here is - MySQL 5.7 ;-)

Let me show now all this by example, using the test scenario as proposed by MariaDB and just vary the amount of point-selects executed within a single transaction. Let's start with 50 point-selects (which is already too much as to me, but well) :

50 point-selects in transaction :

Observations :
  • MySQL 5.7 is still reaching its 1.6M QPS as well
  • MariaDB 10.1 is reaching over 1.4M QPS (so, not only on Power while running within a "workaround" test conditions)..

Then the following are the results with 25, 10, and 5 point-selects executed within the same transaction :

25 point-selects in transaction :

10 point-selects in transaction :

5 point-selects in transaction :

Observations :
  • as you can see, less queries executed within the same single transaction - lower QPS is reached..
  • so, MySQL 5.7 is going from 1.6M QPS to 1.2M QPS
  • while MariaDB 10.1 is going from 1.4M QPS to just 600K QPS..

On the last test, with 5 point-selects within a transaction the lower QPS is also impacted by round-trips of BEGIN and COMMIT statements around transaction.. What the result will be if I'll not use transactions?

5 point-selects without transaction :

Observations :
  • as you can see, MySQL 5.7 is going back to its 1.6M QPS
  • while MariaDB 10.1 is going yet more down to just slightly higher than 400K QPS only..

To get a better understanding of the transaction statements impact let's get a look on the following graph comparing MySQL 5.7 in the last test with 5 point-selects within transaction (left side) -vs- 5 point-selects without transaction (right side) :

Observations :
  • as you can see in overall amount of Queries/sec the left side is even higher! (BEGIN and COMMIT are also counted as queries by MySQL stats)
  • and as we're wasting CPU cycles to process BEGIN and COMMIT - we're doing less SELECTs as the result..
  • while on the right side (without transactions) the whole CPU time is spent for SELECTs, and we're reaching 1.6M QPS then ;-)

Now :
  • it's up to you to consider whenever MariaDB 10.1 is really reaching 1M QPS, or 400K QPS only ;-))
  • however, what is important for me here after all : you can really reach 1.6M QPS with MySQL 5.7 whatever the test conditions are used here ;-))

Then, to avoid any kind of speculations about new Intel chips as the reason of excellent MySQL 5.7 scalability results, let me just remind you about the results obtained on the old severs (32cores-HT and 40cores-HT), running the old Intel CPUs (similar 4CPU sockets each, but just 8cores-HT and 10cores-HT per CPU socket, both are running at 2300Mhz frequency). The following are the results obtained on the same Sysbench RO Point-Select workload :
  • single point-select per iteration, no transactions
  • 8 tables of 1M rows are used in the first test
  • 1 table of 10M rows is used in the second test

32cores-HT :

40cores-HT :

72cores-HT :

Observations :
  • as you can see there is a huge gap between MySQL 5.7 and any other engine on 32, 40 and 72cores-HT HW..
  • the gap on 72cores-HT HW is way bigger only because MySQL 5.7 is continuing to scale and reaching a yet more higher result, while all other engines are already reached their limits and cannot go anymore further..
  • NOTE : only MySQL 5.7 is showing near the same QPS results on both 8-tables and 1-table workloads

  • From where is coming such a great scalability gain in MySQL 5.7 ?
    • first of all it's a continuous improvement process started yet since MySQL 5.6 with kernel_mutex split + RO transactions on InnoDB side, and many various internal contentions improvements on the MySQL Server side
    • then, on MySQL 5.7 InnoDB side : a whole transactions management redesign, improved lock management, redesigned index locking, etc...
    • MySQL 5.7 Server side : resolved contentions around MDL, THR_lock, LOCK_grant, LOCK_plugin, and all other "next-level" locks fixes.. -- in fact for today there is no more any known/visible internal contentions in MySQL 5.7 except coming from InnoDB ;-)
    • so far, all the credit is to our great MySQL Engineering Team! (all the listed stuff above is related to fundamental changes invented and implemented by our Engineering, taking us months and years of heavy work)..
    • and, of course, huge thanks to MySQL Community for all feedback we have about ;-)

  • When MariaDB will be able to scale as far as MySQL 5.7 ?
    • when it'll move to InnoDB SE from MySQL 5.7
    • and when on its Server side the lock contention in the "lock free" table definition cache code will be fixed ;-)

  • When Percona Server will scale as far as MySQL 5.7 ?
    • since Percona Server will move to the MySQL 5.7 code base
    • and XtraDB moves to InnoDB code base from MySQL 5.7
    • not before ;-)

  • or both are re:implementing all these changes in their 5.6 / 10.1 code ;-)
  • And hope I don't say you here any news, because the same story was already with MySQL 5.6 too, but who cares? ;-))

Well, what to say.. -- #MySQL 5.7 rocks! ;-)


Posted by Dimitri at 2:12 - Comments...
Categories: MySQL

Friday, 06 November, 2015

Slides from my talk about MySQL Performance @OpenWorld 2015

Slides from my talk during MySQL Central @OpenWorld 2015 are available from here now :
- http://dimitrik.free.fr/Presentations/MySQL_Perf-Tuning-OOW2015-dim.pdf 

they should be soon available from the OpenWorld site as well.


Posted by Dimitri at 20:33 - Comments...
Categories: MySQL

Thursday, 05 November, 2015

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= --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 !!! ;-))

Posted by Dimitri at 18:32 - Comments...
Categories: MySQL

Monday, 26 October, 2015

MySQL Performance: Yes, we can do more than 1.6M QPS (SQL) on MySQL 5.7 GA

It was exactly 2 years now since we reached 500K QPS with MySQL 5.7 -- it was a great milestone, and the highest ever result seen on MySQL with true SQL queries ;-)) And this was on 32cores-HT machine.. (the linked article contains the whole long story how we arrived to 250K QPS first, then 275K, 350K, 440K, and finally 500K QPS) -- the main improvement here is coming from greatly redesigned transactions and transaction list management in MySQL 5.7.

However, when the 40cores-HT server arrived to MySQL LAB we were not yet ready to show more than 500K QPS -- there were still some other contentions on external to InnoDB level.. Once these all were fixed, we finally reached 645K QPS on 40cores-HT server and pushed the limit more far yet ;-) -- this is what we were able to announce a year ago, during OOW2014.

Since then, an important work was made in MySQL 5.7 code to resolve all known contentions on the "server" side to let InnoDB run on a full speed without limits.. The work was done, but no one from our LAB servers was able to show the potential benefit of all these changes.. - in fact our servers became too old over a time, and it was no more clear what is the real limit of 5.7 and how far we can go today..

So, we started to look for our HW upgrade, and Intel new CPU chips were announced exactly on time (see http://wccftech.com/intel-unleashes-haswell-ex-xeon-e7-v3-processors-18-cores-45-mb-l3-cache-12-tb-ddr4-memory-support-57-billion-transistors/ -- sounds really good "57-billion-transistors" for MySQL 5.7 ;-)) -- and finally in collaboration with Intel we were able to get our hands on our new server with these new CPUs pretty quickly ;-)

Note :

  • the server we used is exactly the same as we have with 32cores-HT and 40cores-HT
  • same 4CPU sockets, etc.
  • just that per CPU socket we got 18cores-HT now (instead of 8 or 10 before)
  • and a slightly higher CPU frequency - 2.5Ghz instead of previous 2.3Ghz
  • however, the difference is not only in the number of cores and CPU frequency ;-)
  • the chip itself is really much more powerful !..
  • NOTE: this CPU chips are already widely comercially available and from a long time ready for production
  • (e.g. I'm not telling you here something new ;-))

Used HW config :
 Intel(R) Xeon(R) CPU E7-8890 v3 (codename Haswell-EX)
 4 CPU sockets x 18 cores-HT (144 CPU threads)
 2.5 Ghz, 512GB RAM
 Linux kernel 3.16

well, a long story short -- here is the QPS graph with the results I've obtained on this machine :

So, yes, our new record is over 1.6M QPS of true SQL queries !!!!! ;-))
(same OLTP_RO Point-Selects as before, same conditions, etc.)

And this is really killing! ;-))

NOTE: from profiler stats I've monitored this is still NOT a limit !!! we're scaling on such kind of workloads perfectly well! -- the limit is just a CPU time..

NOTE: this is near x2.5 times better -vs- 40cores-HT the best 5.7 results, so it's pretty amazing to see such a huge progress on Intel chips (40cores is 2.3Ghz, 72cores is 2.5Ghz, but it's not just frequency and amount of CPU cores)...

NOTE: all other RO results are also mostly x2.5 times better ;-))

An this was yet in August 2015..

Let's see now how well can scale today other vendors on this server and the same workload.

Sysbench RO Point-Selects 1M x 8-tables 72cores-HT :

Observations :
  • MySQL 5.7 is simply x3 times better than any other..
  • no comments ;-)

Now, what about scalability? (each Engine is now running only on 1, then 2, then all 4CPU sockets)

MySQL 5.7 Scalability @Sysbench RO Point-Selects 1M x 8-tables 72cores-HT :

MySQL 5.5 Scalability @Sysbench RO Point-Selects 1M x 8-tables 72cores-HT :

MySQL 5.6 Scalability @Sysbench RO Point-Selects 1M x 8-tables 72cores-HT :

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

MariaDB 10.1 Scalability @Sysbench RO Point-Selects 1M x 8-tables 72cores-HT :

  • MySQL 5.7 is showing a near perfect scalability up to 72cores-HT on 4CPU sockets
  • no one of other engines not scaling even up to 2CPU sockets (32cores-HT)
  • interesting that MySQL 5.6 and Percona 5.6 are able to out-pass 500K QPS here
  • not MariaDB 10.1

Well, now what if you have a single table hot in your workload? -- this was from a long time a very painful problem in MySQL, which is finally completely resolved with MySQL 5.7 :
Sysbench RO Point-Selects 10M x 1-table 72cores-HT :
Observations :
  • near the same performance on MySQL 5.7 on one 10M table as on 8x 1M tables
  • cannot say the same about others: something like 20% regression everywhere..
  • NOTE: x8 Sysbench processes are running here in parallel to get a rid of Sysbench's own contentions.

And scalability?

MySQL 5.7 Scalability @Sysbench RO Point-Selects 10M x 1-table 72cores-HT :

MySQL 5.5 Scalability @Sysbench RO Point-Selects 10M x 1-table 72cores-HT :

MySQL 5.6 Scalability @Sysbench RO Point-Selects 10M x 1-table 72cores-HT :

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

MariaDB 10.1 Scalability @Sysbench RO Point-Selects 10M x 1-table 72cores-HT :

No comments ;-)

Now, let's get a look on impact of this new Intel CPU..

MySQL 5.7 Scalability @Sysbench RO Point-Selects 1M x 8-tables :

Observations :
  • it's amazing to see how the gap is big...
  • NOTE: even on 8 concurrent users the result is near x2 times better than on any other server..
  • killing.. ;-)

MySQL 5.7 Scalability @Sysbench RO Point-Selects 10M x 1-table :

Same here..

Well, it's really amazing to see here that on the new CPU with 2 sockets (36cores-HT) I'm way faster than on 40cores-HT (4 sockets) before.. -- seems to me we should be prepared to see a lot of changes in MySQL Performance with these CPU chips..

Few more details about the presented test :
  • I've used exactly the same config params as mentioned 2 years ago (see: http://dimitrik.free.fr/blog/archives/2013/10/mysql-performance-the-road-to-500k-qps-with-mysql-57.html)
  • each engine as before was tested with tuned spin wait delay= 6/ 96 (helps a lot to lower InnoDB contentions here)
  • the best obtained results is used then for each load level
  • exactly the same Sysbench binaries as before, same scripts, etc..
  • just that the MySQL 5.7 version is the latest one ;-)
  • NOTE: for my curiosity I've also tested older 5.7 binaries as well.. -- no one was able to reach 1.6M QPS, and some even not reached 1M.. -- this is just to make it clear: we're reaching 1.6M QPS not just because the HW is better ;-))
  • MySQL 5.7 is really scaling much more far here (and you need the latest version)

 table_open_cache = 8000

# files
 innodb_log_files_in_group = 3

# buffers

# innodb
 innodb_stats_persistent = 1
 innodb_spin_wait_delay= 6 / 96

# perf special
 innodb_adaptive_flushing = 1
 innodb_flush_neighbors = 0
 innodb_read_io_threads = 4
 innodb_write_io_threads = 4
 innodb_io_capacity = 4000

# monitoring
 innodb_monitor_enable = '%'

in case you need it, Linux Sysbench binaries available from here :

NOTE: interesting that on this server using different Sysbench binaries did not show any difference, while using UNIX socket is still faster -vs- IP port for this workload..

The Sysbench command used to run Point-Selects test via UNIX socket (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-socket=/SSD_raid0/mysql.sock \
--mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
--mysql-table-engine=INNODB  --db-driver=mysql \
--oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
--oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
--oltp-read-only=on run  > /tmp/test_$n.log &

The Sysbench command used to run Point-Selects 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= --mysql-port=5700 \
--mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
--mysql-table-engine=INNODB  --db-driver=mysql \
--oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
--oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
--oltp-read-only=on run  > /tmp/test_$n.log &

What else to say? Still hard to believe, but seems like we're entering completely new levels in MySQL Performance...

Kudos MySQL & Intel Teams !!! ;-)

So far, I have much more yet details to share with all other testings already previewed on http://www.mysql.com/why-mysql/benchmarks/  as well, hope to publish them very soon, stay tuned ;-))

NOTE: if you're looking for some live news
  • come to reach me during my tomorrow's talk on MySQL Connect @OOW2015 about MySQL Performance ("MySQL Performance: Demystified Tuning and Best Practices", Moscone South—262, 10/27/15, 17:15 - 18:00) [Slides: PDF  ]
  • or meet all of us during BOF "Meet MySQL Team" or MySQL Community Reception (Tuesday, Oct 27 @ 7:00 PM - 9:00 PM | Jillian's at Metreon) just after my talk ;-))

Posted by Dimitri at 20:58 - Comments...
Categories: MySQL

Monday, 21 September, 2015

Speaking about MySQL 5.7 Performance at #PerconaLive Amsterdam 2015

I may only admit that #PerconaLive Conference becomes only better and better year to year. And I'm happy to be there and have an occasion to tell you all the latest news about MySQL 5.7 Performance as it for today..

And of course, if we're speaking about benchmarks, I'll not speak only about MySQL 5.7, I'll not miss either Percona Server 5.6 and MariaDB 10.1 ;-) -- hope to publish all these results later, over a time.. I've realized that I'm blogging less this year -- most of the time is going today to dig the performance problems we're hitting and looking for real solutions for them. Hope to get both fixed soon ;-) While during the conference, please, don't hesitate to ask questions and report your problems -- many persons from #MySQLTeam will be there, so be sure you'll be listened! And if you will don't miss their talks as well - this way you'll don't miss them for sure ;-)

Well, time to leave a sunny Paris..

And join a sunny Amsterdam ;-)

But who cares about weather ? ;-)
Day #1 is ready to start..

My talk is on Wednesday, just before a lunch. So, please, come hungry (for questions and sharing I mean ;-))

UPDATE : the slides from my talk are now available from here :
- http://dimitrik.free.fr/Presentations/MySQL_Perf-Benchmarks-PLive_AMS-Sep.2015-dim.pdf 


Posted by Dimitri at 4:20 - Comments...
Categories: MySQL, secondary

Tuesday, 14 April, 2015

MySQL Performance: Pushing yet more far scalability limits with MySQL 5.7

MySQL 5.7-RC1 is available now since the last week and you may find a lot about all the new improvement coming from our Team Blog and many other blog posts on this site as well. While in this article I'll just mention that with MySQL 5.7 all scalability limits are going more and more far! -- it's not that we resolved all of them, no, just that we become better and better ;-)

And one of the huge win coming now with MySQL 5.7-RC1 is that for the first time ever we're getting the same performance levels on a single hot table as on several tables used in parallel!

Here is the Max QPS result on the Sysbench RO Point-Select workload with 8-tables running on 40cores-HT server :

And here is the same load, but bombarding only one single table :

Now a full Sysbench OLTP_RO workload on 8-tables :

And on 1-table :

The story is pretty fun here, and I'll tell it in its full version (and many other "fun" stories) this afternoon (1:20pm) during my talk at Percona Live'15:
- http://www.percona.com/live/mysql-conference-2015/sessions/mysql-57-performance-scalability-benchmarks

UPDATE: Slides are available from here: http://dimitrik.free.fr/Presentations/MySQL_Perf-Benchmarks-PLive_2015-dim.pdf 

What to add.. - MySQL 5.7 really rocks!! kudos MySQL Team! ;-)


Posted by Dimitri at 18:28 - Comments...
Categories: MySQL

Tuesday, 17 February, 2015

MySQL Performance : Impact of InnoDB Transaction Isolation Modes in MySQL 5.7

There were so many valuable articles already written by others over past years explaining all details about InnoDB transaction isolation modes and how to deal with this. So, I'll avoid to repeat what was already said ;-) -- my attention attracted the performance study made by PeterZ and published in the following article: http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/ -- the article is very good and providing a good analyze of the observed problem which is solved by using READ-COMMITTED transaction isolation instead of REPEATABLE-READ (which is default in InnoDB).. The natural question is coming then: why don't we have then the READ-COMMITTED mode by default?.. Is there any danger?..

Let's then investigate together..

First of all, you should keep in your mind not only the theory, but also a way in which all this stuff is implemented within InnoDB :

  • transaction isolation / MVCC in InnoDB is implemented via ReadViews
  • every time a ReadView is created, a mutex (trx_sys) should be acquired
  • in REPEATABLE-READ mode a ReadView is created on transaction start
  • in READ-COMMITTED mode a ReadView is created on every statement(!)
  • means, if your statements are short -- you may hit a storm on trx_sys mutex contention in your workload..

Of course, all depends on a workload, and if you're lucky, you will probably see only a benefit here (all is possible, right? - at least in theory)..

Let me show you now some cases where you'll feel yourself much less lucky ;-)

For my test cases I'll use :

  • 40cores-HT server
  • running OEL 6.5 2.6.32-504 kernel
  • extremely fast flash storage (Fusion-io ioMemory)
  • each workload is using 64 concurrent users
  • 3 test cases executed on each workload :
    • REPEATABLE-READ mode is configured (RR)
    • READ-COMMITTED mode is configured (RC)
    • READ-UNCOMMITTED mode is configured (RU)

DBT-2 500W Workload (TPC-C) :

Observations :
  • you may already observe here a slightly lower TPS on both 2nd (RC) and 3rd (RU) test cases comparing to the first one (RR)
  • the "regression" is not very big, but notable
  • let's get a look now on internal lock contentions within InnoDB..
Lock contentions :

Observations :
  • a jumping contention on trx_sys mutex is very well seen for RC and RU tests
  • however it's not yet too big to make a serious damage..

Now, let's move to a heavy Sysbench OLTP_RW -- I've slightly changed the "classic" OLTP_RW here by adding a Read/Write ratio in my tests :
  • initially the load is starting with 128:1 ratio (mostly Reads, less Writes)
  • then 16:1
  • then 4:1
  • then 2:1
  • and finally 1:1

I'm using this test case also to evaluate the impact of writes in transactions, etc..

So far:

Sysbench OLTP_RW 32x10M-tables, rw128/16/4/2/1 :

Observations :
  • here the impact is more then just notable ;-)
  • and this is only due trx_sys mutex contention? or something else?..
Lock contentions :

Observations :
  • oh, indeed, trx_sys is jumping too high now!..
  • and could it be even more worse??

let's see ;-)

The next workload has a code name "OLTP_RW-p_sel10" - all reads in this test are replaced by 10 point-selects, that's all, making the load much more aggressive on writes and short and fast on reads :

Sysbench OLTP_RW-p_sel10 32x10M-tables, rw128/16/4/2/1 :

Observations :
  • indeed, seeing a x2 time worse performance is really killing..
  • and still due trx_sys mutex??
Lock contentions :

no comments ;-))

Well, you may still say that it's just because this server is too big and that's why I'm observing all these contentions, and you'll be not far from the reality -- on smaller machines all these contentions are, of course, lower - but! still "notable" ;-))

The same OLTP_RW-p_sel10, but on 20cores-HT :

(while many x2 CPU Intel machines today are having more than 32cores-HT in total, so a "small" HW becomes a big one ;-))

  • so, what should we finally conclude from all this presented stuff???..
  • PeterZ told us a so nice story, and now you're coming with your b*** and showing that PeterZ was wrong...
  • Stop, guys, PeterZ was not wrong!!! ;-)
  • ?? -- so, you're lying ??????
  • and I'm not lying either ;-))
  • ???.....
  • well, what you should keep in mind is that there is no a "silver bullet" and the most universal answer in most of the cases will be "it depends" ;-))
  • and with InnoDB Transaction Isolation is the same story here!
  • THE GENERAL RULE could be the following :
    • if your queries and transactions are short : use rather the default REPEATABLE-READ mode!
    • if your queries are long and reading a lot of data which are likely to be modified by other transactions in parallel : then use the READ-COMMITTED mode - this will allow you to read already committed changes while your query is progressing and avoid to be lost in scanning of the old page images instead (as PeterZ well showed you in his story ;-))
useful links for more info :
As usual, any comments are welcome!

Posted by Dimitri at 23:50 - Comments...
Categories: MySQL

Monday, 17 November, 2014

MySQL Performance: 5.7 and RDS Aurora, so what?.. ;-)

It was very fun for me to read last week the announces about RDS Aurora - specially the parts related to its performance gain over MySQL: Aurora is claimed to show x5 times better performance than MySQL! However, without publishing any details about ;-) -- and the only details I was able to find until now and group together are the following:

  • Aurora is a proprietary closed source database engine, "compatible" with MySQL (so, not an improved MySQL fork, as many expected..)
  • Sysbench workloads were used to evaluate Aurora performance
  • the only currently published Sysbench results are the following:
    • 500K SELECT/sec
    • 100K UPDATE/sec
  • again, no details about any of the tests..
  • in some articles instead of UPDATE/sec performance was mentioned INSERT/sec, but keeping in mind that there is simply no pure INSERT test available by default within Sysbench workloads, I may only suppose the mentioned 100K writes/sec are corresponding to the UPDATE/sec performance..

Fine then. But, so what?..

Let's see now where we are with MySQL Performance for today?.. ;-))

Yet more than a year ago we already announced 500K SELECT/sec with MySQL 5.7 on Sysbench (and you may read a full story about, if you got it missed). And this year during MySQL Central @OpenWorld we already presented 645K SELECT/sec with the latest MySQL 5.7! - here are the graphs taken from a live load just this morning :
  • the first graphs is showing the SELECT/sec level
  • the second graph - the corresponding amount of concurrent users (sessions) starting with 8, then 16, 32, 64, 128, 256 users

Sysbench SELECT Performance @MySQL 5.7 :

This is a single MySQL instance running on a single HW server. And as you see, we're reaching 645K SELECT/sec since 128 concurrent users. Is the 645K QPS level is the max limit for this server?.. - of course not, it should be possible to do yet more better here by improving our SQL layer, because on the same HW we're able to reach 1M QPS via InnoDB Memcached plugin (where all the data are going the same way directly from InnoDB, but bypassing all the SQL layers).. Work in progress ;-))

Then, what about UPDATE/sec performance?..

Again, a single HW server, single MySQL 5.7 instance, few simple SSD disks (even not a super fast flash storage as we have from Fusion-io and LSI in our LAB) :
  • the first graphs is showing the UPDATE/sec level
  • the second graph - the corresponding amount of concurrent users (sessions) starting with 8, then 16, 32, 64, 128, 256 users

Sysbench UPDATE Performance @MySQL 5.7 :

As you can see, we're doing even slightly more than 100K UPDATE/sec here ;-)

And you may ask then: Why MySQL Team @Oracle did not publish such a great result on UPDATE performance?.. - Well, just because there is nothing to be proud of for the moment.. ;-)) Of course, if you'll run the same workload on HDD storage you'll get much more worse results (for ex. on this server with HDD I have only 2K UPDATE/sec).. - so, for sure, SSD is much more efficient for random I/O writes. However, we're not doing better when using a much more faster flash storage than SSD, and this is our main headache today for all IO-bound workloads ;-) -- the limit today is still in the MySQL/InnoDB code itself.. - and we know where, and we're working hard to get it fixed.. And once this stuff will be improved, then we'll have something to be proud of, and then you'll hear about us for sure ;-))

Now, looking back to RDS Aurora results on Sysbench.. - so, what?.. MySQL 5.7 is already doing better! ;-)

Well, still looking for claimed x5 times performance difference..

(NOTE: if you have any troubles to reach over 500K SELECT/sec or 100K UPDATE/sec on MySQL 5.7, I have a webinar right this Wednesday to tell you all about MySQL/InnoDB internals and related tuning, don't hesitate to join: http://www.mysql.com/news-and-events/web-seminars/tuning-and-best-practices-for-developers-and-dbas/)

Posted by Dimitri at 16:40 - Comments...
Categories: MySQL

Wednesday, 29 October, 2014

Speaking about MySQL 5.7 Performance @Percona Live London 2014

Percona Live London 2014 is starting next week and I'm happy to speak there about MySQL 5.7 Performance, Scalability & Benchmarks latest news. Indeed, MySQL 5.7 has a lot of positive changes and impressive improvements. And, looking on MySQL 5.7 Performance, there are many cases where the gap comparing to MySQL 5.6 today is simply amazing! ;-) -- but, of course, there are still some pending issues which are requiring yet more work to reach the expected speed-up.. So, I'll tell you all about the most hot stories around, while I'm also always curious about your issues you're hitting on your production systems. Please, don't hesitate to share them - this is helping us to make MySQL yet more better! ;-)

UPDATE - my slides are here : http://dimitrik.free.fr/Presentations/MySQL_Perf-Benchmarks-PLUK_2014-dim-key.pdf 


Posted by Dimitri at 23:40 - Comments...
Categories: MySQL, news, secondary

Wednesday, 08 October, 2014

Indeed, MySQL 5.7 rocks : OLTP_RO/RW 1-table Benchmarks

This is the next part of the stories about MySQL 5.7 Performance..

So far, the previous story was about reaching 645K QPS with SQL queries, while in reality it's only a half of the full story ;-) -- because when last year we've reached 500K QPS due a huge improvement on the TRX-list code, the same improvement made a negative impact on the all single-table test workloads..

What happened finally :

  • the new code changes dramatically lowered contention on TRX-list (trx_sys mutex)
  • which is made MDL related locking much more hot..
  • and if one table becomes hot on a workload, MDL lock contention then is hitting its highest level..

So far, it was clear that MDL is needed a fix. Specially seeing that on 8-tables workload we're reaching 645K QPS. However there was a dilemma: should we push the TRX-list change to the 5.7 code, or wait first for MDL improvement?.. -- and we finally decided to push the changes, even if for some period of time we'll need to accept a regression on all single-table workload..

So, what we got the last year :
  • 500K QPS on 8-tables point-select
  • less than 200K QPS on 1-table.. - which was even worse than in MySQL 5.6 ;-)

It was clear, we have to fix MDL code asap, but the MDL story was not that simple either :
  • Dmitry Lenev made a quick dirty patch just removing completely all MDL related code from the source tree..
  • so, of course, we all expected to see a huge QPS jump after that on this experimental code, right? ;-)
  • however, for our big surprise, QPS just remained on exactly the same level...
  • what is going odd?..
  • in fact instead of the MDL contention we moved to the THR_lock mutex contention!
  • what is interesting that until the MDL code is present, we don't see any sign of THR_lock ;-)
  • and it's only since MDL is no more here -- THR_lock is firing!..
  • finally Dmitry made another dirty patch removing all THR_lock related code too..
  • and bingo! - without MDL & THR_lock code we doubled QPS at once, and it became clear that both contentions should be fixed to bring a speed-up on single-table workloads...

All my kudos here to Dmitry Lenev, who worked very hard to find the most optimal solution to fix both problems. There were several prototypes, less or more successful, until the final solution out-passed all our expectations -- just because it provided us the same QPS level as the binary which has no MDL nor THR_lock code at all!!! - and this is awesome! ;-))

I was happy to follow all this work very closely and play with each new update. Let me show you now just a short "making off" which is representing pretty well a summary of the one step in the progress in this work:

Observations :
  • there are 4 tests running the same OLTP_RO point-select workload on 8/16/32..1024 users :
    • #1 is using the original 5.7 code
    • #2 is using #1 + MDL fix
    • #3 is using #2 + removed THR_lock code
    • #4 is using #1 + removed MDL code + removed THR_lock code

  • as you can see, the original code (#1) is suffering of MDL rwlock contention and not out-passing 200K QPS..
  • the MDL fix in #2 is helping little bit to reach more than 200K QPS, but then the THR_lock mutex contention is killing an overall performance..
  • by removing completely the THR_lock code (#3) we can see the potential level of performance we should have if THR_lock contention was also fixed
  • then in #4 we can see the QPS level on 5.7 if there was no MDL & THR_lock code at all..
  • and what remarkable here that #3 and #4 are showing exactly the same performance!
  • so, the MDL fix worked extremely well to run as efficiently as if MDL code was removed ;-)
  • the next step was to do the same with THR_lock to get the solution simply perfect! ;-)
  • and it's exactly what was done finally in 5.7 and can be seen now in DMR5..

May only say kudos MySQL Runtime Team! kudos Dmitry Lenev!!! ;-))

Let me show you now the final impact of all these changes (and keep in mind that just a year ago the MySQL 5.7 results here were worse on all the following tests comparing to MySQL 5.6)..

Sysbench OLTP_RO 1-table 10M :

Sysbench OLTP_RO Point-Selects 1-table 10M :

Sysbench OLTP_RO Simple-Ranges 1-table 10M :

Sysbench OLTP_RW 1-table 10M trx2 :

Sysbench OLTP_RW 1-table 10M trx1 :

As you can see, the same performance improvement we can see on OLTP_RW workloads as well, where :
  • trx2 : means innodb_flush_log_at_trx_commit=2
  • trx1 : means innodb_flush_log_at_trx_commit=1
More about RW workloads in the next articles (and about "MySQL-5.7-futexV3" too ;-))

to be continued..
Posted by Dimitri at 11:22 - Comments...
Categories: MySQL