[MXS-1515] Benchmark the delay that MaxScale adds to a request Created: 2017-11-08  Updated: 2020-08-25  Resolved: 2018-10-01

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: markus makela Assignee: markus makela
Resolution: Fixed Votes: 1
Labels: None

Sprint: MXS-SPRINT-67

 Description   

Benchmark the minimum delay that MaxScale adds, on average, for each request.
The setup should have a client application on one server with MariaDB running on another server. MaxScale should be installed on the same server as the client application.

The setups that should be benchmarked:

  • Direct connection to database
  • Connection through MaxScale with readconnroute
  • Connection through MaxScale with readwritesplit

 Comments   
Comment by Valerii Kravchuk [ 2018-09-21 ]

If anyone cares, I've got the following results from sysbench 1.0.x /usr/share/sysbench/oltp_read_only.lua test with MariaDB 10.2.17 back end, MaxScale 2.2.14 on the same node and all connections for --host=127.0.0.1 to avoid network impact. Port 3308 is MariaDB direct connection, port 4006 is via readconnroute and port 4008 is via readwritesplit:

openxs@ao756:~/dbs/maria10.2$ sysbench --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run                                       sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            128856
 
        write:                           0
 
        other:                           18408
 
        total:                           147264
 
    transactions:                        9204   (306.78 per sec.)
 
    queries:                             147264 (4908.48 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0020s
 
    total number of events:              9204
 
 
 
Latency (ms):
 
         min:                                  2.60
 
         avg:                                  3.26
 
         max:                                 70.61
 
         95th percentile:                      4.03
 
         sum:                              29965.06
 
 
 
Threads fairness:
 
    events (avg/stddev):           9204.0000/0.00
 
    execution time (avg/stddev):   29.9651/0.00
 
 
 
--
 
 
 
openxs@ao756:~/dbs/maria10.2$ sysbench --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4006 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            83230
 
        write:                           0
 
        other:                           11890
 
        total:                           95120
 
    transactions:                        5945   (198.13 per sec.)
 
    queries:                             95120  (3170.08 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0055s
 
    total number of events:              5945
 
 
 
Latency (ms):
 
         min:                                  3.93
 
         avg:                                  5.04
 
         max:                                 16.39
 
         95th percentile:                      5.99
 
         sum:                              29973.73
 
 
 
Threads fairness:
 
    events (avg/stddev):           5945.0000/0.00
 
    execution time (avg/stddev):   29.9737/0.00
 
 
 
-- 
 
 
openxs@ao756:~/dbs/maria10.2$ sysbench --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4008 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            90104
 
        write:                           0
 
        other:                           12872
 
        total:                           102976
 
    transactions:                        6436   (214.50 per sec.)
 
    queries:                             102976 (3432.06 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0042s
 
    total number of events:              6436
 
 
 
Latency (ms):
 
         min:                                  3.86
 
         avg:                                  4.66
 
         max:                                 26.18
 
         95th percentile:                      5.28
 
         sum:                              29974.69
 
 
 
Threads fairness:
 
    events (avg/stddev):           6436.0000/0.00
 
    execution time (avg/stddev):   29.9747/0.00

I do not see any impact of script's --skip-trx=on parameter, while disabling prepared statements with --db-ps-mode=disable gave me the following:

openxs@ao756:~/dbs/maria10.2$ sysbench --db-ps-mode=disable --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=3308 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            118888
 
        write:                           0
 
        other:                           16984
 
        total:                           135872
 
    transactions:                        8492   (283.05 per sec.)
 
    queries:                             135872 (4528.83 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0016s
 
    total number of events:              8492
 
 
 
Latency (ms):
 
         min:                                  2.83
 
         avg:                                  3.53
 
         max:                                 10.84
 
         95th percentile:                      4.41
 
         sum:                              29966.42
 
 
 
Threads fairness:
 
    events (avg/stddev):           8492.0000/0.00
 
    execution time (avg/stddev):   29.9664/0.00
 
 
 
--
 
 
 
openxs@ao756:~/dbs/maria10.2$ sysbench --db-ps-mode=disable --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4006 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            67718
 
        write:                           0
 
        other:                           9674
 
        total:                           77392
 
    transactions:                        4837   (161.20 per sec.)
 
    queries:                             77392  (2579.22 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0060s
 
    total number of events:              4837
 
 
 
Latency (ms):
 
         min:                                  4.44
 
         avg:                                  6.20
 
         max:                                 15.52
 
         95th percentile:                      7.30
 
         sum:                              29973.10
 
 
 
Threads fairness:
 
    events (avg/stddev):           4837.0000/0.00
 
    execution time (avg/stddev):   29.9731/0.00
 
 
 
--
 
 
 
openxs@ao756:~/dbs/maria10.2$ sysbench --db-ps-mode=disable --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4008 --mysql-user=root --mysql-db=sbtest /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.1.0-2343e4b (using bundled LuaJIT 2.1.0-beta2)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            86128
 
        write:                           0
 
        other:                           12304
 
        total:                           98432
 
    transactions:                        6152   (205.03 per sec.)
 
    queries:                             98432  (3280.51 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0051s
 
    total number of events:              6152
 
 
 
Latency (ms):
 
         min:                                  4.07
 
         avg:                                  4.87
 
         max:                                 15.55
 
         95th percentile:                      5.57
 
         sum:                              29977.46
 
 
 
Threads fairness:
 
    events (avg/stddev):           6152.0000/0.00
 
    execution time (avg/stddev):   29.9775/0.00

Test was performed on slow netbook with 2 cores.

Comment by markus makela [ 2018-09-26 ]

Results of testing with a direct connection, HAProxy and MaxScale (readconnroute).

+------------------+
 
| Direct Connection|
 
+------------------+
 
 
 
[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=3000 --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            430332
 
        write:                           0
 
        other:                           61476
 
        total:                           491808
 
    transactions:                        30738  (1024.54 per sec.)
 
    queries:                             491808 (16392.57 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0006s
 
    total number of events:              30738
 
 
 
Latency (ms):
 
         min:                                  0.51
 
         avg:                                  0.97
 
         max:                                  7.94
 
         95th percentile:                      1.21
 
         sum:                              29967.68
 
 
 
Threads fairness:
 
    events (avg/stddev):           30738.0000/0.00
 
    execution time (avg/stddev):   29.9677/0.00
 
 
 
+---------+
 
| HAProxy |
 
+---------+
 
 
 
[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4012 --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            238686
 
        write:                           0
 
        other:                           34098
 
        total:                           272784
 
    transactions:                        17049  (568.24 per sec.)
 
    queries:                             272784 (9091.83 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0017s
 
    total number of events:              17049
 
 
 
Latency (ms):
 
         min:                                  0.83
 
         avg:                                  1.76
 
         max:                                 30.23
 
         95th percentile:                      2.61
 
         sum:                              29975.22
 
 
 
Threads fairness:
 
    events (avg/stddev):           17049.0000/0.00
 
    execution time (avg/stddev):   29.9752/0.00
 
 
 
+----------+
 
| MaxScale |
 
+----------+
 
 
 
[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4008 --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            234948
 
        write:                           0
 
        other:                           33564
 
        total:                           268512
 
    transactions:                        16782  (559.36 per sec.)
 
    queries:                             268512 (8949.79 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0006s
 
    total number of events:              16782
 
 
 
Latency (ms):
 
         min:                                  0.97
 
         avg:                                  1.79
 
         max:                                  5.43
 
         95th percentile:                      2.76
 
         sum:                              29975.47
 
 
 
Threads fairness:
 
    events (avg/stddev):           16782.0000/0.00
 
    execution time (avg/stddev):   29.9755/0.00

HAProxy configuration:

global
 
    log         127.0.0.1 local2
 
    chroot      /var/lib/haproxy
 
    pidfile     /var/run/haproxy.pid
 
    maxconn     4000
 
    user        haproxy
 
    group       haproxy
 
    daemon
 
 
 
defaults
 
    mode                    tcp
 
    log                     global
 
    option                  httplog
 
    option                  dontlognull
 
    option http-server-close
 
    option forwardfor       except 127.0.0.0/8
 
    option                  redispatch
 
    retries                 3
 
    timeout http-request    10s
 
    timeout queue           1m
 
    timeout connect         10s
 
    timeout client          1m
 
    timeout server          1m
 
    timeout http-keep-alive 10s
 
    timeout check           10s
 
    maxconn                 3000
 
 
 
frontend fr_server1
 
        bind 0.0.0.0:4012
 
        default_backend server1
 
 
 
backend server1
 
        server srv1 127.0.0.1:3000 maxconn 2048

maxscale.cnf

[maxscale]
 
threads=4
 
 
 
[server1]
 
type=server
 
address=127.0.0.1
 
port=3000
 
protocol=MariaDBBackend
 
 
 
[MariaDB-Monitor]
 
type=monitor
 
module=mariadbmon
 
servers=server1
 
user=maxuser
 
password=maxpwd
 
monitor_interval=10000
 
 
 
[Read-Connection-Router]
 
type=service
 
router=readconnroute
 
router_options=running
 
servers=server1
 
user=maxuser
 
password=maxpwd
 
 
 
[Read-Connection-Listener]
 
type=listener
 
service=Read-Connection-Router
 
protocol=MariaDBClient
 
port=4008

Comment by markus makela [ 2018-09-27 ]

Results with socat TCP4-LISTEN:4008,reuseaddr,fork,nodelay TCP4:127.0.0.1:3000,nodelay:

[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-host=127.0.0.1 --mysql-port=4008 --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            284494
 
        write:                           0
 
        other:                           40642
 
        total:                           325136
 
    transactions:                        20321  (677.32 per sec.)
 
    queries:                             325136 (10837.11 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0008s
 
    total number of events:              20321
 
 
 
Latency (ms):
 
         min:                                  0.84
 
         avg:                                  1.48
 
         max:                                  7.23
 
         95th percentile:                      2.35
 
         sum:                              29976.13
 
 
 
Threads fairness:
 
    events (avg/stddev):           20321.0000/0.00
 
    execution time (avg/stddev):   29.9761/0.00

Compared to the result we get when we eliminate one TCP connection by replacing it with a UNIX domain socket connection.

[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-socket=/home/markusjm/build-2.2/rconn.sock --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            325038
 
        write:                           0
 
        other:                           46434
 
        total:                           371472
 
    transactions:                        23217  (773.85 per sec.)
 
    queries:                             371472 (12381.68 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0005s
 
    total number of events:              23217
 
 
 
Latency (ms):
 
         min:                                  0.72
 
         avg:                                  1.29
 
         max:                                  6.78
 
         95th percentile:                      1.89
 
         sum:                              29972.00
 
 
 
Threads fairness:
 
    events (avg/stddev):           23217.0000/0.00
 
    execution time (avg/stddev):   29.9720/0.00

The results above were achieved with the following maxscale.cnf.

[maxscale]
 
threads=4
 
 
 
[server1]
 
type=server
 
address=127.0.0.1
 
port=3000
 
protocol=MariaDBBackend
 
 
 
[MariaDB-Monitor]
 
type=monitor
 
module=mariadbmon
 
servers=server1
 
user=maxuser
 
password=maxpwd
 
monitor_interval=10000
 
 
 
[Read-Connection-Router]
 
type=service
 
router=readconnroute
 
router_options=running
 
servers=server1
 
user=maxuser
 
password=maxpwd
 
 
 
[Read-Connection-Listener]
 
type=listener
 
service=Read-Connection-Router
 
protocol=MariaDBClient
 
socket=/home/markusjm/build-2.2/rconn.sock

Comment by markus makela [ 2018-09-27 ]

Results from socat UNIX-LISTEN:$PWD/mysql.sock,fork TCP4:127.0.0.1:3000,nodelay.

[markusjm@monolith sysbench]$ sysbench --db-driver=mysql --table-size=1000000 --threads=1 --time=30 --mysql-socket=/home/markusjm/MaxScale/mysql.sock --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test /usr/share/sysbench/oltp_read_only.lua run
 
sysbench 1.0.12 (using system LuaJIT 2.1.0-beta3)
 
 
 
Running the test with following options:
 
Number of threads: 1
 
Initializing random number generator from current time
 
 
 
 
 
Initializing worker threads...
 
 
 
Threads started!
 
 
 
SQL statistics:
 
    queries performed:
 
        read:                            317912
 
        write:                           0
 
        other:                           45416
 
        total:                           363328
 
    transactions:                        22708  (756.86 per sec.)
 
    queries:                             363328 (12109.82 per sec.)
 
    ignored errors:                      0      (0.00 per sec.)
 
    reconnects:                          0      (0.00 per sec.)
 
 
 
General statistics:
 
    total time:                          30.0013s
 
    total number of events:              22708
 
 
 
Latency (ms):
 
         min:                                  0.65
 
         avg:                                  1.32
 
         max:                                 67.20
 
         95th percentile:                      1.96
 
         sum:                              29974.78
 
 
 
Threads fairness:
 
    events (avg/stddev):           22708.0000/0.00
 
    execution time (avg/stddev):   29.9748/0.00

Generated at Thu Feb 08 04:07:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.