postgresql压力测试工具用法以及参数解读

一 初始化；这里我使用了自己的数据库mydb；数据用户lottu

pgbench -i -s 10 --foreign-keys --unlogged-tables -U lottu mydb

操作之后会在数据库里面生成下面的表

mydb=> \dt pgbench*
             List of relations
 Schema |       Name       | Type  | Owner 
--------+------------------+-------+-------
 public | pgbench_accounts | table | lottu
 public | pgbench_branches | table | lottu
 public | pgbench_history  | table | lottu
 public | pgbench_tellers  | table | lottu
(4 rows)

二 压力测试演示

[postgres@sdserver40_210 ~]$ pgbench -M prepared -r -c 10 -j 2 -T 10 -U lottu mydb
starting vacuum...end.                                            --默认是非n模式
transaction type: TPC-B (sort of)
scaling factor: 10                                                   --跟上面初始化-s 10一致的
query mode: prepared                                          -- -M prepared 默认为simple
number of clients: 10                                            --客户端连接有10个   -c 10
number of threads: 2                                             --线程为2个    -j2
duration: 10 s                                                         --时间为10s     -T 10
number of transactions actually processed: 146890
latency average: 0.681 ms
tps = 14687.531247 (including connections establishing)
tps = 14690.762892 (excluding connections establishing)
statement latencies in milliseconds:
        0.001784        \set nbranches 1 * :scale
        0.000633        \set ntellers 10 * :scale
        0.000483        \set naccounts 100000 * :scale
        0.001076        \setrandom aid 1 :naccounts
        0.000647        \setrandom bid 1 :nbranches
        0.000654        \setrandom tid 1 :ntellers
        0.000762        \setrandom delta -5000 5000
        0.034427        BEGIN;
        0.094350        UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
        0.064211        SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
        0.110995        UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
        0.154680        UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
        0.155552        INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
        0.052141        END;

--使用-l参数结合 --aggregate-interval  演示
[postgres@sdserver40_210 ~]$ pgbench -M extended --aggregate-interval 2 -l  -c 2 -j 2 -T 10 -U lottu mydb
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 10
query mode: extended
number of clients: 2
number of threads: 2
duration: 10 s
number of transactions actually processed: 22613
latency average: 0.884 ms
tps = 2261.073893 (including connections establishing)
tps = 2261.413071 (excluding connections establishing)

--这样会生成文件
[postgres@sdserver40_210 ~]$ ll pgbench_log.5160*
-rw-rw-r-- 1 postgres postgres 211 Jun 28 16:37 pgbench_log.5160
-rw-rw-r-- 1 postgres postgres 210 Jun 28 16:37 pgbench_log.5160.1

查看文件内容可以判断测试结果！
[postgres@sdserver40_210 ~]$ cat pgbench_log.5160
1467103061 1864 1663000 1739000000 0 9000
1467103063 2256 1998000 2000000000 0 2000
1467103065 2268 1997000 2001000000 0 2000
1467103067 2271 1996000 1998000000 0 2000
1467103069 2250 1996000 2268000000 0 17000
这5列分别代表
interval_start // epoch时间, 指这个统计段的开始时间.
num_of_transactions // 这个统计段运行了多少个"事务", 指独立的文件运行次数.
latency_sum // 这个统计段的事务执行总耗时, 单位微秒.
latency_2_sum // 这个统计段的事务执行耗时平方的总和, 单位微秒.
min_latency // 这个统计段内, 单个事务的最小耗时.
max_latency // 这个统计段内, 单个事务的最大耗时.

--参考德哥视频讲解

postgresql压力测试工具用法以及参数解读

标签：