performance - Postgres 性能问题

Question

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

我们的查询有一段时间运行良好，但从 8 月 1 日起，速度急剧下降。看起来大多数有问题的查询都是 Select 查询(带有 count(*) 的查询特别糟糕)，但一般来说，数据库运行得非常慢。

我们跑了 this在服务器上查询，这些是我们对默认配置文件所做的更改(注意:服务器在这些更改之前运行良好，因此，它们可能无关紧要):

       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

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

我们在一些查询中使用了 Explain 并注意到 Postgres 正在求助于顺序扫描，即使表有索引也是如此。

我们关闭顺序扫描以强制查询计划程序使用索引，但这也无济于事。

然后我们尝试了 this查询以查看我们是否有大量未使用的磁盘空间，Postgres 正在经历这些空间以找到它正在寻找的内容。不幸的是，虽然我们的一些表确实有点大，但似乎还不足以降低整体系统性能。

我们认为减速可能与 I/O 相关，但我们无法确定具体细节。 Postgres 只是愚蠢吗？如果是，那是哪一部分？是虚拟机有问题，还是物理硬件本身有问题？

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

编辑:

很抱歉没有及时更新。我陷入了其他事情。

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

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

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

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

Answer 1

你最大的问题是这一行:

autovacuum                | off

打开它不会立即解决问题，但它应该可以防止问题进一步恶化。几乎没有关闭此功能的好主意。主要的异常(exception)是大批量加载，然后是显式 VACUUM FREEZE ANALYZE，之后应重新打开 autovacuum。关闭 autovacuum 后，您会看到性能下降，就像您遇到的那样。一旦数据库变得如此糟糕，它需要比 autovacuum 提供的恢复更积极的维护。

checkpoint_segments       | 6

增加它有助于数据修改，但对提高 SELECT 语句的速度没有太大作用。

fsync                     | offfull_page_writes          | off

这些设置告诉 PostgreSQL 以牺牲持久性为代价来加速写入。如果您的硬件或操作系统(或虚拟机)崩溃或突然被杀死，您的数据库将被破坏，您最好的选择是从您最后一次已知的良好备份中恢复。 (当然，由于硬件随时都可能发生故障，如果您担心丢失数据，则可以采取良好的备份策略。)

maintenance_work_mem      | 1GB

这对于 8GB VM 来说太高了。在对该连接进行大量维护之前，您始终可以在单个连接上提升它。

wal_writer_delay          | 10ms

即使是经验丰富的专家也很难将其调整为比默认设置更好的性能。它几乎总是最好单独放置。

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

我强烈建议您获得 Greg Smith 的“PostgreSQL 9.0 High Performance”一书的副本，并仔细阅读。 (完全公开，我是这本书的技术审阅者之一，但没有从销售中获利。)他建议的第一件事就是在安装 PostgreSQL 之前获取 RAM 和磁盘速度的基准数据——那您知道自己在处理什么的方式。