Take the 2-minute tour ×

I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application.

At peak hours, we notice performance problems in the database specifically high IO ( disk writes mostly, not reads).

I am suspecting checkpoints to be the problem. Our postgresql.conf file contains the following:

shared_buffers = 3000MB
work_mem = 256MB
maintenance_work_mem = 100MB
max_fsm_pages = 3448000
max_fsm_relations = 10000
wal_buffers = 1MB
checkpoint_segments = 32
checkpoint_completion_target = 0.9

while the rest are default.

Stats from the pg_stat_bgwriter table are the following:

buffers_backend: 33671343
buffers_alloc: 9001524439

Our system also has 20 GB of memory and a RAID1 disk array of two drives.

I turned today the checkpoint logging on though not during high traffic so I haven't been able to test it fully ( I will tomorrow though). In the log file I see a checkpoint every ~5 minutes. Together with the fact that there are about 100.000 checkpoints timed, this makes me believe that the server is performing a timed checkpoint every five minutes ( which is the default ? ). Since the requested checkpoints are only 16, it seems that it never reaches the 32 x 16 MB limit to initiate a checkpoint on request.

This leads me to the speculation that somehow our system is not properly configured. Should I increase the checkpoint timeout to like 30 minutes and the segments to an extend that will cause a checkpoint each time ~6 GB are filled ?

Aside from that, I am using redmine to monitor the machines memory usage and in the graph I see memory used to be about 500 - 800 mb while 6 GB are allocated to the swap and the rest to the file system cache. When issuing a TOP command the postgres processes are using about 4 GB of VIRT memory. Finally free -m indicates that only 500 mb are used the rest of the 20 GB being free.

share|improve this question

migrated from stackoverflow.com Mar 15 '13 at 13:33

This question came from our site for professional and enthusiast programmers.

Thanks for including the server version, config, etc. Voted to move this to DBA.SE, so don't be surprised if you see it jump ship. What's your max_connections and how many active connections are there in practice? Do you see swap activity in vmstat 1? Your work_mem is quite high unless you have a lot of RAM and/or very few connections. Note that work_mem is a per-sort limit not a per-connection limit, so complex queries can use several times work_mem. –  Craig Ringer Mar 15 '13 at 12:01
Are you aware that 8.3 is no longer supported? You should plan for an upgrade to an up-to-date (and supported) version as soon as possible. –  a_horse_with_no_name Mar 15 '13 at 13:54

Your Answer

