From: Steve Poe <spoe@sfnet.cc>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Table locking problems?
Date: Wed, 10 Aug 2005 06:49:07 +0000
Message-ID: <1123656547.13362.43.camel@amd64-laptop-spoe> (raw)
In-Reply-To: <52CFA236-FC43-4018-B399-1854240110FF@drivefaster.net>
References: <52CFA236-FC43-4018-B399-1854240110FF@drivefaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: spoe@sfnet.cc, fbsd@drivefaster.net
Subject: Re: Table locking problems?
In-Reply-To: <1123656547.13362.43.camel@amd64-laptop-spoe>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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