X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E73764753A1 for ; Fri, 21 Feb 2003 12:37:24 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 22270-06 for ; Fri, 21 Feb 2003 12:37:13 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id D6A2B474E5C for ; Fri, 21 Feb 2003 12:37:13 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18mH6t-0006rP-00 for ; Fri, 21 Feb 2003 12:37:19 -0500 Date: Fri, 21 Feb 2003 12:37:19 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Really bad insert performance: what did I do wrong? Message-ID: <20030221123719.C16866@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <3E564695.7030906@digital-ics.com> <20030221115917.A16866@mail.libertyrms.com> <3E566022.5010200@digital-ics.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <3E566022.5010200@digital-ics.com>; from kwhite@digital-ics.com on Fri, Feb 21, 2003 at 12:21:38PM -0500 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/158 X-Sequence-Number: 1214 On Fri, Feb 21, 2003 at 12:21:38PM -0500, Kevin White wrote: > Andrew Sullivan wrote: > > What's the disk subsystem? Is fsync turned on in both cases? And is > > your IDE drive lying to you about what it's doing. > > It is IDE. How do I turn fsync on or off? (All I can find in the man > is a function call to fsync...is there something else?) By default, Postgres calls fsync() at every COMMIT. Lots of IDE drives lie about whether the fsync() succeeded, so you get better performance than you do with SCSI drives; but you're not really getting that performance, because the fsync isn't effectve. On Linux, I think you can use hdparm to fix this. I believe the write caching is turned off under Solaris, but I'm not sure. Anyway, you can adjust your postgresql.conf file to turn off fsync. > This box only has 1 gig, and I've only set up 200 shared buffers...at > this point, it is only me hitting it. Increasing the shared buffers > might help, but I haven't yet found the info I need to do that > intelligently. For stright inserts, it shouldn't matter, and that seems low enough that it shouldn't be a problem. You should put WAL on another disk, as well, if you can. Also, try using truss to see what the backend is doing. (truss -c gives you a count and gives some time info.) A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110