Sample Header Ad - 728x90

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