PostgreSQL 数据库压力测试指南

一、为什么需要压力测试？

数据库需要进行压力测试的原因主要包括以下几个方面：

1. 性能评估：通过压力测试，可以了解数据库在高负载情况下的性能表现，包括响应时间、吞吐量和资源利用率等。这有助于确定系统的性能瓶颈。

2. 稳定性验证：压力测试可以帮助识别数据库在长时间高负载运行时是否会出现崩溃、死锁或其他异常情况，从而确保系统的稳定性和可靠性。

3. 容量规划：通过模拟实际使用场景中的最大负载，压力测试可以帮助确定数据库所需的硬件和软件资源配置，以满足未来增长的需求。

4. 优化指导：测试结果可以为数据库的优化提供依据，例如索引优化、查询优化、缓存策略调整等，从而提升整体性能。

5. 故障预防：压力测试可以暴露潜在的系统缺陷和漏洞，使得开发团队能够提前采取措施进行修复和改进，避免在生产环境中出现问题。

6. 验证扩展能力：对于分布式数据库系统，压力测试可以验证其扩展能力，确保在增加节点或分片后，系统仍能保持良好的性能和一致性。

二、PostgreSQL 压测工具介绍

1、sysbench

Sysbench 是一个广泛使用的开源多线程基准测试工具，主要用于评估系统性能。它可以用来测试 CPU、内存、磁盘 I/O 和数据库（如 MySQL）的性能。以下是关于 Sysbench 的一些关键点：

1. CPU 性能测试：通过计算素数等操作来评估 CPU 的计算能力。
2. 内存性能测试：测试内存的读写速度和带宽。
3. 磁盘 I/O 性能测试：模拟不同类型的磁盘操作，如顺序读写和随机读写，以评估磁盘的 I/O 性能。
4. 数据库性能测试：特别是针对 MySQL、PGSQL 数据库，可以进行 OLTP（在线事务处理）基准测试。

2、pgbench

pgbench 是 PostgreSQL 自带的一个基准测试工具，用于对 PostgreSQL 数据库进行压力测试和性能评估。它模拟了多用户并发执行 SQL 操作的场景，帮助数据库管理员和开发人员了解数据库在不同负载下的表现。以下是关于 pgbench 的一些关键点：

1. 初始化数据库：创建测试所需的表和数据。
2. 执行基准测试：模拟多个客户端并发执行事务。
3. 生成报告：输出测试结果，包括每秒事务数（TPS）、响应时间等指标。

三、PostgreSQL 压测案例

1.1、sysbench

官方文档

https://github.com/akopytov/sysbench

https://wiki.gentoo.org/wiki/Sysbench

https://launchpad.net/sysbench

1.1.1、安装

git clone https://github.com/akopytov/sysbenchsudo apt -y install make automake libtool pkg-config libaio-dev# For PostgreSQL support
sudo apt -y install libpq-devcd sysbench/./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure --with-pgsql 
make -j
sudo make install

遇到报错1，sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory

解决方案，

(base) sam@sam-PC:/data/home/sam/TimeEcho/MyWorkSpace/sysbench$ find / -name libmysqlclient.so.20
/home/sam/anaconda3/lib/libmysqlclient.so.20
/home/sam/anaconda3/pkgs/mysql-5.7.24-h721c034_2/lib/libmysqlclient.so.20
/home/sam/anaconda3/envs/myenv3.8/lib/libmysqlclient.so.20-- 软连
sudo ln -s /home/sam/anaconda3/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20
-- 接着在 /etc/ld.so.cnf 中加入 /usr/lib64 这一行
sudo vim /etc/ld.so.conf
-- 刷新
sudo ldconfig 

遇到报错2，sysbench: error while loading shared libraries: libssl.so.3: cannot open shared object file: No such file or directory

sysbench: error while loading shared libraries: libssl.so.3: cannot open shared object file: No such file or directory

解决方案，

(base) sam@sam-PC:/data/home/sam/TimeEcho/MyWorkSpace/sysbench$ find / -name libssl.so.3
/home/sam/anaconda3/lib/libssl.so.3
/home/sam/anaconda3/pkgs/openssl-3.0.14-h5eee18b_0/lib/libssl.so.3
/home/sam/anaconda3/envs/myenv3.8/lib/libssl.so.3
/home/sam/anaconda3/envs/myenv3.10/lib/libssl.so.3-- 软连
sudo ln -s /home/sam/anaconda3/lib/libssl.so.3 /usr/lib64/libssl.so.3
-- 刷新
sudo ldconfig 

遇到报错3，sysbench: error while loading shared libraries: libcrypto.so.3: cannot open shared object file: No such file or directory

sysbench: error while loading shared libraries: libcrypto.so.3: cannot open shared object file: No such file or directory

解决方案，

