Re: [PERFORM] [ADMIN] syslog slowing the database?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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?
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
