From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: Postgres entering zombie state once a week in production
	evnvironment
Date: Tue, 14 Apr 2009 09:26:26 +0200
User-Agent: KMail/1.9.7
Cc: Bryce Nesbitt <bryce2@obviously.com>
References: <49E42C72.9070902@obviously.com>
In-Reply-To: <49E42C72.9070902@obviously.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200904141026.27567.achill@matrix.gatewaynet.com>

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 14 April 2009 09:25:54 =CE=BF/=CE=B7 Bryce=
 Nesbitt =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> We have a medium scale installation of Postgres 8.3 that is freezing=20
> about once a week.  I'm looking for any hints on how to diagnose the=20
> situation, as nothing is logged.
>=20
> The system is matched pair of Sunfire servers, running Debian Etch with=20
> a 2.6.18-6-amd64 kernel, PostgreSQL 8.3.4, and DRBD 8.0.13.
>=20
> During a failed state, pg_stat_activity will show hundreds of statements=
=20
> pending.  query_start will show the statements arriving at a normal rate=
=20
> (a few per second), but clearly they never complete.  The bulk of these=20
> statement are a simple select that starts each web session, a statement=20
> that generally completes in tenths of milliseconds.  Restarting postgres=
=20
> restores normal operation, at the loss of all chance of figuring out=20
> what was wrong.
>=20
> postgresql.conf has customized:
> log_destination =3D 'syslog'
> log_min_error_statement =3D error
> log_min_duration_statement =3D 5000
>=20
> Where can I go from here?  I'm turning on log_checkpoints now, though=20
> all I see is "postgres[14412]: [4-1] LOG:  checkpoint starting: time"=20
> with no actual time printed.
>=20
>=20

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=
=2Erelation=3Dc.oid ;
Were you able to issue other select commands during the problem? Were you a=
ble to start psql and do some simple command?
Have you ruled out the possibility of the application being locked for othe=
r reasons? (limits, network failure,etc....)


=2D-=20
Achilleas Mantzios