[译文] 使用 pg_stat_monitor 改进 PostgreSQL 查询性能洞察

了解查询性能模式本质上是查询性能调优的基础。它在许多方面决定了数据库集群的发展方式。然后显然也有直接和间接成本的含义。

PostgreSQL 通过许多目录视图和扩展提供非常详细的统计信息，这些视图和扩展可以轻松添加以提供更详细的查询统计信息。每个视图都集中在一个特定方面，几乎总是需要通过组合不同的数据集来将图片拼接在一起。这需要努力，但整个画面可能并不完整。

pg_stat_monitor 扩展试图通过在单个视图中提供急需的查询性能洞察来提供更全面的图片。该扩展在过去一年中一直在发展，现在接近 GA 版本。

一些有用的扩展

目前，您可能依赖许多扩展来了解查询的行为方式、计划和执行阶段所用的时间、最小/最大/平均时间值、索引命中、查询计划和客户端应用程序详细信息。以下是您可能已经非常熟悉的一些扩展。

pg_stat_activity

默认情况下，该视图在 PostgreSQL 中可用。它为每个服务器进程提供一行以及当前活动和查询文本。

如果您想了解更多信息，请在此处跳转到官方PostgreSQL 文档。

pg_stat_statements

此扩展是随 PostgreSQL 服务器提供的 contrib 包的一部分。但是，您必须手动创建扩展。它是统计数据的查询聚合，具有执行和计划时间的最小/最大/平均值/标准偏差以及各种有用的信息和查询文本。

您可以在官方PostgreSQL 文档站点上阅读有关 pg_stat_statements 的更多信息。

自动解释

PostgreSQL 服务器提供了另一个有用的扩展。对于超过 GUC 指定的时间阈值的任何查询，它都会在服务器日志中转储查询计划

（大统一配置）。

您可以在此处找到有关 auto_explain 的更多信息。

pg_stat_monitor

虽然前面提到的所有视图/扩展本身都很棒，但需要手动组合来自 pg_stat_activity 的客户端/连接信息、来自 pg_stat_statements 的统计数据和来自 auto_analyze 的查询计划来完成数据集以了解查询性能模式

而这正是pg_stat_monitor减轻的痛苦。

该功能集在过去一年中一直在增长，它在单一视图中提供了调试低性能查询可能需要的所有与性能相关的信息。有关扩展的更多信息，请参阅我们的GitHub 存储库，或有关特定于用户的文档，请参阅我们的用户指南。

功能集

本博客中已经讨论了早期版本中的一些功能，但是，为了完整起见，我也将在这里讨论这些功能。

时间间隔分组： pg_stat_monitor 不是提供一组不断增加的计数，而是计算配置数量的时间间隔的统计数据；时间段。这允许更好的数据准确性，尤其是在高分辨率或不可靠网络的情况下。
多维分组：虽然 pg_stat_statements 按 (userid, dbid, queryid) 对计数器进行分组，但 pg_stat_monitor 使用更详细的组以获得更高的精度：

- 存储桶 ID（存储桶），

- 用户 ID（用户 ID），

- 数据库 ID (dbid),

- 查询 ID（queryid），

- 客户端 IP 地址 (client_ip)，

- 计划 ID (planid),

- 应用程序名称 (application_name)。

这允许您深入了解来自特定客户端地址和应用程序的查询的性能，我们在 Percona 发现这在许多情况下非常有价值。

在查询中捕获实际参数：pg_stat_monitor 允许您选择是否要查看带有参数占位符的查询或实际查询示例。
查询计划：现在每个 SQL 都伴随着为其执行而构建的实际计划。此外，我们发现拥有查询参数值非常有用，因为您可以在其上运行 EXPLAIN，或者轻松修改查询以使其运行得更好，以及在与其他 DBA 和应用程序开发人员讨论时更清晰地沟通查询.
语句的表访问统计：这使我们能够轻松识别访问给定表的所有查询。该集合与 pg_stat_statements 提供的信息相同。
直方图：当它可以帮助识别问题时，视觉表示非常有用。借助直方图功能，您现在可以查看响应 SQL 查询的计时/调用数据直方图。是的，它甚至适用于 psql。

SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT);       range        | freq |              bar--------------------+------+--------------------------------  (0 - 3)}          |    2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■  (3 - 10)}         |    0 |  (10 - 31)}        |    1 | ■■■■■■■■■■■■■■■  (31 - 100)}       |    0 |  (100 - 316)}      |    0 |  (316 - 1000)}     |    0 |  (1000 - 3162)}    |    0 |  (3162 - 10000)}   |    0 |  (10000 - 31622)}  |    0 |  (31622 - 100000)} |    0 |(10 rows)

SELECT * FROM histogram(0, 'F44CD1B4B33A47AF') AS a(range TEXT, freq INT, bar TEXT);

range | freq | bar

--------------------+------+--------------------------------

(0 - 3)} | 2 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

(3 - 10)} | 0 |

(10 - 31)} | 1 | ■■■■■■■■■■■■■■■

(31 - 100)} | 0 |

(100 - 316)} | 0 |

(316 - 1000)} | 0 |

(1000 - 3162)} | 0 |

(3162 - 10000)} | 0 |

(10000 - 31622)} | 0 |

(31622 - 100000)} | 0 |

(10 rows)

函数：这可能会让人感到惊讶，但我们确实理解函数可以在内部执行语句！！！为了帮助简化跟踪和分析，pg_stat_monitor 现在提供了一个列，专门帮助跟踪语句的顶部查询，以便您可以回溯到原始函数。
关系名称：查询中使用的关系在 pg_stat_monitor 视图的“关系”列中可用。这减少了您的工作并使分析更简单、更快捷。
查询类型：查询分类为 SELECT、INSERT、UPDATE 或 DELETE，分析变得更简单。这是您最终减少的另一项工作，也是 pg_stat_monitor 的另一项简化。

