sysbench–实践–05–OLTP测试

1、介绍

1. 测试事务处理数，和读写数
2. 性能指标
   1. TPS(每秒事务数)
   2. QPS(每秒查询数)
   3. 延迟信息(最大、最小、平均、95%以上语句的延迟时间)
   4. 线程公平统计信息(thread-fairness)，用于表示模拟负载的公平性

2、参数说明

2.1、mysql参数

2.1、通用数据库参数

2.1.1、–db-driver=STRING

特殊的数据库驱动。

2.1.2、–db-ps-mode=STRING

1. SQL是否需要预编译，模式有：auto/disable
2. 默认：disable。

2.1.3、–db-debug=[on|off]

1. 输出数据库层面的debug信息
2. 默认：off。

2.1.4、–oltp-table-size=1000000

每张表记录100万条数据

2.1.5、–oltp-tables-count=10

表的数量，这里是8个表

2.1.6、–num-threads=8

线程数为8

2.1.7、–max-time=60

测试时间为60s

2.1.8、–report-interval=10

间隔10秒输出测试信息

2.1.9、–oltp-read-only=off

非只读操作测试

2.2、MySQL相关参数

2.2.1、–mysql-host=[LIST,…]

1. MySQL服务器IP/hostname
2. 默认：localhost。

2.2.2、–mysql-port=[LIST,…]

1. MySQL端口号
2. 默认：3306。

2.2.3、–mysql-socket=[LIST,…]

MySQL的socket文件。

2.2.4、–mysql-user=STRING

1. MySQL的用户名
2. 默认：sbtest

2.2.5、–mysql-password=STRING

MySQL用户密码。

2.2.6、–mysql-db=STRING

MySQL数据库。

2.2.7、–mysql-table-engine=STRING

1. 用户测试表的搜索引擎
2. 可选项：
   1. myisam
   2. innodb
   3. bdb
   4. heap
   5. ndbcluster
   6. federated
3. 默认值：innodb。

2.2.8、–mysql-engine-trx=STRING

1. 存储引擎是否使用事务
2. 可选项：
   1. yes
   2. no
   3. auto
3. 默认值：auto。

2.2.9、–mysql-ssl=[on|off]

1. 使用SSL连接
2. 默认值：off。

2.2.10、–mysql-ssl-cipher=STRING

为SSL连接指定密码。

2.2.11、–mysql-compression=[on|off]

1. 使用压缩
2. 默认值：off。

2.2.12、–myisam-max-rows=N

1. MyISAM表的最大记录数
2. 默认值：1000000。

2.2.13、–mysql-debug=[on|off]

1. 输出MySQL的debug信息
2. 默认值：off。

2.2.14、–mysql-ignore-errors=[LIST,…]

1. MySQL忽略的错误代码
2. 可选项：1213/1020/1205

2.2.15、–mysql-dry-run=[on|off]

1. 假装MySQL所有客户端API都被调用，但实际并不执行它们
2. 默认值：off

3、操作

3.1、每张表记录100万条数据

命令

# 进入oltp.lua 脚本所在的目录
cd /usr/share/sysbench/tests/include/oltp_legacy# 准备阶段，每张表记录100万条数据
[root@zhoufei oltp_legacy]# sysbench  oltp  --mysql-table-engine=InnoDB --oltp-table-size=1000000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp prepare# 运行阶段，每张表记录100万条数据
sysbench  oltp  --mysql-table-engine=InnoDB --oltp-table-size=1000000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp run# 清空数据 
sysbench  oltp  --mysql-table-engine=InnoDB --oltp-table-size=1000000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp  cleanup

