X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 70A44D1CACF; Wed, 10 Mar 2004 15:52:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26819-03; Wed, 10 Mar 2004 11:51:58 -0400 (AST) Received: from COLSWEEPER.cranel.com (newmail.cranel.com [66.192.200.227]) by svr1.postgresql.org (Postfix) with ESMTP id ECB1ED1BB36; Wed, 10 Mar 2004 11:51:55 -0400 (AST) Received: from colmail01.cranel.com (colmail01.cranel.com) by COLSWEEPER.cranel.com (Content Technologies SMTPRS 4.3.12) with ESMTP id ; Wed, 10 Mar 2004 10:51:09 -0500 Received: from cranel.com (gspiegelberg.cranel.com [192.168.11.134]) by colmail01.cranel.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id GRDD737G; Wed, 10 Mar 2004 10:55:59 -0500 Message-ID: <404F3996.2070105@cranel.com> Date: Wed, 10 Mar 2004 10:51:50 -0500 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: PgSQL Performance ML , Postgres Admin List Subject: Re: syslog slowing the database? References: <404E2937.4060604@cranel.com> <370.1078877805@sss.pgh.pa.us> In-Reply-To: <370.1078877805@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/186 X-Sequence-Number: 12755 Tom Lane wrote: > Greg Spiegelberg 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.