sysbench–实践–05–OLTP测试
1、介绍
- 测试事务处理数，和读写数
- 性能指标
- TPS(每秒事务数)
- QPS(每秒查询数)
- 延迟信息(最大、最小、平均、95%以上语句的延迟时间)
- 线程公平统计信息(thread-fairness)，用于表示模拟负载的公平性
2、参数说明
2.1、mysql参数
2.1、通用数据库参数
2.1.1、–db-driver=STRING
特殊的数据库驱动。
2.1.2、–db-ps-mode=STRING
- SQL是否需要预编译，模式有：auto/disable
- 默认：disable。
2.1.3、–db-debug=[on|off]
- 输出数据库层面的debug信息
- 默认：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,…]
- MySQL服务器IP/hostname
- 默认：localhost。
2.2.2、–mysql-port=[LIST,…]
- MySQL端口号
- 默认：3306。
2.2.3、–mysql-socket=[LIST,…]
MySQL的socket文件。
2.2.4、–mysql-user=STRING
- MySQL的用户名
- 默认：sbtest
2.2.5、–mysql-password=STRING
MySQL用户密码。
2.2.6、–mysql-db=STRING
MySQL数据库。
2.2.7、–mysql-table-engine=STRING
- 用户测试表的搜索引擎
- 可选项：
- myisam
- innodb
- bdb
- heap
- ndbcluster
- federated
- 默认值：innodb。
2.2.8、–mysql-engine-trx=STRING
- 存储引擎是否使用事务
- 可选项：
- yes
- no
- auto
- 默认值：auto。
2.2.9、–mysql-ssl=[on|off]
- 使用SSL连接
- 默认值：off。
2.2.10、–mysql-ssl-cipher=STRING
为SSL连接指定密码。
2.2.11、–mysql-compression=[on|off]
- 使用压缩
- 默认值：off。
2.2.12、–myisam-max-rows=N
- MyISAM表的最大记录数
- 默认值：1000000。
2.2.13、–mysql-debug=[on|off]
- 输出MySQL的debug信息
- 默认值：off。
2.2.14、–mysql-ignore-errors=[LIST,…]
- MySQL忽略的错误代码
- 可选项：1213/1020/1205
2.2.15、–mysql-dry-run=[on|off]
- 假装MySQL所有客户端API都被调用，但实际并不执行它们
- 默认值：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 time
Initializing 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.65
sum: 10050.72
Threads 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 time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 14924
write: 4264
other: 2132
total: 21320
transactions: 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.0082s
total number of events: 1066
Latency (ms):
min: 6.79
avg: 9.38
max: 47.34
95th percentile: 14.73
sum: 9995.97
Threads fairness:
events (avg/stddev): 1066.0000/0.00
execution 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 time
Initializing 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: 176596
write: 50456
other: 25228
total: 252280
transactions: 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.0276s
total number of events: 12614
Latency (ms):
min: 8.29
avg: 38.05
max: 189.96
95th percentile: 59.99
sum: 480017.56
Threads fairness:
events (avg/stddev): 1576.7500/16.11
execution 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 time
Initializing 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: 60676
write: 17336
other: 8668
total: 86680
transactions: 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.0701s
total number of events: 4334
Latency (ms):
min: 23.55
avg: 69.32
max: 623.31
95th percentile: 116.80
sum: 300424.07
Threads fairness:
events (avg/stddev): 433.4000/19.86
execution 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;
扫一扫
469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
