postgresql|数据库|数据库测试工具pgbench之使用

前言：

数据库是项目中的重要组件，也是一个基础的重要组件，其地位说是第一我想应该是没有什么太多问题的。

那么，数据库的设计这些方面是不用多说的，关键的第一步，主要是涉及数据库的部署方式，运行方式，表的逻辑设计，合理的字段，合理的索引，必须的角色，安全方面的考虑，函数，视图，触发器，物化视图等等方面，也就是说项目中哪些数据需要存放到数据库，相关数据怎么存放到数据库是数据库设计阶段需要解决的问题。

数据库设计阶段完成后，进入数据库的运营阶段，在运营前，我们需要明确数据库应该达到的状态，简单来说，就是数据库能够具有三高（三高通常指高可用，高性能，高并发），而高可用是比较容易实现的，一般是通过搭建集群的方式来实现高可用（也就是HA），也比较容易的就可以明确是否达到了高可用（毕竟，集群搭建完毕了，主从切换几次就知道是否确实是高可用了），但高性能和高并发则需要反复的测试并结合实际上线后的运营来判断了，如果没有测试以及相关的测试报告，那么是无法确认数据库是否具有高性能和高并发的。

因此，数据库的测试工作是一个比较关键的但常常被人为忽视的工作。对于postgresql而言，测试并判断一个数据库是否符合我们的预期，可以使用的工具是比较多的，比如pg_profile ，pg_reset , pg_stat 这样的或内或外的插件来收集监控数据库，但这些工具生成的报告内容是比较多的并且生成效率不高，也不是特别的直观。

pgbench这个工具可以解决很大一部分痛点，该工具具有数据库测试流程简单，直接，高效，易于使用，关键是无需特意的安装部署，postgresql数据库自带的小工具。 给人的感觉就是pgbench等于web 测试里的ab工具，非常方便使用。

pgbench可以用于测试PostgreSQL的性能和并发能力。它模拟了一个简单的银行转账场景，可以通过设置参数来模拟不同的负载。pgbench支持多线程并发测试，可以测试事务吞吐量、延迟、并发连接数等指标。pgbench使用简单，但功能有限，只能进行基本的负载测试。

下面对pgbench的使用做一个简单的介绍

一，

pgbench在哪里？

pgbench一般是随数据库安装的内置命令

特别注意，此命令和其它命令基本一样，需要使用postgres用户来执行，root用户不可使用

[root@node1 ~]# whereis pgbench
pgbench: /usr/local/pgsql/bin/pgbench

二，

测试用的数据库介绍

操作系统是centos7，VMware虚拟机两台，内存4G，CPU4核

数据库使用的是postgresql-12.4版本，全部都是默认状态，也就是说没有进行优化，这里的优化指的是数据库的运行参数，操作系统内核参数优化，数据库是简单的主从复制集群。

主数据库  IP 192.168.123.11

从数据库 IP 192.168.123.12

三，

测试工作的数据准备

计划生成一个2000W条的大表，然后对该表做查询，写入的测试，从而得出数据库的性能和并发指标，下面是大表的创建代码：

随机数函数：

create or replace function gen_id(  a date,  b date  
)   
returns text as $$  
select lpad((random()*99)::int::text, 3, '0') ||   lpad((random()*99)::int::text, 3, '0') ||   lpad((random()*99)::int::text, 3, '0') ||   to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||   lpad((random()*99)::int::text, 3, '0') ||   random()::int ||   (case when random()*10 >9 then 'xy' else (random()*9)::int::text end ) ;  
$$ language sql strict;

创建测试表结构：

CREATE SEQUENCE test START 1;
create table if not exists testpg ("id" int8 not null DEFAULT nextval('test'::regclass),CONSTRAINT "user_vendorcode_pkey" PRIMARY KEY ("id"),"suijishuzi" VARCHAR ( 255 ) COLLATE "pg_catalog"."default"
);

插入2000W条数据：

根据机器性能，大概5到10分钟左右

insert into testpg SELECT generate_series(1,20000000) as xm, gen_id('1949-01-01', '2023-10-16') as num;

四，

查看测试表

五，

pgbench初始化

注意，在初始化前，需要创建pgbench数据库，如何创建就不在这废话了

pgbench -U postgres -i pgbench

创建完毕后，将会看到pgbench数据库下有几个表，表的功能目前还不太清楚：

postgres=# \c pgbench 
You are now connected to database "pgbench" as user "postgres".
pgbench=# \dtList of relationsSchema |       Name       | Type  |  Owner   
--------+------------------+-------+----------public | pgbench_accounts | table | postgrespublic | pgbench_branches | table | postgrespublic | pgbench_history  | table | postgrespublic | pgbench_tellers  | table | postgres
(4 rows)

