Message-ID: <4C911E58.6030605@2ndquadrant.com>
Date: Wed, 15 Sep 2010 15:28:24 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Tobias Brox <tobixen@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: locking issue on simple selects?
References: <AANLkTikh++7mouBb7cr2C9zMPKtRffVxDK0QFTf16HLo@mail.gmail.com>
	<4096.1284557987@sss.pgh.pa.us>
	<AANLkTikzssP3AKeQhM+tB_8tScRGs2iGSqTJJUg2w3DP@mail.gmail.com>
In-Reply-To: <AANLkTikzssP3AKeQhM+tB_8tScRGs2iGSqTJJUg2w3DP@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Tobias Brox wrote:
> I thought so, we have a logging script that logs the content of
> the pg_locks table, it didn't log anything interesting but it may be a
> problem with the script itself.  It does an inner join on
> pg_locks.relation = pg_class.oid but when I check now this join seems
> to remove most of the rows in the pg_locks table.

Some of the waits you might be running into will be things waiting on 
another transaction holding a lock to finish, which are probably wiped 
out by this approach.  There are some useful examples of lock views on 
the wiki:

http://wiki.postgresql.org/wiki/Lock_Monitoring
http://wiki.postgresql.org/wiki/Lock_dependency_information
http://wiki.postgresql.org/wiki/Find_Locks

And the idea you have of coverting the pg_class one to an outer join 
will help.

The other thing you should try is toggling on log_lock_waits and 
possibly reducing deadlock_timeout.  This will put a lot of the 
information you're trying to collect right into the logs.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book