PostgreSQL 性能监控工具

PostgreSQL performance monitoring tool

提问by Artem Tikhomirov

I'm setting up a web application with a FreeBSD PostgreSQL back-end. I'm looking for some database performance optimization tool/technique.

我正在使用 FreeBSD PostgreSQL 后端设置 Web 应用程序。我正在寻找一些数据库性能优化工具/技术。

采纳答案by Jason Baker

pgfouineworks fairly well for me. And it looks like there's a FreeBSD portfor it.

pgfouine对我来说效果很好。看起来它有一个FreeBSD 端口。

回答by Michael Renner

Database optimization is usually a combination of two things

数据库优化通常是两件事的结合

1. Reduce the number of queries to the database
2. Reduce the amount of data that needs to be looked at to answer queries

1. 减少对数据库的查询次数
2. 减少回答查询需要查看的数据量

Reducing the amount of queries is usually done by caching non-volatile/less important data (e.g. "Which users are online" or "What are the latest posts by this user?") inside the application (if possible) or in an external - more efficient - datastore (memcached, redis, etc.). If you've got information which is very write-heavy (e.g. hit-counters) and doesn't need ACID-semantics you can also think about moving it out of the Postgres database to more efficient data stores.

减少查询量通常是通过在应用程序内部（如果可能）或外部缓存非易失性/不太重要的数据（例如“哪些用户在线”或“该用户的最新帖子是什么？”）来完成的 -更高效 - 数据存储（memcached、redis 等）。如果您有大量写入的信息（例如命中计数器）并且不需要ACID语义，您还可以考虑将其从 Postgres 数据库移到更高效的数据存储中。

Optimizing the query runtime is more tricky - this can amount to creating special indexes(or indexes in the first place), changing (possibly denormalizing) the data model or changing the fundamental approach the application takes when it comes to working with the database. See for example the Pagination done the Postgres waytalk by Markus Winandon how to rethink the concept of pagination to make it more database efficient

优化查询运行时更加棘手 - 这可能相当于创建特殊索引（或首先创建索引）、更改（可能非规范化）数据模型或更改应用程序在处理数据库时采用的基本方法。例如，参见Markus Winand的Pagination done the Postgres waytalk关于如何重新思考分页的概念以使其更高效的数据库

Measuring queries the slow way

以缓慢的方式衡量查询

But to understand which queries should be looked at first you need to know how often they are executed and how long they run on average.

但是要了解应该首先查看哪些查询，您需要知道它们的执行频率和平均运行时间。

One approach to this is logging all (or "slow") queries including their runtime and then parsing the query log. A good tool for this is pgfouinewhich has already been mentioned earlier in this discussion, it has since been replaced by pgbadgerwhich is written in a more friendly language, is much faster and more actively maintained.

一种方法是记录所有（或“慢”）查询，包括它们的运行时，然后解析查询日志。一个很好的工具pgfouine已经在本讨论的前面提到过，它已被替换pgbadger为以更友好的语言编写的，速度更快，维护更积极。

Both pgfouineand pgbadgersuffer from the fact that they need query-logging enabled, which can cause a noticeable performance hit on the database or bring you into disk space troubles on top of the fact that parsing the log with the tool can take quite some time and won't give you up-to-date insights on what is going in the database.

无论pgfouine和pgbadger一个事实，即他们需要查询启用日志记录，这可能会导致明显的性能损失数据库或带你进入硬盘空间的烦恼上的事实，顶部是解析日志与工具可能需要相当长的时间和韩元遭受不会为您提供有关数据库中正在发生的事情的最新见解。

Speeding it up with extensions

通过扩展加快速度

To address these shortcomings there are now two extensions which track query performance directly in the database - pg_stat_statements(which is only helpful in version 9.2 or newer) and pg_stat_plans. Both extensions offer the same basic functionality - tracking how often a given "normalized query" (Query string minus all expression literals) has been run and how long it took in total. Due to the fact that this is done while the query is actually run this is done in a very efficient manner, the measurable overhead was less than 5% in synthetic benchmarks.

为了解决这些缺点，现在有两个扩展可以直接在数据库中跟踪查询性能 - pg_stat_statements（仅在 9.2 或更新版本中有用）和pg_stat_plans. 两个扩展都提供相同的基本功能 - 跟踪给定的“规范化查询”（查询字符串减去所有表达式文字）运行的频率以及总共花费了多长时间。由于这是在查询实际运行时完成的事实，这是以非常有效的方式完成的，因此在综合基准测试中可测量的开销小于 5%。

Making sense of the data

理解数据

