Re: How to avoid transaction ID wrap

Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> 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
solutionssince 1997             http://www.commandprompt.com/