SysBench ShardingSphere-Proxy Empty Rule Performance Test

    1. Sysbench directly carries out stress testing on the performance of MySQL.
    2. Sysbench directly carries out stress testing on ShardingSphere-Proxy (directly connect MySQL).
    1. Db-related configuration: it is recommended that the memory is larger than the amount of data to be tested, so that the data is stored in the memory hot block, and the rest can be adjusted.
    2. ShardingSphere-Proxy-related configuration: it is recommended to use a high-performance, multi-core CPU, and other configurations can be customized.
    3. Disable swap partitions on all servers involved in the stress testing.

    Database

    ShardingSphere-Proxy

    bin/start.sh

    config.yaml

    1. sysbench oltp_read_write --mysql-host=${DB_IP} --mysql-port=${DB_PORT} --mysql-user=${USER} --mysql-password=${PASSWD} --mysql-db=test --tables=10 --table-size=1000000 --report-interval=10 --time=100 --threads=200  prepare

    Stress testing command

    1. sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
    2. Running the test with following options:
    3. Number of threads: 200
    4. Report intermediate results every 10 second(s)
    5. Initializing random number generator from current time
    6. Initializing worker threads...
    7. Threads started!
    8. # Report test results every 10 seconds, and the number of tps, reads per second, writes per second, and the total response time of more than 95th percentile.
    9. [ 10s ] thds: 200 tps: 11161.70 qps: 223453.06 (r/w/o: 156451.76/44658.51/22342.80) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
    10. ...
    12. SQL statistics:
    13.     queries performed:
    14.         read:                            19560590                       # number of reads     
    15.         write:                           5588740                        # number of writes
    16.         other:                           27943700                       # number of other operations (COMMIT etc.)
    17.         total:                           27943700                       # the total number
    18.     transactions:                        1397185 (11638.59 per sec.)    # number of transactions (per second)
    19.     ignored errors:                      0      (0.00 per sec.)         # number of ignored errors (per second)
    20.     reconnects:                          0      (0.00 per sec.)         # number of reconnections (per second)
    22. General statistics:
    24.     total number of events:              1397185                        # toal number of transactions
    26. Latency (ms):
    27.          min:                                    5.37                   # minimum latency
    28.          avg:                                   17.13                   # average latency
    29.          max:                                  109.75                   # maximum latency
    30.          95th percentile:                       24.83                   # average response time of over 95th percentile.
    31.          sum:                             23999546.19
    33. Threads fairness:
    34.     events (avg/stddev):           6985.9250/34.74                      # On average, 6985.9250 events were completed per thread, and the standard deviation is 34.74
    35.     execution time (avg/stddev):   119.9977/0.01                        # The average time of each thread is 119.9977 seconds, and the standard deviation is 0.01

    Noticeable features

    1. CPU utilization ratio of the server where ShardingSphere-Proxy resides. It is better to make full use of CPU.
    2. I/O of the server disk where the DB resides. The lower the physical read value is, the better.