Обсуждение: Long (really long) queries and not sure how to diagnose

Hi Folks

We are running PostgreSQL 8.3.9 on Ubuntu 9.04 and yesterday the Postgresql log reported about 15 SQL queries that had taken between 30,000 and 50,000 seconds (not ms) to execute.

All (except one) of the queries are single Inserts to the same relatively simple table (no triggers or the like).

A colleague has suggested that it might be a deadlock but my understanding is that Postgresql identifies deadlocks and terminates one of the queries.

I would appreciate any suggestions of where to look and (more importantly) what to look for to figure this out.

Many thanks

Regards, Tony

Tony Day <tonyd@panztel.com> writes:
> We are running PostgreSQL 8.3.9 on Ubuntu 9.04 and yesterday the Postgresql
> log reported about 15 SQL queries that had taken between 30,000 and 50,000
> seconds (not ms) to execute.

> All (except one) of the queries are single Inserts to the same relatively
> simple table (no triggers or the like).

> A colleague has suggested that it might be a deadlock but my understanding
> is that Postgresql identifies deadlocks and terminates one of the queries.

> I would appreciate any suggestions of where to look and (more importantly)
> what to look for to figure this out.

Best guess from here is that some transaction took a write-preventing
lock on that table, and then sat on it for a really long time.  If this
were still going on, a look into pg_stat_activity and/or pg_locks would
quickly prove or refute that theory.  Since it's not, there's probably
not much you can do right now unless you have complete query logs to
study.  What I'd suggest is familiarizing yourself with those views
so you'll be better prepared to investigate next time.

            regards, tom lane

Hi Tom



Regards, Tony

Tony Day <tonyd@panztel.com> writes:
> I am not sure what you mean by "complete query logs" but I do have a 1.3G
> Postgresql log file from the day in question.
> Is there a particular way that a "write-preventing lock" will be logged that
> I can search for in that file?

Well, other than an explicit LOCK TABLE, it'd be some sort of DDL action
on the table.  CREATE INDEX, ALTER TABLE, that sort of thing.  I'd try
grepping the query log for the table name and then filtering out the
easy select/insert/update/delete stuff.

            regards, tom lane

Hi Tom