Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
2  
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
add comment

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.