Tom Lane wrote:
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
I was recently involved in a project where we had to decrease the
checkpoint_timeout . The problem was, that the database was performing
so many transactions that if we waiting for 5 minutes, checkpoint would
take entirely too long.
Seems like the correct fix for that is to make the bgwriter more
aggressive. Narrowing the checkpoint spacing is a pretty horrid answer
because of the resulting increase in full-page-image WAL traffic.
Well we did that as well. Here are the basic symptons:
During normal processing which contained about 250 connections
everything was fine. A checkpoint would start and connections would
start piling up, sometimes breaking 1000.
We narrowed that down to users having to wait longer for query execution
so instead of just reusing connections new connections had to be
initiated because the existing connections were busy.
We tried many different parameters, and bgwriter did significantly help
but the only "solution" was to make checkpoints happen at a much more
aggressive time frame.
Modify bgwriters settings and the checkpoint actually increased our
velocity by about 70% by the time we were done. Bgwriter was definitely
the largest chunk of that although other parameters combined outweighed
it (effective_cache, shared_buffers etc...).
Sincerely,
Joshua D. Drake
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
