Re: Table locking problems?

From: Dan Harris
Subject: Re: Table locking problems?
Date: ,
Msg-id: 2764B744-4CDC-4F2C-99C4-2948D37A9D23@drivefaster.net
(view: Whole thread, Raw)
In response to: Re: Table locking problems?  (Steve Poe)
Responses: Re: Table locking problems?  (John A Meinel)
List: pgsql-performance

Tree view

Table locking problems?  (Dan Harris, )
 Re: Table locking problems?  ("Joshua D. Drake", )
  Re: Table locking problems?  (Tom Lane, )
   Re: Table locking problems?  (Dan Harris, )
 Re: Table locking problems?  (Michael Fuhr, )
 Re: Table locking problems?  (Steve Poe, )
  Re: Table locking problems?  (Dan Harris, )
   Re: Table locking problems?  (John A Meinel, )
    Re: Table locking problems?  (Dan Harris, )
     Re: Table locking problems?  (John A Meinel, )

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




pgsql-performance by date:

From: John A Meinel
Date:
Subject: Re: Table locking problems?
From: Tobias Brox
Date:
Subject: partial index regarded more expensive