« MySQL Performance: What is odd with 1M QPS claimed by MariaDB 10.1 ?.. | Main | MySQL Performance: Re:Visiting InnoDB vs MyISAM with MySQL 5.7 »
Friday, 27 November, 2015
MySQL Performance: Improved Connect/sec Rate in MySQL 5.7
This article is continuing the MySQL 5.7 Performance story, started from 1.6M
QPS on MySQL 5.7 details post , then 1M
QPS on mixed OLTP_RO with MySQL 5.7 article, and detailed story
about why
the Point-Selects performance is so critical (and why the 1M result
published by MariaDB is not fair)..
The current story will be
about Connect/sec (connect/disconnect) performance improvement in
MySQL 5.7 - such kind of metric is very critical for any application
which cannot use persistent connections all the time (and many web apps
are in such a case). Well, I'd say MySQL from the beginning was
extremely good for its lightweight connections, and made in the past the
base of success for many web solutions.. However, time is going, and
we're no more with 4cores as "commodity hardware" (this is rather a
smart-watch today ;-)) - so, there was a need to speed-up this Connect
rate to match higher workloads. This was already greatly done in MySQL
5.6, and finally yet more improved in MySQL 5.7 - you may read all
details about directly from our developers - I'll just present here
a short summary about where we're today..
So far, first of all,
how to test the Connect/sec performance of your MySQL server instance? -
the most simple way here is just to use a standard Sysbench kit, load
10M rows into sysbench database (1 table or several tables, no matter --
the main show-stopper here is the Connect code itself), and then run the
following :
#!/bin/bash # ---------------------------------------------------------------- # Connect/sec test # ---------------------------------------------------------------- for Users in 8 16 32 64 128 256 512 1024 do LD_PRELOAD=/usr/lib64/libjemalloc.so.1 sysbench --num-threads=$Users \ --test=oltp --oltp-table-size=10000000 \ --db-ps-mode=disable --oltp-dist-type=uniform --oltp-table-name=sbtest_10M \ --max-requests=0 --max-time=300 --mysql-socket=/tmp/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 --oltp-reconnect-mode=query --oltp-connect-delay=0 run sleep 30 done # ----------------------------------------------------------------
means on every point-select query your client session will re-connect, and the final QPS result will give you the max Connect/sec rate your MySQL instance is able to reach for a given amount of concurrent users.
And here are the results obtained from older to newer generations Intel-based Linux servers :
12cores-HT @2.9Ghz :

32cores-HT @2.3Ghz :

40cores-HT @2.3Ghz :

72cores-HT @2.5Ghz :

Instead of SUMMARY :
- Connect/sec performance is mainly depending on the MySQL "connect/disconnect" code itself + CPU chip speed
- it's hard to speak about "scalability" here as the max possible Connect/sec rate limit is reached pretty quickly and depending on IP stack performance as well..
- tuning "thread_cache_size" to something bigger than zero is helping here, but not too much..
- MySQL 5.7 showing the best performance here regardless the HW platform, and reaching over 100K Connect/sec on the latest Intel CPU chip
- there is a clear better-and-better tendency in MySQL 5.5 => 5.6 => 5.7 results
- and we still can do yet more better with MySQL 5.8 ! (question of time and resources - so, please, send us your feedback/ votes/ wishes if you want to see it ;-))
MySQL 5.7 rocks! ;-))
Rgds,
-Dimitri
blog comments powered by DisqusNote: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..