Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 06AA49FB298 for ; Fri, 27 Oct 2006 22:01:09 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 77205-08 for ; Fri, 27 Oct 2006 22:01:08 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exprod6og54.obsmtp.com (exprod6og54.obsmtp.com [64.18.1.189]) by postgresql.org (Postfix) with SMTP id A5ED39FB25F for ; Fri, 27 Oct 2006 22:01:04 -0300 (ADT) Received: from source ([63.240.6.44]) by exprod6ob54.postini.com ([64.18.5.12]) with SMTP; Fri, 27 Oct 2006 18:01:02 PDT Received: from 172.16.1.114 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Fri, 27 Oct 2006 21:00:54 -0400 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by d01smtp07.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Fri, 27 Oct 2006 21:00:54 -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 01:00:53 +0000 User-Agent: Microsoft-Entourage/11.2.5.060620 Date: Fri, 27 Oct 2006 18:00:46 -0700 Subject: Re: Best COPY Performance From: "Luke Lonergan" To: "Worky Workerson" cc: pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Best COPY Performance Thread-Index: Acb6LIAFvqJiBGYfEduYOAAWy4o9DA== In-Reply-To: MIME-Version: 1.0 X-OriginalArrivalTime: 28 Oct 2006 01:00:54.0239 (UTC) FILETIME=[84EEDEF0:01C6FA2C] X-WSS-ID: 695C744C2TS5056425-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/588 X-Sequence-Number: 21717 Worky (that your real name? :-) On 10/27/06 12:08 PM, "Worky Workerson" wrote: > Here it is, taken from a spot about halfway through a 'cat file | > psql' load, with the "Oracle-is-installed-and-running" caveat: > > r b swpd free buff cache si so bi bo in cs us sy id wa > 1 0 345732 29328 770980 12947212 0 0 20 16552 1223 3677 12 2 85 1 > 1 0 345732 29840 770520 12946924 0 0 20 29244 1283 2955 11 2 85 1 > 1 0 345732 32144 770560 12944436 0 0 12 16436 1204 2936 11 2 86 1 > 1 0 345732 33744 770464 12942764 0 0 20 16460 1189 2005 10 2 86 1 > 2 0 345732 32656 770140 12943972 0 0 16 7068 1057 3434 13 2 85 0 > 1 0 345732 34832 770184 12941820 0 0 20 9368 1170 3120 11 2 86 1 > 1 0 345732 36528 770228 12939804 0 0 16 32668 1297 2109 11 2 85 1 > 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 It doesn't look like there's anything else running - the runnable "r" is about 1. Your "bo" blocks output rate is about 16MB/s, so divide by 3 and you're about in range with your 5MB/s COPY rate. The interesting thing is that the I/O wait is pretty low. How many CPUs on the machine? Can you send the result of "cat /proc/cpuinfo"? Is your "cat file | psql" being done on the DBMS server or is it on the network? - Luke