前言：
BenchmarkSQL、pgbench以及sysbench用于PG当中，进行性能压测，应该都是比较常的了。BenchmarkSQL基本上是属于TPCC，pgbench属于TPC-B (详见文档：https://www.postgresql.org/docs/current/pgbench.html)。
BenchmarkSQL: 基本上支持很多种数据库。MySQL、FireBird、Oracle、PostgreSQL等，它还可以进行代码扩展，支持更多类型的数据库，用于进行横向对比。我将它改写成maven方式的编译发布。http://github.com/iihero/benchmarksql-v5，让它能支持Sybase ASE、SQLServer、DB2、HANA等多种数据库，方便使用。
pgbench：只支持PG，特点是使用简例，内置支持。直接能用。
sysbench: 经过编译，可以支持MySQL、PG、Oracle三种数据库。同时支持非数据库的一些功能。也是用于度量系统的基本性能。
这里就sysbench的一些基本用法进行总结，数据库方面主要介绍它如何用于PG数据库中的性能测试。
安装及环境准备:
假定你的系统已经安装好PostgreSQL14。这里用的操作系统是CentOS7.9发行版。本文的物理机大概是8G内存。Intel(R) Xeon(R) Platinum 8260 CPU @ 2.40GHz 。而PG则用的是默认参数。只用于演示。
2.1下载源码
下载sysbench本身的源码: git clone https://github.com/akopytov/sysbench/
下载相关的tpcc LUA脚本：这里我直接使用德哥digoal的代码：git clone https://github.com/digoal/sysbench-tpcc sysbench-tpcc-digoal
基本上有这两个也就够了。网上也还有已经编译好的二进制文件：如：https://github.com/funnyzpc/sysbench_bin 大家可以自行验证。
2.2 编译安装
1 yum -y install make automake libtool pkgconfig libaio-devel openssl-devel
2 yum -y install postgresql14-devel.x86_64 (这个在安装pg的时候希望已经安装了)
1[06:33:37-postgres@centos1:/iihero/source/sysbench/sysbench]$ ./autogen.sh
2autoreconf: Entering directory `.'
3autoreconf: configure.ac: not using Gettext
4autoreconf: running: aclocal -I m4
5autoreconf: configure.ac: tracing
6autoreconf: configure.ac: not using Libtool
7autoreconf: running: /usr/bin/autoconf
8configure.ac:61: error: possibly undefined macro: AC_PROG_LIBTOOL
9 If this token and others are legitimate, please use m4_pattern_allow.
10 See the Autoconf documentation.
11autoreconf: /usr/bin/autoconf failed with exit status: 1
12
解决办法是：
1sudo yum install -y autoconf automake libtool
2-- 然后
3[06:39:48-postgres@centos1:/iihero/source/sysbench/sysbench]$ ./autogen.sh
4autoreconf: Entering directory `.'
5autoreconf: configure.ac: not using Gettext
6autoreconf: running: aclocal -I m4
7autoreconf: configure.ac: tracing
8autoreconf: running: libtoolize --copy
9libtoolize: putting auxiliary files in AC_CONFIG_AUX_DIR, `config'.
10libtoolize: copying file `config/ltmain.sh'
11libtoolize: putting macros in AC_CONFIG_MACRO_DIR, `m4'.
12libtoolize: copying file `m4/libtool.m4'
13libtoolize: copying file `m4/ltoptions.m4'
14libtoolize: copying file `m4/ltsugar.m4'
15libtoolize: copying file `m4/ltversion.m4'
16libtoolize: copying file `m4/lt~obsolete.m4'
17autoreconf: running: /usr/bin/autoconf
18autoreconf: running: /usr/bin/autoheader
19autoreconf: running: automake --add-missing --copy --no-force
20autoreconf: Leaving directory `.'
编译安装：
1yum list postgresql14-devel 确保已经安装
2
3./configure --without-mysql --with-pgsql --with-pgsql-includes=/usr/pgsql-14/include --with-pgsql-libs=/usr/pgsql-14/lib
4make -j 2&& sudo make install
5
6[03:34:23-postgres@sean-rh1:/iihero/source/sysbench/sysbench]$ sysbench --version
7sysbench 1.1.0-2ca9e3f
最后安装成功。这里只适配了PG14的相关库，用于支持PG当中的测试。
基本用法
sysbench的测试可运用于下述几种情况：
CPU性能
磁盘IO性能
调动程序性能
内存分配及传输速度
POSIX线程性能
数据库性能
我们分别看看各种使用情况：
1[03:49:49-postgres@sean-rh1:/iihero/source/sysbench/sysbench]$ sysbench --help
2Usage:
3 sysbench [options]... [testname] [command]
4
5Commands implemented by most tests: prepare run cleanup help
6
7General options:
8 --threads=N number of threads to use [1]
9 --events=N limit for total number of events [0]
10 --time=N limit for total execution time in seconds [10]
11 --warmup-time=N execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled [0]
12 --forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
13 --thread-stack-size=SIZE size of stack per thread [64K]
14 --thread-init-timeout=N wait time in seconds for worker threads to initialize [30]
15 --rate=N average transactions rate. 0 for unlimited rate [0]
16 --report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
17 --report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
18 --debug[=on|off] print more debugging info [off]
19 --validate[=on|off] perform validation checks where possible [off]
20 --help[=on|off] print help and exit [off]
21 --version[=on|off] print version and exit [off]
22 --config-file=FILENAME File containing command line options
23 --luajit-cmd=STRING perform LuaJIT control command. This option is equivalent to 'luajit -j'. See LuaJIT documentation for more information
24
25Pseudo-Random Numbers Generator options:
26 --rand-type=STRING random numbers distribution {uniform, gaussian, pareto, zipfian} to use by default [uniform]
27 --rand-seed=N seed for random number generator. When 0, the current time is used as an RNG seed. [0]
28 --rand-pareto-h=N shape parameter for the Pareto distribution [0.2]
29 --rand-zipfian-exp=N shape parameter (exponent, theta) for the Zipfian distribution [0.8]
30
31Log options:
32 --verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
33
34 --percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
35 --histogram[=on|off] print latency histogram in report [off]
36
37General database options:
38
39 --db-driver=STRING specifies database driver to use ('help' to get list of available drivers)
40 --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
41 --db-debug[=on|off] print database-specific debug information [off]
42
43
44Compiled-in database drivers:
45 pgsql - PostgreSQL driver
46
47pgsql options:
48 --pgsql-host=STRING PostgreSQL server host [localhost]
49 --pgsql-port=N PostgreSQL server port [5432]
50 --pgsql-user=STRING PostgreSQL user [sbtest]
51 --pgsql-password=STRING PostgreSQL password []
52 --pgsql-db=STRING PostgreSQL database name [sbtest]
53 --pgsql-sslmode=STRING PostgreSQL SSL mode (disable, allow, prefer, require, verify-ca, verify-full) [prefer]
54
55Compiled-in tests:
56 fileio - File I/O test
57 cpu - CPU performance test
58 memory - Memory functions speed test
59 threads - Threads subsystem performance test
60 mutex - Mutex performance test
61
62See 'sysbench <testname> help' for a list of options for each test.
3.1 测试CPU性能
sysbench cpu help
1参数详解：
2 --cpu-max-prime=N 用来选项指定素数的最大值，具体参数可以根据CPU的性能来设置，默认为10000
1运行命令： sysbench cpu --cpu-max-prime=10000 run
2[03:44:10-postgres@sean-rh1:/iihero/source/sysbench/sysbench]$ sysbench cpu --cpu-max-prime=10000 run
结果如下：
1sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
2Running the test with following options:
3Number of threads: 1
4Initializing random number generator from current time
5Prime numbers limit: 10000
6Initializing worker threads...
7Threads started!
8CPU speed:
9 events per second: 1024.92
10Throughput:
11 events/s (eps): 1024.9152
12 time elapsed: 10.0008s
13 total number of events: 10250
14Latency (ms):
15 min: 0.95
16 avg: 0.97
17 max: 2.13
18 95th percentile: 1.03
19 sum: 9993.04
20Threads fairness:
21 events (avg/stddev): 10250.0000/0.00
22 execution time (avg/stddev): 9.9930/0.00
10秒钟，总计触发10250个事件。每秒1025个。
3.2 测试磁盘IO性能
1[03:50:30-postgres@sean-rh1:/iihero/source/sysbench/sysbench]$ sysbench fileio help
2sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
3
4fileio options:
5 --file-num=N number of files to create [128]
6 --file-block-size=N block size to use in all IO operations [16384]
7 --file-total-size=SIZE total size of files to create [2G]
8 --file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}, 包含：seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。
9 --file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
10 --file-async-backlog=N number of asynchronous operatons to queue per thread [128]
11 --file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
12 --file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
13 --file-fsync-all[=on|off] do fsync() after each write operation [off]
14 --file-fsync-end[=on|off] do fsync() at the end of test [on]
15 --file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
16 --file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]
17 --file-rw-ratio=N reads/writes ratio for combined test [1.5]
运行一把看看：
准备文件：
1sysbench fileio prepare --file-test-mode=rndrw
2..........
32147483648 bytes written in 3.07 seconds (666.90 MiB/sec).
运行：
1[03:56:12-postgres@sean-rh1:/iihero/tmp]$ sysbench fileio run --file-test-mode=rndrw
2Throughput:
3 read: IOPS=3423.73 53.50 MiB/s (56.09 MB/s)
4 write: IOPS=2282.49 35.66 MiB/s (37.40 MB/s)
5 fsync: IOPS=7310.26
6Latency (ms):
7 min: 0.00
8 avg: 0.08
9 max: 5.72
10 95th percentile: 0.46
11 sum: 9917.73
清除：
1sysbench fileio cleanup --file-test-mode=rndrw
2sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
3Removing test files...
3.3 测试Memory
1[03:57:25-postgres@sean-rh1:/iihero/tmp]$ sysbench memory help
2memory options:
3 --memory-block-size=SIZE size of memory block for test [1K]
4 --memory-total-size=SIZE total size of data to transfer [100G]
5 --memory-scope=STRING memory access scope {global,local} [global]
6 --memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off]
7 --memory-oper=STRING type of memory operations {read, write, none} [write]
8 --memory-access-mode=STRING memory access mode {seq,rnd} [seq]
运行下看看：
memory写, block: 4K
1sysbench memory run --memory-block-size=4K
2Running memory speed test with the following options:
3 block size: 4KiB
4 total size: 102400MiB
5 operation: write
6 scope: global
7Initializing worker threads...
8Threads started!
9Total operations: 26214400 (3056462.87 per second)
10102400.00 MiB transferred (11939.31 MiB/sec)
11Throughput:
12 events/s (eps): 3056462.8730
13 time elapsed: 8.5767s
14 total number of events: 26214400
15Latency (ms):
16 min: 0.00
17 avg: 0.00
18 max: 0.06
19 95th percentile: 0.00
20 sum: 5278.43
21Threads fairness:
22 events (avg/stddev): 26214400.0000/0.00
23 execution time (avg/stddev): 5.2784/0.00memory读, block: 4K
1sysbench memory run --memory-block-size=4K
2Running memory speed test with the following options:
3 block size: 4KiB
4 total size: 102400MiB
5 operation: read
6 scope: global
7Initializing worker threads...
8Threads started!
9Total operations: 26214400 (4156277.84 per second)
10102400.00 MiB transferred (16235.46 MiB/sec)
11Throughput:
12 events/s (eps): 4156277.8436
13 time elapsed: 6.3072s
14 total number of events: 26214400
15Latency (ms):
16 min: 0.00
17 avg: 0.00
18 max: 0.17
19 95th percentile: 0.00
20 sum: 3031.50
21Threads fairness:
22 events (avg/stddev): 26214400.0000/0.00
23 execution time (avg/stddev): 3.0315/0.00
3.4 测试线程性能
1[04:03:20-postgres@sean-rh1:/iihero/tmp]$ sysbench threads help
2sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
3
4threads options:
5 --thread-yields=N number of yields to do per request [1000] 指定每个请求的压力，默认为1000
6 --thread-locks=N number of locks per thread [8] 指定每个线程的锁数量，默认为8
运行一下：
sysbench threads --threads=10 --time=30 --thread-yields=1000 --thread-locks=10 run
1[04:03:35-postgres@sean-rh1:/iihero/tmp]$ sysbench threads --threads=10 --time=30 --thread-yields=1000 --thread-locks=10 run
2
3Running the test with following options:
4Number of threads: 10
5Initializing random number generator from current time
6Initializing worker threads...
7Threads started!
8Throughput:
9 events/s (eps): 5244.3472
10 time elapsed: 30.0013s
11 total number of events: 157337
12Latency (ms):
13 min: 0.21
14 avg: 1.91
15 max: 29.86
16 95th percentile: 8.28
17 sum: 299882.29
18Threads fairness:
19 events (avg/stddev): 15733.7000/178.43
20 execution time (avg/stddev): 29.9882/0.00
3.5 mutex性能测量
1[04:49:37-postgres@sean-rh1:/iihero/tmp]$ sysbench mutex help
2mutex options:
3 --mutex-num=N total size of mutex array [4096] 数组互斥的总大小。默认是4096
4 --mutex-locks=N number of mutex locks to do per thread [50000] 每个线程互斥锁的数量。默认是50000
5 --mutex-loops=N number of empty loops to do outside mutex lock [10000] 内部互斥锁的空循环数量。默认是10000
运行下看看：sysbench mutex --threads=2048 --time=30 --mutex-num=4096 run
1[04:54:54-postgres@sean-rh1:/iihero/tmp]$ sysbench mutex --threads=2048 --time=30 --mutex-num=4096 run
2sysbench 1.1.0-2ca9e3f (using bundled LuaJIT 2.1.0-beta3)
3Running the test with following options:
4Number of threads: 2048
5Initializing random number generator from current time
6Initializing worker threads...
7Threads started!
8Throughput:
9 events/s (eps): 21.6791
10 time elapsed: 94.4687s
11 total number of events: 2048
12Latency (ms):
13 min: 8526.34
14 avg: 77070.40
15 max: 92933.78
16 95th percentile: 88157.45
17 sum: 157840177.52
18Threads fairness:
19 events (avg/stddev): 1.0000/0.00
20 execution time (avg/stddev): 77.0704/9.49
3.6 跑下tpcc.lua基于PG
进到目录：sysbench-tpcc-digoal，里边有我们要跑的tpcc的脚本
1、Prepare阶段
sysbench tpcc.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 --pgsql-port=5555 --pgsql-user=postgres --pgsql-password=test123 \
--pgsql-db=postgres --threads=64 --tables=10 --scale=1 --trx_level=RC --db-ps-mode=auto prepare
scale为1时，tables=10，大概是1.2G的空间
2、运行测试
1[07:29:06-postgres@sean-rh1:/iihero/source/sysbench/sysbench-tpcc-digoal]$ sysbench tpcc.lua --db-driver=pgsql --pgsql-host=127.0.0.1 --pgsql-port=5555 --pgsql-user=postgres --pgsql-password=test123 --pgsql-db=postgres --threads=64 --time=30 --tables=10 --scale=1 --trx_level=RC --db-ps-mode=auto run
2
3Running the test with following options:
4Number of threads: 64
5Initializing random number generator from current time
6Initializing worker threads...
7Threads started!
8SQL statistics:
9 queries performed:
10 read: 433029
11 write: 449914
12 other: 98966
13 total: 981909
14 transactions: 33451 (1110.54 per sec.)
15 queries: 981909 (32598.40 per sec.)
16 ignored errors: 112 (3.72 per sec.)
17 reconnects: 0 (0.00 per sec.)
18Throughput:
19 events/s (eps): 1110.5400
20 time elapsed: 30.1214s
21 total number of events: 33451
22Latency (ms):
23 min: 0.51
24 avg: 57.49
25 max: 740.86
26 95th percentile: 161.51
27 sum: 1923113.64
28Threads fairness:
29 events (avg/stddev): 522.6719/20.77
30 execution time (avg/stddev): 30.0487/0.03
从上边可以看到：每秒有32598个查询，有1110个事务处理。
小结：
将benchmarkSQL、pgbench、sysbench结合起来用，可以用到不同场合。如果是涉及到业务运行的性能测试，可以使用k6、JMeter等工具。最终都能对性能进行有效的度量。
文章转载自数据库杂记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
