基准测试工具 sysbench 之二 压测postgresql
os: ubuntu 16.04
postgresql: 9.6.8
sysbench: 1.0.15
# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.3 LTS
Release: 16.04
Codename: xenial
# su - postgres -c "psql -c \"select version();\""
version
----------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Ubuntu 9.6.8-1.pgdg16.04+1), compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)
# sysbench --version
sysbench 1.0.15
有几个大的测试主题,这个主要是针对os层面的。
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
# sysbench fileio help
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
fileio options:
--file-num=N number of files to create [128]
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]
--file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all[=on|off] do fsync() after each write operation [off]
--file-fsync-end[=on|off] do fsync() at the end of test [on]
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]
--file-rw-ratio=N reads/writes ratio for combined test [1.5]
# sysbench cpu help
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
cpu options:
--cpu-max-prime=N upper limit for primes generator [10000]
# sysbench memory help
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
memory options:
--memory-block-size=SIZE size of memory block for test [1K]
--memory-total-size=SIZE total size of data to transfer [100G]
--memory-scope=STRING memory access scope {global,local} [global]
--memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off]
--memory-oper=STRING type of memory operations {read, write, none} [write]
--memory-access-mode=STRING memory access mode {seq,rnd} [seq]
# sysbench threads help
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
threads options:
--thread-yields=N number of yields to do per request [1000]
--thread-locks=N number of locks per thread [8]
# sysbench mutex help
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
mutex options:
--mutex-num=N total size of mutex array [4096]
--mutex-locks=N number of mutex locks to do per thread [50000]
--mutex-loops=N number of empty loops to do outside mutex lock [10000]
oltp*.lua 文件
# find / -name "oltp*.lua"
/usr/share/sysbench/oltp_update_index.lua
/usr/share/sysbench/oltp_read_only.lua
/usr/share/sysbench/oltp_insert.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp_simple.lua
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
/usr/share/sysbench/oltp_delete.lua
/usr/share/sysbench/oltp_write_only.lua
/usr/share/sysbench/oltp_common.lua
/usr/share/sysbench/oltp_update_non_index.lua
/usr/share/sysbench/oltp_read_write.lua
/usr/share/sysbench/oltp_point_select.lua
安装 luajit
# apt-get install luajit
sysbench prepare for postgresql
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=postgres \
--pgsql-db=postgres \
--oltp-table-size=10000 \
--rand-init=on \
--threads=10 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
prepare
简单说明下：
# --oltp-table-size=10000 测试表的记录数。默认是10000
# --threads=10 表示发起 10 个并发连接
# --time=120 表示执行120秒
# --events=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长
# --report-interval=10 表示每10秒输出一次测试进度报告
# --percentile=99 表示设定采样比例，默认是 95%，即丢弃1%的长请求，在剩余的99%里取最大值
sysbench run for postgresql
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=postgres \
--pgsql-db=postgres \
--oltp-table-size=10000 \
--threads=10 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
run
测试结果如下：
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 10 tps: 92.53 qps: 1888.40 (r/w/o: 1326.51/370.93/190.96) lat (ms,99%): 1973.38 err/s: 1.50 reconn/s: 0.00
[ 20s ] thds: 10 tps: 131.60 qps: 2653.01 (r/w/o: 1859.10/526.40/267.50) lat (ms,99%): 137.35 err/s: 1.10 reconn/s: 0.00
[ 30s ] thds: 10 tps: 129.50 qps: 2600.49 (r/w/o: 1820.80/517.60/262.10) lat (ms,99%): 116.80 err/s: 0.60 reconn/s: 0.00
[ 40s ] thds: 10 tps: 125.30 qps: 2520.04 (r/w/o: 1765.06/501.59/253.39) lat (ms,99%): 142.39 err/s: 0.70 reconn/s: 0.00
[ 50s ] thds: 10 tps: 119.40 qps: 2408.55 (r/w/o: 1687.67/477.89/243.00) lat (ms,99%): 173.58 err/s: 1.20 reconn/s: 0.00
[ 60s ] thds: 10 tps: 120.11 qps: 2412.63 (r/w/o: 1689.56/479.85/243.22) lat (ms,99%): 125.52 err/s: 0.60 reconn/s: 0.00
[ 70s ] thds: 10 tps: 119.19 qps: 2395.06 (r/w/o: 1677.63/475.35/242.08) lat (ms,99%): 215.44 err/s: 0.50 reconn/s: 0.00
[ 80s ] thds: 10 tps: 126.21 qps: 2538.20 (r/w/o: 1777.67/504.72/255.81) lat (ms,99%): 137.35 err/s: 1.00 reconn/s: 0.00
[ 90s ] thds: 10 tps: 105.00 qps: 2114.55 (r/w/o: 1480.74/420.31/213.51) lat (ms,99%): 176.73 err/s: 0.80 reconn/s: 0.00
[ 100s ] thds: 10 tps: 119.07 qps: 2397.06 (r/w/o: 1679.45/476.57/241.03) lat (ms,99%): 183.21 err/s: 0.80 reconn/s: 0.00
[ 110s ] thds: 10 tps: 98.10 qps: 1982.46 (r/w/o: 1389.75/393.21/199.51) lat (ms,99%): 248.83 err/s: 1.20 reconn/s: 0.00
[ 120s ] thds: 10 tps: 106.90 qps: 2162.24 (r/w/o: 1515.16/428.99/218.09) lat (ms,99%): 200.47 err/s: 1.20 reconn/s: 0.00
SQL statistics:
queries performed:
read: 196742
write: 55771
other: 28316
total: 280829
transactions: 13941 (116.09 per sec.)
queries: 280829 (2338.45 per sec.)
ignored errors: 112 (0.93 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0903s
total number of events: 13941
Latency (ms):
min: 6.59
avg: 86.11
max: 2195.49
99th percentile: 167.44
sum: 1200469.84
Threads fairness:
events (avg/stddev): 1394.1000/8.73
execution time (avg/stddev): 120.0470/0.02
由于是本地虚拟机，各项指标都偏低。
sysbench cleanup for postgresql
sysbench \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=postgres \
--pgsql-db=postgres \
--oltp-table-size=10000 \
--threads=10 \
--time=120 \
--events=0 \
--report-interval=10 \
--percentile=99 \
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \
cleanup
从结果来看sysbench对postgresql，模拟的表结构太简单，不像tpcc-mysql那样完整的事务系统。但对于性能压测对比还是很有用的，因为sysbench使用的环境参数限制是一样的。