(base) sam@sam-PC:/data/home/sam/TimeEcho/MyWorkSpace/sysbench$  find / -name libcrypto.so.3
/home/sam/anaconda3/lib/libcrypto.so.3
/home/sam/anaconda3/pkgs/openssl-3.0.14-h5eee18b_0/lib/libcrypto.so.3
/home/sam/anaconda3/envs/myenv3.8/lib/libcrypto.so.3
/home/sam/anaconda3/envs/myenv3.10/lib/libcrypto.so.3-- 软连
sudo ln -s /home/sam/anaconda3/lib/libcrypto.so.3 /usr/lib64/libcrypto.so.3
-- 刷新
sudo ldconfig 

验证安装成功，

1.1.2、压测脚本

(base) sam@sam-PC:/usr/local/share$ ls
ca-certificates  fonts  icons  man  sysbench  themes
(base) sam@sam-PC:/usr/local/share$ cd sysbench/
(base) sam@sam-PC:/usr/local/share/sysbench$ ls
bulk_insert.lua  oltp_delete.lua  oltp_point_select.lua  oltp_read_write.lua    oltp_update_non_index.lua  select_random_points.lua  tests
oltp_common.lua  oltp_insert.lua  oltp_read_only.lua     oltp_update_index.lua  oltp_write_only.lua        select_random_ranges.lua
(base) sam@sam-PC:/usr/local/share/sysbench$ 

脚本介绍如下：

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操作

1.1.3、测试准备

-- psql -U postgres -h 主机IP 5 -p 5432 -d postgres
-- create database test_db;–- 修改max_connections参数
show max_connections ;
alter system set max_connections=10000;-- 后续重启数据库生效

1.1.4、测试案例

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

--批量写入
sysbench /usr/local/share/sysbench/oltp_common.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-password=1234567890  --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=100 \
--events=999999999 --time=60 prepare

分别测试 20、50、100、200、500、800线程数，

sysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-password=1234567890 --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=20 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /home/sam/TimeEcho/MyWorkSpace/tmp/sysbench_log/20.logsysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=50 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /home/sam/TimeEcho/MyWorkSpace/tmp/sysbench_log/50.logsysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=100 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /home/sam/TimeEcho/MyWorkSpace/tmp/sysbench_log/100.logsysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=200 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /home/sam/TimeEcho/MyWorkSpace/tmp/sysbench_log/200.logsysbench /usr/local/share/sysbench/oltp_read_write.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=800 \
--events=999999999 --time=60 --report-interval=10 \
--db-ps-mode=disable --forced-shutdown=1 run > /home/sam/TimeEcho/MyWorkSpace/tmp/sysbench_log/800.log

线程数 20 测试日志如下：

(base) sam@sam-PC:~/TimeEcho/MyWorkSpace/tmp/sysbench_log$ cat 20.log 
sysbench 1.1.0-de18a03 (using bundled LuaJIT 2.1.0-beta3)Running the test with following options:
Number of threads: 20
Report intermediate results every 10 second(s)
Initializing random number generator from current timeForcing shutdown in 61 secondsInitializing worker threads...Threads started![ 10s ] thds: 20 tps: 54.09 qps: 1106.07 (r/w/o: 778.14/217.75/110.18) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 48.38 qps: 963.01 (r/w/o: 673.46/192.80/96.75) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 47.13 qps: 940.84 (r/w/o: 657.28/189.41/94.15) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 46.60 qps: 936.77 (r/w/o: 658.28/185.19/93.30) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 46.70 qps: 930.22 (r/w/o: 650.31/186.60/93.30) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 48.30 qps: 961.50 (r/w/o: 671.00/194.00/96.50) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00
SQL statistics:queries performed:read:                            41048write:                           11728other:                           5864total:                           58640transactions:                        2932   (48.43 per sec.)queries:                             58640  (968.68 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)Throughput:events/s (eps):                      48.4340time elapsed:                        60.5360stotal number of events:              2932Latency (ms):min:                                   73.45avg:                                  412.19max:                                 1564.7495th percentile:                      669.89sum:                              1208555.33Threads fairness:events (avg/stddev):           146.6000/10.27execution time (avg/stddev):   60.4278/0.09

测试结果解析，

每10秒钟的统计数据如下
[ 10s ] thds: 20 tps: 54.09 qps: 1106.07 (r/w/o: 778.14/217.75/110.18) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 20 tps: 48.38 qps: 963.01 (r/w/o: 673.46/192.80/96.75) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 20 tps: 47.13 qps: 940.84 (r/w/o: 657.28/189.41/94.15) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 20 tps: 46.60 qps: 936.77 (r/w/o: 658.28/185.19/93.30) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 20 tps: 46.70 qps: 930.22 (r/w/o: 650.31/186.60/93.30) lat (ms,95%): 657.93 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 20 tps: 48.30 qps: 961.50 (r/w/o: 671.00/194.00/96.50) lat (ms,95%): 669.89 err/s: 0.00 reconn/s: 0.00thds: 线程数
tps: 每秒事务数
qps: 每秒查询数
r/w/o: 每秒读/写/其他查询数
lat (ms,95%): 95% 的延迟时间（毫秒）
err/s: 每秒错误数
reconn/s: 每秒重连数