结果

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:
Number of threads: 1
Initializing random number generator from current timeInitializing worker threads...Threads started!SQL statistics:queries performed:read:                            7392  	#读总数write:                           2112  	#写总数other:                           1056  	#其他操作总数(如COMMIT等操作)total:                           10560  #全部总数和 transactions:                        528    (52.45 per sec.)  	#事务总数，每秒事务数(tps)queries:                             10560  (1049.10 per sec.) 	#查询总数，每秒查询数(qps)ignored errors:                      0      (0.00 per sec.) 	#忽略错误数reconnects:                          0      (0.00 per sec.) 	#重新连接数General statistics:total time:                          10.0602s  	#总耗时total number of events:              528 		#总请求数量(读、写、其它)Latency (ms):min:                                    7.89		#最小耗时avg:                                   19.04		#平均耗时max:                                  142.98		#最大耗时95th percentile:                       38.94	 	#95%通过的请求时间为65.65sum:                                10050.72Threads fairness:events (avg/stddev):           528.0000/0.00 #平均每个线程完成envet的次数/在相同时间内，多个线程分别完成envet是否稳定，如果数值越低，则表示多个线程的结果越接近(即越稳定)。该参数对于单线程无意义execution time (avg/stddev):   10.0507/0.00  	#平均每个线程平均耗时/在相同时间内，多个线程分别完成envet是否稳定，如果数值越低，则表示多个线程的结果越接近(即越稳定)。该参数对于单线程无意义

3.2、单表10万数据，10张表

命令

# 进入oltp.lua 脚本所在的目录
cd /usr/share/sysbench/tests/include/oltp_legacy# 准备阶段，单表10万数据，10张表
sysbench oltp --mysql-table-engine=InnoDB  --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp prepare # 运行阶段，单表10万数据，10张表
sysbench oltp --mysql-table-engine=InnoDB  --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp run# 清空数据 
sysbench oltp --mysql-table-engine=InnoDB  --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp cleanup

结果

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:
Number of threads: 1
Initializing random number generator from current timeInitializing worker threads...Threads started!SQL statistics:queries performed:read:                            14924write:                           4264other:                           2132total:                           21320transactions:                        1066   (106.48 per sec.)queries:                             21320  (2129.60 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)General statistics:total time:                          10.0082stotal number of events:              1066Latency (ms):min:                                    6.79avg:                                    9.38max:                                   47.3495th percentile:                       14.73sum:                                 9995.97Threads fairness:events (avg/stddev):           1066.0000/0.00execution time (avg/stddev):   9.9960/0.00

3.3、单表10万数据，10张表，测试线程数为8，执行时间为60秒，每10s打印一次报告，读写模式

命令

# 进入oltp.lua 脚本所在的目录
cd /usr/share/sysbench/tests/include/oltp_legacy# 准备阶段，单表10万数据，10张表
sysbench oltp --mysql-table-engine=InnoDB  --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp prepare # 运行阶段，单表10万数据，10张表，测试线程数为8，执行时间为60秒，每10s打印一次报告，读写模式
sysbench oltp --mysql-table-engine=InnoDB  --num-threads=8 --max-time=60 --report-interval=10 --oltp-read-only=off --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp run# 清空数据 
sysbench oltp --mysql-table-engine=InnoDB  --num-threads=8 --max-time=60 --report-interval=10 --oltp-read-only=off --oltp-tables-count=10 --oltp-table-size=100000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp cleanup

结果

WARNING: --num-threads is deprecated, use --threads instead
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current timeInitializing worker threads...Threads started!thds(线程数)、tps(每秒事务数)、qps(每秒查询数)、r/w/o(每秒的读/写/其它次数) lat(延迟),err/s(每秒错误数)、reconn(每秒重连次数)
[ 10s ] thds: 8 tps: 210.27 qps: 4216.28 (r/w/o: 2952.64/842.30/421.35) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 226.78 qps: 4532.89 (r/w/o: 3172.88/906.54/453.47) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 198.72 qps: 3978.09 (r/w/o: 2785.27/795.28/397.54) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 186.59 qps: 3727.55 (r/w/o: 2608.80/745.57/373.18) lat (ms,95%): 69.29 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 204.31 qps: 4087.38 (r/w/o: 2861.63/817.14/408.62) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 233.86 qps: 4678.07 (r/w/o: 3274.29/936.15/467.63) lat (ms,95%): 50.11 err/s: 0.00 reconn/s: 0.00
SQL statistics:queries performed:read:                            176596write:                           50456other:                           25228total:                           252280transactions:                        12614  (210.13 per sec.)queries:                             252280 (4202.55 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)General statistics:total time:                          60.0276stotal number of events:              12614Latency (ms):min:                                    8.29avg:                                   38.05max:                                  189.9695th percentile:                       59.99sum:                               480017.56Threads fairness:events (avg/stddev):           1576.7500/16.11execution time (avg/stddev):   60.0022/0.01

