Thread: what could cause inserts getting queued up and db locking??
Background Info: I have a table with a approx 2.5 million rows. The table often gets 200-300 inserts per second. We are see that the database(7.4.1 Red Hat Enterprise ED 4 way Xeon) will periodically lock up all of a sudden and force the database to queueup hundreds of queries. The database comes to complete halt until eventually it clears itself out. There is otherdatabase activity going on but the above activity represents 75% of the frequency of the queries. The table describedabove has few reads as we try to restrict reads. We have it set so it does one read every 20 seconds collectingall of the newest data in the last 20 seconds and moves it to a reporting table. What could cause the database to lock up and queue up all the queries?
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. 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 >
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