官方参考:http://www.postgres.cn/docs/12/pgbench.html
[postgres@pg01 ~]$ psql psql (12.8) Type "help" for help.
postgres=# \l ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? List of databases ? ?Name ? ?| ?Owner ? | Encoding | ? Collate ? | ? ?Ctype ? ?| ? Access privileges ?? -----------+----------+----------+-------------+-------------+----------------------- ?postgres ?| postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 |? ?t ? ? ? ? | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 |? ?template0 | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ? ? ? ? ?+ ? ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ? ? | ? ? ? ? ? ? | postgres=CTc/postgres ?template1 | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ? ? ? ? ?+ ? ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ? ? | ? ? ? ? ? ? | postgres=CTc/postgres (4 rows)
postgres=# create database pgbench; CREATE DATABASE postgres=# \l ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? List of databases ? ?Name ? ?| ?Owner ? | Encoding | ? Collate ? | ? ?Ctype ? ?| ? Access privileges ?? -----------+----------+----------+-------------+-------------+----------------------- ?pgbench ? | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 |? ?postgres ?| postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 |? ?t ? ? ? ? | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 |? ?template0 | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ? ? ? ? ?+ ? ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ? ? | ? ? ? ? ? ? | postgres=CTc/postgres ?template1 | postgres | UTF8 ? ? | en_US.UTF-8 | en_US.UTF-8 | =c/postgres ? ? ? ? ?+ ? ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ?| ? ? ? ? ? ? | ? ? ? ? ? ? | postgres=CTc/postgres (5 rows)
postgres=# \q [postgres@pg01 ~]$ pgbench -i pgbench 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... 100000 of 100000 tuples (100%) done (elapsed 0.08 s, remaining 0.00 s) vacuuming... creating primary keys... done. [postgres@pg01 ~]$ psql psql (12.8) Type "help" for help.
postgres=# \c pgbench You are now connected to database "pgbench" as user "postgres". pgbench=# \d ? ? ? ? ? ? ? List of relations ?Schema | ? ? ? Name ? ? ? | Type ?| ?Owner ?? --------+------------------+-------+---------- ?public | pgbench_accounts | table | postgres ?public | pgbench_branches | table | postgres ?public | pgbench_history ?| table | postgres ?public | pgbench_tellers ?| table | postgres (4 rows)
pgbench=# \q [postgres@pg01 ~]$ pgbench --help pgbench is a benchmarking tool for PostgreSQL.
Usage: ? pgbench [OPTION]... [DBNAME]
Initialization options: ? -i, --initialize ? ? ? ? invokes initialization mode ? -I, --init-steps=[dtgvpf]+ (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 ? --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%)
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>. [postgres@pg01 ~]$ pgbench ?-T 20 -c 100 -j 5 -r pgbench -p 5432 -U postgres? starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 100 number of threads: 5 duration: 20 s number of transactions actually processed: 23543 latency average = 85.498 ms tps = 1169.614558 (including connections establishing) tps = 1174.241185 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.001 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.000 ?\set bid random(1, 1 * :scale) ? ? ? ? ?0.000 ?\set tid random(1, 10 * :scale) ? ? ? ? ?0.000 ?\set delta random(-5000, 5000) ? ? ? ? ?0.248 ?BEGIN; ? ? ? ? ?0.645 ?UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; ? ? ? ? ?0.350 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; ? ? ? ? 69.488 ?UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid; ? ? ? ? ?6.965 ?UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid; ? ? ? ? ?0.142 ?INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); ? ? ? ? ?0.654 ?END; [postgres@pg01 ~]$ pgbench ?-T 20 -c 100 -j 5 -r pgbench -p 5432 -U postgres -N starting vacuum...end. transaction type: <builtin: simple update> scaling factor: 1 query mode: simple number of clients: 100 number of threads: 5 duration: 20 s number of transactions actually processed: 112134 latency average = 17.902 ms tps = 5585.883031 (including connections establishing) tps = 5588.777503 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.001 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.000 ?\set bid random(1, 1 * :scale) ? ? ? ? ?0.000 ?\set tid random(1, 10 * :scale) ? ? ? ? ?0.000 ?\set delta random(-5000, 5000) ? ? ? ? ?3.191 ?BEGIN; ? ? ? ? ?3.357 ?UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid; ? ? ? ? ?3.231 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; ? ? ? ? ?3.104 ?INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP); ? ? ? ? ?4.704 ?END; [postgres@pg01 ~]$ pgbench ?-T 20 -c 100 -j 5 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 100 number of threads: 5 duration: 20 s number of transactions actually processed: 543312 latency average = 3.690 ms tps = 27102.580734 (including connections establishing) tps = 27123.551894 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.000 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?3.527 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench ?-T 20 -c 20 -j 5 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 20 number of threads: 5 duration: 20 s number of transactions actually processed: 596310 latency average = 0.671 ms tps = 29807.085020 (including connections establishing) tps = 29821.585896 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.001 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.668 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench ?-T 20 -c 10 -j 5 -r pgbench -p 5432 -U postgres -S starting vacuum...end. ^C [postgres@pg01 ~]$ pgbench ?-T 20 -c 10 -j 2 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 2 duration: 20 s number of transactions actually processed: 588036 latency average = 0.340 ms tps = 29395.765161 (including connections establishing) tps = 29399.156870 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.000 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.318 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench ?-T 20 -c 10 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. ^C [postgres@pg01 ~]$ pgbench ?-T 20 -c 5 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. ^C [postgres@pg01 ~]$ pgbench ?-T 20 -c 2 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 2 number of threads: 1 duration: 20 s number of transactions actually processed: 613686 latency average = 0.065 ms tps = 30683.961932 (including connections establishing) tps = 30687.614664 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.001 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.064 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench ?-T 20 -c 1 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. ^C [postgres@pg01 ~]$ pgbench ?-c 1 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 latency average = 0.573 ms tps = 1744.616115 (including connections establishing) tps = 4318.786939 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.002 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.227 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench ?-T 0 -c 1 -j 1 -r pgbench -p 5432 -U postgres -S invalid duration: "0" [postgres@pg01 ~]$ pgbench ?-T 10 -c 1 -j 1 -r pgbench -p 5432 -U postgres -S starting vacuum...end. transaction type: <builtin: select only> scaling factor: 1 query mode: simple number of clients: 1 number of threads: 1 duration: 10 s number of transactions actually processed: 147404 latency average = 0.068 ms tps = 14740.254635 (including connections establishing) tps = 14743.995403 (excluding connections establishing) statement latencies in milliseconds: ? ? ? ? ?0.001 ?\set aid random(1, 100000 * :scale) ? ? ? ? ?0.067 ?SELECT abalance FROM pgbench_accounts WHERE aid = :aid; [postgres@pg01 ~]$ pgbench -i pgbench dropping old tables... creating tables... generating data... 100000 of 100000 tuples (100%) done (elapsed 0.05 s, remaining 0.00 s) vacuuming... creating primary keys... done. [postgres@pg01 ~]$?
其它参考:
pgbench(postgresql压力测试工具)_被IT耽误的网红的博客-CSDN博客_pgbench
pgbench使用实践 -- cnDBA.cn_中国DBA社区
|