Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C6F479FB1FB for ; Sat, 28 Oct 2006 00:12:37 -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 70217-10 for ; Sat, 28 Oct 2006 03:12:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id 752079FB1E2 for ; Sat, 28 Oct 2006 00:12:28 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id g2so1291185nfe for ; Fri, 27 Oct 2006 20:12:27 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ZjfKsdoOTKHxAkWfIdnq3cC1bYUz7DwGefyLhx2cJ/0Ogs+p1gqmeGQpiYuW8zRIGoAXzKcQKd46hzRArfci77DUJzoIyN0lU89wfz0O8gtQw69ex4q9zjeBNYYd75fjRg5z1wdgZqnrB+0BeZ23NHST7OeYuZq8mMX1/RH0ues= Received: by 10.78.185.7 with SMTP id i7mr585359huf; Fri, 27 Oct 2006 20:12:26 -0700 (PDT) Received: by 10.78.45.3 with HTTP; Fri, 27 Oct 2006 20:12:26 -0700 (PDT) Message-ID: Date: Sat, 28 Oct 2006 08:42:26 +0530 From: "Merlin Moncure" To: "Luke Lonergan" Subject: Re: Best COPY Performance Cc: "Worky Workerson" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/589 X-Sequence-Number: 21718 On 10/28/06, Luke Lonergan wrote: > 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? 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. merlin