跟着外包干了3天，学会了PostgreSQL压测的精髓

📢📢📢📣📣📣
作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
CSDN博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理

📣 前言

本文详细的介绍了PostgreSQL压测工具的使用及注意要点

📣 1.压测简介

数据库的基准测试是对数据库的性能指标进行定量的、可复现的、可对比的测试。
基准测试可以理解为针对系统的一种压力测试。但基准测试不关心业务逻辑，更加简单、直接、易于测试，数据可以由工具生成，不要求真实；而压力测试一般考虑业务逻辑(如购物车业务)，要求真实的数据。

压力测试的指标：
lQPS（Queries Per Second）就是每秒的查询数，对数据库而言就是数据库每秒执行
SQL数（含insert、select、update、delete等）。
lTPS（Transactions Per Second）就是每秒的事务数。TPS对于数据库而言就是数据库
秒执行的事务数，以commit成功次数为准。

📣 2.压测工具

1.Sysbench：MySQL、PostgreSQL、Oracle
2.HammerDB：MySQL、Oracle、PostgreSQL、SQL Server、DB2、TimesTen、3.MariaDB、Postgres Plus Advanced Server、Greenplum、Redis、Amazon Aurora、Redshift
4.BenchmarkSQL：PostgreSQL、MySQL、Oracle、SQL Server、DB2
5.Swingbench：Oracle
6.mysqlslap：MySQL
7.tpcc-mysql：MySQL
8.Mydbtest:MySQL
9.pgbench:PostgreSQL

📣 3.sysbench

https://github.com/akopytov/sysbench
https://wiki.gentoo.org/wiki/Sysbench
https://launchpad.net/sysbench

✨ 3.1 sysbench安装

yum install sudo
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench --version
sysbench --help

pgsql options:
  --pgsql-host=STRING     PostgreSQL server host [localhost]
  --pgsql-port=N          PostgreSQL server port [5432]
  --pgsql-user=STRING     PostgreSQL user [sbtest]
  --pgsql-password=STRING PostgreSQL password []
  --pgsql-db=STRING       PostgreSQL database name [sbtest]

✨ 3.2 压测PG

① oltp_common.lua 脚本是提供给其他脚本如oltp_read_only.lua调用的，是基本的一系列函数。
② bulk_insert.lua批量写入操作
③ oltp_delete.lua写入和删除并行操作
④ oltp_insert.lua纯写入操作
⑤ oltp_point_select.lua只读操作，条件为唯一索引列
⑥ oltp_read_only.lua只读操作，包含聚合，去重等操作
⑦ oltp_read_write.lua读写混合操作，最常用的脚本。在一个事务中，默认比例是：select:update_key:update_non_key:delete:insert=14:1:1:1:1。这也是为什么，我们测试出来的TPS和QPS的比例，大概在1:18~20左右。相当于说，一个事务中，有18个读写操作。
⑧ oltp_update_index.lua更新操作，通过主键进行更新
⑨ oltp_update_non_index.lua更新操作，不通过索引列
⑩ oltp_write_only.lua纯写操作，常用脚本，包括insert update delete
⑪ select_random_points.lua随机集合只读操作，常用脚本，聚集索引列的selete in操作
⑫ select_random_ranges.lua随机范围只读操作，常用脚本，聚集索引列selete between操作

✨ 3.3 测试准备

psql -U postgres -h 192.168.3.14 -p 5432 -d postgres
create database sbtest;

– 修改max_connections参数
show max_connections ;
alter system set max_connections=10000;
后续重启数据库生效

✨ 3.4 测试案例

生成10张表，每张表10万的数据量，测试时间1分钟

--批量写入
sysbench /usr/share/sysbench/oltp_common.lua --db-driver=pgsql \
--pgsql-host=192.168.3.14 --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-password=jeames --pgsql-db=sbtest \
--table-size=100000 --tables=10 --threads=100 \
--events=999999999 --time=60 prepare

若出现以下报错，解决办法是yum install -y libicu libicu-devel
FATAL: Connection to database failed: SCRAM authentication requires libpq version 10 or above
官网下载postgresql12-libs-12.8-1PGDG.rhel7.x86_64.rpm安装即可

--分别测试20、50、100、200、500、800线程数
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=192.168.3.14 --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-password=jeames --pgsql-db=sbtest \
--table-size=100000 --tables=10 --threads=20 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /tmp/sysbench_log/20.log

ps -ef|grep sbtest | wc -l

select datid,datname,pid,usesysid,
usename,application_name,
client_addr,client_port,state,query 
from pg_stat_activity;

📣 4.pgbench

pgbench是一个用于在PostgreSQL数据库中运行基准测试的简单程序。pgbench在多个并发的数据库会话中反复运行一系列相同的SQL命令，并计算事务执行的平均速率（每秒执行的事务个数）。 pgbench默认测试的是一种基于TPC-B的松散的测试，即一个事务中包括5个SELECT,UPDATE和INSERT语句。同时允许基于开发者自己书写的事务脚本文件进行其他场景的测试。

✨ 4.1 初始化数据

psql -U postgres -h 192.168.3.14 -p 5432 -d postgres
create database testdb;

说明；主要用到两个参数，-i：初始化模式，-s 插入的倍数，默认是1，即插入100000条；也就是执行多少次generate_series(1,100000)。
pgbench -i -s 100 -U postgres testdb
pgbench -i -s 100 -h 127.0.0.1 -p 5432 -U postgres testdb

✨ 4.2 压测过程

模拟80个用户，64个线程多并发，每10秒显示一次进度报告，运行60秒
pgbench -n -T 60 -P 10 -c 80 -j 64 -U postgres testdb
select count(*) from pg_stat_activity where datname=‘testdb’;