1 pgbench
1.1 介绍
pgbench是PostgreSQL自带的压测工具。
1.2 参数介绍
pgbench --help
点击查看代码
[postgres]$ pgbench --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTION]... [DBNAME]
Initialization options:
-i, --initialize invokes initialization mode
-F, --fillfactor=NUM set fill factor
-n, --no-vacuum do not run VACUUM after initialization
-q, --quiet quiet logging (one message each 5 seconds)
-s, --scale=NUM scaling factor
--foreign-keys create foreign key constraints between tables
--index-tablespace=TABLESPACE
create indexes in the specified tablespace
--tablespace=TABLESPACE create tables in the specified tablespace
--unlogged-tables create tables as unlogged tables
Options to select what to run:
-b, --builtin=NAME[@W] add builtin script NAME weighted at W (default: 1)
(use "-b list" to list available scripts)
-f, --file=FILENAME[@W] add script FILENAME weighted at W (default: 1)
-N, --skip-some-updates skip updates of pgbench_tellers and pgbench_branches
(same as "-b simple-update")
-S, --select-only perform SELECT-only transactions
(same as "-b select-only")
Benchmarking options:
-c, --client=NUM number of concurrent database clients (default: 1)
-C, --connect establish new connection for each transaction
-D, --define=VARNAME=VALUE
define variable for use by custom script
-j, --jobs=NUM number of threads (default: 1)
-l, --log write transaction times to log file
-L, --latency-limit=NUM count transactions lasting more than NUM ms as late
-M, --protocol=simple|extended|prepared
protocol for submitting queries (default: simple)
-n, --no-vacuum do not run VACUUM before tests
-P, --progress=NUM show thread progress report every NUM seconds
-r, --report-latencies report average latency per command
-R, --rate=NUM target rate in transactions per second
-s, --scale=NUM report this scale factor in output
-t, --transactions=NUM number of transactions each client runs (default: 10)
-T, --time=NUM duration of benchmark test in seconds
-v, --vacuum-all vacuum all four standard tables before tests
--aggregate-interval=NUM aggregate data over NUM seconds
--progress-timestamp use Unix epoch timestamps for progress
--sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%)
Common options:
-d, --debug print debugging output
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=USERNAME connect as specified database user
-V, --version output version information, then exit
-?, --help show this help, then exit
Report bugs to <pgsql-bugs@postgresql.org>.
常用参数：
| 类型 | 参数 | 说明 |
| 初始化 | -i, --initialize | 调用初始化模式 |
| -F, --fillfactor=NUM | 填充元素个数 | |
| -n, --no-vacuum | 初始化后不运行VACUUM | |
| -q, --quiet | 记录日志（每5秒记录一条日志） | |
| -s, --scale=NUM | 比例因子 | |
| --foreign-keys | 创建表之间的外键约束 | |
| --index-tablespace=TABLESPACE | 指定索引默认存储表空间 | |
| --tablespace=TABLESPACE | 指定表默认存储表空间 | |
| --unlogged-tables | 指定创建的表不记录日志 | |
| select运行选项 | -b, --builtin=NAME[@W] | 指定运行脚本 |
| -f, --file=FILENAME[@W] | 指定运行脚本 | |
| -N, --skip-some-updates | 跳过pgbench_tellers和pgbench_branches的更新 | |
| -S, --select-only | 只执行select操作 | |
| 压测选项 | -c, --client=NUM | 指定客户端数量 |
| -C, --connect | 为每个事务建立新的连接 | |
| -D, --define=VARNAME=VALUE | 定义变量以供自定义脚本使用 | |
| -j, --jobs=NUM | 指定线程数 | |
| -l, --log | 将事务时间写入日志文件 | |
| -L, --latency-limit=NUM | 将持续时间超过M毫秒的事务计数为延迟 | |
| -M, --protocol=simple|extended|prepared | 查询的协议(默认:simple) | |
| -n, --no-vacuum | 测试前不运行VACUUM操作 | |
| -P, --progress=NUM | 指定N秒显示线程进度报告 | |
| -r, --report-latencies | 报告每个命令的平均延迟 | |
| -R, --rate=NUM | 每秒事务处理的目标速率 | |
| -s, --scale=NUM | 在输出中报告这个比例因素 | |
| -t, --transactions=NUM | 每个客户端运行的事务数(默认为10) | |
| -T, --time=NUM | 基准测试持续时间(以秒为单位) | |
| -v, --vacuum-all | 在测试前清空4个标准表 | |
| --aggregate-interval=NUM | 指定N秒内聚合数据 | |
| --progress-timestamp | 进程使用Unix时间戳 | |
| --sampling-rate=NUM | 事务占日志的百分比(例如，1%占0.01) | |
| 常用选项 | -d, --debug | 输出调试结果 |
| -h, --host=HOSTNAME | 数据库主机或socket路径 | |
| -p, --port=PORT | 数据库端口 | |
| -U, --username=USERNAME | 指定连接数据库用户名 |
1.3 压力测试
初始化（生成4个测试表）
[postgres]$ pgbench -h 127.0.0.1 -p 5432 -U postgres --initialize
Password:
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.02 s, remaining 0.00 s)
vacuum...
set primary keys...
done.
200个客户端，10个线程，将事务写入日志文件，每5秒报告进度，每个客户端100个事务，测试前清空标准的测试表
[postgres]$ pgbench -h 127.0.0.1 -p 5432 -U postgres --client=200 --connect --jobs=10 --log --progress=5 --transactions=100 --vacuum-all
Password:
starting vacuum...end.
starting vacuum pgbench_accounts...end.
progress: 5.0 s, 1217.7 tps, lat 152.132 ms stddev 197.571
progress: 10.0 s, 1025.9 tps, lat 188.665 ms stddev 314.154
progress: 15.0 s, 1185.9 tps, lat 170.930 ms stddev 235.899
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 200
number of threads: 10
number of transactions per client: 100
number of transactions actually processed: 20000/20000
latency average = 157.293 ms
latency stddev = 239.600 ms
tps = 1177.215350 (including connections establishing)
tps = 1189.014326 (excluding connections establishing)
2 sysbench
2.1 介绍
sysbench是一款开源的多线程性能压测工具，可以对CPU、内存、线程、IO、数据库等进行性能压力测试。
2.2 安装
源码安装
[root]# git clone https://github.com/akopytov/sysbench.git
[root]# cd sysbench
[root]# sh autogen.sh
[root]# ./configure --with-pgsql --with-pgsql-includes=/usr/local/pgsql/include --with-pgsql-libs=/usr/local/pgsql/lib --without-mysql
[root]# make && make install
查看sysbench版本
[root]# sysbench --version
sysbench 1.1.0-ead26892.3 参数介绍
查看帮助手册
点击查看代码
[root]# sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
--warmup-time=N execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled [0]
--forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
--thread-stack-size=SIZE size of stack per thread [64K]
--thread-init-timeout=N wait time in seconds for worker threads to initialize [30]
--rate=N average transactions rate. 0 for unlimited rate [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--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. []
--debug[=on|off] print more debugging info [off]
--validate[=on|off] perform validation checks where possible [off]
--help[=on|off] print help and exit [off]
--version[=on|off] print version and exit [off]
--config-file=FILENAME File containing command line options
--luajit-cmd=STRING perform LuaJIT control command. This option is equivalent to 'luajit -j'. See LuaJIT documentation for more information
Pseudo-Random Numbers Generator options:
--rand-type=STRING random numbers distribution {uniform, gaussian, pareto, zipfian} to use by default [uniform]
--rand-seed=N seed for random number generator. When 0, the current time is used as an RNG seed. [0]
--rand-pareto-h=N shape parameter for the Pareto distribution [0.2]
--rand-zipfian-exp=N shape parameter (exponent, theta) for the Zipfian distribution [0.8]
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
--percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
--histogram[=on|off] print latency histogram in report [off]
General database options:
--db-driver=STRING specifies database driver to use ('help' to get list of available drivers)
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
--db-debug[=on|off] print database-specific debug information [off]
Compiled-in database drivers:
pgsql - PostgreSQL driver
pgsql options:
--pgsql-host=STRING PostgreSQL server host [localhost]
--pgsql-port=N PostgreSQL server port [5432]
--pgsql-user=STRING PostgreSQL user [sbtest]
--pgsql-password=STRING PostgreSQL password []
--pgsql-db=STRING PostgreSQL database name [sbtest]
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
See 'sysbench <testname> help' for a list of options for each test.
常用参数：
| 类型 | 参数 | 说明 |
| 一般参数 | --threads=N | 指定线程数量（默认是1） |
| --events=N | 指定总事件数（默认0） | |
| --time=N | 指定压测运行时间（默认是10秒） | |
| --warmup-time=N | 在启用统计信息的情况下运行实际基准测试之前，在禁用统计信息的情况下执行这一秒的事件（默认0） | |
| --forced-shutdown=STRING | --time参数之后，指定是否强制关机（默认off） | |
| --thread-stack-size=SIZE | 每个线程的堆栈大小（默认64K） | |
| --thread-init-timeout=N | 以秒为单位的工作线程初始化的等待时间（默认30秒） | |
| --rate=N | 平均事务率。0表示无限利率（默认0） | |
| --report-interval=N | 以秒为单位，定期报告中间统计信息。0禁用中间报告（默认0） | |
| --report-checkpoints=[LIST,...] | 转储全部统计数据并在指定的时间点重置所有计数器。该参数是一个以逗号分隔的值列表，表示从测试开始到必须执行报告检查点时所经过的时间(以秒为单位)。默认情况下，报告检查点是关闭的。 | |
| --debug[=on|off] | 打印更多调试信息（默认off） | |
| --validate[=on|off] | 在可能的情况下执行验证检查（off） | |
| --help[=on|off] | 打印帮助并退出（默认off） | |
| --version[=on|off] | 打印版本并退出（默认off） | |
| --config-file=FILENAME | 包含命令行选项的文件 | |
| 伪随机数生成器选项 | --rand-type=STRING | 默认使用随机数分布{uniform, gaussian, pareto, zipfian}（默认uniform） |
| --rand-seed=N | 随机数生成器的种子。当为0时，当前时间被用作RNG种子。 | |
| --rand-pareto-h=N | 形状参数的帕累托分布（默认0.2） | |
| --rand-zipfian-exp=N | 形状参数(exponent, theta)为Zipfian分布（默认0.8） | |
| LOG选项 | --verbosity=N | 日志详细级别{5 - debug, 0 - only critical messages}（默认3） |
| --percentile=N | 在延迟统计中要计算的百分比(1-100)。使用特殊值0禁用百分比计算（默认95） | |
| --histogram[=on|off] | 在报告中打印延迟直方图（默认off） | |
| 数据库选项 | --db-driver=STRING | 指定要使用的数据库驱动程序 |
| --db-ps-mode=STRING | 预置语句使用模式{auto, disable}（默认auto） | |
| --db-debug[=on|off] | 打印特定于数据库的调试信息（默认off） | |
| 数据库驱动 | pgsql | PostgreSQL数据库驱动 |
| pgsql选项 | --pgsql-host=STRING | 连接PostgreSQL的主机（默认是localhost） |
| --pgsql-port=N | 连接PostgreSQL的服务端口（默认是5432） | |
| --pgsql-user=STRING | 连接PostgreSQL的用户（默认是sbtest） | |
| --pgsql-password=STRING | 连接PostgreSQL的用户密码 | |
| --pgsql-db=STRING | 连接PostgreSQL的数据库名称（默认是sbtest） | |
| 其他 | fileio | 文件系统I/O测试 |
| cpu | CPU性能测试 | |
| memory | 内存功能速度测试 | |
| threads | 线程子系统性能测试 | |
| mutex | 互斥锁的性能测试 |
2.4 压力测试
提示：
在sysbench/src/lua路径有十几种压测脚本，应根据压测目的选择对应的压测脚本。
select_random_ranges.lua
select_random_points.lua
prime-test.lua
oltp_write_only.lua
oltp_update_non_index.lua
oltp_update_index.lua
oltp_read_write.lua
oltp_read_only.lua
oltp_point_select.lua
oltp_insert.lua
oltp_delete.lua
oltp_common.lua
empty-test.lua
bulk_insert.lua
准备数据
创建10张测试表，每张表100万行
[postgres]$ sysbench /home/postgres/sysbench/src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=Passwd@123 \
--pgsql-db=postgres \
--tables=10 \
--table-size=1000000 \
prepare
sysbench 1.1.0-ead2689 (using bundled LuaJIT 2.1.0-beta3)
Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 1000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 1000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 1000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 1000000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 1000000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 1000000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 1000000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 1000000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...
压力测试
10张测试表，每张表100万行，每5秒打印测试结果，100个线程，总运行100秒
[root]$ sysbench /home/postgres/sysbench/src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=Passwd@123 \
--pgsql-db=postgres \
--tables=10 \
--table-size=1000000 \
--report-interval=5 \
--threads=100 \
--time=60 \
run
sysbench 1.1.0-ead2689 (using bundled LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 100
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 100 tps: 74693.84 qps: 74693.84 (r/w/o: 0.00/74693.84/0.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 100 tps: 77222.15 qps: 77222.15 (r/w/o: 0.00/77222.15/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 100 tps: 76497.68 qps: 76497.68 (r/w/o: 0.00/76497.68/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 78223.86 qps: 78223.86 (r/w/o: 0.00/78223.86/0.00) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 100 tps: 77842.72 qps: 77842.92 (r/w/o: 0.00/77842.92/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 77398.63 qps: 77398.43 (r/w/o: 0.00/77398.43/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 100 tps: 72489.04 qps: 72489.04 (r/w/o: 0.00/72489.04/0.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 76023.09 qps: 76023.09 (r/w/o: 0.00/76023.09/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 100 tps: 75742.57 qps: 75742.57 (r/w/o: 0.00/75742.57/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 75072.73 qps: 75072.73 (r/w/o: 0.00/75072.73/0.00) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 100 tps: 75156.60 qps: 75156.80 (r/w/o: 0.00/75156.80/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 73926.29 qps: 73926.09 (r/w/o: 0.00/73926.09/0.00) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 4551688
other: 0
total: 4551688
transactions: 4551688 (75831.07 per sec.)
queries: 4551688 (75831.07 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
Throughput:
events/s (eps): 75831.0714
time elapsed: 60.0240s
total number of events: 4551688
Latency (ms):
min: 0.09
avg: 1.32
max: 47.33
95th percentile: 3.89
sum: 5990512.42
Threads fairness:
events (avg/stddev): 45516.8800/281.21
execution time (avg/stddev): 59.9051/0.01
清除压测数据
[root]$ sysbench /home/postgres/sysbench/src/lua/oltp_insert.lua \
--db-driver=pgsql \
--pgsql-host=127.0.0.1 \
--pgsql-port=5432 \
--pgsql-user=postgres \
--pgsql-password=Passwd@123 \
--pgsql-db=postgres \
--tables=10 \
cleanup
sysbench 1.1.0-ead2689 (using bundled LuaJIT 2.1.0-beta3)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
