CNDBA社区
在之前的博客我们看了sysbench对pg的压测，如下：
PostgreSQL 学习笔记（18） — PG 性能压测工具 sysbench 使用说明
https://www.cndba.cn/dave/article/116397
本篇我们来看一个自带的工具：pgbench。它可以在并发的数据库会话中一遍一遍地运行相同序列的 SQL 命令，并且计算平均事务率（每秒的事务数）；默认情况下，pgbench会测试一种基于 TPC-B 但是要更宽松的场景，其中在每个事务中涉及五个SELECT、UPDATE以及INSERT命令。
1 Pgbench 工具帮助
这里直接查看，不再补充说明：
-bash-4.2$ which pgbench
/usr/pgsql-14/bin/pgbench
-bash-4.2$ pgbench --help
pgbench is a benchmarking tool for PostgreSQL.
Usage:
pgbench [OPTION]... [DBNAME]
Initialization options:
-i, --initialize invokes initialization mode
-I, --init-steps=[dtgGvpf]+ (default "dtgvp")
run selected initialization steps
-F, --fillfactor=NUM set fill factor
-n, --no-vacuum do not run VACUUM during 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
--partition-method=(range|hash)
partition pgbench_accounts with this method (default: range)
--partitions=NUM partition pgbench_accounts into NUM parts (default: 0)
--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
--log-prefix=PREFIX prefix for transaction time log file
(default: "pgbench_log")
--progress-timestamp use Unix epoch timestamps for progress
--random-seed=SEED set random seed ("time", "rand", integer)
--sampling-rate=NUM fraction of transactions to log (e.g., 0.01 for 1%)
--show-script=NAME show builtin script code, then exit
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@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
-bash-4.2$
2 Pgbench 使用示例
2.1 初始化
pgbench -i [ other-options ] dbname
主要选项：
- -i：初始化模式
- -s 插入的倍数，默认是1，即插入100000条；也就是执行多少次generate_series(1,100000)。
创建四个表，如果同名表已经存在会被先删除
- pgbench_accounts #账户表
- pgbench_branches #支行表
- pgbench_history #历史信息表
- pgbench_tellers #出纳表
在默认的情况下-s(比例因子)为 1，这些表初始包含的行数为：
- table # of rows
- pgbench_branches 1
- pgbench_tellers 10
- pgbench_accounts 100000
- pgbench_history 0
-bash-4.2$ pgbench -h 127.0.0.1 -p 5432 -U postgres -d cndba --initialize
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.04 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.27 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 0.15 s, vacuum 0.07 s, primary keys 0.05 s).
-bash-4.2$
2.2 测试
20个客户端，10个线程，将事务写入日志文件，每5秒报告进度，每个客户端10个事务，测试前清空标准的测试表
-bash-4.2$ pgbench -h 127.0.0.1 -p 5432 -U postgres -d cndba --client=20 --connect --jobs=10 --log --progress=5 --transactions=10 --vacuum-all
pgbench (14.6)
pgbench: pghost: 127.0.0.1 pgport: 5432 nclients: 20 nxacts: 10 dbName: cndba
starting vacuum...end.
starting vacuum pgbench_accounts...end.
pgbench: client 8 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 0 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 16 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 4 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 14 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 2 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 10 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 12 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 6 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 18 executing script "<builtin: TPC-B (sort of)>"
pgbench: client 14 executing /set aid
pgbench: client 14 executing /set bid
pgbench: client 14 executing /set tid
pgbench: client 14 executing /set delta
pgbench: client 14 sending BEGIN;
……
pgbench: client 7 sending INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (9, 1, 68028, 1104, CURRENT_TIMESTAMP);
pgbench: client 7 receiving
pgbench: client 7 receiving
pgbench: client 7 sending END;
pgbench: client 7 receiving
pgbench: client 7 receiving
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 10
number of transactions per client: 10
number of transactions actually processed: 200/200
latency average = 97.044 ms
latency stddev = 18.596 ms
average connection time = 77.714 ms
tps = 109.443915 (including reconnection times)
-bash-4.2$
版权声明：本文为博主原创文章，未经博主允许不得转载。
