Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D61121335B36 for ; Thu, 4 Nov 2010 18:03:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 20997-03 for ; Thu, 4 Nov 2010 21:03:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 5D96F1337B79 for ; Thu, 4 Nov 2010 18:03:44 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Thu, 04 Nov 2010 16:03:43 -0500 Message-Id: <4CD2D95702000025000372EC@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Thu, 04 Nov 2010 16:03:35 -0500 From: "Kevin Grittner" To: "pgsql-admin@postgresql.org" , "Jonathan Hoover" Subject: Re: Disk Performance Problem on Large DB References: <68C107706843A54BA7D9CAEF123ADF05010346C85A@SP2-EX07VS03.ds.corp.yahoo.com> In-Reply-To: <68C107706843A54BA7D9CAEF123ADF05010346C85A@SP2-EX07VS03.ds.corp.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-4.21 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/33 X-Sequence-Number: 35999 "Jonathan Hoover" wrote: > I have a simple database, with one table for now. It has 4 > columns: > > anid serial primary key unique, > time timestamp, > source varchar(5), > unitid varchar(15), > guid varchar(32) > > There is a btree index on each. > > I am loading data 1,000,000 (1M) rows at a time using psql and a > COPY command. Once I hit 2M rows, my performance just drops out Drop the indexes and the primary key before you copy in. Personally, I strongly recommend a VACUUM FREEZE ANALYZE after the bulk load. Then use ALTER TABLE to restore the primary key, and create all the other indexes. Also, if you don't mind starting over from initdb if it crashes partway through you can turn fsync off. You want a big maintenance_work_mem setting during the index builds -- at least 200 MB. -Kevin