Re: hanging for 30sec when checkpointing - Mailing list pgsql-admin

Tom,


Damn, why didn't I think of that myself...


Although, is there any performance implication of not doing
checkpoints very often?  (aside from, I assume, that each checkpoint
will take longer and hence saturate available I/O for longer)


Cheers


Shane


On 6 Feb 2004, at 15:33, Tom Lane wrote:


<excerpt>Shane Wright <<me@shanewright.co.uk> writes:

<excerpt>Hmm that gives me an idea, for bulk processing, is there a
way of

detecting from a client when a checkpoint is about to happen so it can

wait until it's finished?

</excerpt>

No, but maybe you could think about scheduling checkpoints yourself

to not coincide with your bulk jobs.  You could issue CHECKPOINT

commands from cron or whatever is dispatching your bulk jobs, and then

widen the checkpoint-timing parameters in postgresql.conf enough to

avoid automatic CHECKPOINTs.


The only real drawback of less-frequent CHECKPOINTs is that the amount

of WAL space required goes up proportionally.  (Oh, and it might take

proportionally longer to recover after a crash, too.)


            regards, tom lane



</excerpt><fontfamily><param>Arial</param><smaller>Shane Wright

Technical Manager

eDigitalResearch.com

2 Berrywood Business Village

Hedge End

Hampshire

SO30 2UN

T +44 (0) 1489 772920

F +44 (0) 1489 772922

 

This message is sent in confidence for the addressee only.  The
contents are not to be disclosed to anyone other than the addressee. 
Unauthorised recipients must preserve this confidentiality and should
please advise the sender immediately of any error in transmission.

 

Any attachment(s) to this message has been checked for viruses, but
please rely on your own virus checker and procedures.</smaller></fontfamily>

Tom,

Damn, why didn't I think of that myself...

Although, is there any performance implication of not doing checkpoints
very often?  (aside from, I assume, that each checkpoint will take
longer and hence saturate available I/O for longer)

Cheers

Shane

On 6 Feb 2004, at 15:33, Tom Lane wrote:

> Shane Wright <me@shanewright.co.uk> writes:
>> Hmm that gives me an idea, for bulk processing, is there a way of
>> detecting from a client when a checkpoint is about to happen so it can
>> wait until it's finished?
>
> No, but maybe you could think about scheduling checkpoints yourself
> to not coincide with your bulk jobs.  You could issue CHECKPOINT
> commands from cron or whatever is dispatching your bulk jobs, and then
> widen the checkpoint-timing parameters in postgresql.conf enough to
> avoid automatic CHECKPOINTs.
>
> The only real drawback of less-frequent CHECKPOINTs is that the amount
> of WAL space required goes up proportionally.  (Oh, and it might take
> proportionally longer to recover after a crash, too.)
>
>             regards, tom lane
>
>
Shane Wright
Technical Manager
eDigitalResearch.com
2 Berrywood Business Village
Hedge End
Hampshire
SO30 2UN
T +44 (0) 1489 772920
F +44 (0) 1489 772922
 
This message is sent in confidence for the addressee only.  The
contents are not to be disclosed to anyone other than the addressee. 
Unauthorised recipients must preserve this confidentiality and should
please advise the sender immediately of any error in transmission.
 
Any attachment(s) to this message has been checked for viruses, but
please rely on your own virus checker and procedures.