PGSQL PERFORMANCE 40 RE PG LOGGING IS SLOW

PGSQL PERFORMANCE 40 RE PG LOGGING IS SLOW

From: Theo.Galanakis@no-spam (Theo Galanakis)

Subject: Re: PG Logging is Slow

Date: Tue, 21 Dec 2004 09:40:35 +1100

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@no-spam

Sent: Monday, 20 December 2004 3:49 PM To: Theo Galanakis Cc: pgsql-performance@no-spam

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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=
=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version=
5.5.2654.45">
<TITLE>RE: [PERFORM] PG Logging is Slow</TITLE>
</HEAD>
<BODY>

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 [<A HREF=
=3D"mailto:gregab@no-spam">mailto:gregab@no-spam</A>] 
 Sent: Monday, 20 December 2004 3:49 PM
 To: Theo Galanakis
 Cc: pgsql-performance@no-spam
 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 =3D 2
 > syslog_facility =3D 'LOCAL0'
 > syslog_ident =3D 'postgres'
 > 
 > log_connections =3D true
 > log_duration =3D true 
 > log_pid =3D true 
 > log_statement =3D true 
 > log_timestamp =3D 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


</BODY>
</HTML>
<table><tr><td bgcolor=3D#ffffff>________________________________________________________________=
______ 
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. 
</td></tr></table>

From: alexander.borkowski@no-spam (Alexander Borkowski)

Subject: Re: PG Logging is Slow

Date: Tue, 21 Dec 2004 10:09:06 +1100

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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster