运行测试

接下来就是真正意义上的测试了，这里你需要先使用MySQL标准客户端连接 TiDB

  1. > mysql -h 120.92.150.39 -P 4000 -u root -D test
  2. > create database sbtest1;

然后你就需要用到刚刚获取的beanch的代码了：

  1.  > cd ~/tidb-bench-master/sysbench

修改conf.sh:

  1. > cat conf.sh 
  2.  . ./conf.sh
  3.  host=127.0.0.1
  4.  port=4000
  5.  user=root
  6.  password=
  7.  tcount=16
  8.  tsize=1000000
  9.  threads=256
  10.  dbname=sbtest1
  11.  interval=10
  12.  maxtime=600
  13.  requests=2000000000
  14.  driver=mysql

详解：

  • —test=tests/db/oltp.lua 表示调用 tests/db/oltp.lua 脚本进行 oltp 模式测试.
  • —oltp_tables_count=16 表示会生成16张测试表 .
  • —oltp-table-size=500000 表示每张测试表填充数据量为500000 —rand-init=on 表示每张测试表都是用随机数据来填充的.

如果在本机，也可以使用 –mysql-socket 指定 socket 文件来连接。加载测试数据时长视数据量而定，若过程比较久需要稍加耐心等待。 真实测试场景中，数据表建议不低于10个，单表数据量不低于500万行，当然了，要视服务器硬件配置而定。如果是配备了SSD或者PCIE SSD这种高IOPS设备的话，则建议单表数据量最少不低于1亿行。

测试运行:

  1. > ./prepare.sh

执行完成后，生成16张测试表，每张表500000条数据。

开始测试 OLTP，这里解释写关于里面的参数:

  • —num-threads=256 表示发起 256 个并发连接.
  • —oltp-read-only=off 表示不要进行只读测试也就是会采用读写混合模式测试.
  • —report-interval=10 表示每10秒输出一次测试进度报告.
  • —rand-type=uniform 表示随机类型为固定模式，其他几个可选随机模式：uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托).
  • —max-time=600 表示最大执行时长为 600 秒.
  • —max-requests=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长.
  • —percentile=95 表示设定采样比例，默认是 95%，即丢弃5%的长请求，在剩余的95%里取最大值.

模拟 对256个表并发OLTP测试，每个表500000行记录，持续压测时间为10分钟。 真实测试场景中，建议持续压测时长不小于30分钟，否则测试数据可能不具参考意义。

  1. > ./oltp.sh
  2. + sysbench --test=./lua-tests/db/oltp.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --mysql-db=sbtest1 --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=256 --max-requests=2000000000 --oltp-read-only=off --report-interval=10 --rand-type=uniform --max-time=600 --percentile=95 run

oltp 测试结果如下：

  1. sysbench 1.0.9 (using system LuaJIT 2.0.4)
  3. Running the test with following options:
  4. Number of threads: 256
  5. Report intermediate results every 10 second(s)
  6. Initializing random number generator from current time
  8. -- 每10秒钟报告一次测试结果，tps、每秒读、每秒写、99%以上的响应时长统计
  10. [ 580s ] thds: 256 tps: 721.70 qps: 14451.55 (r/w/o: 10116.46/2890.59/1444.49) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00
  11. [ 590s ] thds: 256 tps: 717.67 qps: 14343.25 (r/w/o: 10038.02/2869.89/1435.35) lat (ms,95%): 427.07 err/s: 0.00 reconn/s: 0.00
  12. [ 600s ] thds: 256 tps: 731.61 qps: 14657.79 (r/w/o: 10265.53/2931.24/1461.02) lat (ms,95%): 404.61 err/s: 0.00 reconn/s: 0.00
  13. SQL statistics:
  14.     queries performed:
  15.         read:                            5846120  -- 读总数
  16.         write:                           1670320  -- 写总数
  17.         other:                           835160   -- 其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
  18.         total:                           8351600  -- 全部总数
  19.     transactions:                        417580 (693.25 per sec.) -- 总事务数(每秒事务数)
  20.     queries:                             8351600 (13864.98 per sec.)
  21.     ignored errors:                      0      (0.00 per sec.)
  22.     reconnects:                          0      (0.00 per sec.)
  24. General statistics:
  25.     total time:                          602.3476s  -- 总耗时
  26.     total number of events:              417580  -- 共发生多少事务数
  28. Latency (ms):
  29.          min:                                 25.62   -- 最小耗时
  30.          avg:                                367.90   -- 平均耗时
  31.          max:                               5886.23   -- 最长耗时
  32.          95th percentile:                    442.73   
  33.          sum:                            153666263.39
  35. Threads fairness:
  36.     events (avg/stddev):           749.0078/2.01
  37.     execution time (avg/stddev):   600.2588/0.17

测试　select:

  1. > ./select.sh
  3. + sysbench --test=./lua-tests/db/select.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --mysql-db=sbtest1 --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=256 --report-interval=10 --max-requests=2000000000 --percentile=95 --max-time=600 run

select　测试结果如下：

  1. [ 550s ] thds: 256 tps: 9072.09 qps: 9072.09 (r/w/o: 9072.09/0.00/0.00) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
  2. [ 560s ] thds: 256 tps: 7348.60 qps: 7348.60 (r/w/o: 7348.60/0.00/0.00) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
  3. [ 570s ] thds: 256 tps: 7644.35 qps: 7644.35 (r/w/o: 7644.35/0.00/0.00) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00
  4. [ 580s ] thds: 256 tps: 7989.24 qps: 7989.24 (r/w/o: 7989.24/0.00/0.00) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
  5. [ 590s ] thds: 256 tps: 8266.79 qps: 8266.79 (r/w/o: 8266.79/0.00/0.00) lat (ms,95%): 58.92 err/s: 0.00 reconn/s: 0.00
  6. [ 600s ] thds: 256 tps: 8126.22 qps: 8126.22 (r/w/o: 8126.22/0.00/0.00) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
  7. SQL statistics:
  8.     queries performed:
  9.         read:                            5071951
  10.         write:                           0
  11.         other:                           0
  12.         total:                           5071951
  13.     transactions:                        5071951 (8451.70 per sec.)
  14.     queries:                             5071951 (8451.70 per sec.)
  15.     ignored errors:                      0      (0.00 per sec.)
  16.     reconnects:                          0      (0.00 per sec.)
  18. General statistics:
  19.     total time:                          600.1084s
  20.     total number of events:              5071951
  22. Latency (ms):
  23.          min:                                  2.87
  24.          avg:                                 30.28
  25.          max:                                369.50
  26.          95th percentile:                     55.82
  27.          sum:                            153576184.73
  29. Threads fairness:
  30.     events (avg/stddev):           19812.3086/50.79
  31.     execution time (avg/stddev):   599.9070/0.04

测试　insert：

  1. > ./insert.sh
  3. + sysbench --test=./lua-tests/db/insert.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --mysql-db=sbtest1 --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=256 --report-interval=10 --max-requests=2000000000 --percentile=95 --max-time=600 run

insert　测试结果如下：

  1. [ 550s ] thds: 256 tps: 3084.73 qps: 3084.73 (r/w/o: 0.00/3084.73/0.00) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
  2. [ 560s ] thds: 256 tps: 3164.70 qps: 3164.70 (r/w/o: 0.00/3164.70/0.00) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
  3. [ 570s ] thds: 256 tps: 3118.42 qps: 3118.42 (r/w/o: 0.00/3118.42/0.00) lat (ms,95%): 108.68 err/s: 0.00 reconn/s: 0.00
  4. [ 580s ] thds: 256 tps: 3251.01 qps: 3251.01 (r/w/o: 0.00/3251.01/0.00) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
  5. [ 590s ] thds: 256 tps: 3109.42 qps: 3109.42 (r/w/o: 0.00/3109.42/0.00) lat (ms,95%): 106.75 err/s: 0.00 reconn/s: 0.00
  6. SQL statistics:
  7.     queries performed:
  8.         read:                            0
  9.         write:                           1765555
  10.         other:                           0
  11.         total:                           1765555
  12.     transactions:                        1765555 (2942.26 per sec.)
  13.     queries:                             1765555 (2942.26 per sec.)
  14.     ignored errors:                      0      (0.00 per sec.)
  15.     reconnects:                          0      (0.00 per sec.)
  17. General statistics:
  18.     total time:                          600.0653s
  19.     total number of events:              1765555
  21. Latency (ms):
  22.          min:                                 34.19
  23.          avg:                                 86.99
  24.          max:                                927.90
  25.          95th percentile:                    114.72
  26.          sum:                            153582581.05
  28. Threads fairness:
  29.     events (avg/stddev):           6896.6992/13.22
  30.     execution time (avg/stddev):   599.9320/0.05

测试 delete:

  1. > ./delete.sh
  3. +  sysbench --test=./lua-tests/db/delete.lua --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4000 --mysql-user=root --mysql-password= --mysql-db=sbtest1 --oltp-tables-count=16 --oltp-table-size=1000000 --num-threads=256 --report-interval=10 --max-requests=2000000000 --max-time=600 --percentile=95 run

delete　测试结果如下：

  1. [ 550s ] thds: 256 tps: 6291.62 qps: 6291.62 (r/w/o: 0.00/1284.15/5007.48) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
  2. [ 560s ] thds: 256 tps: 6294.83 qps: 6294.83 (r/w/o: 0.00/1278.81/5016.02) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
  3. [ 570s ] thds: 256 tps: 6161.33 qps: 6161.33 (r/w/o: 0.00/1252.58/4908.75) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
  4. [ 580s ] thds: 256 tps: 6190.38 qps: 6190.48 (r/w/o: 0.00/1244.62/4945.86) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
  5. [ 590s ] thds: 256 tps: 6278.86 qps: 6278.76 (r/w/o: 0.00/1279.15/4999.61) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
  6. SQL statistics:
  7.     queries performed:
  8.         read:                            0
  9.         write:                           887497
  10.         other:                           2589396
  11.         total:                           3476893
  12.     transactions:                        3476893 (5793.68 per sec.)
  13.     queries:                             3476893 (5793.68 per sec.)
  14.     ignored errors:                      0      (0.00 per sec.)
  15.     reconnects:                          0      (0.00 per sec.)
  17. General statistics:
  18.     total time:                          600.1166s
  19.     total number of events:              3476893
  21. Latency (ms):
  22.          min:                                  5.06
  23.          avg:                                 44.17
  24.          max:                               5644.54
  25.          95th percentile:                    102.97
  26.          sum:                            153575902.59
  28. Threads fairness:
  29.     events (avg/stddev):           13581.6133/78.13
  30.     execution time (avg/stddev):   599.9059/0.05