Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 87D999FB3A3 for ; Wed, 25 Oct 2006 10:20:46 -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 67839-09 for ; Wed, 25 Oct 2006 13:20:05 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 8C4559FB3A5 for ; Wed, 25 Oct 2006 10:20:05 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 30so74478ugc for ; Wed, 25 Oct 2006 06:20:03 -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=UxVYk+IdI032S+FECoSiKsd/idEcTfupridUBJiyxhExej2/Bc1hBgpSIPn+Y2nrLH+yJT3wgn62POV+6N6VZDblb14sGZIGnjijPu7svOsIsoeLp9tQeIqn93DV9yhtpyOMQ1AK6tncpW8AKSKWYdyUqFuGzisbSspx8VAAsFs= Received: by 10.78.94.37 with SMTP id r37mr392894hub; Wed, 25 Oct 2006 06:20:03 -0700 (PDT) Received: by 10.78.45.3 with HTTP; Wed, 25 Oct 2006 06:20:01 -0700 (PDT) Message-ID: Date: Wed, 25 Oct 2006 09:20:02 -0400 From: "Merlin Moncure" To: "Worky Workerson" Subject: Re: Best COPY Performance Cc: "Markus Schaber" , 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: <453C8B0D.4070803@logix-tt.com> 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/513 X-Sequence-Number: 21642 On 10/23/06, Worky Workerson wrote: > The disk load is where I start to get a little fuzzy, as I haven't > played with iostat to figure what is "normal". The local drives > contain PG_DATA as well as all the log files, but there is a > tablespace on the FibreChannel SAN that contains the destination > table. The disk usage pattern that I see is that there is a ton of > consistent activity on the local disk, with iostat reporting an > average of 30K Blk_wrtn/s, which I assume is the log files. Every > several seconds there is a massive burst of activity on the FC > partition, to the tune of 250K Blk_wrtn/s. > > > On a table with no indices, triggers and contstraints, we managed to > > COPY about 7-8 megabytes/second with psql over our 100 MBit network, so > > here the network was the bottleneck. I'm guessing the high bursts are checkpoints. Can you check your log files for pg and see if you are getting warnings about checkpoint frequency? You can get some mileage here by increasing wal files. Have you determined that pg is not swapping? try upping maintenance_work_mem. What exactly is your architecture? is your database server direct attached to the san? if so, 2gb/4gb fc? what san? have you bonnie++ the san? basically, you can measure iowait to see if pg is waiting on your disks. regarding perl, imo the language performance is really about which libraries you use. the language itself is plenty fast. merlin