3.4、 单表10万，10个表，测试数据库读写tps

这是使用源码编译安装的

命令

# 准备阶段：单表10万，10个表，测试数据库读写tps
sysbench /usr/local/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 --mysql-user=root --mysql-password=1234 --mysql-host=192.168.187.171 --mysql-port=3306 --mysql-db=bpmapp prepare# 运行阶段：单表10万，10个表，测试数据库读写tps，执行时间30秒，测试线程10个，每3秒打印一次报告
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=1234 --mysql-host=192.168.187.171 --mysql-port=3306 --mysql-db=bpmapp --tables=10 --table_size=100000 --threads=10 --time=30 --report-interval=3 run# 清空数据 
sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-password=1234 --mysql-host=192.168.187.171 --mysql-port=3306 --mysql-db=bpmapp --tables=10 --table_size=100000 --threads=10 --time=30 --report-interval=3 cleanup

结果

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)Running the test with following options:
Number of threads: 10
Report intermediate results every 3 second(s)
Initializing random number generator from current timeInitializing worker threads...Threads started![ 3s ] thds: 10 tps: 101.53 qps: 2083.21 (r/w/o: 1462.04/415.11/206.06) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 124.01 qps: 2476.27 (r/w/o: 1734.19/493.72/248.36) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 10 tps: 158.33 qps: 3168.33 (r/w/o: 2218.00/633.67/316.67) lat (ms,95%): 95.81 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 10 tps: 176.01 qps: 3502.51 (r/w/o: 2452.12/698.37/352.02) lat (ms,95%): 86.00 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 10 tps: 153.34 qps: 3082.10 (r/w/o: 2158.07/617.35/306.68) lat (ms,95%): 106.75 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 10 tps: 168.62 qps: 3358.47 (r/w/o: 2349.39/672.49/336.58) lat (ms,95%): 87.56 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 10 tps: 121.35 qps: 2415.63 (r/w/o: 1690.21/483.06/242.36) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 10 tps: 129.35 qps: 2616.42 (r/w/o: 1832.29/524.42/259.71) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 10 tps: 158.63 qps: 3162.86 (r/w/o: 2218.10/627.51/317.25) lat (ms,95%): 97.55 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 150.02 qps: 2995.34 (r/w/o: 2093.57/601.74/300.03) lat (ms,95%): 101.13 err/s: 0.00 reconn/s: 0.00
SQL statistics:queries performed:read:                            60676write:                           17336other:                           8668total:                           86680transactions:                        4334   (144.11 per sec.)queries:                             86680  (2882.30 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)General statistics:total time:                          30.0701stotal number of events:              4334Latency (ms):min:                                   23.55avg:                                   69.32max:                                  623.3195th percentile:                      116.80sum:                               300424.07Threads fairness:events (avg/stddev):           433.4000/19.86execution time (avg/stddev):   30.0424/0.02

4、错误处理

4.1、error 2059: Authentication plugin ‘caching_sha2_password’

日志

[root@zhoufei oltp_legacy]# sysbench  oltp  --mysql-table-engine=InnoDB --oltp-table-size=1000000  --mysql-user=root --mysql-password=1234 --mysql-db=bpmapp prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)FATAL: unable to connect to MySQL server on host 'localhost', port 3306, aborting...
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
FATAL: `prepare' function failed: ./common.lua:111: Failed to connect to the database

解决方案

# 登录
mysql -uroot -p1234# 设置密码策略
set global validate_password.policy=0;
set global validate_password.length=4;
# 使用哪个用户就改哪个
ALTER USER root@'%' IDENTIFIED WITH mysql_native_password BY '1234';
ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY '1234';
FLUSH PRIVILEGES;