sysbench测试TiDB

一、环境的基础校验

sysbench工具的下载见此篇文章：https://www.cnblogs.com/syw20170419/p/16967596.html

tidb中自动选择引擎的方式，并不是默认选择tiflash的方式进行测试，用测试oltp_update_non_index.lua进行举例，如何测试tidb的方法。

1、首先搭建到sysbench的环境

2、创建测试表

sysbench /usr/local/share/sysbench/oltp_update_non_index.lua --mysql-user=root --mysql-password='' --mysql-host=192.168.30.43 --mysql-port=4000 --mysql-db=sysbench_test --tables=5 --table_size=1 --threads=4 --time=300 --report-interval=10 prepare

3、按表构建tiflash副本

ALTER TABLE sysbench_test.sbtest1 SET TIFLASH REPLICA 1;
ALTER TABLE sysbench_test.sbtest2 SET TIFLASH REPLICA 1;
ALTER TABLE sysbench_test.sbtest3 SET TIFLASH REPLICA 1;
ALTER TABLE sysbench_test.sbtest4 SET TIFLASH REPLICA 1;
ALTER TABLE sysbench_test.sbtest5 SET TIFLASH REPLICA 1;

4、查看每个表的结构

表构建为tiflash之前的显示如下：

mysql> desc select count(*) from sbtest5;
+----------------------------+-------------+-----------+---------------+---------------------------------+
| id                         | estRows     | task      | access object | operator info                   |
+----------------------------+-------------+-----------+---------------+---------------------------------+
| StreamAgg_16               | 1.00        | root      |               | funcs:count(Column#7)->Column#5 |
| └─TableReader_17           | 1.00        | root      |               | data:StreamAgg_8                |
|   └─StreamAgg_8            | 1.00        | cop[tikv] |               | funcs:count(1)->Column#7        |
|     └─TableFullScan_15     | 10138288.00 | cop[tikv] | table:sbtest5 | keep order:false                |
+----------------------------+-------------+-----------+---------------+---------------------------------+
4 rows in set (0.01 sec)

表构建为tiflash之后的显示如下：

mysql> desc select count(*) from sbtest1;
+------------------------------+----------+--------------+---------------+---------------------------------+
| id                           | estRows  | task         | access object | operator info                   |
+------------------------------+----------+--------------+---------------+---------------------------------+
| HashAgg_21                   | 1.00     | root         |               | funcs:count(Column#7)->Column#5 |
| └─TableReader_23             | 1.00     | root         |               | data:ExchangeSender_22          |
|   └─ExchangeSender_22        | 1.00     | mpp[tiflash] |               | ExchangeType: PassThrough       |
|     └─HashAgg_9              | 1.00     | mpp[tiflash] |               | funcs:count(1)->Column#7        |
|       └─TableFullScan_20     | 10000.00 | mpp[tiflash] | table:sbtest1 | keep order:false, stats:pseudo  |
+------------------------------+----------+--------------+---------------+---------------------------------+
5 rows in set (0.00 sec)

二、sysbench测试Tiflash的insert

1、创建5张表，并插入一行数据到表中，脚本如下

[root@HAST04 ~]# sysbench /usr/local/share/sysbench/oltp_insert.lua --mysql-user=root --mysql-password='' --mysql-host=192.168.30.43 --mysql-port=4000 --mysql-db=sysbench_test --tables=5 --table_size=1 --threads=4 --time=300 --report-interval=10 prepare

2、执行按表构建tiflash副本，分别是sbtest1到sbtest5都要执行

ALTER TABLE sysbench_test.sbtest5 SET TIFLASH REPLICA 1;

3、查看表的显示

mysql> desc select count(*) from sbtest5;
+----------------------------+----------+-----------+---------------+---------------------------------+
| id                         | estRows  | task      | access object | operator info                   |
+----------------------------+----------+-----------+---------------+---------------------------------+
| StreamAgg_16               | 1.00     | root      |               | funcs:count(Column#7)->Column#5 |
| └─TableReader_17           | 1.00     | root      |               | data:StreamAgg_8                |
|   └─StreamAgg_8            | 1.00     | cop[tikv] |               | funcs:count(1)->Column#7        |
|     └─TableFullScan_15     | 10000.00 | cop[tikv] | table:sbtest5 | keep order:false, stats:pseudo  |
+----------------------------+----------+-----------+---------------+---------------------------------+

4、此时你会疑惑，为啥tiflash的副本已经构建了，为什么还是cop[tikv] 存储？那么此时不用着急，继续往下看，再次向表中插入一条数据，就会发现已经变成了cop[tiflash]

#####查看表结构
mysql> show create table sbtest5;
| sbtest5 | CREATE TABLE `sbtest5` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=667583 
1 row in set (0.05 sec)

#####插入数据与查看表结构
mysql> insert into sbtest5 values(123,123,'re','r');
Query OK, 1 row affected (0.05 sec)

mysql> desc select count(*) from sbtest5;
+--------------------------+---------+--------------+---------------+--------------------------------+
| id                       | estRows | task         | access object | operator info                  |
+--------------------------+---------+--------------+---------------+--------------------------------+
| StreamAgg_11             | 1.00    | root         |               | funcs:count(1)->Column#5       |
| └─TableReader_19         | 1.00    | root         |               | data:TableFullScan_18          |
|   └─TableFullScan_18     | 1.00    | cop[tiflash] | table:sbtest5 | keep order:false, stats:pseudo |
+--------------------------+---------+--------------+---------------+--------------------------------+
3 rows in set (0.01 sec)

5、此时继续运行sysbench的insert脚本即可，insert的过程中你观察表结构仍然会是tiflash的存储。如下的执行结果中可以看出tidb的tiflash引擎在5分钟内，4个并发的条件下insert插入数据总共是39004行，通过测试结果可得637.83 per sec的速度在插入数据。

mysql> desc select count(*) from sbtest5;
+------------------------------+----------+--------------+---------------+---------------------------------+
| id                           | estRows  | task         | access object | operator info                   |
+------------------------------+----------+--------------+---------------+---------------------------------+
| HashAgg_21                   | 1.00     | root         |               | funcs:count(Column#7)->Column#5 |
| └─TableReader_23             | 1.00     | root         |               | data:ExchangeSender_22          |
|   └─ExchangeSender_22        | 1.00     | mpp[tiflash] |               | ExchangeType: PassThrough       |
|     └─HashAgg_9              | 1.00     | mpp[tiflash] |               | funcs:count(1)->Column#7        |
|       └─TableFullScan_20     | 39004.00 | mpp[tiflash] | table:sbtest5 | keep order:false                |
+------------------------------+----------+--------------+---------------+---------------------------------+
5 rows in set (0.03 sec)