SQL 统计信息
SQL statistics:queries performed:read:                            41048write:                           11728other:                           5864total:                           58640transactions:                        2932   (48.43 per sec.)queries:                             58640  (968.68 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)queries performed: 执行的查询数，包括读、写和其他查询。
transactions: 总事务数及每秒事务数。
queries: 总查询数及每秒查询数。
ignored errors: 忽略的错误数及每秒忽略错误数。
reconnects: 重连次数及每秒重连次数。    

吞吐量
Throughput:events/s (eps):                      48.4340time elapsed:                        60.5360stotal number of events:              2932events/s (eps): 每秒事件数。
time elapsed: 测试总耗时。
total number of events: 总事件数。    

延迟
Latency (ms):min:                                   73.45avg:                                  412.19max:                                 1564.7495th percentile:                      669.89sum:                              1208555.33min: 最小延迟时间（毫秒）。
avg: 平均延迟时间（毫秒）。
max: 最大延迟时间（毫秒）。
95th percentile: 95% 的延迟时间（毫秒）。
sum: 延迟时间总和（毫秒）。         

线程公平性
Threads fairness:events (avg/stddev):           146.6000/10.27execution time (avg/stddev):   60.4278/0.09events (avg/stddev): 每个线程的平均事件数及标准差。
execution time (avg/stddev): 每个线程的平均执行时间及标准差。    

压测综合总结，

• 在60秒内，共执行了2932个事务，每秒约48.43个事务。

• 查询总数为58640，每秒约968.68个查询。

• 平均延迟时间为412.19毫秒，95%的延迟时间在669.89毫秒以内。

• 没有出现错误或重连情况。

• 每个线程的平均事件数为146.6，标准差为10.27，表明线程之间的负载较为均衡。

-- 监控过程状态
select datid,datname,pid,usesysid,usename,application_name,client_addr,client_port,state,query 
from pg_stat_activity;-- ps -ef|grep pmms | wc -l

1.1.5、清理数据

sysbench /usr/local/share/sysbench/oltp_common.lua --db-driver=pgsql \
--pgsql-host=主机IP --pgsql-port=5432 \
--pgsql-user=postgres --pgsql-password=0123456789  --pgsql-db=test_db \
--table-size=100000 --tables=10 --threads=1000 \
--events=999999999 --time=60 cleanup

1.2、pgbench

1.2.1、安装 pgbench

sudo apt-get install postgresql-contrib sysstat

1.2.2、初始化数据

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

1.2.3、压测过程

-- 模拟80个用户，64个线程多并发，每10秒显示一次进度报告，运行60秒
pgbench -n -T 60 -P 10 -c 80 -j 64 -h 主机IP -p 5432 -U postgres test_db-- 状态监控
select count(*) from pg_stat_activity where datname='test_db';

(base) sam@sam-PC:~$ pgbench -n -T 60 -P 10 -c 80 -j 64 -h 127.0.0.1 -p 5432 -U postgres test_db
progress: 10.0 s, 185.5 tps, lat 231.286 ms stddev 161.302
progress: 20.0 s, 504.3 tps, lat 159.039 ms stddev 45.080
progress: 30.0 s, 517.3 tps, lat 154.581 ms stddev 36.330
progress: 40.0 s, 478.3 tps, lat 167.215 ms stddev 42.947
progress: 50.0 s, 551.1 tps, lat 145.115 ms stddev 27.300
progress: 60.0 s, 599.8 tps, lat 133.631 ms stddev 26.195
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 80
number of threads: 64
duration: 60 s
number of transactions actually processed: 28443
latency average = 156.186 ms
latency stddev = 58.525 ms
tps = 472.832911 (including connections establishing)
tps = 500.746326 (excluding connections establishing)
(base) sam@sam-PC:~$ 

日志解析

• 在每个10秒的间隔内，报告了事务处理速度（tps）、延迟（latency）和标准差（stddev）。

• 从第10秒到第60秒，tps逐渐增加，延迟逐渐减少，标准差也有所下降。

• 实际处理的事务数：28443

• 平均延迟：156.186 ms

• 延迟标准差：58.525 ms

• 吞吐量（包括连接建立时间）：472.832911 tps

• 吞吐量（不包括连接建立时间）：500.746326 tps

1.2.4、压测结论

吞吐量（TPS）：

吞吐量在测试过程中逐渐增加，从最初的185.5 tps增长到最后的599.8 tps。这表明系统在运行一段时间后，性能得到了提升，可能是因为缓存的热身或者资源的更有效利用。

延迟（Latency）：

延迟在测试过程中逐渐减少，从最初的231.286 ms降到最后的133.631 ms。这与吞吐量的增加相一致，表明系统在处理请求时变得更加高效。

标准差（Stddev）：

标准差在测试过程中显著减少，从最初的161.302降到最后的26.195。这表明系统的性能变得更加稳定，延迟的波动性减少。

平均延迟和标准差：

平均延迟为156.186 ms，标准差为58.525 ms。虽然平均延迟较高，但标准差的降低表明系统在大多数情况下能够保持较为一致的性能。