postgresql 8.3 possible checkpoint and memory misconfiguration
1
vote
0
answers
300
views
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.
Asked by John Papsi
Mar 14, 2013, 12:27 PM
Last activity: Mar 15, 2013, 01:33 PM
Last activity: Mar 15, 2013, 01:33 PM