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 9358C9DC982 for ; Wed, 4 Jan 2006 10:16:35 -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 24887-01 for ; Wed, 4 Jan 2006 10:16:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mtiwmhc11.worldnet.att.net (mtiwmhc11.worldnet.att.net [204.127.131.115]) by postgresql.org (Postfix) with ESMTP id 58C2F9DC983 for ; Wed, 4 Jan 2006 10:16:33 -0400 (AST) Received: from [10.10.10.3] (166.denver-06rh16rt-07rh15rt.co.dial-access.att.net[12.73.182.166]) by worldnet.att.net (mtiwmhc11) with ESMTP id <2006010414163611100n974je>; Wed, 4 Jan 2006 14:16:37 +0000 Message-ID: <43BBD8C1.9080807@computer.org> Date: Wed, 04 Jan 2006 07:16:33 -0700 From: Steve Eckmann User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ian Westmacott CC: pgsql-performance@postgresql.org Subject: Re: improving write performance for logging application References: <43BB0C5C.8020207@computer.org> <1136382865.24450.7.camel@spectre.intellivid.com> In-Reply-To: <1136382865.24450.7.camel@spectre.intellivid.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.538 required=5 tests=[AWL=0.059, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.538 X-Spam-Level: X-Archive-Number: 200601/12 X-Sequence-Number: 16490 Ian Westmacott wrote: >We have a similar application thats doing upwards of 2B inserts >per day. We have spent a lot of time optimizing this, and found the >following to be most beneficial: > >1) use COPY (BINARY if possible) >2) don't use triggers or foreign keys >3) put WAL and tables on different spindles (channels if possible) >4) put as much as you can in each COPY, and put as many COPYs as > you can in a single transaction. >5) watch out for XID wraparound >6) tune checkpoint* and bgwriter* parameters for your I/O system > Thanks, Ian. I will look at how to implement your suggestions. Regards, Steve