如何使用pg_stat_activity？ [英] How to use pg_stat_activity?

问题描述

我想查看在实时Django应用程序上正在执行哪些查询，以及它们占用了多少内存。我已经读到 pg_stat_activity 对监视Postgres数据库很有用。

I'd like to see which queries are being executed on a live Django application, and how much memory they are taking up. I have read that pg_stat_activity can be useful to monitor a Postgres database.

我有查看了Postgres文档，但是我有一个非常简单的问题，似乎在这里没有得到回答。

I have looked at the Postgres documentation, but I have a very simple question that doesn't seem to be answered there.

我实际上如何开始使用 pg_stat_activity ？我要输入什么才能使用它，以及在哪里输入？

How do I actually get started with pg_stat_activity? What do I type to use it, and where do I type it?

推荐答案

请参见这个密切相关的答案。

pg_stat_activity 是 pg_catalog 模式中的视图。

您可以通过 SELECT 像其他任何表一样从中进行访问，例如 SELECT * FROM pg_stat_activity 。您链接到的手册页解释了其列。

You can query it by SELECTing from it like any other table, eg SELECT * FROM pg_stat_activity. The manual page you linked to explains its columns.

有时您会发现自己想加入其他表，例如 pg_class （表）， pg_namespace （方案）等。

You'll sometimes find yourself wanting to join on other tables like pg_class (tables), pg_namespace (schemas), etc.

pg_stat_activity 不会不公开有关后端内存使用的信息。您需要为此使用操作系统级别的设施。它可以告诉您进程ID，活动用户，当前正在运行的查询，活动状态，最后一次查询开始的时间等。这对于识别事务中长时间运行的空闲会话，运行时间非常长的查询等。

pg_stat_activity does not expose information about back-end memory use. You need to use operating-system level facilities for that. It does tell you the process ID, active user, currently running query, activity status, time the last query started, etc. It's good for identifying long-running idle in transaction sessions, very long running queries, etc.

坦率地说，PostgreSQL的内置监视功能非常简单。这是一项工作上并不令人兴奋的领域，而且商业客户并不经常愿意为此提供资金。大多数人都将诸如 check_postgres 之类的工具与Icinga和Munin结合使用，或使用Zabbix或其他外部监视代理。

Frankly, PostgreSQL's built-in monitoring is rather rudimentary. It's one of the areas that's not that exciting to work on, and commercial clients aren't often willing to fund it. Most people couple tools like check_postgres with Icinga and Munin, or use Zabbix or other external monitoring agents.

在您的情况下，听起来您确实想要 pg_stat_statements ，并且/或PgBadger日志分析以及适当的日志记录设置以及 auto_explain 模块。

In your case it sounds like you really want pg_stat_statements, and/or PgBadger log analysis with suitable logging settings and possibly the auto_explain module.

这篇关于如何使用pg_stat_activity？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！