DimitriK's (dim) Weblog

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