本文的主题是出色的pg_stat_statements扩展。
在这篇文章中，我展示了我喜欢使用pg_stat_statements的一种方式：跟踪配置更改对特定工作负载的影响。我对配置进行了人为的更改，很快就产生了明显的影响。
测试流程
我使用PgOSM Flex将Colorado OpenStreetMap数据加载到PostGIS。PgOSM-Flex使用多步骤ETL来准备数据库，运行osm2pgsql，然后运行多个后处理步骤。这导致2.4 GB的数据进入Postgres。这应该是足够的活动来展示一些有趣的东西。
配置更改
像PgOSM-Flex这样的进程对postgresql.conf中的一些配置选项很敏感。我想到的第一个配置更改是maintenance_work_mem。我决定把shared_buffers和work_mem也降低到更低的值。只是为了确保我的测试结果有所不同！
我的本地Postgres 15实例有这些设置供我日常使用。这是这篇文章的配置A。
shared_buffers = 4GB
work_mem = 10MB
maintenance_work_mem = 1GB |
用来对比的配置B，将上面设置中的每一个都降低到了令人难以忍受的低值。我认为设置maintenance_work_mem=1MB就足以显示差异。
shared_buffers = 4MB
work_mem = 1MB
maintenance_work_mem = 1MB |
清空数据库
在运行每个测试之前，我设置了一个带有所需前提条件的干净数据库，并重置pg_stat_statements以便开始一个全新的状态。
SELECT pg_stat_statements_reset(); |
此时，我运行此流程并等待。当我测试PgOSM-Flex时，您可以测试任何您喜欢的东西，只要您在受控的环境中有一个脚本化的、可复制的流程。
以下查询将在测试运行完成后运行。这将计算带有一些典型分组的query_type列。一些query_type值是特定于项目的（例如，WHEN query ILIKE'%CALL osm%'THEN'PgOSM Flex processing'）。我把它放在一个临时表中，这样我就可以进一步查询和操作它。这种类型的查询通常是相当临时性的，特定于所讨论的数据库。
DROP TABLE IF EXISTS queries;
CREATE TABLE queries AS
SELECT CASE WHEN query ILIKE '%COMMENT ON%'
OR query ILIKE 'BEGIN'
OR query ILIKE 'SET%'
OR query ILIKE '%COMMIT%'
OR query ILIKE '%ROLLBACK%'
OR query ILIKE '%SHOW%'
OR query ILIKE '%LOCK%TABLE%'
THEN 'Controls/Docs'
WHEN query ILIKE '%UPDATE%'
OR query ILIKE '%DELETE%'
THEN 'Update / Delete'
WHEN query ILIKE '%COPY%'
OR query ILIKE '%INSERT%'
THEN 'Data Load'
WHEN query ILIKE '%CREATE%MAT%VIEW%'
THEN 'Create MV'
WHEN query ILIKE '%CREATE%TABLE%'
OR query ILIKE '%CREATE%FUNCTION%'
OR query ILIKE '%CREATE%SCHEMA%'
OR query ILIKE '%ALTER%TABLE%'
OR query ILIKE '%CREATE%VIEW%'
OR query ILIKE '%CREATE%EXTENSION%'
THEN 'DDL'
WHEN query ILIKE '%DROP%TABLE%'
OR query ILIKE '%DROP%TRIGGER%'
OR query ILIKE '%DROP%FUNCTION%'
OR query ILIKE '%DROP%PROCEDURE%'
THEN 'Drop objects'
WHEN query ILIKE '%CALL osm%'
THEN 'PgOSM Flex processing'
WHEN query ILIKE '%CREATE%INDEX%'
THEN 'Create Index'
WHEN query ILIKE 'ANALYZE%'
THEN 'Analyze (Stats)'
WHEN query ILIKE '%SELECT%'
THEN 'SELECT'
ELSE 'Unknown'
END AS query_type,
*
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
; |
我将数据从这个临时表聚合到一个非临时表中，以便以后可以比较结果。对于这个例子，我创建了一个统计模式来存储聚合结果。
以下查询按query_type聚合了一些常见值。对toplevel的筛选是可选的，我经常把它包括在内以减少噪音。其他时候我会把它放在一边，这样我就可以更详细地了解正在发生的事情。
CREATE TABLE stats.agg_config_a AS
SELECT query_type, COUNT(*), COUNT(DISTINCT queryid) AS distinct_query_count,
COUNT(*) FILTER (WHERE plans > 0) AS queries_with_plans,
SUM(plans) AS total_plans,
SUM(calls) AS total_calls,
SUM(total_exec_time) AS total_exec_time,
MAX(max_exec_time) AS max_exec_time,
SUM(rows) AS total_rows,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(temp_blks_written) AS temp_blks_written,
SUM(temp_blk_read_time) AS temp_blk_read_time,
SUM(temp_blk_write_time) AS temp_blk_write_time,
SUM(wal_records) AS wal_records,
pg_size_pretty(SUM(wal_bytes)) AS wal_size
FROM queries
WHERE toplevel
GROUP BY query_type
ORDER BY total_exec_time DESC
; |
保存两个测试后
我运行了每个测试，将结果从pg_stat_statements保存到stats.agg_config_a和stats.agg_config_b中。下面的查询比较了这两个测试的结果。
SELECT a.query_type, a.total_calls,
a.total_exec_time::BIGINT AS total_time_a,
b.total_exec_time::BIGINT AS total_time_b,
(b.total_exec_time - a.total_exec_time)::BIGINT AS time_diff_ms,
(b.total_exec_time - a.total_exec_time) / a.total_exec_time
AS percent_diff
FROM stats.agg_config_a a
FULL JOIN stats.agg_config_b b
ON a.query_type = b.query_type
ORDER BY time_diff_ms DESC
;
┌───────────────────────┬─────────────┬──────────────┬──────────────┬──────────────┬──────────────────────┐
│ query_type │ total_calls │ total_time_a │ total_time_b │ time_diff_ms │ percent_diff │
╞═══════════════════════╪═════════════╪══════════════╪══════════════╪══════════════╪══════════════════════╡
│ Data Load │ 245 │ 358854 │ 393957 │ 35103 │ 0.09782046502939316 │
│ Create Index │ 119 │ 11929 │ 17155 │ 5225 │ 0.43799236073983533 │
│ DDL │ 202 │ 7745 │ 12279 │ 4534 │ 0.5854177939107483 │
│ Analyze (Stats) │ 39 │ 3373 │ 4878 │ 1505 │ 0.4462918308554119 │
│ Create MV │ 3 │ 1586 │ 1740 │ 154 │ 0.09696164745742934 │
│ PgOSM Flex processing │ 2 │ 1413 │ 1512 │ 99 │ 0.07015269212804308 │
│ Update / Delete │ 39 │ 46 │ 68 │ 22 │ 0.48861133692158826 │
│ Drop objects │ 166 │ 118 │ 135 │ 17 │ 0.1403158906113987 │
│ SELECT │ 30 │ 65 │ 76 │ 11 │ 0.17006003902495692 │
│ Controls/Docs │ 634 │ 38 │ 34 │ -5 │ -0.12137879643848994 │
└───────────────────────┴─────────────┴──────────────┴──────────────┴──────────────┴──────────────────────┘ |
图像化
这些结果可以很容易地放入图表中。第一个图表显示query_type的时间之间的%差异。这种数据视图没有考虑对总时间的影响，只显示了按操作类型的影响。
下一个图表按query_type显示时间上的总差异。虽然上图显示配置A的数据加载（COPY/INSERT）速度快了10%，但下图显示10%的差异达35秒。另一方面，当总影响为22毫秒时，UPDATE/DELETE显示出50%的差异。
总结
这篇文章展示了我使用pg_stat_statements跟踪PostgreSQL数据库性能的一种方法。这种基本方法可以用于多种场景并进行调整。