From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Cc: Bryce Nesbitt <bryce2@obviously.com>
Subject: Re: Postgres entering zombie state once a week in production evnvironment
Date: Tue, 14 Apr 2009 09:26:26 +0200
Message-ID: <200904141026.27567.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <49E42C72.9070902@obviously.com>
References: <49E42C72.9070902@obviously.com>
Στις Tuesday 14 April 2009 09:25:54 ο/η Bryce Nesbitt έγραψε:
> We have a medium scale installation of Postgres 8.3 that is freezing
> about once a week. I'm looking for any hints on how to diagnose the
> situation, as nothing is logged.
>
> The system is matched pair of Sunfire servers, running Debian Etch with
> a 2.6.18-6-amd64 kernel, PostgreSQL 8.3.4, and DRBD 8.0.13.
>
> During a failed state, pg_stat_activity will show hundreds of statements
> pending. query_start will show the statements arriving at a normal rate
> (a few per second), but clearly they never complete. The bulk of these
> statement are a simple select that starts each web session, a statement
> that generally completes in tenths of milliseconds. Restarting postgres
> restores normal operation, at the loss of all chance of figuring out
> what was wrong.
>
> postgresql.conf has customized:
> log_destination = 'syslog'
> log_min_error_statement = error
> log_min_duration_statement = 5000
>
> Where can I go from here? I'm turning on log_checkpoints now, though
> all I see is "postgres[14412]: [4-1] LOG: checkpoint starting: time"
> with no actual time printed.
>
>
Did you check for locks?
SELECT * from pg_locks;
SELECT l.locktype,c.relname,l.pid,l.mode from pg_locks l,pg_class c where l.relation=c.oid ;
Were you able to issue other select commands during the problem? Were you able to start psql and do some simple command?
Have you ruled out the possibility of the application being locked for other reasons? (limits, network failure,etc....)
--
Achilleas Mantzios
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com, bryce2@obviously.com
Subject: Re: Postgres entering zombie state once a week in production evnvironment
In-Reply-To: <200904141026.27567.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox