时间:2021-07-01 10:21:17 帮助过:4人阅读
一 初始化;这里我使用了自己的数据库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压力测试工具用法以及参数解读
标签: