1.软件安装 

yum -y install sysbench 
................................
** Found 3 pre-existing rpmdb problem(s), 'yum check' output follows:
mysql-community-client-8.0.27-1.el7.x86_64 has missing requires of mysql-community-libs(x86-64) >= ('0', '8.0.11', None)
mysql-community-devel-8.0.27-1.el7.x86_64 has missing requires of libmysqlclient.so.21()(64bit)
mysql-community-devel-8.0.27-1.el7.x86_64 has missing requires of mysql-community-libs(x86-64) >= ('0', '8.0.11', None)
  Installing : postgresql-libs-9.2.24-4.el7_8.x86_64                                                                                                                                          1/4 
  Installing : luajit-2.0.5-1.20220913.46e62cd.el7.x86_64                                                                                                                                     2/4 
  Installing : ck-0.5.2-2.el7.x86_64                                                                                                                                                          3/4 
  Installing : sysbench-1.0.17-2.el7.x86_64                                                                                                                                                   4/4 
  Verifying  : ck-0.5.2-2.el7.x86_64                                                                                                                                                          1/4 
  Verifying  : luajit-2.0.5-1.20220913.46e62cd.el7.x86_64                                                                                                                                     2/4 
  Verifying  : sysbench-1.0.17-2.el7.x86_64                                                                                                                                                   3/4 
  Verifying  : postgresql-libs-9.2.24-4.el7_8.x86_64                                                                                                                                          4/4 

Installed:
  sysbench.x86_64 0:1.0.17-2.el7                                                                                                                                                                  

Dependency Installed:
  ck.x86_64 0:0.5.2-2.el7                             luajit.x86_64 0:2.0.5-1.20220913.46e62cd.el7                             postgresql-libs.x86_64 0:9.2.24-4.el7_8                            

Complete!

[tidb@mysql1 bin]$ sysbench --version
sysbench 1.0.17

2.准备配置文件 

vi config.txt
mysql-host=192.168.1.11
mysql-port=3390
mysql-user=root
mysql-password=rootroot
mysql-db=sbtest
time=60  
threads=16  
report-interval=10  
db-driver=mysql  

3.开启TIDB 的事务失败自动重试 

mysql> set global tidb_disable_txn_auto_retry = off;
Query OK, 0 rows affected (0.12 sec)

mysql> show variables like 'tidb_disable_txn_auto_retry';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| tidb_disable_txn_auto_retry | 1     |
+-----------------------------+-------+
1 row in set (0.00 sec)
--创建数据库 
mysql> create database sbtest;
Query OK, 0 rows affected (1.53 sec)

4.准备数据 
sysbench --config-file=config.txt oltp_point_select --tables=32 --table-size=100000 prepare
5.select/UPDATE/READ_ONLY 测试 

[tidb@mysql1 ~]$ sysbench --config-file=config.txt.16 oltp_point_select --tables=32 --table-size=100000 run >> report-sysbench-select-16.log
[tidb@mysql1 ~]$ sysbench --config-file=config.txt.8 oltp_point_select --tables=32 --table-size=100000 run >> report-sysbench-select-8.log
[tidb@mysql1 ~]$ sysbench --config-file=config.txt.4 oltp_point_select --tables=32 --table-size=100000 run >> report-sysbench-select-4.log

