Re: [PERFORM] PG Logging is Slow

[Theo Galanakis]

Title: RE: [PERFORM] PG Logging is Slow





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:[EMAIL PROTECTED]] 
Sent: Monday, 20 December 2004 3:49 PM
To: Theo Galanakis
Cc: [EMAIL PROTECTED]
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.

[PERFORM] PG Logging is Slow

[Theo Galanakis]

Title: Message



Under postgres 7.3 logging is incredibly 
slow!
I have 
applied the following settings:
syslog 
= 2
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

log_connections =truelog_duration =truelog_pid =truelog_statement =truelog_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 
loggingwith such performance degradation.

Theo



  
  
__This 
  email, including attachments, is intended only for the addresseeand 
  may be confidential, privileged and subject to copyright. If youhave 
  received this email in error, please advise the sender and deleteit. 
  If you are not the intended recipient of this email, you must notuse, 
  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.

[PERFORM] indentifying the database in a Postgres log file.

[Theo Galanakis]

Title: indentifying the database in a Postgres log file.





I have written a program that parses a syslog file, reading all the postgres transactions. I would like to know if there is a way for postgres to log also the specific database the sql statement originated from. 

The only options available in the postgresql.conf are:
#log_connections = false
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false

Is this possible? Or is there a smart work around.


Regards,
 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.