sysbench压力测试TIDB

环境介绍

服务组件	版本	ip
tidb	v4.0.15	192.168.33.41-3
pd		192.168.33.41-3
kv		192.168.33.41-3
tiflash		192.168.33.44
sysbench	v1.0.20	192.168.33.31

TIDB安装

TIDB安装文档详细请见官方文档按照“增加 TiFlash 拓扑架构”部署，本文不介绍tidb部署过程

sysbench部署

安装sysbench

1 2	# curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh \| sudo bash sudo yum -y install sysbench

查看版本

1 2	# sysbench --version sysbench 1.0.20

修改配置文件

# cat sysbench.conf
# tidb数据库信息
mysql-host=192.168.33.41
mysql-port=4000
mysql-user=root
mysql-password=
mysql-db=sbtest
# 测试时长
time=120
# 测试线程数
threads=16
# 输出的报告时间间隔
report-interval=10
# db驱动
db-driver=mysql

导入测试数据

在数据导入前，需要对 TiDB 进行简单设置。在 MySQL 客户端中执行如下命令：

1	> set global tidb_disable_txn_auto_retry = off;

创建数据库sbtest:

1	> create database sbtest;

命令行输入以下命令，开始导入数据，config文件为上一步中配置的文件

1	# sysbench --config-file=sysbench.conf oltp_point_select --tables=32 --table-size=1000000 prepare

Point select测试命令

1	# sysbench --config-file=sysbench.conf oltp_point_select --tables=32 --table-size=1000000 run >>noFlashSelect.log

Update index测试命令

1	# sysbench --config-file=sysbench.conf oltp_update_index --tables=32 --table-size=1000000 run >>noFlashUpdate.log

Read-only 测试命令

1	# sysbench --config-file=sysbench.conf oltp_read_only --tables=32 --table-size=1000000 run >>noFlashReadOnly.log

将测试结果都输出到文件内，后续将tables增加TiFlash副本后在重新测试方便对比结果

启用TiFlash

按表构建TiFlash副本

TiFlash 接入 TiKV 集群后，默认不会开始同步数据。可通过 MySQL 客户端向 TiDB 发送 DDL 命令来为特定的表建立 TiFlash 副本：

1	ALTER TABLE table_name SET TIFLASH REPLICA count

该命令的参数说明如下：

count 表示副本数，0 表示删除。

将sbtest所有表启动TiFlash，由于我们只有一台TiFlash，所以最后的参数需要为1，0为删除TiFlash副本即禁用TiFlash

for x in `seq 1 32`
do
  mysql -uroot -h192.168.33.41 -P4000 -p -e "ALTER TABLE sbtest.sbtest${x} SET TIFLASH REPLICA 1;"
done

查看TiFlash同步进度

启用后TiFlash会同步表内数据至TiFlash，可以用以下mysql命令查询是否同步完成：

mysql> SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = 'sbtest' and TABLE_NAME = 'sbtest2';
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ID | REPLICA_COUNT | LOCATION_LABELS | AVAILABLE | PROGRESS |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
| sbtest       | sbtest2    |       99 |             1 |                 |         1 |        1 |
+--------------+------------+----------+---------------+-----------------+-----------+----------+
1 row in set (0.01 sec)

查询结果中：

AVAILABLE 字段表示该表的 TiFlash 副本是否可用。1 代表可用，0 代表不可用。副本状态为可用之后就不再改变，如果通过 DDL 命令修改副本数则会重新计算同步进度。
PROGRESS 字段代表同步进度，在 0.0~1.0 之间，1 代表至少 1 个副本已经完成同步。

使用 TiDB 读取 TiFlash

TiDB 提供三种读取 TiFlash 副本的方式。如果添加了 TiFlash 副本，而没有做任何 engine 的配置，则默认使用 CBO 方式。

智能选择
对于创建了 TiFlash 副本的表，TiDB 优化器会自动根据代价估算选择是否使用 TiFlash 副本。具体有没有选择 TiFlash 副本，可以通过 desc 或 explain analyze 语句查看，例如：

