Re: [ADMIN] syslog slowing the database? - Mailing list pgsql-performance

Tom Lane wrote:
> Greg Spiegelberg <gspiegelberg@cranel.com> 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.000000000 -0800
--- rotatelogs.c    2004-03-10 11:01:55.000000000 -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;
           }