如何为获得最佳性能而基准测试 PostgreSQL

随着PostgreSQL的普及，数据库管理员（DBA）和开发人员经常需要评估其性能，以确保他们的应用在不同工作负载下运行高效。性能基准测试是一个关键过程，用于衡量PostgreSQL处理不同负载的能力，帮助识别瓶颈和优化领域。本文探讨了工具、指标和测试场景，帮助您像专家一样对PostgreSQL进行基准测试。

为什么要对PostgreSQL进行基准测试？

基准测试允许您：

1. 衡量数据库在特定工作负载下的吞吐量和延迟。
2. 识别硬件或配置瓶颈。
3. 比较优化的影响，如索引更改或查询重写。
4. 模拟高并发用户活动或大数据写入等真实场景。

关键要跟踪的指标

在对PostgreSQL进行基准测试时，关注这些指标：

• TPS（每秒事务数）：衡量数据库每秒完成的事务数。
• IOPS（每秒输入/输出操作数）：跟踪磁盘活动。
• 延迟：衡量执行查询所需的时间，影响用户体验。
• 资源利用率：跟踪基准测试期间的CPU、内存和磁盘使用情况。

PostgreSQL基准测试工具

1. pgbench

pgbench是什么？ 

pgbench是PostgreSQL内置的基准测试工具。它模拟并发客户端执行事务并测量数据库的性能。

安装

它与PostgreSQL安装捆绑在一起。要验证，请运行：

bash
pgbench --version

入门

1. 初始化基准数据库：

bash
pgbench -i -s 50 mydb

这里，-s设置了比例因子，确定数据集的大小。

2. 运行简单的基准测试：

bash
pgbench -c 10 -j 2 -T 60 mydb

• -c 10：客户端连接数。
• -j 2：线程数。
• -T 60：基准测试持续时间（秒）。

示例输出：

transaction type: TPC-B (sort of)
scaling factor: 50
number of clients: 10
number of threads: 2
duration: 60 s
tps = 1420.123 (excluding connections establishing)

2. Sysbench

为什么使用Sysbench？ 

Sysbench是一个多才多艺的数据库和系统基准测试工具。相比于pgbench，它为自定义工作负载提供了更多灵活性。

安装

使用以下命令安装Sysbench：

bash
sudo apt-get install sysbench

入门

1. 准备基准测试：

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --tables=10 --table-size=1000000 \
  oltp_read_write prepare

2. 运行基准测试：

bash
sysbench --db-driver=pgsql --pgsql-db=mydb \
  --pgsql-user=postgres --threads=4 \
  --time=60 oltp_read_write run

3. pg_stat_statements

pg_stat_statements是什么？

一个跟踪查询性能和执行统计的PostgreSQL扩展。虽然它不会模拟工作负载，但有助于分析基准测试期间的慢查询。

设置

1. 在postgresql.conf中启用该扩展：

shared_preload_libraries = 'pg_stat_statements'

2. 重新加载配置并创建扩展：

CREATE EXTENSION pg_stat_statements;

用法

运行以下查询以识别运行时间较长的语句：

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

基准测试场景

以下是三种场景（读取密集型、写入密集型和混合工作负载）的基准测试结果的可视化表示。每秒事务数（TPS）图表展示了PostgreSQL高效处理并发事务的能力，而延迟图表说明了查询执行所需的时间（以毫秒为单位）。

PostgreSQL每秒事务数（TPS）

PostgreSQL查询延迟

工作负载类型

1. 读取密集型工作负载

目标：测试数据库在高读取活动下的性能。

设置：使用默认的只读事务来执行pgbench：

bash
pgbench -c 50 -T 120 -S mydb

• -S：仅执行SELECT查询。
• -c 50：模拟50个并发客户端。

2. 写入密集型工作负载

目标：衡量数据库在频繁插入或更新时的性能。

设置： 修改基准测试以包括写入操作：

bash
pgbench -c 20 -j 4 -T 120 -N mydb

• -N：执行非SELECT查询。

3. 混合读写工作负载

目标：模拟混合读写的真实工作负载。

设置： 使用平衡的配置：

bash
pgbench -c 30 -j 4 -T 180 mydb

优化 PostgreSQL 以获得更好的基准测试结果

调整内存设置

调整这些参数在 postgresql.conf 中：

shared_buffers = 25% of system memory
work_mem = 4MB
maintenance_work_mem = 64MB

启用并行查询执行

调整这些参数在 postgresql.conf 中：

max_parallel_workers_per_gather = 4

优化磁盘 I/O

使用 SSD 用于 WAL 文件并调整这些设置：

wal_buffers = 16MB
synchronous_commit = off

示例结果和解释

场景：50 个并发客户端运行读取密集型工作负载 60 秒。

输出：

tps = 2500.456 (excluding connections establishing)

解释：在此工作负载下，数据库能够处理每秒 2500 个事务。

如果 TPS 低于预期，请使用 EXPLAIN ANALYZE 分析查询计划以识别性能瓶颈。

结论

基准测试PostgreSQL是识别性能限制和优化数据库以适应各种工作负载的有效方法。像pgbench和sysbench这样的工具，结合pg_stat_statements的洞察，使您能够模拟真实场景并微调PostgreSQL配置。

通过掌握这些工具和技术，您可以确保您的PostgreSQL实例在读密集型和写密集型应用程序中都能提供高性能。