六，

pgbench的几种模式

pgbench有内置模式和外置模式两种，内置就是使用pgbench刚刚创建的那四个表里的内容直接测试，一般是基准测试使用（基准测试指的是基础性的，准确度方面的测试），外置模式是使用自定义的SQL语句进行测试，一般是压力性能测试使用。

内置模式：

内建模式具体的细化有如下三种，根据名字大概猜测第一个是综合性能方面的简单测试，第二个是写入性能的简单测试，第三个是读性能的简单测试，都是使用pgbench自带的那四个表和它自己的逻辑来进行测试的。

[postgres@node1 ~]$ pgbench -b list
Available builtin scripts:tpcb-likesimple-updateselect-only

 第一种小模式（tpcb-like）：

 pgbench  -U postgres -T 10 -c 10 -h 192.168.123.11 -d pgbench   > 1111.txt  2>&1 >>1111.txt

截取输出的部分结果，可以看到pgbench有update，insert，和select这些动作，都是在上面四张表内完成，该过程不可控，也基本不是非常准确的测试 

client 5 executing script "<builtin: TPC-B (sort of)>"
client 5 executing \set aid
client 5 executing \set bid
client 5 executing \set tid
client 5 executing \set delta
client 5 sending BEGIN;
client 5 receiving
client 0 receiving
client 0 sending END;
client 0 receiving
client 5 receiving
client 5 sending UPDATE pgbench_accounts SET abalance = abalance + -1444 WHERE aid = 99838;
client 5 receiving
client 9 receiving
client 9 sending UPDATE pgbench_tellers SET tbalance = tbalance + -1294 WHERE tid = 6;
client 9 receiving
client 0 receiving
client 5 receiving
client 5 sending SELECT abalance FROM pgbench_accounts WHERE aid = 99838;
client 5 receiving
client 8 receiving
client 8 sending INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (1, 1, 78380, -2573, CURRENT_TIMESTAMP);
client 8 receiving
client 0 executing script "<builtin: TPC-B (sort of)>"
client 0 executing \set aid
client 0 executing \set bid
client 0 executing \set tid
client 0 executing \set delta
client 0 sending BEGIN;
client 0 receiving
client 0 receiving
client 0 sending UPDATE pgbench_accounts SET abalance = abalance + -2452 WHERE aid = 40167;
client 0 receiving
client 5 receiving
client 5 sending UPDATE pgbench_tellers SET tbalance = tbalance + -1444 WHERE tid = 10;
client 5 receiving
client 8 receiving
client 8 sending END;
client 8 receiving
client 5 receiving
client 5 sending UPDATE pgbench_branches SET bbalance = bbalance + -1444 WHERE bid = 1;
client 5 receiving

第二种小模式(select-only)；

pgbench  -U postgres -b select-only  -c 10 -h 192.168.123.11 -d pgbench    > 1111.txt  2>&1 >>1111.txt

第三种小模式（simple-update）

pgbench  -U postgres -b simple-update  -c 10 -h 192.168.123.11 -d pgbench    > 1111.txt  2>&1 >>1111.txt

外置模式：

pgbench -M prepared -v -r -P 1 -f ./ro.sql -c 60 -j 60 -T 120 -D scale=10000 -D range=500000 -Upostgres test -P 5 -h 192.168.123.222 -p 15433

七，

pgbench命令的参数说明：

参数说明：

-r 在基准结束后，报告平均的每个命令的每语句等待时间（从客户端的角度来说是执行时间）。

-j pgbench中的工作者线程数量。在多 CPU 机器上使用多于一个线程会有用。客户端会尽可能均匀地分布到可用的线程上。默认为 1。

-c 模拟的客户端数量，也就是并发数据库会话数量。默认为 1。

-t 每个客户端运行的事务数量。默认为 10。

-T 运行测试这么多秒，而不是为每个客户端运行固定数量的事务。

-D VARNAME=VALUE 传递测试脚本中变量值

define variable for use by custom script 

-v vacuum all four standard tables before tests，一般测试的时候为了去除上次测试的结果影响，需要vacuum一下pgbench的数据库。

报告说明：

transaction type 表明本次测试所使用的测试类型

scaling factor 表明pgbench在初始化时设置的数据量的比例因子

query mode 表明指定的查询模式，包括 simple查询模式(默认)、extended查询模式和prepared 查询模式

number of clients表明指定的客户端连接数

number of threads表明测试时每个客户端的线程数

number of transactions actually processed 测试结束时实际处理的事务数

latency average 测试过程的平均响应时间

tps 单位时间内执行的事务数

未完待续！！！