参考文档:
http://postgres.cn/docs/10/pgbench.html
-- pg版本
mydb=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 10.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
mydb=#
官方文档中,对于pgbench有以下的描述(详细的描述可以参考官方文档):
描述
pgbench是一种在PostgreSQL上运行基准测试的简单程序。它可能在并发的数据库会话中一遍一遍地运行相同序列的 SQL 命令,并且计算平均事务率(每秒的事务数)。默认情况下,pgbench会测试一种基于 TPC-B 但是要更宽松的场景,其中在每个事务中涉及五个SELECT
、UPDATE
以及INSERT
命令。但是,通过编写自己的事务脚本文件很容易用来测试其他情况。
pgbench的典型输出像这样:
transaction type: <builtin: TPC-B (sort of)> scaling factor: 10 query mode: simple number of clients: 10 number of threads: 1 number of transactions per client: 1000 number of transactions actually processed: 10000/10000 tps = 85.184871 (including connections establishing) tps = 85.296346 (excluding connections establishing)
前六行报告一些最重要的参数设置。接下来的行报告完成的事务数以及预期的事务数(后者就是客户端数量与每个客户端事务数的乘积),除非运行在完成之前失败,这些值应该是相等的(在-T
模式中,只有实际的事务数会被打印出来)。最后两行报告每秒的事务数,分别代表包括和不包括开始数据库会话所花时间的情况。
默认的类 TPC-B 事务测试要求预先设置好特定的表。可以使用-i
(初始化)选项调用pgbench来创建并且填充这些表(当你在测试一个自定义脚本时,你不需要这一步,但是需要按你自己的测试需要做一些设置工作)。初始化类似这样:
pgbench -i [other-options
]dbname
其中dbname
是要在其中进行测试的预先创建好的数据库的名称(你可能还需要-h
、-p
或-U
选项来指定如何连接到数据库服务器)。
-- pgbench具体的用法,可以通过pgbench --help来查看
-- 创建测试数据
vi test.sql
start transaction;
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
insert into a select generate_series(1,100);
commit;
-- 进行测试,测试插入数据,clients number为32, thread 为8 。使用的查询协议为prepared。不运行 VACUUM 。 关于测试用的几个参数说明如下:
-M querymode
--protocol=querymode
要用来提交查询到服务器的协议:
simple:使用简单查询协议。
extended使用扩展查询协议。
prepared:使用带预备语句的扩展查询语句。
默认是简单查询协议(详见第 52 章)。
-n
--no-vacuum
初始化以后不执行清理。
-r
--report-latencies
在基准结束后,报告平均的每个命令的每语句等待时间(从客户端的角度来说是执行时间)。详见下文。
-c clients
--client=clients
模拟的客户端数量,也就是并发数据库会话数量。默认为 1。
-j threads
--jobs=threads
pgbench中的工作者线程数量。在多 CPU 机器上使用多于一个线程会有用。客户端会尽可能均匀地分布到可用的线程上。默认为 1。
-t transactions
--transactions=transactions
每个客户端运行的事务数量。默认为 10。
-T seconds
--time=seconds
运行测试这么多秒,而不是为每个客户端运行固定数量的事务。-t和-T是互斥的。
在以下的结果中,实际事务14522,耗时60秒,平均每秒14522/60=242个事务 。与测试的结果tps=241 差不多。
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 8 -T 60 -h 127.0.0.1 -p 5432 -U postgres mydb
-bash-4.2$ pgbench -M prepared -n -r -f ./test.sql -c 32 -j 8 -T 60 -h 127.0.0.1 -p 5432 -U postgres mydb
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 32
number of threads: 8
duration: 60 s
number of transactions actually processed: 14522
latency average = 132.701 ms
tps = 241.142810 (including connections establishing)
tps = 241.354126 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
3.236 start transaction;
5.177 insert into a select generate_series(1,100);
3.516 insert into a select generate_series(1,100);
3.795 insert into a select generate_series(1,100);
3.992 insert into a select generate_series(1,100);
4.001 insert into a select generate_series(1,100);
3.932 insert into a select generate_series(1,100);
3.968 insert into a select generate_series(1,100);
4.140 insert into a select generate_series(1,100);
3.979 insert into a select generate_series(1,100);
3.781 insert into a select generate_series(1,100);
3.878 insert into a select generate_series(1,100);
3.619 insert into a select generate_series(1,100);
3.905 insert into a select generate_series(1,100);
4.259 insert into a select generate_series(1,100);
3.986 insert into a select generate_series(1,100);
3.853 insert into a select generate_series(1,100);
3.951 insert into a select generate_series(1,100);
3.876 insert into a select generate_series(1,100);
3.884 insert into a select generate_series(1,100);
3.692 insert into a select generate_series(1,100);
49.421 commit;
-bash-4.2$
-- 测试的时候,top的值
top - 12:37:17 up 1 day, 8:28, 3 users, load average: 12.10, 4.10, 2.21
52487 postgres 20 0 563432 1992 800 S 18.3 0.1 0:06.78 pgbench
7659 postgres 20 0 583416 269008 268388 R 5.8 8.8 0:02.89 postgres
52499 postgres 20 0 585164 75556 74176 R 4.8 2.5 0:01.86 postgres
52496 postgres 20 0 585164 77388 76012 R 4.5 2.5 0:01.76 postgres
52507 postgres 20 0 585164 73972 72600 S 4.5 2.4 0:01.65 postgres
52509 postgres 20 0 585164 72364 70988 R 4.5 2.4 0:01.90 postgres
52510 postgres 20 0 585164 68424 67048 S 4.5 2.2 0:01.73 postgres
52511 postgres 20 0 585164 87700 86328 R 4.5 2.9 0:01.68 postgres
52515 postgres 20 0 585164 76604 75228 S 4.5 2.5 0:01.74 postgres
52519 postgres 20 0 585164 77136 75764 S 4.5 2.5 0:01.69 postgres
52527 postgres 20 0 585164 83208 81832 S 4.5 2.7 0:01.69 postgres
52497 postgres 20 0 585164 76336 74964 S 4.2 2.5 0:01.63 postgres
52498 postgres 20 0 585164 60244 58868 S 4.2 2.0 0:01.81 postgres
52500 postgres 20 0 585164 84000 82624 S 4.2 2.7 0:01.67 postgres
52501 postgres 20 0 585164 75808 74428 S 4.2 2.5 0:01.75 postgres
52502 postgres 20 0 585164 81356 79980 S 4.2 2.7 0:02.00 postgres
52504 postgres 20 0 585164 66312 64936 S 4.2 2.2 0:01.93 postgres
52505 postgres 20 0 585164 60756 59380 S 4.2 2.0 0:01.66 postgres
52513 postgres 20 0 585164 75008 73632 R 4.2 2.5 0:01.72 postgres
52514 postgres 20 0 585164 71840 70468 S 4.2 2.3 0:01.59 postgres
52520 postgres 20 0 585164 69996 68620 S 4.2 2.3 0:01.70 postgres
-- 再次进行测试,测试查询数据
pgbench -M prepared -n -r -f ./query.sql -c 32 -j 8 -T 60 -h 127.0.0.1 -p 5432 -U postgres mydb
负载太大,直接自己killed了
-bash-4.2$ pgbench -M prepared -n -r -f ./query.sql -c 32 -j 8 -T 60 -h 127.0.0.1 -p 5432 -U postgres mydb
Killed
-bash-4.2$
查看top,已经70多了
top - 12:44:06 up 1 day, 8:35, 3 users, load average: 71.27, 26.45, 10.74
Tasks: 348 total, 19 running, 329 sleeping, 0 stopped, 0 zombie
%Cpu(s): 7.9 us, 67.9 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 24.1 si, 0.0 st
KiB Mem : 3060308 total, 56972 free, 2067216 used, 936120 buff/cache
KiB Swap: 3538940 total, 2096124 free, 1442816 used. 38068 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
119570 oracle -2 0 1490760 1188 896 S 37.3 0.0 6:35.44 ora_vktm_test
8727 grid 20 0 1260212 7464 0 S 14.0 0.2 16:26.00 oraagent.bin
54343 postgres 20 0 1670252 1.1g 156 D 12.0 37.6 0:10.56 pgbench
修改c和j为8 还是kill了 。使用c和j为默认的1
pgbench -M prepared -n -r -f ./query.sql -c 1 -j 1 -T 30 -h 127.0.0.1 -p 5432 -U postgres mydb
此时查看,top,一直稳定在20左右
top - 12:59:41 up 1 day, 8:50, 4 users, load average: 17.13, 47.12, 37.05
Tasks: 318 total, 4 running, 314 sleeping, 0 stopped, 0 zombie
%Cpu(s): 30.7 us, 13.4 sy, 0.0 ni, 30.7 id, 0.2 wa, 0.0 hi, 25.0 si, 0.0 st
KiB Mem : 3060308 total, 875604 free, 610932 used, 1573772 buff/cache
KiB Swap: 3538940 total, 1516936 free, 2022004 used. 1860292 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
56637 postgres 20 0 584912 269600 268500 R 80.1 8.8 0:22.53 postgres
56635 postgres 20 0 246332 180176 1948 R 64.5 5.9 0:11.46 pgbench
119570 oracle -2 0 1490760 928 608 S 3.7 0.0 8:42.27 ora_vktm_test
54718 grid 20 0 1248232 14904 8440 S 1.0 0.5 0:42.90 oraagent.bin
1 root 20 0 191088 2464 1416 S 0.3 0.1 1:11.95 systemd
运行出的结果如下:
-bash-4.2$ pgbench -M prepared -n -r -f ./query.sql -c 1 -j 1 -T 30 -h 127.0.0.1 -p 5432 -U postgres mydb
transaction type: ./query.sql
scaling factor: 1
query mode: prepared
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 3
latency average = 11329.355 ms
tps = 0.088266 (including connections establishing)
tps = 0.088284 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
1.778 start transaction;
11324.090 select * from a;
1.228 commit;
-bash-4.2$
END