High load,

Hi all,

we are running a fairly big Ruby on Rails application on Postgres 8.4.
Our traffic grew quite a bit lately, and since then we are facing DB
performance issues. System load occasionally explodes (around 170
yesterday on a 16 core system), which seems to be caused by disk I/O
(iowait in our Munin graphs goes up significantly during these
periods). At other times the laod stays rather low under pretty much
the same circumstances.

There are 6 application servers with 18 unicorns each, as well as 12
beanstalk workers talking to the DB. I know the problem description is
very vague, but so far we haven't consistently managed to reproduce
the problem. Turning of the beanstalk workers usually leads to a great
decreases in writes and system loads, but during yesterday's debugging
session they obviously ran fine (thanks, Murphy).

Below you'll find our system information and Postgres config, maybe
someone could be so kind as to point out any obvious flaws in our
current configuration while I'm trying to get a better description of
the underlying problem.

Postgres version: 8.4.6

Number of logical CPUs: 16 (4x Quadcore Xeon E5520  @ 2.27GHz)

RAM: 16GB

             total       used       free     shared    buffers     cached
Mem:      16461012   16399520      61492          0      72392   12546112
-/+ buffers/cache:    3781016   12679996
Swap:       999992     195336     804656

HDD: 2x 120 GB OCZ Vertex 2 SSD; RAID 1

Concurrent connections (according to our monitoring tool): 7 (min), 74
(avg), 197 (max)

Our config (all other settings at default value):

max_connections = 200
ssl = true
shared_buffers = 4096MB
work_mem = 256MB
maintenance_work_mem = 512MB
synchronous_commit = off
wal_buffers = 8MB
checkpoint_segments = 30
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
random_page_cost = 2.0
effective_cache_size = 8192MB
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000
log_connections = on
log_disconnections = on
log_line_prefix = '%t '
datestyle = 'iso, mdy'
gin_fuzzy_search_limit = 10000

The config options are a mix of the article "Configuring PostgreSQL
for Pretty Good Performance" [1] and the talk "PostgreSQL as a secret
weapon for high-performance Ruby on Rails applications" [2].

Thanks,
Michael

[1] http://www.linux.com/learn/tutorials/394523-configuring-postgresql-for-pretty-good-performance
[2] http://www.pgcon.org/2010/schedule/events/210.en.html