Re: random delays

On Fri, Dec 09, 2005 at 02:29:02PM +0100, Baldur Norddahl wrote:
> I noticed that sometimes we seem to have a problem with simple queries
> that take a long time to execute. For example "select * from config
> where key='abc'" which normally only takes a few milliseconds, but in
> some rare cases suddenly takes 10-30 seconds.
>
> It is a production system, so there are always other queries going on.
> But the server is not neccessarly loaded when this happens. I can't
> think of any reasons that there should be a lock on the tables in question.

Do you know there's a lock or is that just a guess?

> Is there a way to find out what the query is waiting for?

Query pg_locks in another session and look for rows where "granted"
is false; if you find any then look for who holds the conflicting
lock.  If you have stats_command_string enabled then you might be
able to query pg_stat_activity and identify the query that holds
the lock.

Do you see any pattern to the slow queries?  Do they always happen
around a certain time of day, or during or shortly after a certain
kind of activity (e.g., large inserts or routine database maintenace)?

--
Michael Fuhr