Comparing ProxySQL 1.4.3 vs. 1.4.4

ProxySQL 1.4.4 has recently been released (GA on Dec 20, 2017) which naturally leads to the question “What performance gains are to be expected when moving to the new version?”. In this article we compare performance between 1.4.3 and 1.4.4 in a CPU bound workload. The instances are configured with the default settings for the initial benchmark and then again after tuning one specific variable, mysql-max_stmts_per_connection, which can lead to substantial performance gains.

Lets first discuss what the ProxySQL variable mysql-max_stmts_per_connection affects and how it is evaluated. ProxySQL maintains a counter for each backend connection which increments each time a statement is prepared on that connection. Just before the connection is returned to the pool, this counter is evaluated against mysql-max_stmts_per_connection, and if the threshold is exceeded then the connection is closed (behaviour up to version 1.4.3) else the connection is reset (behaviour starting version 1.4.4 in order to improve the efficiency of connection handling as pointed out in Percona’s blog post regarding: XtraDB Cluster best practices for AWS).

Note: The mysql-max_stmts_per_connection variable is configured to 20 by default and can be tuned up however keep in mind that when increasing mysql-max_stmts_per_connection you may need to also increase the value of the MySQL variable max_prepared_stmt_count which has a maximum limit of 1048576.

In the graphs below performance is compared between ProxySQL version 1.4.3 and 1.4.4 using default values and two benchmarks for each (one with mysql-max_stmts_per_connection set to 20 [default] and another with the variable set to 100). A Sysbench benchmark was executed for 300 seconds with 64x threads performing a mixture of point and range selects on 10x tables consisting of 40,000,000 rows each running on a 3x node Percona XtraDB Cluster each running on 40x cores and 1 Gbps NICs.

The key averages for each benchmark are as follows:

Metric 1.4.3 (default) 1.4.4 (default) 1.4.3 (mspc 100) 1.4.4 (mspc 100)
**QPS (average)** `126179.53` `141689.68` `155537.27` `163209.93`
**Latency (average)** `14.67` `13.10` `11.93` `11.37`
**Latency (95th percentile)** `18.28` `16.41` `15.55` `14.73`

QPS Benchmark

  • It is interesting to note that ProxySQL 1.4.4 is 13% faster out of the box with the default settings compared to ProxySQL 1.4.3.
  • It is also quite interesting to see that when mysql-max_stmts_per_connection is tuned to 100, and for this specific workload, ProxySQL 1.4.3 in itself could perform 24% faster!
  • With ProxySQL 1.4.4 we can see that when mysql-max_stmts_per_connection is tuned to 100 performance is 15% faster, however this is still 5% faster than ProxySQL 1.4.3 when tuned as the code is more efficient in the newer release.

Latency Benchmark

  • A similar trend can be seen in terms of latency between the various versions of ProxySQL and the tuning of the mysql-max_stmts_per_connection variable.
  • Once again ProxySQL 1.4.4 exhibits the lowest amount of latency (especially when mysql-max_stmts_per_connection is tuned higher than the default value).
  • Naturally the effects of the mysql-max_stmts_per_connection variable largely depend on your workload and a synthetic read only sysbench workload serves more for comparative purposes.

Based on the above benchmarks it is fair to say that ProxySQL 1.4.4 has more consistent and efficient performance with regards to 1.4.3 resulting in at least a 13% improvement in average QPS and a 15% improvement in terms of average Latency out of the box.

### For reference, the command used to benchmark was:

sysbench  --threads=64 /usr/share/sysbench/oltp_read_only.lua --tables=10 --table-size=40000000 --report-interval=5 --rand-type=pareto 
--forced-shutdown=1 --time=300 --events=0 --percentile=95  --mysql-user=sbtest --mysql-password=$pw --mysql-db=sbtest10t40M 
--mysql-storage-engine=INNODB --mysql-host=127.0.0.1 --mysql-port=6033 --point-selects=25 --range_size=5 --skip_trx=on run

Authored by: Nick Vyzas