Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-15 Thread scott.marlowe
On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote:

  You could also consider not using syslog at all: let the postmaster
  output to its stderr, and pipe that into a log-rotation program.
  I believe some people use Apache's log rotator for this with good
  results.
 
 Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
 the first rotation...
 
 I've read in the docs that syslog logging is the only production 
 solution...

Can you use the apache log rotator?  It's known to work in my environment 
(redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my 
rc.local file:

su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 21'


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-15 Thread Bruce Momjian
scott.marlowe wrote:
 On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote:
 
   You could also consider not using syslog at all: let the postmaster
   output to its stderr, and pipe that into a log-rotation program.
   I believe some people use Apache's log rotator for this with good
   results.
  
  Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
  the first rotation...
  
  I've read in the docs that syslog logging is the only production 
  solution...
 
 Can you use the apache log rotator?  It's known to work in my environment 
 (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my 
 rc.local file:
 
 su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 21'

Sure, our documentation specifically mentions using rotatelogs.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (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 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-15 Thread scott.marlowe
On Mon, 15 Mar 2004, Bruce Momjian wrote:

 scott.marlowe wrote:
  On Thu, 11 Mar 2004, Christopher Kings-Lynne wrote:
  
You could also consider not using syslog at all: let the postmaster
output to its stderr, and pipe that into a log-rotation program.
I believe some people use Apache's log rotator for this with good
results.
   
   Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
   the first rotation...
   
   I've read in the docs that syslog logging is the only production 
   solution...
  
  Can you use the apache log rotator?  It's known to work in my environment 
  (redhat 7.2, postgresql 7.2 and 7.4) with this command to start it in my 
  rc.local file:
  
  su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 21'
 
 Sure, our documentation specifically mentions using rotatelogs.


hehe.  What I meant was can Christopher use it, or does he have a 
limitation in his environment where he can't get ahold of the apache log 
rotater... :-)  


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-14 Thread Andrew Sullivan
On Thu, Mar 11, 2004 at 09:34:54AM +0800, Christopher Kings-Lynne wrote:
 You could also consider not using syslog at all: let the postmaster
 output to its stderr, and pipe that into a log-rotation program.
 
 Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
 the first rotation...

Actually, this is what we do.  Last year we offered an (admittedly
expensive) bespoke log rotator written in Perl for just this purpose. 
It was rejected on the grounds that it didn't do anything Apache's
rotator didn't do, so I didn't pursue it.  I'm willing to put it up
on gborg, though, if anyone thinks it'll be worth having around. 
FWIW, we use ours in production.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The plural of anecdote is not data.
--Roger Brinner

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-13 Thread Magnus Naeslund(t)
Tom Lane wrote:
Greg Spiegelberg [EMAIL PROTECTED] writes:

I turned syslog back on and the restore slowed down again.  Turned
it off and it sped right back up.


We have heard reports before of syslog being quite slow.  What platform
are you on exactly?  Does Richard's suggestion of turning off syslog's
fsync help?
Another tip is to use a better (well atleast more optimized) syslog 
implementation, like metalog. It optimizes log writes to a blocksize 
that is better for disk throughput.
You can also use per line mode with those if you want, i think.

I use another logger that is called multilog (see at http://cr.yp.to), 
that's a pipe logger thing, like one per postmaster.
It also gives very exact timestamps to every line, has built in log 
rotation and works nice with all programs i use it for.

One thing is for sure, if you log much, standard syslog (atleast on 
linux) sucks big time.
I gained back approx 30% CPU on a mailserver over here by changing to 
another logger.

Cheers
Magnus


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-11 Thread Joshua D. Drake

Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
the first rotation...
Are you using a copy truncate method to rotate the logs? In RedHat add
the keyword COPYTRUCATE to your /etc/logrotate.d/syslog file.
Sincerely,

Joshua D. Drake





I know some people use this in production.  Dunno what went wrong in
your test, but it can be made to work.
			regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0034
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We  provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored  the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard


---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-10 Thread Greg Spiegelberg
Tom Lane wrote:
Greg Spiegelberg [EMAIL PROTECTED] writes:

I turned syslog back on and the restore slowed down again.  Turned
it off and it sped right back up.
We have heard reports before of syslog being quite slow.  What platform
are you on exactly?  Does Richard's suggestion of turning off syslog's
fsync help?
RedHat 7.3 w/ 2.4.24 kernel on a dual Intel PIII 1.3Ghz, 2GB memory,
U160 internal on integrated controller, 1Gbps SAN for database.
Database file being restored and the actual database are on different
disk and controllers than syslog files.
With the ``-'' in front of the syslog file postgres logs too gives
me roughly 75% of the I/O the performance as reported by iostat.  So,
it helps though turning syslog off gives the optimum performance.
If the log and database were on the same disk I'd be okay with the
current workaround.  If the ``-'' gave me near the same performance as
turning syslog off I'd be okay with that too.  However, neither of these
are the case so there has to be something else blocking between the two
processes.
2 hours and multiple test later

I've found that hardware interrupts are the culprit.  Given my system
config both SCSI and fibre controllers were throttling the system with
the interrupts required to write the data (syslog  database) and read
the data from the restore.  I'm okay with that.
In the order of worst to best.

* There were, on average about 450 interrupts/sec with the default
  config of syslog on one disk, database on the SAN and syslog using
  fsync.
* Turning fsync off in syslog puts interrupts around 105/sec and.

* Having syslog fsync turned off in syslog AND moving the syslog file
  to a filesystem serviced by the same fibre controller put interrupts
  at around 92/sec.  I decided to do this after watching the I/O on
  the SAN with syslog turned off and found that it had bandwidth to
  spare.  FYI, the system when idle generated about 50 interrupts/sec.
I'm going with the later for now on the test system and after running
it through it's paces with all our processes I'll make the change in
production.  I'll post if I run into anything else.
Greg

BTW, I like what metalog has to offer but I prefer using as many of the
default tools as possible and replacing them only when absolutely
necessary.  What I've learned with syslog here is that it is still
viable but likely requires a minor tweak.  If this tweak fails in
testing I'll look at metalog then.
--
Greg Spiegelberg
 Sr. Product Development Engineer
 Cranel, Incorporated.
 Phone: 614.318.4314
 Fax:   614.431.8388
 Email: [EMAIL PROTECTED]
Cranel. Technology. Integrity. Focus.


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-10 Thread Mark Harrison
Tom Lane wrote:
Greg Spiegelberg [EMAIL PROTECTED] writes:

If the log and database were on the same disk I'd be okay with the
current workaround.  If the ``-'' gave me near the same performance as
turning syslog off I'd be okay with that too.  However, neither of these
are the case so there has to be something else blocking between the two
processes.


You could also consider not using syslog at all: let the postmaster
output to its stderr, and pipe that into a log-rotation program.
I believe some people use Apache's log rotator for this with good
results.
I do this... here's the relevant lines from my startup script:

ROTATE=/inst/apache/bin/rotatelogs $PGLOGS/postgresql 86400
$PGBIN/pg_ctl start -s -D $PGDATA | $ROTATE 
Following is a patch to rotatelogs that does two things:

- makes a symbolic link 'foo.current' that points to the
  current output file.
- gzips the rotated logfile

If you have gnu tools installed, you can
tail --retry --follow=name foo.current
and it will automatically track the most recent
log file.
HTH,
Mark
--
Mark Harrison
Pixar Animation Studios
*** rotatelogs.c-orig   2004-03-10 10:24:02.0 -0800
--- rotatelogs.c2004-03-10 11:01:55.0 -0800
***
*** 25,30 
--- 25,32 
  int main (int argc, char **argv)
  {
  char buf[BUFSIZE], buf2[MAX_PATH], errbuf[ERRMSGSZ];
+ char linkbuf[MAX_PATH];
+ char oldbuf2[MAX_PATH];
  time_t tLogEnd = 0, tRotation;
  int nLogFD = -1, nLogFDprev = -1, nMessCount = 0, nRead, nWrite;
  int utc_offset = 0;
***
*** 75,80 
--- 77,84 
  setmode(0, O_BINARY);
  #endif
+ sprintf(linkbuf, %s.current, szLogRoot);
+ sprintf(oldbuf2, );
  use_strftime = (strstr(szLogRoot, %) != NULL);
  for (;;) {
  nRead = read(0, buf, sizeof buf);
***
*** 99,104 
--- 103,111 
  sprintf(buf2, %s.%010d, szLogRoot, (int) tLogStart);
  }
  tLogEnd = tLogStart + tRotation;
+ printf(oldbuf2=%s\n,oldbuf2);
+ printf(buf2=%s\n,buf2);
+ printf(linkbuf=%s\n,linkbuf);
  nLogFD = open(buf2, O_WRONLY | O_CREAT | O_APPEND, 0666);
  if (nLogFD  0) {
  /* Uh-oh. Failed to open the new log file. Try to clear
***
*** 125,130 
--- 132,146 
  }
  else {
  close(nLogFDprev);
+ /* use: tail --follow=name foo.current */
+ unlink(linkbuf);
+ symlink(buf2,linkbuf);
+ if (strlen(oldbuf2)  0) {
+ char cmd[MAX_PATH+100];
+ sprintf(cmd, gzip %s , oldbuf2);
+ system(cmd);
+ }
+ strcpy(oldbuf2, buf2);
  }
  nMessCount = 0;
  }
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-10 Thread Christopher Kings-Lynne
You could also consider not using syslog at all: let the postmaster
output to its stderr, and pipe that into a log-rotation program.
I believe some people use Apache's log rotator for this with good
results.
Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
the first rotation...

I've read in the docs that syslog logging is the only production 
solution...

Chris

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-10 Thread Chris Smith
It might depend on how you're rotating it.

Try the copy/truncate method instead of moving the log file. If you move
the log file to another filename you usually have to restart the app
doing the logging before it starts logging again.

Chris.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christopher
Kings-Lynne
Sent: Thursday, March 11, 2004 12:35 PM
To: Tom Lane
Cc: Greg Spiegelberg; PgSQL Performance ML; Postgres Admin List
Subject: Re: [PERFORM] [ADMIN] syslog slowing the database?


 You could also consider not using syslog at all: let the postmaster 
 output to its stderr, and pipe that into a log-rotation program. I 
 believe some people use Apache's log rotator for this with good 
 results.

Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
the first rotation...

I've read in the docs that syslog logging is the only production 
solution...

Chris


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-10 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes:
 You could also consider not using syslog at all: let the postmaster
 output to its stderr, and pipe that into a log-rotation program.
 I believe some people use Apache's log rotator for this with good
 results.

 Not an option I'm afraid.  PostgreSQL just jams and stops logging after 
 the first rotation...

I know some people use this in production.  Dunno what went wrong in
your test, but it can be made to work.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-09 Thread Tom Lane
Greg Spiegelberg [EMAIL PROTECTED] writes:
 I turned syslog back on and the restore slowed down again.  Turned
 it off and it sped right back up.

We have heard reports before of syslog being quite slow.  What platform
are you on exactly?  Does Richard's suggestion of turning off syslog's
fsync help?

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-09 Thread Gavin M. Roy
Might want to look at metalog, it does delayed writes, though ultimately 
your issue is io bound and there's not much you can do to reduce io if 
you want to keep syslog logging your pgsql queries and such.

Tom Lane wrote:

Greg Spiegelberg [EMAIL PROTECTED] writes:
 

I turned syslog back on and the restore slowed down again.  Turned
it off and it sped right back up.
   

We have heard reports before of syslog being quite slow.  What platform
are you on exactly?  Does Richard's suggestion of turning off syslog's
fsync help?
			regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly
 



---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [PERFORM] [ADMIN] syslog slowing the database?

2004-03-09 Thread Stephen Robert Norris
On Wed, 2004-03-10 at 12:09, Gavin M. Roy wrote:
 Might want to look at metalog, it does delayed writes, though ultimately 
 your issue is io bound and there's not much you can do to reduce io if 
 you want to keep syslog logging your pgsql queries and such.

Yeah, but syslog with fsync() after each line is much, much worse than
syslog without it, assuming anything else is on the same disk (array).
It just guarantees to screw up your drive head movements...

-- 
Stephen Norris[EMAIL PROTECTED]
Farrow Norris Pty Ltd   +61 417 243 239


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org