SELECT bucket, substr(query,0, 50) AS query, cmd_type FROM pg_stat_monitor WHERE elevel = 0; bucket |                       query                       | cmd_type --------+---------------------------------------------------+----------      4 | END                                               |       4 | SELECT abalance FROM pgbench_accounts WHERE aid = | SELECT      4 | vacuum pgbench_branches                           |       4 | select count(*) from pgbench_branches             | SELECT      4 | UPDATE pgbench_accounts SET abalance = abalance + | UPDATE      4 | truncate pgbench_history                          |       4 | INSERT INTO pgbench_history (tid, bid, aid, delta | INSERT

SELECT bucket, substr(query,0, 50) AS query, cmd_type FROM pg_stat_monitor WHERE elevel = 0;

bucket | query | cmd_type

--------+---------------------------------------------------+----------

4 | END |

4 | SELECT abalance FROM pgbench_accounts WHERE aid = | SELECT

4 | vacuum pgbench_branches |

4 | select count(*) from pgbench_branches | SELECT

4 | UPDATE pgbench_accounts SET abalance = abalance + | UPDATE

4 | truncate pgbench_history |

4 | INSERT INTO pgbench_history (tid, bid, aid, delta | INSERT

查询元数据：Google 的 Sqlcommenter是一个有用的工具，它在某种程度上弥合了 ORM 库和理解数据库性能之间的差距。我们支持它。因此，您现在可以将任何键值数据放在SQL 语句中 /* ... */ 语法的注释中（有关详细信息，请参阅Sqlcommenter 文档），并且该信息将由 pg_stat_monitor 解析并在 pg_stat_monitor 视图的注释列中可用.

CREATE EXTENSION hstore;CREATE FUNCTION text_to_hstore(s text) RETURNS hstore AS $$BEGIN    RETURN hstore(s::text[]);EXCEPTION WHEN OTHERS THEN    RETURN NULL;END; $$ LANGUAGE plpgsql STRICT;SELECT 1 AS num /* { "application", java_app, "real_ip", 192.168.1.1} */; num -----   1(1 row)

CREATE EXTENSION hstore;

CREATE FUNCTION text_to_hstore(s text) RETURNS hstore AS $$

BEGIN

RETURN hstore(s::text[]);

EXCEPTION WHEN OTHERS THEN

RETURN NULL;

END; $$ LANGUAGE plpgsql STRICT;

SELECT 1 AS num /* { "application", java_app, "real_ip", 192.168.1.1} */;

num

-----

(1 row)

SELECT query, text_to_hstore(comments)->'real_ip' AS real_ip from pg_stat_monitor;query                                                                       |  real_ip ----------------------------------------------------------------------------+------------- SELECT $1 AS num /* { "application", psql_app, "real_ip", 192.168.1.3) */  | 192.168.1.1

SELECT query, text_to_hstore(comments)->'real_ip' AS real_ip from pg_stat_monitor;

query | real_ip

----------------------------------------------------------------------------+-------------

SELECT $1 AS num /* { "application", psql_app, "real_ip", 192.168.1.3) */ | 192.168.1.1

记录错误和警告：正如在不同的监控/静态收集器工具中所见，大多数工具/扩展只监控成功的查询。但在许多情况下，监控 ERROR、WARNING 和 LOG 会提供有意义的信息来调试问题。pg_stat_monitor 不仅监控 ERROR/WARNINGS/LOG 还收集有关这些查询的统计信息。在带有 ERROR/WARNING 的 PostgreSQL 查询中，有错误级别 (elevel)、SQL 代码 (sqlcode)，并附有错误消息。Pg_stat_monitor 收集所有这些信息及其聚合。

SELECT substr(query,0,50) AS query, decode_error_level(elevel) AS elevel,sqlcode, calls, substr(message,0,50) message FROM pg_stat_monitor;                       query                       | elevel | sqlcode | calls |                      message                      ---------------------------------------------------+--------+---------+-------+--------------------------------------------------- select substr(query,$1,$2) as query, decode_error |        |       0 |     1 |  select bucket,substr(query,$1,$2),decode_error_le |        |       0 |     3 |  select 1/0;                                       | ERROR  |     130 |     1 | division by zero

SELECT substr(query,0,50) AS query, decode_error_level(elevel) AS elevel,sqlcode, calls, substr(message,0,50) message

FROM pg_stat_monitor;

query | elevel | sqlcode | calls | message

---------------------------------------------------+--------+---------+-------+---------------------------------------------------

select substr(query,$1,$2) as query, decode_error | | 0 | 1 |

select bucket,substr(query,$1,$2),decode_error_le | | 0 | 3 |

select 1/0; | ERROR | 130 | 1 | division by zero

我们已经走了很长一段路

最初是一个概念，现在接近其最终方法。pg_stat_monitor 扩展已经发展并且功能非常丰富。我们毫不怀疑它对 DBA、性能工程师、应用程序开发人员和任何需要查看查询性能的人的用处。我们相信它可以帮助节省大量时间并帮助识别意外的查询行为。

pg_stat_monitor在 Github 上可用。在我们将 pg_stat_monitor 作为普遍可用的版本发布之前，我们发布它是为了从社区获得关于我们做对了什么以及我们应该做些什么不同的反馈，以便在未来几年得到支持。请检查一下，给我们留言，提出问题，或提出拉取请求！

立即试用 Percona Distribution for PostgreSQL；可以免费下载和使用！