pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres entering zombie state once a week in production evnvironment
6+ messages / 3 participants
[nested] [flat]
* Postgres entering zombie state once a week in production evnvironment
@ 2009-04-14 06:25 Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:26 ` Re: Postgres entering zombie state once a week in production evnvironment Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-04-14 07:29 ` Re: Postgres entering zombie state once a week in production evnvironment Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Bryce Nesbitt @ 2009-04-14 06:25 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres entering zombie state once a week in production evnvironment
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
@ 2009-04-14 07:26 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-04-14 20:07 ` Re: Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2009-04-14 07:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: Bryce Nesbitt <bryce2@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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres entering zombie state once a week in production evnvironment
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:26 ` Re: Postgres entering zombie state once a week in production evnvironment Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2009-04-14 20:07 ` Bryce Nesbitt <bryce2@obviously.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bryce Nesbitt @ 2009-04-14 20:07 UTC (permalink / raw)
To: pgsql-sql; +Cc: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Thanks for the thoughts on what to check. Unfortunately, the priority
of the people responding to the incidents has been to get the system
live again. I will add these items to a list that, hopefully, will be
run through prior to restarting Postgres.
Achilleas Mantzios wrote:
> 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....
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres entering zombie state once a week in production evnvironment
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
@ 2009-04-14 07:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-04-14 20:59 ` Re: Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
1 sibling, 1 reply; 6+ messages in thread
From: Scott Marlowe @ 2009-04-14 07:29 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql
On Tue, Apr 14, 2009 at 12:25 AM, Bryce Nesbitt <bryce2@obviously.com> wrote:
> 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.
What does pg_locks say during this time? Specifically about locks
that aren't granted?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres entering zombie state once a week in production evnvironment
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:29 ` Re: Postgres entering zombie state once a week in production evnvironment Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-04-14 20:59 ` Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 21:05 ` Re: Postgres entering zombie state once a week in production evnvironment Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Bryce Nesbitt @ 2009-04-14 20:59 UTC (permalink / raw)
To: pgsql-sql
Scott Marlowe wrote:
> What does pg_locks say during this time? Specifically about locks
> that aren't granted?
I don't know, yet. Though these events go for 15-30 minutes before
postgres restart, and no deadlocks are detected, so I don't think it is
locks.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Postgres entering zombie state once a week in production evnvironment
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:29 ` Re: Postgres entering zombie state once a week in production evnvironment Scott Marlowe <scott.marlowe@gmail.com>
2009-04-14 20:59 ` Re: Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
@ 2009-04-14 21:05 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2009-04-14 21:05 UTC (permalink / raw)
To: Bryce Nesbitt <bryce2@obviously.com>; +Cc: pgsql-sql
On Tue, Apr 14, 2009 at 2:59 PM, Bryce Nesbitt <bryce2@obviously.com> wrote:
> Scott Marlowe wrote:
>>
>> What does pg_locks say during this time? Specifically about locks
>> that aren't granted?
>
> I don't know, yet. Though these events go for 15-30 minutes before postgres
> restart, and no deadlocks are detected, so I don't think it is locks.
Ummm, deadlocks <> locks blocking other queries.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-04-14 21:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-14 06:25 Postgres entering zombie state once a week in production evnvironment Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:26 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2009-04-14 20:07 ` Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 07:29 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-04-14 20:59 ` Bryce Nesbitt <bryce2@obviously.com>
2009-04-14 21:05 ` Scott Marlowe <scott.marlowe@gmail.com>
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