Re: How to avoid transaction ID wrap

Tom Lane wrote:
> "Joshua D. Drake" <jd(at)commandprompt(dot)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 solutions since 1997
http://www.commandprompt.com/