摘要
先进行了一个PG数据库的测试.
Mysql数据库的测试稍后跟上.
紧接着上一篇的安装, 部分文件可能需要特定路径才可以.sysbench 测试的说明
一个参数
这里稍微说一下参数的问题
sysbench \
--db-driver=pgsql \ # 针对POSTGRESQL 数据库测试
--pgsql-host=127.0.0.1 \ #连接数据库地址
--pgsql-port=5432 \ #连接数据库端口号
--pgsql-user=sbtest \ #数据库用户，最好具有superuser
--pgsql-password=sbtest \ #密码
--pgsql-db=sbtest \ #数据库名
--oltp-table-size=200000 \ #每个表的数据行数
--oltp-tables-count=10 \ #在一个数据库中有多少表
--rand-init=on \ #数据的随机性是否打开
--threads=10 \ #工作是并行的线程数
--time=30 \ # 测试多长秒数
--events=0 \ #是否对事务执行的数据量进行限制 0 是不限制
--report-interval=10 \ #每10秒产生一次报告
--percentile=99 \ #针对测试数据进行汇总，汇总数据占据总数据的百分比
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \ #使用哪个 lua脚本
prepare
其中需要注意的是选择的脚本应该从开始到测试是一致的，不能修改，测试的过程，也主要分为 prepare, run, cleanup
自己编译打包的注意事项
需要将编译好的安装包放到
/sysbench1.0.20 这个目录
然后可以看到 share 里面有对应的 lua 脚本.
可以使用相对路径的方式进行测试和指导简单测试脚本
# prepare
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
prepare
#run
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
run
#cleanup
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--oltp-table-size=200000 \
--oltp-tables-count=10 \
--rand-init=on \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/tests/include/oltp_legacy/select_random_points.lua \
cleanup注意不同的脚本
ll ../share/sysbench/tests/include/oltp_legacy
-rwxr-xr-x 1 root root 1195 2月 17 13:23 bulk_insert.lua
-rwxr-xr-x 1 root root 4696 2月 17 13:23 common.lua
-rwxr-xr-x 1 root root 366 2月 17 13:23 delete.lua
-rwxr-xr-x 1 root root 1171 2月 17 13:23 insert.lua
-rwxr-xr-x 1 root root 3004 2月 17 13:23 oltp.lua
-rwxr-xr-x 1 root root 368 2月 17 13:23 oltp_simple.lua
-rwxr-xr-x 1 root root 527 2月 17 13:23 parallel_prepare.lua
-rwxr-xr-x 1 root root 369 2月 17 13:23 select.lua
-rwxr-xr-x 1 root root 1448 2月 17 13:23 select_random_points.lua
-rwxr-xr-x 1 root root 1556 2月 17 13:23 select_random_ranges.lua
-rwxr-xr-x 1 root root 369 2月 17 13:23 update_index.lua
-rwxr-xr-x 1 root root 578 2月 17 13:23 update_non_index.lua可以测试带不带索引的update结果
# 只需要将上一个脚本里面的select_random_points.lua 修改为:update_index.lua
# 准备数据大约需要150秒
# 带索引的测试结果
[ 10s ] thds: 10 tps: 239.66 qps: 239.66 (r/w/o: 0.00/239.66/0.00) lat (ms,99%): 116.80 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 227.70 qps: 227.70 (r/w/o: 0.00/227.70/0.00) lat (ms,99%): 211.60 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 218.30 qps: 218.30 (r/w/o: 0.00/218.30/0.00) lat (ms,99%): 397.39 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 6867
other: 0
total: 6867
transactions: 6867 (228.58 per sec.)
queries: 6867 (228.58 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0405s
total number of events: 6867
Latency (ms):
min: 0.74
avg: 43.74
max: 678.37
99th percentile: 257.95
sum: 300342.67
Threads fairness:
events (avg/stddev): 686.7000/2.76
execution time (avg/stddev): 30.0343/0.00# 不带索引的测试环境
# 只需要将上一个脚本里面的select_random_points.lua 修改为:update_non_index.lua
[ 10s ] thds: 10 tps: 166.95 qps: 166.95 (r/w/o: 0.00/166.95/0.00) lat (ms,99%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 10 tps: 213.13 qps: 213.13 (r/w/o: 0.00/213.13/0.00) lat (ms,99%): 193.38 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 213.40 qps: 213.40 (r/w/o: 0.00/213.40/0.00) lat (ms,99%): 219.36 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 5945
other: 0
total: 5945
transactions: 5945 (197.95 per sec.)
queries: 5945 (197.95 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0309s
total number of events: 5945
Latency (ms):
min: 0.74
avg: 50.50
max: 626.36
99th percentile: 227.40
sum: 300244.16
Threads fairness:
events (avg/stddev): 594.5000/1.96
execution time (avg/stddev): 30.0244/0.00感想
sysbench应该具有很多玩法. 不同的lua脚本应该能够实现不同的测试场景
感觉可以作为一个比较简单的测试场景 作为一个基础基线
低于基线基本不可用
高于基线可能不好用
测试的数据肯定比生产的环境要简单和单纯
直接导出生产遇到复杂的场景和SQL时出现巨大的性能衰退.简单的测试读写
# 如下脚本的测试结果就明显好于上面一个脚本
[ 10s ] thds: 10 tps: 221.27 qps: 4572.62 (r/w/o: 3212.19/889.75/470.68) lat (ms,99%): 272.27 err/s: 7.19 reconn/s: 0.00
[ 20s ] thds: 10 tps: 198.86 qps: 4112.85 (r/w/o: 2889.27/799.13/424.46) lat (ms,99%): 272.27 err/s: 7.61 reconn/s: 0.00
[ 30s ] thds: 10 tps: 218.50 qps: 4496.90 (r/w/o: 3159.23/874.28/463.39) lat (ms,99%): 227.40 err/s: 7.10 reconn/s: 0.00
SQL statistics:
queries performed:
read: 92624
write: 25653
other: 13597
total: 131874
transactions: 6397 (212.95 per sec.)
queries: 131874 (4390.05 per sec.)
ignored errors: 219 (7.29 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0378s
total number of events: 6397
Latency (ms):
min: 3.42
avg: 46.92
max: 1354.94
99th percentile: 262.64
sum: 300162.00
Threads fairness:
events (avg/stddev): 639.7000/22.64
execution time (avg/stddev): 30.0162/0.01详细内容
# 脚本为:
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/oltp_read_write.lua \
prepare
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/oltp_read_write.lua \
run
./sysbench \
--db-driver=pgsql \
--pgsql-host=10.110.139.222 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=TestMypassword \
--pgsql-db=myapp0203 \
--threads=10 --time=30 \
--events=0 \
--report-interval=10 \
--percentile=99 ../share/sysbench/oltp_read_write.lua \
cleanup
