Postgres 性能问题

我们正在运行 Postgres 9.1.3，最近我们的一台服务器开始遇到重大性能问题。

我们的查询在一段时间内运行良好，但截至 8 月 1 日，速度显着减慢。看起来大多数有问题的查询都是 Select 查询（带有 count(*) 的查询尤其糟糕），但总的来说，数据库运行速度非常慢。

We ran this http://wiki.postgresql.org/wiki/Server_Configuration在服务器上查询，这些是我们对默认配置文件所做的更改（注意：服务器之前在这些更改下运行良好，因此，它们可能并不重要）：

       name            |                                                current_setting
---------------------------+---------------------------------------------------------------------------------------------------------------
version                   | PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by  gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit
autovacuum                | off
bgwriter_delay            | 20ms
checkpoint_segments       | 6
checkpoint_warning        | 0
client_encoding           | UTF8
default_statistics_target | 1000
effective_cache_size      | 4778MB
effective_io_concurrency  | 2
fsync                     | off
full_page_writes          | off
lc_collate                | en_US.UTF-8
lc_ctype                  | en_US.UTF-8
listen_addresses          | *
maintenance_work_mem      | 1GB
max_connections           | 100
max_stack_depth           | 2MB
port                      | 5432
random_page_cost          | 2
server_encoding           | UTF8
shared_buffers            | 1792MB
synchronous_commit        | off
temp_buffers              | 16MB
TimeZone                  | US/Eastern
wal_buffers               | 16MB
wal_level                 | minimal
wal_writer_delay          | 10ms
work_mem                  | 16MB
(28 rows)

Time: 210.231 ms

通常，当出现此类问题时，人们建议的第一件事就是吸尘，我们已经尝试过。我们对大部分数据库进行了真空分析，但没有帮助。

We used Explain在我们的一些查询中，我们注意到 Postgres 正在诉诸顺序扫描，即使表有索引。

我们关闭顺序扫描以强制查询规划器使用索引，但这也没有帮助。

然后我们尝试了this http://wiki.postgresql.org/wiki/Show_database_bloat查询以查看 Postgres 是否有大量未使用的磁盘空间，以便找到它要查找的内容。不幸的是，虽然我们的一些表确实有点大，但它似乎不足以降低整体系统性能。

我们认为速度下降可能与 I/O 有关，但我们无法弄清楚具体细节。 Postgres 是否只是愚蠢？如果是，那是哪一部分？虚拟机有问题，还是物理硬件本身有问题？

对于我们可以尝试或检查的事情，你们还有其他建议吗？

EDIT:

我很抱歉没有早点更新这个。我陷入了其他事情之中。

在这台特定的机器上，通过对虚拟机的设置进行一点小小的修改，我们的性能得到了极大的提高。

有一个处理 IO 缓存的设置。它最初设置为ON。我们认为不断缓存东西会减慢速度，我们是对的。我们把它关掉了，事情有了很大的改善。

有趣的是，我们的大多数其他服务器已经关闭了此设置。

还有其他问题，我相信我们会采纳您的很多建议，所以，非常感谢您的帮助。

你最大的问题是这一行：

autovacuum                | off
  

打开它并不能立即解决问题，但应该可以防止问题进一步恶化。几乎在任何情况下关闭此功能都是一个好主意。主要的例外是大批量加载，然后是显式 VACUUM FREEZE ANALYZE，之后应重新打开 autovacuum。关闭 autovacuum 后，您会发现性能下降，就像您所看到的那样。一旦数据库变得如此糟糕，它需要比 autovacuum 所能提供的更积极的维护来恢复。

checkpoint_segments       | 6
  

增加这个将有助于数据修改，但对提高速度没有太大帮助SELECT声明。

fsync                     | off
full_page_writes          | off
  

这些设置告诉 PostgreSQL 以牺牲持久性为代价来加快写入速度。如果您的硬件或操作系统（或虚拟机）崩溃或突然终止，您的数据库将被损坏，您最好的选择是从上次已知的良好备份进行恢复。 （当然，由于硬件随时可能发生故障，因此如果您担心丢失数据，则需要制定良好的备份策略。）

maintenance_work_mem      | 1GB
  

这对于 8GB 虚拟机来说太高了。在对该连接进行一些繁重的维护之前，您始终可以在单个连接上增强它。

wal_writer_delay          | 10ms
  

即使是经验丰富的专家也很难将其调整为比默认值更好的性能。几乎总是最好不要管它。

此时最好的选择是使用 pg_dumpall 将数据库集群转储到其他介质，从新的 initdb 开始，然后恢复。作为数据库超级用户，运行VACUUM FREEZE ANALYZE (the FREEZE通常不建议除非在像这样的批量加载之后），并在打开 autovacuum 的情况下运行。

我强烈建议您阅读 Greg Smith 的《PostgreSQL 9.0 High Performance》一书，并仔细阅读。 （坦白说，我是这本书的技术审稿人之一，但没有从销售中获得任何收入。）他建议的第一件事就是在安装 PostgreSQL 之前获取 RAM 和磁盘速度的基准数据——即让你知道自己正在处理什么。