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

Hi


Thanks to you all for your help!  I'm continually impressed with how
responsive and knowledgeable y'all are!


To clarify; it's an IDE drive with a reiserfs filesystem.  DMA is
definately enabled, sequential reads pull 35Mb/sec sustained.


The I/O caused by the checkpoint just seems to be too much while other
transactions are running.  As it's a managed server at our ISP
throwing more hardware at it isn't an option at the moment
unfortunately, so I think I'm left with optimising the app to reduce
the number of INSERTs/UPDATEs.


Is what Iain said correct about [committed] transactions only being
written to WAL, and actual table data files are only updated at
checkpoint?


I guess really it's something I hadn't thought of - in effect, the
database is able to handle _bursts_ of high load, but sustaining it is
hard (because checkpoint happens sooner or later).


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?  Some way that's easier than -z `ps fax |
grep post | grep checkpoint` that is ;)


Cheers


Shane





On 3 Feb 2004, at 22:35, Shane Wright wrote:


<excerpt>Hi,


I'm running a reasonable sized (~30Gb) 7.3.4 database on Linux and I'm
getting some weird performance at times.


When the db is under medium-heavy load, it periodically spawns a
'checkpoint subprocess' which runs for between 15 seconds and a
minute.  Ok, fair enough, the only problem is the whole box becomes
pretty much unresponsive during this time - from what I can gather
it's because it writes out roughly 1Mb (vmstat says ~1034 blocks) per
second until its done.


Other processes can continue to run (e.g. vmstat) but other things do
not (other queries, mostly running 'ps fax', etc).  So everything gets
stacked up till the checkpoint finishes and all is well again, untill
the next time...


This only really happens under medium/high load, but doesn't seem
related to the length/complexity of transactions done.


The box isn't doing a lot else at the same time - most queries some in
from separate web server boxes.


The disks, although IDE, can definately handle more than 1Mb/sec -
even with multiple concurrent writes.  The box is powerful (2.6Ghz
Xeon, 2Gb RAM).  Its a clean compile from source of 7.3.4, although I
can't really upgrade to 7.4.x at this time as I can't afford the 18
hours downtime to dump/restore the database.  Fsync is on.  Most other
settings at their defaults.


I've looked at the documentation and various bits about adjusting
checkpoint segments and timings - but it seems reducing
segments/timeout is implied to be bad, but it seems to me that
increasing either will just make the same thing happen less often but
more severely.


If it makes any odds, this seems to happen much more often when doing
bulk UPDATEs and INSERTs - athough these are in transactions grouping
them together - and they don't affect the same tables as other queries
that still get stalled (no lock contention causing the problem).


What am I missing?  I'm sure I'm missing something blatantly obvious,
but as it's only really happening on production systems (only place
with the load and the volume of data) I'm loathe to experiment.


Any help appreciated,


Cheers,


Shane


---------------------------(end of
broadcast)---------------------------

TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org



</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>

Hi

Thanks to you all for your help!  I'm continually impressed with how
responsive and knowledgeable y'all are!

To clarify; it's an IDE drive with a reiserfs filesystem.  DMA is
definately enabled, sequential reads pull 35Mb/sec sustained.

The I/O caused by the checkpoint just seems to be too much while other
transactions are running.  As it's a managed server at our ISP throwing
more hardware at it isn't an option at the moment unfortunately, so I
think I'm left with optimising the app to reduce the number of
INSERTs/UPDATEs.

Is what Iain said correct about [committed] transactions only being
written to WAL, and actual table data files are only updated at
checkpoint?

I guess really it's something I hadn't thought of - in effect, the
database is able to handle _bursts_ of high load, but sustaining it is
hard (because checkpoint happens sooner or later).

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?  Some way that's easier than -z `ps fax |
grep post | grep checkpoint` that is ;)

Cheers

Shane




On 3 Feb 2004, at 22:35, Shane Wright wrote:

> Hi,
>
> I'm running a reasonable sized (~30Gb) 7.3.4 database on Linux and I'm
> getting some weird performance at times.
>
> When the db is under medium-heavy load, it periodically spawns a
> 'checkpoint subprocess' which runs for between 15 seconds and a
> minute.  Ok, fair enough, the only problem is the whole box becomes
> pretty much unresponsive during this time - from what I can gather
> it's because it writes out roughly 1Mb (vmstat says ~1034 blocks) per
> second until its done.
>
> Other processes can continue to run (e.g. vmstat) but other things do
> not (other queries, mostly running 'ps fax', etc).  So everything gets
> stacked up till the checkpoint finishes and all is well again, untill
> the next time...
>
> This only really happens under medium/high load, but doesn't seem
> related to the length/complexity of transactions done.
>
> The box isn't doing a lot else at the same time - most queries some in
> from separate web server boxes.
>
> The disks, although IDE, can definately handle more than 1Mb/sec -
> even with multiple concurrent writes.  The box is powerful (2.6Ghz
> Xeon, 2Gb RAM).  Its a clean compile from source of 7.3.4, although I
> can't really upgrade to 7.4.x at this time as I can't afford the 18
> hours downtime to dump/restore the database.  Fsync is on.  Most other
> settings at their defaults.
>
> I've looked at the documentation and various bits about adjusting
> checkpoint segments and timings - but it seems reducing
> segments/timeout is implied to be bad, but it seems to me that
> increasing either will just make the same thing happen less often but
> more severely.
>
> If it makes any odds, this seems to happen much more often when doing
> bulk UPDATEs and INSERTs - athough these are in transactions grouping
> them together - and they don't affect the same tables as other queries
> that still get stalled (no lock contention causing the problem).
>
> What am I missing?  I'm sure I'm missing something blatantly obvious,
> but as it's only really happening on production systems (only place
> with the load and the volume of data) I'm loathe to experiment.
>
> Any help appreciated,
>
> Cheers,
>
> Shane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
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.