Re: phantom long-running query (check_postgres.pl reports a long-running query, but the database logs don't show it)

On Πεμ 09 Φεβ 2012 02:32:37 Aleksey Tsalolikhin wrote:
> I got an alert from check_postgres.pl today on a long-running query on
> our production database, but our PostgreSQL 8.4.9 server log, which is
> configured to log queries over 5 seconds long
> ("log_min_duration_statement = 5000") does not show the query.
>
> check_postgres.pl showed:
> Date/Time: Wed Feb 8 08:41:36 PST 2012
> POSTGRES_QUERY_TIME WARNING: (host:xxx) longest query: 264s
> (database:xxx PID:xxx port:xxx address:xxx username:xxx)
>
> postgres log showed other long-running queries, but nothing over 48s.
>
> It's really interesting why the database server log does not show this
> query.
>
> Can you think of some scenario where check_postgres.pl would see a
> long-running query but it would not get logged by the database server?
>
> I checked the server log and there is nothing there for 08:41 at all.
> 08:40 and 08:42, yes, some queries over 5 secs, but nothing even close
> to 264 secs.
>
> I've added the verbose switch to my check_postgres.pl script so we'll
> have a record of what was the long-running query.
>

You should either look at the source of this perl script to see what it is
doing, or enable log_statement = 'all' in postgresql.conf and send the
postmaster the -HUP signal, and then watch the postgresql log for the queries
that the perl script is issueing. Then replay those queries by hand and
examine them.
Also you can always check pg_stat_activity table.

> Best,
> -at

--
Achilleas Mantzios
IT DEPT