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'...
作者：高&玉
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
