pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTable locking problems?
10+ messages / 6 participants
[nested] [flat]
* Table locking problems?
@ 2005-08-09 18:04 Dan Harris <fbsd@drivefaster.net>
0 siblings, 3 replies; 10+ messages in thread
From: Dan Harris @ 2005-08-09 18:04 UTC (permalink / raw)
To: pgsql-performance
I thought I would send this to pg-performance since so many people
helped me with my speed issues recently. I was definitely IO-
bottlenecked.
Since then, I have installed 2 RAID arrays with 7 15k drives in them
in RAID 0+1 as well as add a new controller card with 512MB of cache
on it. I also created this new partition on the RAID as XFS instead
of ext3.
These changes have definitely improved performance, but I am now
finding some trouble with UPDATE or DELETE queries "hanging" and
never releasing their locks. As this happens, other statements queue
up behind it. It seems to occur at times of very high loads on the
box. Is my only option to kill the query ( which usually takes down
the whole postmaster with it! ouch ).
Could these locking issues be related to the other changes I made?
I'm really scared that this is related to choosing XFS, but I sure
hope not. How should I go about troubleshooting the "problem"
queries? They don't seem to be specific to a single table or single
database.
I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that
matters..
-Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 18:33 Joshua D. Drake <jd@commandprompt.com>
parent: Dan Harris <fbsd@drivefaster.net>
2 siblings, 1 reply; 10+ messages in thread
From: Joshua D. Drake @ 2005-08-09 18:33 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
> Could these locking issues be related to the other changes I made? I'm
> really scared that this is related to choosing XFS, but I sure hope
> not. How should I go about troubleshooting the "problem" queries?
> They don't seem to be specific to a single table or single database.
My experience is that when this type of thing happens it is typically
specific queries that cause the problem. If you turn on statement
logging you can get the exact queries and debug from there.
Here are some things to look for:
Is it a large table (and thus large indexes) that it is updating?
Is the query using indexes?
Is the query modifying ALOT of rows?
Of course there is also the RTFM of are you analyzing and vacuuming?
Sincerely,
Joshua D. Drake
>
> I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that matters..
>
>
> -Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 18:52 Michael Fuhr <mike@fuhr.org>
parent: Dan Harris <fbsd@drivefaster.net>
2 siblings, 0 replies; 10+ messages in thread
From: Michael Fuhr @ 2005-08-09 18:52 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
On Tue, Aug 09, 2005 at 12:04:11PM -0600, Dan Harris wrote:
> These changes have definitely improved performance, but I am now
> finding some trouble with UPDATE or DELETE queries "hanging" and
> never releasing their locks. As this happens, other statements queue
> up behind it.
Have you examined pg_locks to see if the UPDATE or DELETE is blocked
because of a lock another session holds?
Are you using foreign keys? When updating referencing rows, released
versions of PostgreSQL acquire a lock on the referenced row that can
hurt concurrency or cause deadlock (this will be improved in 8.1).
--
Michael Fuhr
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 19:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2005-08-09 19:08 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Dan Harris <fbsd@drivefaster.net>; pgsql-performance
"Joshua D. Drake" <jd@commandprompt.com> writes:
> My experience is that when this type of thing happens it is typically
> specific queries that cause the problem. If you turn on statement
> logging you can get the exact queries and debug from there.
> Here are some things to look for:
> Is it a large table (and thus large indexes) that it is updating?
> Is the query using indexes?
> Is the query modifying ALOT of rows?
Another thing to look at is foreign keys. Dan could be running into
problems with an update on one side of an FK being blocked by locks
on the associated rows on the other side.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 20:42 Dan Harris <fbsd@drivefaster.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Dan Harris @ 2005-08-09 20:42 UTC (permalink / raw)
To: pgsql-performance
On Aug 9, 2005, at 1:08 PM, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
>> My experience is that when this type of thing happens it is typically
>> specific queries that cause the problem. If you turn on statement
>> logging you can get the exact queries and debug from there.
>>
>
>
>> Here are some things to look for:
>>
>
>
>> Is it a large table (and thus large indexes) that it is updating?
>> Is the query using indexes?
>> Is the query modifying ALOT of rows?
>>
>
> Another thing to look at is foreign keys. Dan could be running into
> problems with an update on one side of an FK being blocked by locks
> on the associated rows on the other side.
>
> regards, tom lane
>
Tom, Steve, Josh:
Thank you for your ideas. The updates are only on a single table, no
joins. I had stats collection turned off. I have turned that on
again so that I can try and catch one while the problem is
occurring. The last table it did this on was about 3 million
records. 4 single-column indexes on it.
The problem I had with statement logging is that if the query never
finishes, it doesn't get logged as far as I can tell. So everything
that did get logged was normal and would run with no isses in psql by
copy and pasting it. The rows updated will certainly vary by query.
I really need to "catch it in the act" with stats collection on so I
can get the query from pg_stat_activity. Once I get it, I will play
with explains and see if I can reproduce it outside the wild.
Thanks again for your help.
-Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 20:51 Dan Harris <fbsd@drivefaster.net>
parent: Steve Poe <spoe@sfnet.cc>
0 siblings, 1 reply; 10+ messages in thread
From: Dan Harris @ 2005-08-09 20:51 UTC (permalink / raw)
To: pgsql-performance
On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
> Dan,
>
> Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
> clarification, since RAID 0 is still a single-point of failure even if
> RAID1 is on top of RAID0.
Well, you tell me if I stated incorrectly. There are two raid
enclosures with 7 drives in each. Each is on its own bus on a dual-
channel controller. Each box has a stripe across its drives and the
enclosures are mirrors of each other. I understand the controller
could be a single point of failure, but I'm not sure I understand
your concern about the RAID structure itself.
>
> How many users are connected when your update / delete queries are
> hanging? Have you done an analyze verbose on those queries?
Most of the traffic is from programs we run to do analysis of the
data and managing changes. At the time I noticed it this morning,
there were 10 connections open to the database. That rarely goes
above 20 concurrent. As I said in my other response, I believe that
the log will only contain the query at the point the query finishes,
so if it never finishes...
>
> Have you made changes to the postgresql.conf? kernel.vm settings? IO
> scheduler?
I set shmmax appropriately for my shared_buffers setting, but that's
the only kernel tweak.
>
> If you're not doing so already, you may consider running sar
> (iostat) to
> monitor when the hanging occurs if their is a memory / IO bottleneck
> somewhere.
>
I will try that. Thanks
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 21:51 John A Meinel <john@arbash-meinel.com>
parent: Dan Harris <fbsd@drivefaster.net>
0 siblings, 1 reply; 10+ messages in thread
From: John A Meinel @ 2005-08-09 21:51 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan Harris wrote:
>
> On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
>
>> Dan,
>>
>> Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
>> clarification, since RAID 0 is still a single-point of failure even if
>> RAID1 is on top of RAID0.
>
>
> Well, you tell me if I stated incorrectly. There are two raid
> enclosures with 7 drives in each. Each is on its own bus on a dual-
> channel controller. Each box has a stripe across its drives and the
> enclosures are mirrors of each other. I understand the controller
> could be a single point of failure, but I'm not sure I understand your
> concern about the RAID structure itself.
In this configuration, if you have a drive fail on both controllers, the
entire RAID dies. Lets label them A1-7, B1-7, because you stripe within
a set, if a single one of A dies, and a single one of B dies, you have
lost your entire mirror.
The correct way of doing it, is to have A1 be a mirror of B1, and then
stripe above that. Since you are using 2 7-disk enclosures, I'm not sure
how you can do it well, since it is not an even number of disks. Though
if you are using software RAID, there should be no problem.
The difference is that in this scenario, *all* of the A drives can die,
and you haven't lost any data. The only thing you can't lose is a
matched pair (eg losing both A1 and B1 will cause complete data loss)
I believe the correct notation for this last form is RAID 1 + 0 (RAID10)
since you have a set of RAID1 drives, with a RAID0 on-top of them.
>
>>
>> How many users are connected when your update / delete queries are
>> hanging? Have you done an analyze verbose on those queries?
>
>
> Most of the traffic is from programs we run to do analysis of the data
> and managing changes. At the time I noticed it this morning, there
> were 10 connections open to the database. That rarely goes above 20
> concurrent. As I said in my other response, I believe that the log
> will only contain the query at the point the query finishes, so if it
> never finishes...
>
>>
>> Have you made changes to the postgresql.conf? kernel.vm settings? IO
>> scheduler?
>
>
> I set shmmax appropriately for my shared_buffers setting, but that's
> the only kernel tweak.
>
>>
>> If you're not doing so already, you may consider running sar (iostat) to
>> monitor when the hanging occurs if their is a memory / IO bottleneck
>> somewhere.
>>
>
> I will try that. Thanks
>
When you discover that an update is hanging, can you get into the
database, and see what locks currently exist? (SELECT * FROM pg_locks)
That might help you figure out what is being locked and possibly
preventing your updates.
It is also possible that your UPDATE query is trying to do something
funny (someone just recently was talking about an UPDATE that wanted to
do a hash join against 12M rows). Which probably meant that it had to
spill to disk, where a merge join would have worked better.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 22:05 Dan Harris <fbsd@drivefaster.net>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 1 reply; 10+ messages in thread
From: Dan Harris @ 2005-08-09 22:05 UTC (permalink / raw)
To: pgsql-performance
On Aug 9, 2005, at 3:51 PM, John A Meinel wrote:
> Dan Harris wrote:
>
>> On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
>>
>>> Dan,
>>>
>>> Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
>>> clarification, since RAID 0 is still a single-point of failure
>>> even if
>>> RAID1 is on top of RAID0.
>>>
>> Well, you tell me if I stated incorrectly. There are two raid
>> enclosures with 7 drives in each. Each is on its own bus on a
>> dual- channel controller. Each box has a stripe across its drives
>> and the enclosures are mirrors of each other. I understand the
>> controller could be a single point of failure, but I'm not sure I
>> understand your concern about the RAID structure itself.
>>
>
> In this configuration, if you have a drive fail on both
> controllers, the entire RAID dies. Lets label them A1-7, B1-7,
> because you stripe within a set, if a single one of A dies, and a
> single one of B dies, you have lost your entire mirror.
>
> The correct way of doing it, is to have A1 be a mirror of B1, and
> then stripe above that. Since you are using 2 7-disk enclosures,
> I'm not sure how you can do it well, since it is not an even number
> of disks. Though if you are using software RAID, there should be no
> problem.
>
> The difference is that in this scenario, *all* of the A drives can
> die, and you haven't lost any data. The only thing you can't lose
> is a matched pair (eg losing both A1 and B1 will cause complete
> data loss)
>
> I believe the correct notation for this last form is RAID 1 + 0
> (RAID10) since you have a set of RAID1 drives, with a RAID0 on-top
> of them.
>
I have read up on the difference now. I don't understand why it's a
"single point of failure". Technically any array could be a "single
point" depending on your level of abstraction. In retrospect, I
probably should have gone 8 drives in each and used RAID 10 instead
for the better fault-tolerance, but it's online now and will require
some planning to see if I want to reconfigure that in the future. I
wish HP's engineer would have promoted that method instead of 0+1..
-Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-09 22:30 John A Meinel <john@arbash-meinel.com>
parent: Dan Harris <fbsd@drivefaster.net>
0 siblings, 0 replies; 10+ messages in thread
From: John A Meinel @ 2005-08-09 22:30 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan Harris wrote:
>
> On Aug 9, 2005, at 3:51 PM, John A Meinel wrote:
>
>> Dan Harris wrote:
>>
>>> On Aug 10, 2005, at 12:49 AM, Steve Poe wrote:
>>>
>>>> Dan,
>>>>
>>>> Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
>>>> clarification, since RAID 0 is still a single-point of failure even if
>>>> RAID1 is on top of RAID0.
>>>>
>>> Well, you tell me if I stated incorrectly. There are two raid
>>> enclosures with 7 drives in each. Each is on its own bus on a dual-
>>> channel controller. Each box has a stripe across its drives and
>>> the enclosures are mirrors of each other. I understand the
>>> controller could be a single point of failure, but I'm not sure I
>>> understand your concern about the RAID structure itself.
>>>
>>
>> In this configuration, if you have a drive fail on both controllers,
>> the entire RAID dies. Lets label them A1-7, B1-7, because you stripe
>> within a set, if a single one of A dies, and a single one of B dies,
>> you have lost your entire mirror.
>>
>> The correct way of doing it, is to have A1 be a mirror of B1, and
>> then stripe above that. Since you are using 2 7-disk enclosures, I'm
>> not sure how you can do it well, since it is not an even number of
>> disks. Though if you are using software RAID, there should be no
>> problem.
>>
>> The difference is that in this scenario, *all* of the A drives can
>> die, and you haven't lost any data. The only thing you can't lose is
>> a matched pair (eg losing both A1 and B1 will cause complete data loss)
>>
>> I believe the correct notation for this last form is RAID 1 + 0
>> (RAID10) since you have a set of RAID1 drives, with a RAID0 on-top of
>> them.
>>
>
> I have read up on the difference now. I don't understand why it's a
> "single point of failure". Technically any array could be a "single
> point" depending on your level of abstraction. In retrospect, I
> probably should have gone 8 drives in each and used RAID 10 instead for
> the better fault-tolerance, but it's online now and will require some
> planning to see if I want to reconfigure that in the future. I wish
> HP's engineer would have promoted that method instead of 0+1..
I wouldn't say that it is a single point of failure, but I *can* say
that it is much more likely to fail. (2 drives rather than on average n
drives)
If your devices will hold 8 drives, you could simply do 1 8-drive, and
one 6-drive. And then do RAID1 with pairs, and RAID0 across the
resultant 7 RAID1 sets.
I'm really surprised that someone promoted RAID 0+1 over RAID10. I think
I've heard that there is a possible slight performance improvement, but
really the failure mode makes it a poor tradeoff.
John
=:->
>
> -Dan
>
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Table locking problems?
@ 2005-08-10 06:49 Steve Poe <spoe@sfnet.cc>
parent: Dan Harris <fbsd@drivefaster.net>
2 siblings, 1 reply; 10+ messages in thread
From: Steve Poe @ 2005-08-10 06:49 UTC (permalink / raw)
To: Dan Harris <fbsd@drivefaster.net>; +Cc: pgsql-performance
Dan,
Do you mean you did RAID 1 + 0 (RAID 10) or RAID 0 + 1? Just a
clarification, since RAID 0 is still a single-point of failure even if
RAID1 is on top of RAID0.
How many users are connected when your update / delete queries are
hanging? Have you done an analyze verbose on those queries?
Have you made changes to the postgresql.conf? kernel.vm settings? IO
scheduler?
If you're not doing so already, you may consider running sar (iostat) to
monitor when the hanging occurs if their is a memory / IO bottleneck
somewhere.
Good luck.
Steve Poe
On Tue, 2005-08-09 at 12:04 -0600, Dan Harris wrote:
> I thought I would send this to pg-performance since so many people
> helped me with my speed issues recently. I was definitely IO-
> bottlenecked.
>
> Since then, I have installed 2 RAID arrays with 7 15k drives in them
> in RAID 0+1 as well as add a new controller card with 512MB of cache
> on it. I also created this new partition on the RAID as XFS instead
> of ext3.
>
> These changes have definitely improved performance, but I am now
> finding some trouble with UPDATE or DELETE queries "hanging" and
> never releasing their locks. As this happens, other statements queue
> up behind it. It seems to occur at times of very high loads on the
> box. Is my only option to kill the query ( which usually takes down
> the whole postmaster with it! ouch ).
>
> Could these locking issues be related to the other changes I made?
> I'm really scared that this is related to choosing XFS, but I sure
> hope not. How should I go about troubleshooting the "problem"
> queries? They don't seem to be specific to a single table or single
> database.
>
> I'm running 8.0.1 on kernel 2.6.12-3 on 64-bit Opterons if that
> matters..
>
>
> -Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-08-10 06:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-09 18:04 Table locking problems? Dan Harris <fbsd@drivefaster.net>
2005-08-09 18:33 ` Joshua D. Drake <jd@commandprompt.com>
2005-08-09 19:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-09 20:42 ` Dan Harris <fbsd@drivefaster.net>
2005-08-09 18:52 ` Michael Fuhr <mike@fuhr.org>
2005-08-10 06:49 ` Steve Poe <spoe@sfnet.cc>
2005-08-09 20:51 ` Dan Harris <fbsd@drivefaster.net>
2005-08-09 21:51 ` John A Meinel <john@arbash-meinel.com>
2005-08-09 22:05 ` Dan Harris <fbsd@drivefaster.net>
2005-08-09 22:30 ` John A Meinel <john@arbash-meinel.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox