用pg_stat_statements跟踪性能差异

本文的主题是出色的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
;

我将数据从这个临时表聚合到一个非临时表中，以便以后可以比较结果。对于这个例子，我创建了一个统计模式来存储聚合结果。

CREATE SCHEMA stats;

以下查询按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数据库性能的一种方法。这种基本方法可以用于多种场景并进行调整。