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.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1479" name=GENERATOR></HEAD>
<BODY>
<P><FONT face="Times New Roman"><SPAN class=151240404-20122004><FONT face=Arial
color=#0000ff size=2>Under postgres 7.3 logging is incredibly
slow!</FONT></SPAN></FONT></P>
<P><SPAN class=151240404-20122004><FONT face=Arial color=#0000ff size=2>I have
applied the following settings:</FONT></SPAN></P>
<DIV><SPAN class=151240404-20122004><FONT face=Arial color=#0000ff size=2>syslog
= 2</FONT></SPAN></DIV>
<DIV><SPAN class=151240404-20122004><FONT face=Arial color=#0000ff
size=2>syslog_facility = 'LOCAL0'</FONT></SPAN></DIV>
<DIV><SPAN class=151240404-20122004></SPAN><SPAN class=151240404-20122004><FONT
face=Arial color=#0000ff size=2>syslog_ident = 'postgres'</FONT></SPAN></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=151240404-20122004> </SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=151240404-20122004> </SPAN>log_connections = <SPAN
class=151240404-20122004> true </SPAN><BR>log_duration = <SPAN
class=151240404-20122004> true </SPAN><BR>log_pid = <SPAN
class=151240404-20122004> true </SPAN><BR></FONT></FONT></FONT><FONT
face=Arial color=#0000ff size=2>log_statement = <SPAN
class=151240404-20122004> true </SPAN><BR>log_timestamp = <SPAN
class=151240404-20122004> true </SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=151240404-20122004></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=151240404-20122004>This
severely impacted the performance of our production system, a search page which
took 1-3 seconds now takes over 30, is this normal?</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=151240404-20122004></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=151240404-20122004></SPAN></FONT><FONT face=Arial color=#0000ff
size=2><SPAN class=151240404-20122004></SPAN></FONT><FONT face=Arial
color=#0000ff size=2><SPAN class=151240404-20122004>I need to get some
performance indicators from our production db, however I cant turn on
logging with such performance degradation.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=151240404-20122004></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=151240404-20122004>Theo</SPAN></DIV>
<DIV><BR><BR><BR></DIV></FONT>
<P>
<TABLE>
<TBODY>
<TR>
<TD bgColor=#ffffff><FONT
color=#000000>______________________________________________________________________<BR>This
email, including attachments, is intended only for the addressee<BR>and
may be confidential, privileged and subject to copyright. If you<BR>have
received this email in error, please advise the sender and delete<BR>it.
If you are not the intended recipient of this email, you must not<BR>use,
copy or disclose its content to anyone. You must not copy or
<BR>communicate to others content that is confidential or subject to
<BR>copyright, unless you have the consent of the content
owner.<BR></FONT></TD></TR></TBODY></TABLE></P></BODY></HTML>
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@no-spam | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
=2E..and on Mon, Dec 20, 2004 at 03:17:11PM +1100, Theo Galanakis used the =
keyboard:
> Under postgres 7.3 logging is incredibly slow!
>=20
> I have applied the following settings:
>=20
> syslog =3D 2
> syslog_facility =3D 'LOCAL0'
> syslog_ident =3D 'postgres'
> =20
> log_connections =3D true=20
> log_duration =3D true=20
> log_pid =3D true=20
> log_statement =3D true=20
> log_timestamp =3D true=20
> =20
> This severely impacted the performance of our production system, a search
> page which took 1-3 seconds now takes over 30, is this normal?
> =20
> I need to get some performance indicators from our production db, however=
I
> cant turn on logging with such performance degradation.
> =20
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,
--=20
Grega Bremec
gregab at p0f dot net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFBxlmifu4IwuB3+XoRAjgzAJ9lfCTvp0gTnaP7v93GBECPACCU7ACffJZp
Xk5D4GaUvLXq1g9zkA5Tt5A=
=YRXU
-----END PGP SIGNATURE-----
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@no-spam