PostgreSQL压测工具sqlbench

sqlbench是阿里云数据库内核团队的mark wong大大开源的一款TPC-C测试软件，完全遵循TPC-C标准编写，关键它是纯C的，效率非常高，并且在遵循TPC-C的标准同时对请求模型进行优化。
https://github.com/swida/sqlbench

环境介绍：

RHEL 7.5
PG 11.5

环境准备：

PG数据库初始化完成(Port:5435)，创建压测用户(sqlbench)、数据库(sqlbench)。

[postgres@rhel75 ~]$createuser -s sqlbench
[postgres@rhel75 ~]$createdb sqlbench

sqlbench安装，通过git进行安装，需要连接外网，可下载后上传的服务器进行解压：
--提前安装相关软件
[root@rhel75 ~]# yum -y install automake.noarch autoconf-2.69-11.el7.noarch

[root@rhel75 soft]# git clone https://github.com/swida/sqlbench  
Cloning into 'sqlbench'...
remote: Enumerating objects: 62, done.
remote: Counting objects: 100% (62/62), done.
remote: Compressing objects: 100% (40/40), done.
remote: Total 916 (delta 23), reused 45 (delta 22), pack-reused 854
Receiving objects: 100% (916/916), 414.13 KiB | 9.00 KiB/s, done.
Resolving deltas: 100% (567/567), done. 

[root@rhel75 sqlbench]# autoreconf -if
configure.ac:13: installing './compile'
configure.ac:6: installing './install-sh'
configure.ac:6: installing './missing'
src/core/Makefile.am: installing './depcomp'

查看支持的配置选项,如--with-postgresql=yes会导致Command pg_config not found问题
[root@rhel75 sqlbench]# ./configure --help
[root@rhel75 sqlbench]# ./configure --with-postgresql=/usr/local/pgsql
checking for a BSD-compatible install... /usr/bin/install -c
checking whether build environment is sane... yes
checking for a thread-safe mkdir -p... /usr/bin/mkdir -p
checking for gawk... gawk
checking whether make sets $(MAKE)... yes
......
config.status: creating config.h
config.status: executing depfiles commands

make && make install
注：如不执行make install，可以进入以下路径执行,本次实验环境通过make install进行测试
[postgres@rhel75 core]$pwd
/soft/sqlbench/src/core

sqlbench支持命令参数
[postgres@rhel75 ~]$sqlbench --help
usage: sqlbench -t <dbms> -c # -w # -l # [-r #] [-s #] [-e #] [-o p] [-z]
-t <dbms>
        available: postgresql
        for postgresql: --dbname=<dbname> --host=<host> --port=<port> --user=<user>
-c #
        number of database connections
-w #
        warehouse cardinality, default 1
-l #
        the duration of the run in seconds

-r #
        the duration of ramp up in seconds
-s #
        lower warehouse id, default 1
-e #
        upper warehouse id, default <w>
-o p
        output directory of log files, default current directory
-z
        perform database integrity check
......
--sqlapi
        run test using sql interface, available:
        simple      default, just send sql statement to database
        extended    use extended (prepare/bind/execute) protocol, better than simple
        storeproc   use store procedure

初始化测试数据
/soft/sqlbench/src/scripts/pgsql
--配置数据库及路径
[postgres@rhel75 pgsql]$export DBT2DBNAME=sqlbench
[postgres@rhel75 pgsql]$export DBT2TSDIR=/postgres/data5435
[postgres@rhel75 pgsql]$sh create-tables 
[postgres@rhel75 pgsql]$datagen -t postgresql --dbname=sqlbench --user=sqlbench -w 10 -j 4
[postgres@rhel75 pgsql]$sh create-indexes 
分析表
psql -U sqlbench -d sqlbench <<EOF
VACUUM analyze customer;  
VACUUM analyze district;  
vacuum analyze history;  
vacuum analyze item;  
vacuum analyze new_order;  
vacuum analyze order_line;  
vacuum analyze orders;  
vacuum analyze stock;  
vacuum analyze warehouse;  
checkpoint;  
EOF
--配置日志地址
[postgres@rhel75 pgsql]$export OUTPUT_DIR=~/

sqlbench=# \d
           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | customer   | table | postgres
 public | district   | table | postgres
 public | history    | table | postgres
 public | item       | table | postgres
 public | new_order  | table | postgres
 public | order_line | table | postgres
 public | orders     | table | postgres
 public | stock      | table | postgres
 public | warehouse  | table | postgres
(9 rows)

sqlbench=# \di
                   List of relations
 Schema |     Name      | Type  |  Owner   |   Table    
--------+---------------+-------+----------+------------
 public | i_customer    | index | postgres | customer
 public | i_orders      | index | postgres | orders
 public | pk_customer   | index | postgres | customer
 public | pk_district   | index | postgres | district
 public | pk_item       | index | postgres | item
 public | pk_new_order  | index | postgres | new_order
 public | pk_order_line | index | postgres | order_line
 public | pk_orders     | index | postgres | orders
 public | pk_stock      | index | postgres | stock
 public | pk_warehouse  | index | postgres | warehouse
(10 rows)

sqlbench=# select pg_database_size('sqlbench')/1024/1024||'mb';
 ?column? 
----------
 1022mb
(1 row)

压测：
[postgres@rhel75 ~]$sqlbench -t postgresql --dbname=sqlbench --user=sqlbench --no-thinktime -w10 --sleep 10 -c10 -l120 -r5 --sqlapi extended  
use extended sql interface
Output directory of log files: current directory

database connection:
connections = 10
.....
opening 10 connection(s) to database...
10 DB worker threads have started
driver is starting to ramp up at time 2019-10-30 13:53:02
driver will ramp up in 5 seconds
will stop test at time 2019-10-30 13:55:07
terminals started...
steady status started...
driver is exiting normally

[postgres@rhel75 ~]$post_process -l mix.log 
                         Response Time (s)
 Transaction      %    Average :    90th %        Total        Rollbacks      %
------------  -----  ---------------------  -----------  ---------------  -----
    Delivery   3.93      0.136 :     0.152         1759                0   0.00
   New Order  45.13      0.035 :     0.049        20203             1088   5.39
Order Status   4.05      0.005 :     0.007         1815                0   0.00
     Payment  42.90      0.011 :     0.018        19203                0   0.00
 Stock Level   3.99      0.013 :     0.021         1785                0   0.00
------------  -----  ---------------------  -----------  ---------------  -----

10101.50 new-order transactions per minute (NOTPM)
2.0 minute duration
0 total unknown errors
5 second(s) ramping up

参考文档：https://github.com/digoal/blog/blob/master/201805/20180530_01.md