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

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

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.

Thanks. We do have it set to 15 mb. I would think that 16 mb would not
make a big difference. Do you have any other ideas?

-----Original Message-----
From: Kevin Barnard [mailto:kevin.barnard@gmail.com]
Sent: Tuesday, October 26, 2004 2:32 PM
To: Brian Maguire
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] what could cause inserts getting queued up and db
locking??

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

Brian Maguire <bmaguire@vantage.com> wrote:

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.

In that case it's not a locking problem, but just a resource-saturation
problem. I'm wondering if you are maxing out your disk drives'
throughput.

Are the slowdowns correlated with checkpoints? (Watch to see if there
is a postmaster child process spawned for checkpointing when it
happens.) Fooling with checkpoint intervals might help some, though
I suspect the only real answer will be 8.0's background-writer feature.

regards, tom lane

On Tue, Oct 26, 2004 at 03:10:04PM -0400, Brian Maguire wrote:

Thanks. We do have it set to 15 mb. I would think that 16 mb would not
make a big difference. Do you have any other ideas?

Huh? No, you have it set to 15 *segments*, each of which is 16 MB long.
Maybe setting it higher will help you, but maybe it won't, depending on
wheter there's a checkpoint run when the system is in a somewhat idle
state.

Oh, you may also want to increase checkpoint_timeout, so that
checkpoints are run less frequently.

But then, checkpoints will be run less frequently and they will take
longer. If you do have idle or low-load periods, try to run a
checkpoint when they occur. Else you may need faster disks ...

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"This is a foot just waiting to be shot" (Andrew Dunstan)

Tom,
You hit the nail on the head with what we did. We did two things and it
made a world of difference.

We moved from RAID 5 SCSII drives to our EMC SAN RAID 10 and adjusted
the checkpoint segments from 15 to 30.

The bottleneck disappeared totally and actually have never seen better
performance.

Two questions:

What are the implications to further increasing the checkpoint so say
40?

Also how does 8.0's background-writer feature work and what are going to
benefits?

Brian

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, October 26, 2004 5:59 PM
To: Brian Maguire
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] what could cause inserts getting queued up and db
locking??

Brian Maguire <bmaguire@vantage.com> wrote:

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.

In that case it's not a locking problem, but just a resource-saturation
problem. I'm wondering if you are maxing out your disk drives'
throughput.

Are the slowdowns correlated with checkpoints? (Watch to see if there
is a postmaster child process spawned for checkpointing when it
happens.) Fooling with checkpoint intervals might help some, though
I suspect the only real answer will be 8.0's background-writer feature.

regards, tom lane

"Brian Maguire" <bmaguire@vantage.com> writes:

What are the implications to further increasing the checkpoint so say
40?

AFAIK the downsides are (a) more disk space eaten for pg_xlog,
(b) if you suffer a crash, it will take longer to recover (because
there'll be more uncheckpointed work to replay); (c) the checkpoint
itself could require more I/O because there's more pending write
activity.

Also how does 8.0's background-writer feature work and what are going to
benefits?

The idea of the bgwriter is to trickle out disk writes continuously
instead of having a big write storm at each checkpoint.

regards, tom lane