mysql> desc select count(*) from sbtest.sbtest2;
+----------------------------+------------+-------------------+---------------+----------------------------------+
| id                         | estRows    | task              | access object | operator info                    |
+----------------------------+------------+-------------------+---------------+----------------------------------+
| StreamAgg_24               | 1.00       | root              |               | funcs:count(Column#10)->Column#5 |
| └─TableReader_25           | 1.00       | root              |               | data:StreamAgg_8                 |
|   └─StreamAgg_8            | 1.00       | batchCop[tiflash] |               | funcs:count(1)->Column#10        |
|     └─TableFullScan_22     | 1000000.00 | batchCop[tiflash] | table:sbtest2 | keep order:false                 |
+----------------------------+------------+-------------------+---------------+----------------------------------+
4 rows in set (0.00 sec)

mysql> explain analyze select count(*) from sbtest.sbtest2;
+----------------------------+------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| id                         | estRows    | actRows | task              | access object | execution info                                                                        | operator info                    | memory    | disk |
+----------------------------+------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------+----------------------------------+-----------+------+
| StreamAgg_24               | 1.00       | 1       | root              |               | time:63.4ms, loops:2                                                                  | funcs:count(Column#10)->Column#5 | 372 Bytes | N/A  |
| └─TableReader_25           | 1.00       | 1       | root              |               | time:63.4ms, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache: disabled} | data:StreamAgg_8                 | 77 Bytes  | N/A  |
|   └─StreamAgg_8            | 1.00       | 1       | batchCop[tiflash] |               | tikv_task:{time:58.6ms, loops:1}                                                      | funcs:count(1)->Column#10        | N/A       | N/A  |
|     └─TableFullScan_22     | 1000000.00 | 1000000 | batchCop[tiflash] | table:sbtest2 | tikv_task:{time:58.6ms, loops:17}                                                     | keep order:false                 | N/A       | N/A  |
+----------------------------+------------+---------+-------------------+---------------+---------------------------------------------------------------------------------------+----------------------------------+-----------+------+
4 rows in set (0.07 sec)

cop[tiflash] 表示该任务会发送至 TiFlash 进行处理。如果没有选择 TiFlash 副本，可尝试通过 analyze table 语句更新统计信息后，再查看 explain analyze 结果。

需要注意的是，如果表仅有单个 TiFlash 副本且相关节点无法服务，智能选择模式下的查询会不断重试，需要指定 Engine 或者手工 Hint 来读取 TiKV 副本。

参考文档

重新测试

重新跑一下之前的测试命令

Point select测试命令

1	# sysbench --config-file=sysbench.conf oltp_point_select --tables=32 --table-size=1000000 run >>FlashSelect.log

Update index测试命令

1	# sysbench --config-file=sysbench.conf oltp_update_index --tables=32 --table-size=1000000 run >>FlashUpdate.log

Read-only 测试命令

1	# sysbench --config-file=sysbench.conf oltp_read_only --tables=32 --table-size=1000000 run >>FlashReadOnly.log

对比结果

只选取部分输出结果

ReadOnly 未开启TiFlash结果

[ 100s ] thds: 16 tps: 441.90 qps: 7067.50 (r/w/o: 6183.60/0.00/883.90) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 438.50 qps: 7015.51 (r/w/o: 6138.71/0.00/876.80) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 445.50 qps: 7130.73 (r/w/o: 6239.73/0.00/891.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            752332
        write:                           0
        other:                           107476
        total:                           859808
    transactions:                        53738  (447.69 per sec.)
    queries:                             859808 (7163.07 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0324s
    total number of events:              53738

Latency (ms):
         min:                                   13.78
         avg:                                   35.73
         max:                                  138.05
         95th percentile:                       51.02
         sum:                              1919990.80

Threads fairness:
    events (avg/stddev):           3358.6250/9.39
    execution time (avg/stddev):   119.9994/0.01

ReadOnly 开启TiFlash结果

[ 100s ] thds: 16 tps: 155.80 qps: 2493.71 (r/w/o: 2182.11/0.00/311.60) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 153.40 qps: 2453.49 (r/w/o: 2146.69/0.00/306.80) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 153.40 qps: 2455.27 (r/w/o: 2148.48/0.00/306.80) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            263046
        write:                           0
        other:                           37578
        total:                           300624
    transactions:                        18789  (156.50 per sec.)
    queries:                             300624 (2504.01 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0558s
    total number of events:              18789

Latency (ms):
         min:                                   46.63
         avg:                                  102.21
         max:                                  193.45
         95th percentile:                      137.35
         sum:                              1920448.46

Threads fairness:
    events (avg/stddev):           1174.3125/3.79
    execution time (avg/stddev):   120.0280/0.02

Select未开启TiFlash结果

[ 90s ] thds: 16 tps: 10829.06 qps: 10829.06 (r/w/o: 10829.06/0.00/0.00) lat (ms,95%): 3.55 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 11199.95 qps: 11199.95 (r/w/o: 11199.95/0.00/0.00) lat (ms,95%): 3.30 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 11704.21 qps: 11704.21 (r/w/o: 11704.21/0.00/0.00) lat (ms,95%): 3.13 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 11607.82 qps: 11607.82 (r/w/o: 11607.82/0.00/0.00) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1357050
        write:                           0
        other:                           0
        total:                           1357050
    transactions:                        1357050 (11308.09 per sec.)
    queries:                             1357050 (11308.09 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0060s
    total number of events:              1357050

Latency (ms):
         min:                                    0.23
         avg:                                    1.41
         max:                                   33.71
         95th percentile:                        3.30
         sum:                              1918124.53

Threads fairness:
    events (avg/stddev):           84815.6250/186.08
    execution time (avg/stddev):   119.8828/0.01

Select开启TiFlash结果

[ 90s ] thds: 16 tps: 14514.71 qps: 14514.71 (r/w/o: 14514.71/0.00/0.00) lat (ms,95%): 2.39 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 14552.57 qps: 14552.57 (r/w/o: 14552.57/0.00/0.00) lat (ms,95%): 2.35 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 14396.17 qps: 14396.17 (r/w/o: 14396.17/0.00/0.00) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 14272.27 qps: 14272.27 (r/w/o: 14272.27/0.00/0.00) lat (ms,95%): 2.43 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1746664
        write:                           0
        other:                           0
        total:                           1746664
    transactions:                        1746664 (14554.71 per sec.)
    queries:                             1746664 (14554.71 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0058s
    total number of events:              1746664

Latency (ms):
         min:                                    0.21
         avg:                                    1.10
         max:                                   28.12
         95th percentile:                        2.39
         sum:                              1917955.78

Threads fairness:
    events (avg/stddev):           109166.5000/625.78
    execution time (avg/stddev):   119.8722/0.02

Update未开启TiFlash结果

[ 90s ] thds: 16 tps: 922.91 qps: 922.91 (r/w/o: 0.00/914.81/8.10) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 912.90 qps: 912.90 (r/w/o: 0.00/905.20/7.70) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 934.99 qps: 934.99 (r/w/o: 0.00/925.49/9.50) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 927.70 qps: 927.70 (r/w/o: 0.00/920.90/6.80) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           108917
        other:                           948
        total:                           109865
    transactions:                        109865 (915.15 per sec.)
    queries:                             109865 (915.15 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0505s
    total number of events:              109865

Latency (ms):
         min:                                    0.87
         avg:                                   17.48
         max:                                  105.48
         95th percentile:                       27.66
         sum:                              1920213.77

Threads fairness:
    events (avg/stddev):           6866.5625/21.87
    execution time (avg/stddev):   120.0134/0.01

Update开启TiFlash结果

[ 90s ] thds: 16 tps: 997.80 qps: 997.80 (r/w/o: 0.00/987.80/10.00) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 16 tps: 976.60 qps: 976.60 (r/w/o: 0.00/968.60/8.00) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 16 tps: 1000.80 qps: 1000.80 (r/w/o: 0.00/992.70/8.10) lat (ms,95%): 24.38 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 16 tps: 998.80 qps: 998.80 (r/w/o: 0.00/991.10/7.70) lat (ms,95%): 23.95 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           119843
        other:                           1040
        total:                           120883
    transactions:                        120883 (1007.21 per sec.)
    queries:                             120883 (1007.21 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          120.0168s
    total number of events:              120883

Latency (ms):
         min:                                    0.94
         avg:                                   15.88
         max:                                  152.27
         95th percentile:                       23.95
         sum:                              1919902.69

Threads fairness:
    events (avg/stddev):           7555.1875/11.60
    execution time (avg/stddev):   119.9939/0.00

可能由于我本地只有一个TiFlash节点，整体结果并没有大幅提升，并且有一些还不如未开启TiFlash的性能好，还需要更完整的环境进行测试。

参考文档

开启TiFlash

TiDB部署

sysbench

sysbench测试TIDB及TiFlash

sysbench压力测试TIDB

环境介绍

TIDB安装

sysbench部署

安装sysbench

查看版本

修改配置文件

导入测试数据

Point select测试命令

Update index测试命令

Read-only 测试命令

启用TiFlash

按表构建TiFlash副本

查看TiFlash同步进度

使用 TiDB 读取 TiFlash

重新测试

Point select测试命令

Update index测试命令

Read-only 测试命令

对比结果

参考文档