Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 41FDD9FB317 for ; Sat, 28 Oct 2006 01:08:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 82620-04 for ; Sat, 28 Oct 2006 04:08:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exprod6og56.obsmtp.com (exprod6og56.obsmtp.com [64.18.1.208]) by postgresql.org (Postfix) with SMTP id 4E5A09F9F7D for ; Sat, 28 Oct 2006 01:08:11 -0300 (ADT) Received: from source ([63.240.6.46]) by exprod6ob56.postini.com ([64.18.5.12]) with SMTP; Fri, 27 Oct 2006 21:08:08 PDT Received: from 172.16.1.239 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D2)); Sat, 28 Oct 2006 00:07:35 -0400 X-Server-Uuid: 7829E76E-BB9E-4995-8473-3C0929DF7DD1 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by d01smtp06.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Sat, 28 Oct 2006 00:07:22 -0400 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.105]) with Microsoft Exchange Server HTTP-DAV ; Sat, 28 Oct 2006 04:07:22 +0000 User-Agent: Microsoft-Entourage/11.2.5.060620 Date: Fri, 27 Oct 2006 21:07:18 -0700 Subject: Re: Best COPY Performance From: "Luke Lonergan" To: "Worky Workerson" , "Merlin Moncure" cc: pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Best COPY Performance Thread-Index: Acb6Ro75zcnEF2Y5EduYOAAWy4o9DA== In-Reply-To: MIME-Version: 1.0 X-OriginalArrivalTime: 28 Oct 2006 04:07:22.0627 (UTC) FILETIME=[91BB6530:01C6FA46] X-WSS-ID: 695C08F02ZG368177-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/592 X-Sequence-Number: 21721 Worky, On 10/27/06 8:47 PM, "Worky Workerson" wrote: >>>> 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1 >>>> 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1 >>>> 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1 >> >> iirc, he is running quad opteron 885 (8 cores), so if my math is >> correct he can split up his process for an easy gain. > > Are you saying that I should be able to issue multiple COPY commands > because my I/O wait is low? I was under the impression that I am I/O > bound, so multiple simeoultaneous loads would have a detrimental > effect ... The reason I asked how many CPUs was to make sense of the 12% usr CPU time in the above. That means you are CPU bound and are fully using one CPU. So you aren't being limited by the I/O in this case, it's the CPU. I agree with Merlin that you can speed things up by breaking the file up. Alternately you can use the OSS Bizgres java loader, which lets you specify the number of I/O threads with the "-n" option on a single file. OTOH, you should find that you will only double your COPY speed with this approach because your write speed as you previously found was limited to 30 MB/s. For now, you could simply split the file in two pieces and load two copies at once, then watch the same "vmstat 1" for 10 seconds and look at your "bo" rate. If this does speed things up, you really should check out the Bizgres Java loader. The other thing to wonder about though is why you are so CPU bound at 5 MB/s. What version of Postgres is this? - Luke