Re: [ADMIN] syslog slowing the database?

Tom Lane wrote:
> Greg Spiegelberg <gspiegelberg@cranel.com> 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: gspiegelberg@Cranel.com
Cranel. Technology. Integrity. Focus.