Message-ID: <42F8F717.6030106@commandprompt.com>
Date: Tue, 09 Aug 2005 11:33:59 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
Organization: Command Prompt, Inc.
User-Agent: Mozilla Thunderbird 1.0.6-1.1.fc4 (X11/20050720)
MIME-Version: 1.0
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Table locking problems?
References: <52CFA236-FC43-4018-B399-1854240110FF@drivefaster.net>
In-Reply-To: <52CFA236-FC43-4018-B399-1854240110FF@drivefaster.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> 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/