postgresql pgbench工具
pgbench is a benchmarking tool for PostgreSQL ,pgbench是postgresql自带的一个性能基准测试工具。
可以通过pgbench --help看一下参数的相关信息
Usage:
pgbench [OPTIONS]... [DBNAME]
Initialization options:
-i invokes initialization mode
-F NUM fill factor
-s NUM scaling factor
Benchmarking options:
-c NUM number of concurrent database clients (default: 1)
-C establish new connection for each transaction
-D VARNAME=VALUE
define variable for use by custom script
-f FILENAME read transaction script from FILENAME
-j NUM number of threads (default: 1)
-l write transaction times to log file
-M {simple|extended|prepared}
protocol for submitting queries to server (default: simple)
-n do not run VACUUM before tests
-N do not update tables "pgbench_tellers" and "pgbench_branches"
-r report average latency per command
-s NUM report this scale factor in output
-S perform SELECT-only transactions
-t NUM number of transactions each client runs (default: 10)
-T NUM duration of benchmark test in seconds
-v vacuum all four standard tables before tests
Common options:
-d print debugging output
-h HOSTNAME database server host or socket directory
-p PORT database server port number
-U USERNAME connect as specified database user
--help show this help, then exit
--version output version information, then exit
初始化：scale为10，pgbench_accounts记录有100W，
pgbench -i -s 10 pgbench -h 127.0.0.1 -p 1931 -U testuser
这里初始化会创建4张表，如下
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+----------+--------+-------------
public | pgbench_accounts | table | testuser | 130 MB |
public | pgbench_branches | table | testuser | 168 kB |
public | pgbench_history | table | testuser | 960 kB |
public | pgbench_tellers | table | testuser | 456 kB |
测试：100个client并发，每个有100个事务
pgbench -c 100 -t 100 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
tps = 114.339158 (including connections establishing) --包含网络开销的事务数
tps = 114.643324 (excluding connections establishing) --不包含网络开销的事务数
pgbench -c 100 -t 100 -j 100 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 100
number of transactions per client: 100
number of transactions actually processed: 10000/10000
tps = 134.473603 (including connections establishing)
tps = 134.616838 (excluding connections establishing)
这里注意-c必须是-j的倍数，也就是client是threads的倍数。
pgbench -c 100 -j 100 -T 10 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: simple
number of clients: 100
number of threads: 100
duration: 10 s
number of transactions actually processed: 2144
tps = 209.617862 (including connections establishing)
tps = 211.282453 (excluding connections establishing)
注意：-t和-T不能同时使用，
脚本的内容：
\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 1000