This sounds like a WAL segment recycling issue. I believe 8.0 should
relieve some of the stress. I've had this problem before and found
that increasing the number of check point segments has helped a
little. You still get the a wallop when this happens, increasing the
size should make that happen less often. The downside is each of the
files, segments, is 16meg.
On Tue, 26 Oct 2004 13:27:53 -0400, Brian Maguire <bmaguire@vantage.com> wrote:
> One observation that we made was right when the statements pile up there
> is a large increase in the number of disk reads. The entire issue lasts
> approx. 20 secs and then everything recovers. There will be a backlog
> of 300+ statements and then all a sudden it seems to get resolved.
>
> We though there might be locking, but noticed that there were not any
> queries in wait mode indicating that no statements were blocked by
> another statement's lock.
>
> Can anyone suggest what may be causing the pile up/locking like
> occurrences?
>
> Brian
>
> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Tuesday, October 26, 2004 4:46 AM
> To: Brian Maguire
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] what could cause inserts getting queued up and db
> locking??
>
> Brian Maguire wrote:
> >
> > What could cause the database to lock up and queue up all the
> > queries?
>
> You'll want to check the lock details (pg_locks: see "Monitoring
> Database Activity" in the reference manuals) and also what the system as
>
> a whole is doing (vmstat/iostat).
>
> I seem to recall some configuration of ext3 could cause bursts of
> activity with certain write patterns, but don't recall PG being affected
>
> by this.
>
> There have been discussions of context-switching issues with Xeons IIRC
> - don't know the details, so check the -performance/hackers archives for
>
> details.
>
> HTH
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>