X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 8A0659DCC69 for ; Tue, 21 Mar 2006 17:15:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66939-04 for ; Tue, 21 Mar 2006 17:15:07 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpauth07.mail.atl.earthlink.net (smtpauth07.mail.atl.earthlink.net [209.86.89.67]) by postgresql.org (Postfix) with ESMTP id F36809DCC46 for ; Tue, 21 Mar 2006 17:15:01 -0400 (AST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327; d=earthlink.net; b=anWzPBaW0o7V9ktH16jXM0rkstLnrMt5QE7BxAfDCIIXou6QNcBDlGzqKIv2ngpz; h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type:X-ELNK-Trace:X-Originating-IP; Received: from [71.243.21.155] (helo=ron-6d52adff2a6.earthlink.net) by smtpauth07.mail.atl.earthlink.net with asmtp (Exim 4.34) id 1FLoBy-00017h-N3; Tue, 21 Mar 2006 16:15:02 -0500 Message-Id: <7.0.1.0.2.20060320161133.03a0b5a8@earthlink.net> X-Mailer: QUALCOMM Windows Eudora Version 7.0.1.0 Date: Mon, 20 Mar 2006 16:17:09 -0500 To: Mikael Carneholm , pgsql-performance@postgresql.org From: Ron Subject: Re: Migration study, step 1: bulk write performance In-Reply-To: <1142973890.24487.510.camel@localhost.localdomain> References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com> <1142973890.24487.510.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-ELNK-Trace: acd68a6551193be5d780f4a490ca69563f9fea00a6dd62bcedd2f1ab4701a7980bda57ed15b92132350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 71.243.21.155 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.914 required=5 tests=[AWL=-0.446, DATE_IN_PAST_12_24=0.881, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.914 X-Spam-Level: X-Archive-Number: 200603/428 X-Sequence-Number: 17865 At 03:44 PM 3/21/2006, Simon Riggs wrote: >On Mon, 2006-03-20 at 15:59 +0100, Mikael Carneholm wrote: > > > This gives that 10Gb takes ~380s => ~27Mb/s (with fsync=off), > compared to the raw dd result (~75.5Mb/s). > > > > I assume this difference is due to: > > - simultaneous WAL write activity (assumed: for each byte written > to the table, at least one byte is also written to WAL, in effect: > 10Gb data inserted in the table equals 20Gb written to disk) > > - lousy test method (it is done using a function => the > transaction size is 10Gb, and 10Gb will *not* fit in wal_buffers :) ) > > - poor config > > > checkpoint_segments = 3 > >With those settings, you'll be checkpointing every 48 Mb, which will be >every about once per second. Since the checkpoint will take a reasonable >amount of time, even with fsync off, you'll be spending most of your >time checkpointing. bgwriter will just be slowing you down too because >you'll always have more clean buffers than you can use, since you have >132MB of shared_buffers, yet flushing all of them every checkpoint. IIRC, Josh Berkus did some benches that suggests in pg 8.x a value of 64 - 256 is best for checkpoint_segments as long as you have the RAM available. I'd suggest trying values of 64, 128, and 256 and setting checkpoint_segments to the best of those. Ron