pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PG Logging is Slow
7+ messages / 5 participants
[nested] [flat]

* PG Logging is Slow
@ 2004-12-20 04:17 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  2004-12-20 04:31 ` Re: PG Logging is Slow Bruce Momjian <pgman@candle.pha.pa.us>
  2004-12-20 04:48 ` Re: PG Logging is Slow Grega Bremec <gregab@p0f.net>
  2004-12-20 14:07 ` Re: PG Logging is Slow Michael Adler <adler@pobox.com>
  0 siblings, 3 replies; 7+ messages in thread

From: Theo Galanakis @ 2004-12-20 04:17 UTC (permalink / raw)
  To: pgsql-performance

Under postgres 7.3 logging is incredibly slow!

I have applied the following settings:

syslog = 2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
 
 log_connections =  true 
log_duration =  true 
log_pid =  true 
log_statement =  true 
log_timestamp =  true 
 
This severely impacted the performance of our production system, a search
page which took 1-3 seconds now takes over 30, is this normal?
 
I need to get some performance indicators from our production db, however I
cant turn on logging with such performance degradation.
 
Theo





______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.
	

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
  2004-12-20 04:17 PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-12-20 04:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 2004-12-20 04:31 UTC (permalink / raw)
  To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-performance

Theo Galanakis wrote:
> Under postgres 7.3 logging is incredibly slow!
> 
> I have applied the following settings:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>  
>  log_connections =  true 
> log_duration =  true 
> log_pid =  true 
> log_statement =  true 
> log_timestamp =  true 
>  
> This severely impacted the performance of our production system, a search
> page which took 1-3 seconds now takes over 30, is this normal?
>  
> I need to get some performance indicators from our production db, however I
> cant turn on logging with such performance degradation.

Linux syslog has this bad behavior of fsync'ing all log writes.  See the
syslog manual page for a way to turn off the fsync.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
  2004-12-20 04:17 PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-12-20 04:48 ` Grega Bremec <gregab@p0f.net>
  2 siblings, 0 replies; 7+ messages in thread

From: Grega Bremec @ 2004-12-20 04:48 UTC (permalink / raw)
  To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-performance

...and on Mon, Dec 20, 2004 at 03:17:11PM +1100, Theo Galanakis used the keyboard:
> Under postgres 7.3 logging is incredibly slow!
> 
> I have applied the following settings:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>  
>  log_connections =  true 
> log_duration =  true 
> log_pid =  true 
> log_statement =  true 
> log_timestamp =  true 
>  
> This severely impacted the performance of our production system, a search
> page which took 1-3 seconds now takes over 30, is this normal?
>  
> I need to get some performance indicators from our production db, however I
> cant turn on logging with such performance degradation.
>  

Hi Theo,

One thing you should be sure about is that whichever logfile you have
configured for the local0 facility is being written to asynchronously.
Synchronous logging is REALLY expensive.

If you're using the standard syslogd, you can achieve that by prefixing
the filename in syslogd.conf with a dash. For example,

    local0.*		/var/log/postgresql.log

would become

    local0.*		-/var/log/postgresql.log

One other option would be to turn off syslog logging completely and let
postmaster take care of the log on its own, which may or may not be
possible for you, depending on the policy in effect (remote logging, etc.).

Hope this helped,
-- 
    Grega Bremec
    gregab at p0f dot net

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
  2004-12-20 04:17 PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-12-20 14:07 ` Michael Adler <adler@pobox.com>
  2 siblings, 0 replies; 7+ messages in thread

From: Michael Adler @ 2004-12-20 14:07 UTC (permalink / raw)
  To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-performance


On Mon, Dec 20, 2004 at 03:17:11PM +1100, Theo Galanakis wrote:
> Under postgres 7.3 logging is incredibly slow!
> 
> I have applied the following settings:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>  
>  log_connections =  true 
> log_duration =  true 
> log_pid =  true 
> log_statement =  true 
> log_timestamp =  true 
>  
> This severely impacted the performance of our production system, a search
> page which took 1-3 seconds now takes over 30, is this normal?
>  
> I need to get some performance indicators from our production db, however I
> cant turn on logging with such performance degradation.


I've experienced this problem many times due to hanging dns
lookups. /etc/resolv.conf may point to a nonexistent
nameserver. Comment it out and restart syslogd. Or use a syslog
implementation that allows you to disable dns lookups. Or just give
the nameserver a kick.

 -Mike Adler



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
@ 2004-12-20 22:40 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  2004-12-20 23:09 ` Re: PG Logging is Slow Alexander Borkowski <alexander.borkowski@abri.une.edu.au>
  0 siblings, 1 reply; 7+ messages in thread