SELECT 报告分析:
[tidb@mysql1 ~]$ more report-sysbench-select-4.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 1939.96 qps: 1939.96 (r/w/o: 1939.96/0.00/0.00) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 1318.87 qps: 1318.87 (r/w/o: 1318.87/0.00/0.00) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 878.05 qps: 878.05 (r/w/o: 878.05/0.00/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 1330.77 qps: 1330.77 (r/w/o: 1330.77/0.00/0.00) lat (ms,95%): 7.04 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 1715.99 qps: 1715.99 (r/w/o: 1715.99/0.00/0.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 1955.90 qps: 1955.90 (r/w/o: 1955.90/0.00/0.00) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            91403
        write:                           0
        other:                           0
        total:                           91403
    transactions:                        91403  (1523.19 per sec.)
    queries:                             91403  (1523.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0062s
    total number of events:              91403

Latency (ms):
         min:                                    0.54
         avg:                                    2.62
         max:                                  156.25
         95th percentile:                        6.67
         sum:                               239868.90

Threads fairness:
    events (avg/stddev):           22850.7500/1826.32
    execution time (avg/stddev):   59.9672/0.00

[tidb@mysql1 ~]$ more report-sysbench-select-8.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 8 tps: 2251.06 qps: 2251.06 (r/w/o: 2251.06/0.00/0.00) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 2216.28 qps: 2216.28 (r/w/o: 2216.28/0.00/0.00) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 2298.76 qps: 2298.76 (r/w/o: 2298.76/0.00/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 2254.37 qps: 2254.37 (r/w/o: 2254.37/0.00/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 2276.38 qps: 2276.38 (r/w/o: 2276.38/0.00/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 2269.05 qps: 2269.05 (r/w/o: 2269.05/0.00/0.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            135676
        write:                           0
        other:                           0
        total:                           135676
    transactions:                        135676 (2260.56 per sec.)
    queries:                             135676 (2260.56 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0172s
    total number of events:              135676

Latency (ms):
         min:                                    0.53
         avg:                                    3.54
         max:                                   88.56
         95th percentile:                       10.65
         sum:                               479890.04

Threads fairness:
    events (avg/stddev):           16959.5000/828.50
    execution time (avg/stddev):   59.9863/0.00

[tidb@mysql1 ~]$ more report-sysbench-select-16.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 3018.75 qps: 3018.75 (r/w/o: 3018.75/0.00/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 3039.14 qps: 3039.14 (r/w/o: 3039.14/0.00/0.00) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 3116.57 qps: 3116.57 (r/w/o: 3116.57/0.00/0.00) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 3137.55 qps: 3137.55 (r/w/o: 3137.55/0.00/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 3117.02 qps: 3117.02 (r/w/o: 3117.02/0.00/0.00) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 3044.11 qps: 3044.11 (r/w/o: 3044.11/0.00/0.00) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            184755
        write:                           0
        other:                           0
        total:                           184755
    transactions:                        184755 (3078.23 per sec.)
    queries:                             184755 (3078.23 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0184s
    total number of events:              184755

Latency (ms):
         min:                                    0.55
         avg:                                    5.20
         max:                                  137.08
         95th percentile:                       13.46
         sum:                               959838.44

Threads fairness:
    events (avg/stddev):           11547.1875/2012.84
    execution time (avg/stddev):   59.9899/0.01

--4线程:1523.19 per sec 
--8线程:2260.56 per sec
--16线程:3078.23 per sec 
--说明16线程，每秒处理的事务数最多。16线程相比较优。

--Update index 测试命令

sysbench --config-file=config.txt.4 oltp_update_index --tables=32 --table-size=100000 run >> report-sysbench-update-4.log
sysbench --config-file=config.txt.8 oltp_update_index --tables=32 --table-size=100000 run >> report-sysbench-update-8.log
sysbench --config-file=config.txt.16 oltp_update_index --tables=32 --table-size=100000 run >> report-sysbench-update-16.log

--结果分析：
[tidb@mysql1 ~]$ more report-sysbench-update-4.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 256.44 qps: 256.44 (r/w/o: 0.00/256.14/0.30) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 253.61 qps: 253.61 (r/w/o: 0.00/253.41/0.20) lat (ms,95%): 25.28 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 257.29 qps: 257.29 (r/w/o: 0.00/257.29/0.00) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 258.61 qps: 258.61 (r/w/o: 0.00/258.51/0.10) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 261.09 qps: 261.09 (r/w/o: 0.00/260.69/0.40) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 243.71 qps: 243.71 (r/w/o: 0.00/243.61/0.10) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           15301
        other:                           11
        total:                           15312
    transactions:                        15312  (255.13 per sec.)
    queries:                             15312  (255.13 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0157s
    total number of events:              15312

Latency (ms):
         min:                                    2.00
         avg:                                   15.67
         max:                                  150.37
         95th percentile:                       24.83
         sum:                               239989.40

Threads fairness:
    events (avg/stddev):           3828.0000/388.93
    execution time (avg/stddev):   59.9974/0.00

[tidb@mysql1 ~]$ more report-sysbench-update-8.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 8 tps: 301.42 qps: 301.42 (r/w/o: 0.00/301.22/0.20) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 326.78 qps: 326.78 (r/w/o: 0.00/326.48/0.30) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 359.02 qps: 359.02 (r/w/o: 0.00/358.72/0.30) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 360.72 qps: 360.72 (r/w/o: 0.00/360.22/0.50) lat (ms,95%): 41.10 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 333.28 qps: 333.28 (r/w/o: 0.00/333.08/0.20) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 353.29 qps: 353.29 (r/w/o: 0.00/352.59/0.70) lat (ms,95%): 41.85 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           20331
        other:                           22
        total:                           20353
    transactions:                        20353  (339.10 per sec.)
    queries:                             20353  (339.10 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0196s
    total number of events:              20353

Latency (ms):
         min:                                    1.98
         avg:                                   23.58
         max:                                  215.35
         95th percentile:                       46.63
         sum:                               480023.35

Threads fairness:
    events (avg/stddev):           2544.1250/484.02
    execution time (avg/stddev):   60.0029/0.01

[tidb@mysql1 ~]$ more report-sysbench-update-16.log 
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 465.69 qps: 465.69 (r/w/o: 0.00/465.19/0.50) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 459.81 qps: 459.81 (r/w/o: 0.00/458.91/0.90) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 457.49 qps: 457.49 (r/w/o: 0.00/457.09/0.40) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 469.00 qps: 469.00 (r/w/o: 0.00/468.80/0.20) lat (ms,95%): 65.65 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 447.61 qps: 447.61 (r/w/o: 0.00/447.31/0.30) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 447.31 qps: 447.31 (r/w/o: 0.00/446.91/0.40) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           27459
        other:                           27
        total:                           27486
    transactions:                        27486  (457.82 per sec.)
    queries:                             27486  (457.82 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0347s
    total number of events:              27486

Latency (ms):
         min:                                    2.28
         avg:                                   34.93
         max:                                  338.67
         95th percentile:                       68.05
         sum:                               960186.30

Threads fairness:
    events (avg/stddev):           1717.8750/416.42
    execution time (avg/stddev):   60.0116/0.01

--4线程:255.13 per sec
--8线程:339.10 per sec
--16线程:457.82 per sec 
--由此可见，在当前16线程UPDATE事务较优。

--Read-only 测试命令
sysbench --config-file=config.txt.4 oltp_read_only --tables=32 --table-size=100000 run >> report-sysbench-read_only-4.log
sysbench --config-file=config.txt.8 oltp_read_only --tables=32 --table-size=100000 run >> report-sysbench-read_only-8.log
sysbench --config-file=config.txt.16 oltp_read_only --tables=32 --table-size=100000 run >> report-sysbench-read_only-16.log

--测试结果 
[tidb@mysql1 ~]$ more report-sysbench-read_only-4.log
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 4
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4 tps: 73.17 qps: 1174.69 (r/w/o: 1027.94/0.00/146.75) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 78.21 qps: 1249.69 (r/w/o: 1093.28/0.00/156.41) lat (ms,95%): 82.96 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 4 tps: 77.70 qps: 1244.22 (r/w/o: 1088.81/0.00/155.40) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 4 tps: 47.50 qps: 758.55 (r/w/o: 663.64/0.00/94.91) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 4 tps: 48.60 qps: 778.72 (r/w/o: 681.43/0.00/97.29) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 4 tps: 60.01 qps: 959.58 (r/w/o: 839.57/0.00/120.01) lat (ms,95%): 118.92 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            53984
        write:                           0
        other:                           7712
        total:                           61696
    transactions:                        3856   (64.21 per sec.)
    queries:                             61696  (1027.29 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0555s
    total number of events:              3856

Latency (ms):
         min:                                   15.45
         avg:                                   62.27
         max:                                  439.16
         95th percentile:                      121.08
         sum:                               240122.80

Threads fairness:
    events (avg/stddev):           964.0000/23.28
    execution time (avg/stddev):   60.0307/0.01

[tidb@mysql1 ~]$ more report-sysbench-read_only-8.log
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 8 tps: 82.08 qps: 1319.14 (r/w/o: 1154.19/0.00/164.96) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 84.91 qps: 1360.09 (r/w/o: 1190.28/0.00/169.81) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 85.60 qps: 1367.22 (r/w/o: 1196.12/0.00/171.10) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 78.20 qps: 1254.14 (r/w/o: 1097.65/0.00/156.49) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 90.90 qps: 1453.13 (r/w/o: 1271.44/0.00/181.69) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 92.20 qps: 1474.57 (r/w/o: 1290.06/0.00/184.51) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            72058
        write:                           0
        other:                           10294
        total:                           82352
    transactions:                        5147   (85.65 per sec.)
    queries:                             82352  (1370.36 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0936s
    total number of events:              5147

Latency (ms):
         min:                                   26.97
         avg:                                   93.32
         max:                                  414.15
         95th percentile:                      189.93
         sum:                               480336.12

Threads fairness:
    events (avg/stddev):           643.3750/106.90
    execution time (avg/stddev):   60.0420/0.03

[tidb@mysql1 ~]$ more report-sysbench-read_only-16.log
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 16 tps: 105.37 qps: 1702.44 (r/w/o: 1490.10/0.00/212.34) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 107.79 qps: 1720.07 (r/w/o: 1504.48/0.00/215.58) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 16 tps: 107.29 qps: 1721.44 (r/w/o: 1506.96/0.00/214.48) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 16 tps: 107.62 qps: 1717.15 (r/w/o: 1501.80/0.00/215.34) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 16 tps: 105.60 qps: 1691.79 (r/w/o: 1480.60/0.00/211.20) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 16 tps: 108.70 qps: 1736.67 (r/w/o: 1519.27/0.00/217.40) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            90160
        write:                           0
        other:                           12880
        total:                           103040
    transactions:                        6440   (107.07 per sec.)
    queries:                             103040 (1713.19 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1433s
    total number of events:              6440

Latency (ms):
         min:                                   31.30
         avg:                                  149.25
         max:                                  512.12
         95th percentile:                      292.60
         sum:                               961141.70

Threads fairness:
    events (avg/stddev):           402.5000/69.74
    execution time (avg/stddev):   60.0714/0.03

--只读测试:
---4线程:查询:1027.29 per sec
---8线程:查询:1370.36 per sec
---16线程:查询:1713.19 per sec
--由此可见，16线程的情况下，读取的查询次数最多。

--由此可将，当前系统:16线程在SELECT,UPDATE,read_only 时均是最优的。