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张表，如下

--------+------------------+-------+----------+--------+-------------

测试：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 100000 * :scale

\setrandom aid 1 :naccounts

\setrandom bid 1 :nbranches

\setrandom tid 1 :ntellers

\setrandom delta -5000 5000

BEGIN;

UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

END;

举例测试一下机器insert的速度有多快

insertsize.sql内容：create table data(filler text);

insert-size.sql内容：insert into data (filler) values (repeat('X',:scale));

$psql -d pgbench -f insertsize.sql

$pgbench -s 100 -c 10 -t 10000 pgbench -f insert-size.sql

starting vacuum...end.

transaction type: Custom query

scaling factor: 100

query mode: simple

number of clients: 10

number of threads: 1

number of transactions per client: 10000

number of transactions actually processed: 100000/100000

tps = 608.144907 (including connections establishing)

tps = 608.234303 (excluding connections establishing)