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:
checkpoints_timed:103881 checkpoints_requested:16 buffers_checkpoints:92105172 buffers_clean:83695499 max_written_clean:430652 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.