阅读完需:约 19 分钟
pgbench 是一个简单的给 PostgreSQL 做性能测试的程序。它反复运行同样的 SQL 命令序列,可能是在多个并发数据库会话上头,然后检查平均的事务速度(每秒的事务数 tps)。缺省的时候,pgbench 测试一个(松散的)接近 TPC-B 的情况,每个事务包括五个 SELECT,UPDATE,和 INSERT命令。不过,我们可以很轻松地使用自己的事务脚本文件来实现其它情况。
通常来自pgbench的输出看起来像:
transaction type: 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)
头四行只是报告一些最重要的参数设置。跟着的一行报告完成的事务数和期望完成的事务数(后者只是客户端数乘以事务数);这两个会相等,除非在完成之前运行就失败了。最后两行报告 TPS 速率,分别有计算启动数据库会话时间和不计算启动会话时间的。
pgbench测试库初始化
postgres$ pgbench --help # 和postgres其他命令的使用方式一样,--help获取命令使用方式的简单介绍
postgres$ createdb pgbench # 创建测试库
postgres$ pgbench -i pgbench # 初始化测试库
默认会在测试库中建4张表pgbench_accounts
,pgbench_branches
,pgbench_history
,pgbench_tellers
。当然也可以自己建表,自己写测试脚本,这四张表只是默认的测试脚本会用到。
pgbench在建默认库时 -s
参数设定测设表的大小,默认参数是1 。pgbench_accounts
总行数是10W,-s
后面接具体数值如100,则pgbench_accounts
中的测试数据将达到1千万行
pgbench -i
创建四个表pgbench_accounts
、 pgbench_branches
、pgbench_history
和pgbench_tellers
,销毁任何已存在的同名的表。 如果你有这些名字的表,要非常小心的使用另外一个数据库。
缺省”比例因子”为1,表最初包含这些行:
table # of rows
---------------------------------
pgbench_branches 1
pgbench_tellers 10
pgbench_accounts 100000
pgbench_history 0
你可以(对于大多数目的,可能应该)使用-s(比例因子)选项增加行数。 -F(填充因子)选项也可以用于这点。
一旦你完成了必要的步骤,你可以用一个不包含-i 的命令运行你的benchmark,也就是
pgbench [ options ] dbname
在几乎所有情况下,你都将需要一些选项来做一个有用的测试。 最重要的选项是-c(客户端的数量)、-t(事务的数量)、 -T(时间限制)和-f(声明一个自定义的脚本文件)。
选项
初始化选项
pgbench接受下列的命令行初始化参数:
-i
--initialize
要求调用初始化模式。
-F fillfactor
--fillfactor=fillfactor
用给定的填充因子创建pgbench_accounts、pgbench_tellers 和pgbench_branches表。缺省是100。
-n
--no-vacuum
在初始化之后不执行清理。
-q
--quiet
日志切换到安静模式,每5秒钟只产生一条进度消息。缺省的日志输出是每100000行一条消息, 通常每秒输出多行(特别是在好的硬件上)。
-s scale_factor
--scale=scale_factor
乘以比例因子生成的行数。例如,-s 100将在 pgbench_accounts表中创建10,000,000行。缺省是1。 当比例是20,000或更大时,用于保存计数标识符的字段(aid字段) 将切换到使用更大的整数(bigint),以足够保存计数标识符的范围。
--foreign-keys
在标准表之间创建外键约束。
--index-tablespace=index_tablespace
在指定的表空间中创建索引,而不是在缺省的表空间中。
--tablespace=tablespace
在指定的表空间中创建表,而不是在缺省的表空间中。
--unlogged-tables
创建所有的表为unlogged表,而不是永久表。
基准选项
pgbench接受下列的命令行基准参数:
-c clients
--client=clients
模拟客户端的数量,也就是并发数据库会话的数量。缺省是1。
-C
--connect
为每个事务建立一个新的连接,而不是每客户端会话只执行一次。 这对于测量连接开销是有用的。
-d
--debug
打印调试输出。
-D varname=value
--define=varname=value
定义一个自定义脚本使用的变量(见下文)。允许使用多个-D选项。
-f filename
--file=filename
从filename中读取事务脚本。见下文获取细节。 -N、-S、和-f是互相排斥的。
-j threads
--jobs=threads
pgbench中工作线程的数量。在多CPU的机器上使用多个线程会很有帮助。 客户端的数量必须是线程数量的倍数,因为每个线程都有相同数量的客户端会话管理。 缺省是1。
-l
--log
记录每个事务写入日志文件的时间。见下文获取细节。
-M querymode
--protocol=querymode
提交查询到服务器使用的协议:
simple:使用简单的查询协议。
extended:使用扩展的查询协议。
prepared:使用带有预备语句的扩展查询协议。
缺省是简单的查询协议。(参阅第 49 章获取更多信息。)
-n
--no-vacuum
运行测试时不执行清理。如果你正在运行一个不包含标准表pgbench_accounts、 pgbench_branches、pgbench_history、和 pgbench_tellers的自定义测试,那么该选项是必需的。
-N
--skip-some-updates
不要更新pgbench_tellers和pgbench_branches。 这将避免争用这些表,但是它使得测试用例更不像TPC-B。
-P sec
--progress=sec
每sec秒显示一次进程报告。该报告包括自运行开始以来的时间, 自最后一次报告以来的tps,和自最后一次报告以来的事务延迟平均和标准偏差。 在节流模式下(-R),延迟是按照事务预约的启动时间计算的, 而不是事务实际开始的时间,因此它也包括平均进度延后时间。
-r
--report-latencies
在benchmark完成后报告每个命令的平均每语句延迟(从客户的角度看的执行时间)。 见下文获取细节。
-R rate
--rate=rate
以指定的速率执行事务,而不是越快运行越好(缺省)。 速率是以事务每秒给出的。如果目标速率超过了最大可能的速率, 那么速率限制将不会影响结果。
该速率是通过沿着泊松分布的规定时间线启动事务定向的。 预计的启动时间表基于客户端的第一次启动向前移动,而不是基于前一个事务的结束。 该方法意味着当事务经过它们的原计划结束时间时,后面的事务有可能再次追赶上来。
当开启了节流时,在运行结束的事务延迟报告是从计划开始时间计算的, 所以它包括每个事务必须等待前一个事务完成的时间。 该等待时间称为进度滞后时间,并且分别报告它的平均值和最大值。 事务延迟对事务实际开始时间,也就是在数据库中执行事务花费的时间, 可以通过报告的延迟减去进度滞后时间计算得到。
高进度滞后时间表明系统不能以选定数量的客户端和进程、指定的速率处理事务。 当平均事务执行时间要比计划的每个事务之间的间隔长时, 那么每个成功的事务将在后面失败,并且测试运行的时间越长, 进度滞后时间将也越长。当发生这种情况时,你将必须降低指定的事务速率。
-s scale_factor
--scale=scale_factor
在pgbench的输出中报告指定的比例因子。在内建的测试中,这不是必需的; 正确的比例因子将通过计数pgbench_branches表中的行数检测到。 不过,在测试自定义benchmark(-f选项)时,比例因子将报告为1,除非使用了该选项。
-S
--select-only
执行只有select的事务,替代类似TPC-B的测试。
-t transactions
--transactions=transactions
每个客户端运行的事务数量。缺省是10。
-T seconds
--time=seconds
运行测试这么多秒,而不是每客户端固定数量的事务。-t 和-T是互相排斥的。
-v
--vacuum-all
在运行测试之前清理四个标准表。既不用-n也不用-v, pgbench将清理pgbench_tellers和pgbench_branches表, 截断pgbench_history表。
--aggregate-interval=seconds
汇总时间间隔的长度(以秒计)。可能只与-l选项一起使用, 日志包含每间隔的总结(事务的数量、最小/最大延迟和可用于方差估计的两个额外字段)。
目前在Windows上不支持这个选项。
--sampling-rate=rate
采样率,在写入数据到日志时使用,以减少生成日志的数量。如果给出了这个选项, 则只记录指定比例的事务。1.0意味着记录所有事务,0.05意味着只记录了5%的事务。
在处理日志文件时记得计算上采样率。例如,计算tps值时,需要乘以相应的数字 (比如,0.01的采样率,将只得到1/100的实际tps)。
公共选项
pgbench接受下列的命令行公共参数:
-h hostname
--host=hostname
数据库服务器的主机名
-p port
--port=port
数据库服务器的端口号
-U login
--username=login
要连接的用户名
-V
--version
打印pgbench的版本并退出。
-?
--help
显示关于pgbench命令行参数的帮助并退出。
什么是在pgbench上实际执行的”事务”?
缺省的事务脚本每事务发出7个命令:
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;
如果你声明了-N,那么不包含步骤4和5。如果你声明了-S, 那么只发出SELECT。
默认的测试脚本介绍
默认的测试脚本可以在官方文档中找到(新的版本中不指定模板就会使用默认模板)
$ cat test.sql
\set nbranches 1 * :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;
脚本说明:
可以看到脚本中的一个事物包含了update,select,insert操作,不同的操作起到不同的测试目的
- (1)UPDATE pgbench_accounts:作为最大的表,起到促发磁盘I/O的作用。
- (2)SELECT abalance:由于上一条UPDATE语句更新一些信息,存在于缓存内用于回应这个查询。
- (3)UPDATE pgbench_tellers:职员的数量比账号的数量要少得多,所以这个表也很小,并且极有可能存在于内存中。
- (4)UPDATE pgbench_branches:作为更小的表,内容被缓存,如果用户的环境是数量较小的数据库和多个客户端时,对其锁操作可能会成为性能的瓶颈。
- (5)INSERT INTO pgbench_history:history表是个附加表,后续并不会进行更新或查询操作,而且也没有任何索引。相对于UPDATE语句,对其的插入操作对磁盘的写入成本也很小。
测试结果说明
postgres$ pgbench -c 15 -t 300 pgbench -r -f test.sql #执行命令
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 15 #-c参数控制并发量
number of threads: 1
number of transactions per client: 300 #每个客户端执行事务的数量
number of transactions actually processed: 4500/4500 #总执行量
tps = 453.309203 (including connections establishing) #tps每秒钟处理的事务数包含网络开销
tps = 457.358998 (excluding connections establishing) #不包含网络开销
statement latencies in milliseconds: #带-r的效果,每个客户端事务具体的执行时间,单位是毫秒
0.005198 \set nbranches 1 * :scale
0.001144 \set ntellers 10 * :scale
0.001088 \set naccounts 100000 * :scale
0.001400 \setrandom aid 1 :naccounts
0.000814 \setrandom bid 1 :nbranches
0.000929 \setrandom tid 1 :ntellers
0.000981 \setrandom delta -5000 5000
0.613757 BEGIN;
1.027969 UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
0.754162 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
14.167980 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
13.587156 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
0.582075 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
1.628262 END;
默认的基准测试给出了一个指标TPS,同样的测试参数,tps的值越高,相对来说服务器的性能越好。上面的测试由于数据量的问题,表的内容全部缓存进了内存,磁盘io对上面的结果影响较小。
注意:
在使用使用docker 安装的 PGSQL中,只需要进入数据库容器内部即可
docker exec -it 容器名 /bin/bash
进入后需要切换到自己数据库的用户名
可以使用 getent passwd 查看当前 linux 下的用户有哪些
创建新的用户 useradd 用户名(新的用户需要与数据用户名一致)
(与数据用户名一致是因为下面测试的语句是用默认参数的,如果不一致,可以使用 -U postgres 来指定用户名,比如现在指定的是 postgres)
初始化构建测试环境
pgbench -i -s 2000 测试数据库名
最后测试数据库
pgbench -c 80 -j 4 -t 2000 -r 测试数据库名
自定义测试环境
在实际的应用中测试可以自己定义测试环境,模拟生产需求。
测试举例
pgbench=# create table pg_test (a1 serial,a2 int,a3 varchar(20),a4 timestamp); #创建测试表
postgres$cat pg_test.sql
pgbench=# insert into pg_test(a2,a3,a4) select (random()*(2*10^5)),substr('abcdefghijklmnopqrstuvwxyz',1, (random()*26)::integer),now();
#每个事务插入一条数据
postgres$pgbench -c 90 -T 10 pgbench -r -f pg_test.sql #90个并发测试每秒插入的数据量
测试结果截取:
number of transactions actually processed: 20196 #10秒钟90并发用户共插入20196条数据,每条数据插入费时42ms,平均每秒插入2000条数据
tps = 1997.514876 (including connections establishing)
tps = 2119.279239 (excluding connections establishing)
statement latencies in milliseconds:
42.217948
pgbench=# select count(*) from pg_test;
count
-------
20196
pgbench在参数调节上的辅助使用
简单举例:work_mem
postgres=# show work_mem ; #数据库当前的work_mem
work_mem
----------
1MB
查询样本:
postgres$cat select.sql
SELECT customerid FROM customers ORDER BY zip; #orders表是一张postgres样例表,样例库全名dellstore2
postgres$pgbench -c 90 -T 5 pgbench -r -f select.sql #多用户并发做单表排序操作单个事务执行的时间可能会很大,但是平均事务执行时间和单个用户的执行时间差距没那么明显。
执行结果截取
number of clients: 90
number of threads: 1
duration: 5 s
number of transactions actually processed: 150
tps = 26.593887 (including connections establishing)
tps = 27.972988 (excluding connections establishing)
statement latencies in milliseconds:
3115.754673 SELECT customerid FROM customers ORDER BY zip;
测试环境相同调节work_mem
参数为2M试试
number of clients: 90
number of threads: 1
duration: 5 s
number of transactions actually processed: 243
tps = 44.553026 (including connections establishing)
tps = 47.027276 (excluding connections establishing)
statement latencies in milliseconds:
1865.636761 SELECT customerid FROM customers ORDER BY zip; #5s内事务执行的总量明显增加一共做了243次单表排序
原因分析,由于排序操作会关系到work_mem
,排序操作能全在缓存中进行当然速度会明显加快,查看执行计划
postgres=# explain analyze SELECT customerid FROM customers ORDER BY zip;
QUERY PLAN
--------------------------------------------------------------------------------------------
Sort (cost=2116.77..2166.77 rows=20000 width=8) (actual time=42.536..46.117 rows=20000 loo
ps=1)
Sort Key: zip
Sort Method: external sort Disk: 352kB
-> Seq Scan on customers (cost=0.00..688.00 rows=20000 width=8) (actual time=0.013..8.9
42 rows=20000 loops=1)
Total runtime: 48.858 ms
由上面的执行计划可以看出在work_mem
大小为1M的时候排序一共需要1.352M空间做排序,所以加大work_mem
参数排序速度明显增加。
这只是个简单的例子,work_mem
的大小调节还有很多其他方面要考虑的,比如在高并发的情况下,需要为每个用户分配同样大小的排序空间,会占用大量的内存空间。参数调节在任何时候保持一个均衡才是应该考虑的。
官方:http://www.postgres.cn/docs/9.4/pgbench.html
高并发小事务压测
创建1024个表,使用merge insert写入200亿数据。
连接到主节点执行如下:
1、建表
do language plpgsql $$
declare
begin
execute 'drop table if exists test';
execute 'create table test(id int8 primary key, info text, crt_time timestamp)';
for i in 0..1023 loop
execute format('drop table if exists test%s', i);
execute format('create table test%s (like test including all)', i);
end loop;
end;
$$;
2、创建动态数据写入函数
create or replace function dyn_pre(int8) returns void as $$
declare
suffix int8 := mod($1,1024);
begin
execute format('execute p%s(%s)', suffix, $1);
exception when others then
execute format('prepare p%s(int8) as insert into test%s values($1, md5(random()::text), now()) on conflict(id) do update set info=excluded.info,crt_time=excluded.crt_time', suffix, suffix);
execute format('execute p%s(%s)', suffix, $1);
end;
$$ language plpgsql strict;
3、创建压测脚本,使用merge insert写入200亿数据。
vi test.sql
\set id random(1,20000000000)
select dyn_pre(:id);
4、写入压测
pgbench -M prepared -n -r -P 1 -f ./test.sql -h 127.0.0.1 -p 1921 -U postgres postgres -c 56 -j 56 -T 1200000
如果是主从的数据库可以查看主从延迟
select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(),write_lsn)) delay_wal_size,* from pg_stat_replication ;
高并发大事务压测
创建1024个表,大批量INSERT。
连接到主节点执行如下:
1、建表
do language plpgsql $$
declare
begin
execute 'drop table if exists test';
execute 'create table test(id int8, info text, crt_time timestamp)';
for i in 0..1023 loop
execute format('drop table if exists test%s', i);
execute format('create table test%s (like test including all)', i);
end loop;
end;
$$;
2、创建动态数据写入函数
create or replace function dyn_pre(int8) returns void as $$
declare
suffix int8 := mod($1,1024);
begin
execute format('execute p%s(%s)', suffix, $1);
exception when others then
execute format('prepare p%s(int8) as insert into test%s select generate_series(1,10000), %L, now()', suffix, suffix, 'postgres: wal receiver process streaming 4C/6BC37028');
execute format('execute p%s(%s)', suffix, $1);
end;
$$ language plpgsql strict;
3、创建压测脚本,批量写入,每批写入1万条。
vi test.sql
\set id random(1,20000000000)
select dyn_pre(:id);
4、写入压测
pgbench -M prepared -n -r -P 1 -f ./test.sql -h 127.0.0.1 -p 1921 -U postgres postgres -c 56 -j 56 -T 1200000