pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDB cluster hanging
7+ messages / 5 participants
[nested] [flat]
* DB cluster hanging
@ 2005-10-12 14:15 Nigel Bishop <Nigel.Bishop@ioko.com>
0 siblings, 0 replies; 7+ messages in thread
From: Nigel Bishop @ 2005-10-12 14:15 UTC (permalink / raw)
To: pgsql-admin-owner@postgresql.org
Hi,
I'm hoping that someone will be able to answer this query:
Last night at 3am our Postgresql DB cluster hung. At the time data was
being loaded. The parameter log_statement_stats in the postgresql.conf
file was set to true. This was churning out data into the logfile which
was switching every 10Mb. Eventually the partition where the logfiles
are written too filled up - fair enough - this had been going on since
about 5.30pm the previous evening and the logfiles were being generated
at the rate of 4/5 a minute. The partition was cleared of old logs and
I expected the DB to spring in to life, but no it just sat there. I
could not connect with psql or pg_ctl to shutdown the cluster.
Eventually I had to issue a kill -9 on the postmaster, set
log_statement_stats to false and restarted the cluster, It recovered
itself and the data load carried on.
My question is, is this normal behaviour when the logfile destination
fills up? There was nothing in the logfile being used at the time of
the hang, just stats data.
PG version 8.0.3 with archived WAL logs enabled
O/S version RH ES4 with 2 CPUs & 2Gb RAM
Thanks very much for any input.
Regards,
Nigel Bishop
Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
^ permalink raw reply [nested|flat] 7+ messages in thread
* DB Cluster hanging
@ 2005-10-13 15:22 Nigel Bishop <nigel.bishop@gmail.com>
2005-10-13 15:48 ` Re: DB Cluster hanging codeWarrior <gpatnude@hotmail.com>
2005-10-13 16:12 ` Re: DB Cluster hanging Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 7+ messages in thread
From: Nigel Bishop @ 2005-10-13 15:22 UTC (permalink / raw)
To: pgsql-admin
Hi,
I'm hoping that someone will be able to answer this query:
Last night at 3am our Postgresql DB cluster hung. At the time data
was being loaded. The parameter log_statement_stats in the
postgresql.conf file was set to true. This was churning out data into
the logfile which was switching every 10Mb. Eventually the partition
where the logfiles are written to filled up – fair enough – this had
been going on since about 5.30pm the previous evening and the logfiles
were being generated at the rate of 4/5 a minute. The partition was
cleared of old logs and I expected the DB to spring in to life, but no
it just sat there. I could not connect with psql or pg_ctl to
shutdown the cluster.
Eventually I had to issue a kill -9 on the postmaster, set
log_statement_stats to false and restarted the cluster, It recovered
itself and the data load carried on.
My question is, is this normal behaviour when the logfile destination
fills up? There was nothing in the logfile being used at the time of
the hang, just stats data.
PG version 8.0.3 with archived WAL logs enabled
O/S version RH ES4 with 2 CPUs & 2Gb RAM
Thanks very much for any input.
Regards,
Nigel Bishop
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DB Cluster hanging
2005-10-13 15:22 DB Cluster hanging Nigel Bishop <nigel.bishop@gmail.com>
@ 2005-10-13 15:48 ` codeWarrior <gpatnude@hotmail.com>
2005-10-13 16:31 ` Re: DB Cluster hanging Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 1 reply; 7+ messages in thread
From: codeWarrior @ 2005-10-13 15:48 UTC (permalink / raw)
To: pgsql-admin
Are you possibly running logrotate on the postgreSQL server logs ? Logrotate
is usually set to signal (SIGHUP) the log owner (in this case -- postgreSQL)
after rotating the log file...
You normally can't just delete the logfiles and expect postgreSQL to
continue wherever you left it... you normally need to pg_ctl reload or
pg_ctl restart after dinking with the log files...
"Nigel Bishop" <nigel.bishop@gmail.com> wrote in message
news:7e974e490510130822q3a853039n4c96991179deae5a@mail.gmail.com...
> Hi,
>
> I'm hoping that someone will be able to answer this query:
>
> Last night at 3am our Postgresql DB cluster hung. At the time data
> was being loaded. The parameter log_statement_stats in the
> postgresql.conf file was set to true. This was churning out data into
> the logfile which was switching every 10Mb. Eventually the partition
> where the logfiles are written to filled up fair enough this had
> been going on since about 5.30pm the previous evening and the logfiles
> were being generated at the rate of 4/5 a minute. The partition was
> cleared of old logs and I expected the DB to spring in to life, but no
> it just sat there. I could not connect with psql or pg_ctl to
> shutdown the cluster.
>
> Eventually I had to issue a kill -9 on the postmaster, set
> log_statement_stats to false and restarted the cluster, It recovered
> itself and the data load carried on.
>
> My question is, is this normal behaviour when the logfile destination
> fills up? There was nothing in the logfile being used at the time of
> the hang, just stats data.
>
> PG version 8.0.3 with archived WAL logs enabled
>
> O/S version RH ES4 with 2 CPUs & 2Gb RAM
>
> Thanks very much for any input.
>
>
> Regards,
>
> Nigel Bishop
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DB Cluster hanging
2005-10-13 15:22 DB Cluster hanging Nigel Bishop <nigel.bishop@gmail.com>
2005-10-13 15:48 ` Re: DB Cluster hanging codeWarrior <gpatnude@hotmail.com>
@ 2005-10-13 16:31 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 7+ messages in thread
From: Scott Marlowe @ 2005-10-13 16:31 UTC (permalink / raw)
To: codeWarrior <gpatnude@hotmail.com>; +Cc: pgsql-admin
If you can't / won't / don't use syslog and want your logs rotated
anyway, look at apache's log rotator. It works a charm, and rotates
every night at midnight the way I use it. Quite simple to set up and
very reliable.
On Thu, 2005-10-13 at 10:48, codeWarrior wrote:
> Are you possibly running logrotate on the postgreSQL server logs ? Logrotate
> is usually set to signal (SIGHUP) the log owner (in this case -- postgreSQL)
> after rotating the log file...
>
> You normally can't just delete the logfiles and expect postgreSQL to
> continue wherever you left it... you normally need to pg_ctl reload or
> pg_ctl restart after dinking with the log files...
>
> "Nigel Bishop" <nigel.bishop@gmail.com> wrote in message
> news:7e974e490510130822q3a853039n4c96991179deae5a@mail.gmail.com...
> > Hi,
> >
> > I'm hoping that someone will be able to answer this query:
> >
> > Last night at 3am our Postgresql DB cluster hung. At the time data
> > was being loaded. The parameter log_statement_stats in the
> > postgresql.conf file was set to true. This was churning out data into
> > the logfile which was switching every 10Mb. Eventually the partition
> > where the logfiles are written to filled up fair enough this had
> > been going on since about 5.30pm the previous evening and the logfiles
> > were being generated at the rate of 4/5 a minute. The partition was
> > cleared of old logs and I expected the DB to spring in to life, but no
> > it just sat there. I could not connect with psql or pg_ctl to
> > shutdown the cluster.
> >
> > Eventually I had to issue a kill -9 on the postmaster, set
> > log_statement_stats to false and restarted the cluster, It recovered
> > itself and the data load carried on.
> >
> > My question is, is this normal behaviour when the logfile destination
> > fills up? There was nothing in the logfile being used at the time of
> > the hang, just stats data.
> >
> > PG version 8.0.3 with archived WAL logs enabled
> >
> > O/S version RH ES4 with 2 CPUs & 2Gb RAM
> >
> > Thanks very much for any input.
> >
> >
> > Regards,
> >
> > Nigel Bishop
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DB Cluster hanging
2005-10-13 15:22 DB Cluster hanging Nigel Bishop <nigel.bishop@gmail.com>
@ 2005-10-13 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-10-13 16:12 UTC (permalink / raw)
To: Nigel Bishop <nigel.bishop@gmail.com>; +Cc: pgsql-admin
Nigel Bishop <nigel.bishop@gmail.com> writes:
> Last night at 3am our Postgresql DB cluster hung. At the time data
> was being loaded. The parameter log_statement_stats in the
> postgresql.conf file was set to true. This was churning out data into
> the logfile which was switching every 10Mb. Eventually the partition
> where the logfiles are written to filled up fair enough this had
> been going on since about 5.30pm the previous evening and the logfiles
> were being generated at the rate of 4/5 a minute. The partition was
> cleared of old logs and I expected the DB to spring in to life, but no
> it just sat there. I could not connect with psql or pg_ctl to
> shutdown the cluster.
How was the logging being done exactly? syslog? redirect_stderr?
If redirect_stderr, this could well be a PG bug --- I kinda doubt
anyone has tested that scenario.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DB Cluster hanging
@ 2005-10-13 16:48 Nigel Bishop <Nigel.Bishop@ioko.com>
2005-10-13 17:18 ` Re: DB Cluster hanging Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Nigel Bishop @ 2005-10-13 16:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Hi
Hmm... a possible bug eh! I'll make sure that the log destination
doesn't fill again
This is what I have in the postgresql.conf file:
log_destination = 'stderr'
redirect_stderr = true
log_directory = '/opt/postgres/admin/log'
log_filename = 'PG-%Y-%m-%d_%H%M%S.log'
Thanks,
Nigel
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane
Sent: 13 October 2005 17:12
To: Nigel Bishop
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] DB Cluster hanging
Nigel Bishop <nigel.bishop@gmail.com> writes:
> Last night at 3am our Postgresql DB cluster hung. At the time data
> was being loaded. The parameter log_statement_stats in the
> postgresql.conf file was set to true. This was churning out data into
> the logfile which was switching every 10Mb. Eventually the partition
> where the logfiles are written to filled up - fair enough - this had
> been going on since about 5.30pm the previous evening and the logfiles
> were being generated at the rate of 4/5 a minute. The partition was
> cleared of old logs and I expected the DB to spring in to life, but no
> it just sat there. I could not connect with psql or pg_ctl to
> shutdown the cluster.
How was the logging being done exactly? syslog? redirect_stderr?
If redirect_stderr, this could well be a PG bug --- I kinda doubt
anyone has tested that scenario.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.
Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.
ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DB Cluster hanging
2005-10-13 16:48 Re: DB Cluster hanging Nigel Bishop <Nigel.Bishop@ioko.com>
@ 2005-10-13 17:18 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2005-10-13 17:18 UTC (permalink / raw)
To: Nigel Bishop <Nigel.Bishop@ioko.com>; +Cc: pgsql-admin
"Nigel Bishop" <Nigel.Bishop@ioko.com> writes:
> Hmm... a possible bug eh! I'll make sure that the log destination
> doesn't fill again
> This is what I have in the postgresql.conf file:
> log_destination = 'stderr'
> redirect_stderr = true
I tried to reproduce the problem, without any success. What I did:
* set up a small loopback filesystem, so I didn't have to actually fill
my whole disk;
* point Postgres logging into the loopback filesystem;
* deliberately fill the filesystem.
I didn't see any lockup. The syslogger subprocess started bleating on
the original postmaster stderr:
could not write to log file: No space left on device
could not write to log file: No space left on device
but it dropped the messages rather than hanging up, and the rest
of the database sailed on just fine. When I freed up space on the
loopback filesystem, logging resumed without any problem.
So I dunno what went wrong for you. System-specific issue maybe?
I tried this on Fedora Core 4 with CVS-tip Postgres (but the syslogger
code hasn't changed materially since 8.0.2).
Also, are you sure the postmaster is 8.0.3? There was an
infinite-recursion-on-error bug in syslogger in 8.0 and 8.0.1.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-10-13 17:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-12 14:15 DB cluster hanging Nigel Bishop <Nigel.Bishop@ioko.com>
2005-10-13 15:22 DB Cluster hanging Nigel Bishop <nigel.bishop@gmail.com>
2005-10-13 15:48 ` Re: DB Cluster hanging codeWarrior <gpatnude@hotmail.com>
2005-10-13 16:31 ` Re: DB Cluster hanging Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-13 16:12 ` Re: DB Cluster hanging Tom Lane <tgl@sss.pgh.pa.us>
2005-10-13 16:48 Re: DB Cluster hanging Nigel Bishop <Nigel.Bishop@ioko.com>
2005-10-13 17:18 ` Re: DB Cluster hanging Tom Lane <tgl@sss.pgh.pa.us>
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