From: Theo Galanakis @ 2004-12-20 22:40 UTC (permalink / raw)
  To: Grega Bremec <gregab@p0f.net>; +Cc: pgsql-performance


Thank-you Grega,

	I ended up using the pg_ctl -l parameter to write the output to a
specified file. Much quicker to do so.

	I tried the -/var/log/postgresql.log option however I noticed no
performance improvement. May be the fact that we use redhad linux and
syslog, I'm no sys-admin, so I'm not sure if there is a difference between
syslogd and syslog.

Theo

-----Original Message-----
From: Grega Bremec [mailto:gregab@p0f.net] 
Sent: Monday, 20 December 2004 3:49 PM
To: Theo Galanakis
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] PG Logging is Slow


...and on Mon, Dec 20, 2004 at 03:17:11PM +1100, Theo Galanakis used the
keyboard:
> Under postgres 7.3 logging is incredibly slow!
> 
> I have applied the following settings:
> 
> syslog = 2
> syslog_facility = 'LOCAL0'
> syslog_ident = 'postgres'
>  
>  log_connections =  true
> log_duration =  true 
> log_pid =  true 
> log_statement =  true 
> log_timestamp =  true 
>  
> This severely impacted the performance of our production system, a 
> search page which took 1-3 seconds now takes over 30, is this normal?
>  
> I need to get some performance indicators from our production db, 
> however I cant turn on logging with such performance degradation.
>  

Hi Theo,

One thing you should be sure about is that whichever logfile you have
configured for the local0 facility is being written to asynchronously.
Synchronous logging is REALLY expensive.

If you're using the standard syslogd, you can achieve that by prefixing the
filename in syslogd.conf with a dash. For example,

    local0.*		/var/log/postgresql.log

would become

    local0.*		-/var/log/postgresql.log

One other option would be to turn off syslog logging completely and let
postmaster take care of the log on its own, which may or may not be possible
for you, depending on the policy in effect (remote logging, etc.).

Hope this helped,
-- 
    Grega Bremec
    gregab at p0f dot net


______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
  2004-12-20 22:40 Re: PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
@ 2004-12-20 23:09 ` Alexander Borkowski <alexander.borkowski@abri.une.edu.au>
  0 siblings, 0 replies; 7+ messages in thread

From: Alexander Borkowski @ 2004-12-20 23:09 UTC (permalink / raw)
  To: Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>; +Cc: pgsql-performance

Theo,

 > 	I tried the -/var/log/postgresql.log option however I noticed no
 > performance improvement. May be the fact that we use redhad linux and
 > syslog, I'm no sys-admin, so I'm not sure if there is a difference 
between
 > syslogd and syslog.

Did you restart syslogd (that's the server process implementing the 
syslog (= system log) service) after you changed its configuration?

In order to do so, try running

/etc/init.d/syslog restart

as root from a commandline.

HTH

Alex



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: PG Logging is Slow
@ 2004-12-21 05:53 Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
  0 siblings, 0 replies; 7+ messages in thread

From: Theo Galanakis @ 2004-12-21 05:53 UTC (permalink / raw)
  To: Alexander Borkowski <alexander.borkowski@abri.une.edu.au>; +Cc: pgsql-performance


Thankyou Alexander,

	That has worked and appears to have fixed the issue with syslog.

Theo

-----Original Message-----
From: Alexander Borkowski [mailto:alexander.borkowski@abri.une.edu.au] 
Sent: Tuesday, 21 December 2004 10:09 AM
To: Theo Galanakis
Cc: 'pgsql-performance@postgresql.org'
Subject: Re: [PERFORM] PG Logging is Slow


Theo,

 > 	I tried the -/var/log/postgresql.log option however I noticed no
 > performance improvement. May be the fact that we use redhad linux and  >
syslog, I'm no sys-admin, so I'm not sure if there is a difference 
between
 > syslogd and syslog.

Did you restart syslogd (that's the server process implementing the 
syslog (= system log) service) after you changed its configuration?

In order to do so, try running

/etc/init.d/syslog restart

as root from a commandline.

HTH

Alex


______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright.  If you
have received this email in error, please advise the sender and delete
it.  If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone.  You must not copy or 
communicate to others content that is confidential or subject to 
copyright, unless you have the consent of the content owner.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2004-12-21 05:53 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-20 04:17 PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-12-20 04:31 ` Bruce Momjian <pgman@candle.pha.pa.us>
2004-12-20 04:48 ` Grega Bremec <gregab@p0f.net>
2004-12-20 14:07 ` Michael Adler <adler@pobox.com>
2004-12-20 22:40 Re: PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>
2004-12-20 23:09 ` Alexander Borkowski <alexander.borkowski@abri.une.edu.au>
2004-12-21 05:53 Re: PG Logging is Slow Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>

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