|
version | PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE Linux) 4.3.2 [gcc-4_3-branch revision 141291], 64-bit bgwriter_delay | 50ms bgwriter_lru_maxpages | 500 bgwriter_lru_multiplier | 4 checkpoint_segments | 100 checkpoint_timeout | 40min checkpoint_warning | 30min checkpoint_completion_target | 0.5 effective_cache_size | 16GB effective_io_concurrency | 4 extra_float_digits | 3 max_connections | 2000 max_stack_depth | 7MB shared_buffers | 16GB synchronous_commit | off temp_buffers | 8192 TimeZone | US/Pacific wal_buffers | 8MB work_mem | 64MB Since our checkpoint_completion_target is 0.5, we complete a checkpoint around 20 mins after it started, and checkpoints occur like clockwork every 40 minutes, no extra's are forced. Performance overall is fairly good, but of course we're trying to squeeze as much out of it as we can. One main thing is trying to lower 'spikey' disk IO so that performance is more consistent at any given time. - Brian F On 11/01/2011 12:44 PM, Kevin Grittner wrote: Brian Fehrle <brianf@xxxxxxxxxxxxxxxxxxx> wrote:On 11/01/2011 08:58 AM, Kevin Grittner wrote:What problem are you trying to solve?We're trying to offload dirty buffer writes from checkpoints and from backends (not the bgwriter) to the background writer, I believe with the hope that it's the same amount of disk IO, but spread out evenly rather than just when a checkpoint is occurring.What version of PostgreSQL are you using? Recent versions spread out the checkpoint activity using the same process which does the background writing, so there is no benefit from moving writes from its background writing phase to its distributed checkpoint phase. Depending on your setting of checkpoint_completion_target you are probably spending as much or more time spreading the checkpoint as doing background writing between checkpoints. Each of the last few major releases has made this much better, so if you're spending time tweaking something prior to 9.1, you'd probably be better served putting that time into upgrading.Yes. Writing dirty buffers when there are enough buffers available to service requests would tend to increase overall disk writes and degrade performance. You don't have a problem unless you have a high percentage of writes from normal backends which need to flush a buffer in order to get one.This seems to be the case, as buffers_backend is between checkpoint_buffers and clean_buffers from pg_stat_bgwriter. For example, on 2011-10-19: checkpoint buffers: 622,519 clean_buffers: 65,879 clean_max_written: 56 backend_buffers: 460,471 Am I reading these right in wanting to reduce backend_buffers and checkpoint_buffers?Hmm. That is a higher percentage of backend writes than I would like to see. What is your shared_memory setting? Actually, please show the results of the query here: http://wiki.postgresql.org/wiki/Server_Configuration How is your performance? -Kevin |
