Skip site navigation (1) Skip section navigation (2)

Re: PG Logging is Slow

From: Grega Bremec <gregab(at)p0f(dot)net>
To: Theo Galanakis <Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: PG Logging is Slow
Date: 2004-12-20 04:48:35
Message-ID: 20041220044835.GA24979@lunik.p0f.net (view raw or flat)
Thread:
Lists: 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

In response to

pgsql-performance by date

Next:From: Michael AdlerDate: 2004-12-20 14:07:51
Subject: Re: PG Logging is Slow
Previous:From: Alexander KirpaDate: 2004-12-20 04:45:11
Subject: Re: Processor optimization compile options?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group