简单的postgresql插入/更新查询每5-7分钟需要*秒*来响应

Question

我有一个使用Postgresql数据库的Ruby on Rails应用程序。我注意到我的数据库性能每隔5-7分钟就会出现一个巨大的峰值。

​

​

我看到简单查询的1+第二次响应时间如下：

UPDATE users SET last_seen_at = ? where id = ?

或

INSERT INTO emails (email, created_at, updated_at) VALUES (?, ?, ?)

VPS是一个亚马逊网络服务EC2实例(m2.2xlarge)，具有4核至强2.4 The和34 of内存。

Here is my postgresql.conf

我对conf进行了以下更改，试图找出它(比如减少检查点超时的数量)，但没有效果。

root:/etc/postgresql/9.2/main# diff postgresql.conf.bck postgresql.conf
176,178c176,178
< #checkpoint_segments = 3    # in logfile segments, min 1, 16MB each
< #checkpoint_timeout = 5min    # range 30s-1h
< #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
---
> checkpoint_segments = 10    # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 30min    # range 30s-1h
> checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
361c361
< #log_min_duration_statement = -1  # -1 is disabled, 0 logs all statements
---
> log_min_duration_statement = 2s  # -1 is disabled, 0 logs all statements
370,371c370,371
< #debug_print_rewritten = off
< #debug_print_plan = off
---
> #debug_print_rewritten = on
> #debug_print_plan = on
376c376
< #log_duration = off
---
> #log_duration = on
378c378
< #log_hostname = off
---
> #log_hostname = on
399c399
< #log_lock_waits = off     # log lock waits >= deadlock_timeout
---
> log_lock_waits = on     # log lock waits >= deadlock_timeout

Answer 1

在检查点结束时，您会遇到严重的IO问题。请注意，较慢的查询大多是提交，这应该只会刷新WAL日志，并且同步文件需要41.604秒(包括11秒来同步一个文件！)。

在PostgreSQL中，您可能做不了什么来改进这一点。我听说过降低shared_buffers可能会有所帮助的传言，但我还没有亲眼看到。

您可能需要对操作系统进行更改，比如降低/proc/sys/vm/dirty_ratio，这样它就不会允许在检查点之间建立如此多的脏数据。此外，如果您可以将WAL日志从主数据中分离出来，这将会有所帮助。

您使用的是什么文件系统？什么内核/发行版？

还有一种可能性是，您使用的IO系统根本无法容纳您的工作负载，您需要迁移到功能更强大的硬件。