The list of queries itself is very "dry" from an information perspective. There's been work on a third extension trying to address this fact and offer nicer representation of the data called pg_statsinfo(along with pg_stats_reporter), but it's a bit of an undertaking to get it up and running.

从信息的角度来看，查询列表本身非常“枯燥”。已经有第三个扩展试图解决这个事实，并提供更好的数据表示pg_statsinfo（连同pg_stats_reporter），但是让它启动和运行有点困难。

To offer a more convenient solution to this problem I started working on a commercial project which is focussed around pg_stat_statementsand pg_stat_plansand augments the information collected by lots of other data pulled out of the database. It's called pganalyzeand you can find it at https://pganalyze.com/.

为了提供更方便的解决这个问题，我开始在其上集中围绕一个商业项目的工作pg_stat_statements和pg_stat_plans并增强通过大量的其他数据的收集到的信息拉到数据库的出来。它被称为pganalyze，您可以在https://pganalyze.com/ 上找到它。

To offer a concise overview of interesting tools and projects in the Postgres Monitoring area i also started compiling a list at the Postgres Wikiwhich is updated regularly.

为了简要概述 Postgres 监控区域中有趣的工具和项目，我还开始在Postgres Wiki 上编制一个列表，该列表会定期更新。

回答by brianestes

I've used pgtop a little. It is quite crude, but at least I can see which query is running for each process ID.

我用过一点 pgtop。这很粗糙，但至少我可以看到每个进程 ID 正在运行哪个查询。

I tried pgfouine, but if I remember, it's an offline tool.

我试过 pgfouine，但如果我记得的话，它是一个离线工具。

I also tail the psql.log file and set the logging criteria down to a level where I can see the problem queries.

我还跟踪 psql.log 文件并将日志记录标准设置为可以看到问题查询的级别。

#log_min_duration_statement = -1        # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this time.

I also use EMS Postgres Manager to do general admin work. It doesn't do anything for you, but it does make most tasks easier and makes reviewing and setting up your schema more simple. I find that when using a GUI, it is much easier for me to spot inconsistencies (like a missing index, field criteria, etc.). It's only one of two programs I'm willing to use VMWare on my Mac to use.

我还使用 EMS Postgres Manager 进行一般管理工作。它不会为您做任何事情，但它确实使大多数任务变得更容易，并使查看和设置您的架构更简单。我发现在使用 GUI 时，我更容易发现不一致（例如缺少索引、字段标准等）。它只是我愿意在 Mac 上使用 VMWare 的两个程序之一。

回答by John P

Munin is quite simple yet effective to get trends of how the database is evolving and performing over time. In the standard kit of Munin you can among other thing monitor the size of the database, number of locks, number of connections, sequential scans, size of transaction log and long running queries.

Munin 非常简单而有效地获取数据库如何随着时间的推移而演变和执行的趋势。在 Munin 的标准套件中，您可以监控数据库的大小、锁的数量、连接的数量、顺序扫描、事务日志的大小和长时间运行的查询。

Easy to setup and to get started with and if needed you can write your own plugin quite easily.

易于设置和入门，如果需要，您可以非常轻松地编写自己的插件。

Check out the latest postgresql plugins that are shipped with Munin here:

在此处查看 Munin 随附的最新 postgresql 插件：

http://munin-monitoring.org/browser/branches/1.4-stable/plugins/node.d/

http://munin-monitoring.org/browser/branches/1.4-stable/plugins/node.d/

回答by John P

Check out Lightning Admin, it has a GUI for capturing log statements, not perfect but works great for most needs. http://www.amsoftwaredesign.com

查看 Lightning Admin，它有一个用于捕获日志语句的 GUI，虽然不完美，但可以满足大多数需求。 http://www.amsoftwaredesign.com

回答by Paul Tomblin

Well, the first thing to do is try all your queries from psql using "explain" and see if there are sequential scans that can be converted to index scans by adding indexes or rewriting the query.

好吧，首先要做的是使用“explain”尝试所有来自 psql 的查询，看看是否有顺序扫描可以通过添加索引或重写查询来转换为索引扫描。

Other than that, I'm as interested in the answers to this question as you are.

除此之外，我和你一样对这个问题的答案感兴趣。

回答by dhorton

DBTuna http://www.dbtuna.com/postgresql_monitor.phphas recently started supporting PostgreSQL monitoring. We use it extensively for MySQL monitoring, so if it provides the same for Postgres then it should be a good fit for you too.

DBTuna http://www.dbtuna.com/postgresql_monitor.php最近开始支持 PostgreSQL 监控。我们广泛地将它用于 MySQL 监控，因此如果它为 Postgres 提供相同的功能，那么它也应该非常适合您。