Thread: log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

From

Kong Man

Date:

23 December 2012, 08:52:17

We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.

Facts:

The postgresql.conf file has always been using the default value, which is 'warning'.
An attempt to explicitly set log_min_messages=warning and reload could not override the setting of 'debug5'.
The heavy debug5 logging on our production system drastically slowed down our database, and eventually filled up the root file system (due to the huge /var/log/postgresql)
The LifeKeeper failover has been done in a recent past with the same unmodified postgresql.log file without such an excessive logging behavior.
Changing log_destination from syslog to stderr appeared to have done the trick, after hours of troubleshooting. The log_min_messages value took the explicit setting from postgresql.log ('warning') and restored our database performance.

I could not find any relevant issue reported and want to see if anyone has a clue about this issue.

Thank you,
-Kong

Re: log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

From

Tom Lane

Date:

23 December 2012, 20:21:17

Kong Man <kong_mansatiansin@hotmail.com> writes:
> We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from
oneto another.  We noticed that the database was 1000 times slower than it usually is, then realized that the
log_min_messagessetting was set to 'debug5' for no reason. 

How was the postmaster started?  From memory, "-d5" on its command line
would do that, and would not have been overridable from postgresql.conf.

            regards, tom lane

Re: log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

From

Kong Man

Date:

24 December 2012, 08:59:49

Hi Tom,

Thank you for some clue in this. So, I assume changing log_destination (from syslog to stderr) was a way around it to override log_min_messages when the -d option is specified?

I am checking with the vender, SIOS, on what scenarios LifeKeeper would start up PostgreSQL with the -d5 option.

Best regards,
-Kong

> From: tgl@sss.pgh.pa.us
> To: kong_mansatiansin@hotmail.com
> CC: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf
> Date: Sun, 23 Dec 2012 12:21:12 -0500
>
> Kong Man <kong_mansatiansin@hotmail.com> writes:
> > We ran into a production issue during our maintenance window when I switched over LifeKeeper/PostgreSQL nodes from one to another. We noticed that the database was 1000 times slower than it usually is, then realized that the log_min_messages setting was set to 'debug5' for no reason.
>
> How was the postmaster started? From memory, "-d5" on its command line
> would do that, and would not have been overridable from postgresql.conf.
>
> regards, tom lane

Re: log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf

From

Kong Man

Date:

27 December 2012, 01:57:00

So, LifeKeeper default setting of LKPGSQLDEBUG=5 on /etc/default/LifeKeeper was the culprit for this issue, probably by starting postmaster with -d5 option.

Thank you for pointing us to the right direction.

-Kong

From: kong_mansatiansin@hotmail.com
To: tgl@sss.pgh.pa.us
CC: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] log_min_messages=debug5, despite an explicit setting to warning on postgresql.conf
Date: Sun, 23 Dec 2012 21:59:35 -0800