Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 48B969FB429 for ; Tue, 24 Oct 2006 21:17:18 -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 77877-10 for ; Tue, 24 Oct 2006 21:17:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7B4AE9FB39D for ; Tue, 24 Oct 2006 21:17:13 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 4F81F56423; Tue, 24 Oct 2006 19:17:12 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 24 Oct 2006 19:17:10 -0500 Date: Tue, 24 Oct 2006 19:17:10 -0500 From: "Jim C. Nasby" To: Worky Workerson Cc: Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: Best COPY Performance Message-ID: <20061025001709.GA26892@nasby.net> References: <20061023215913.GW26892@nasby.net> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061025:worky.workerson@gmail.com::HZCT5mzZGWqYiJFi:000000000000 0000000000000000000000000JlZ X-Hashcash: 1:20:061025:mmoncure@gmail.com::rCI7HqPm5Z2dMP1g:0000000000000000000 0000000000000000000000000UOa X-Hashcash: 1:20:061025:pgsql-performance@postgresql.org::PjIqpv3hpNqCrpjH:00000 000000000000000000000000188r X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.075 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/505 X-Sequence-Number: 21634 On Tue, Oct 24, 2006 at 09:17:08AM -0400, Worky Workerson wrote: > >http://stats.distributed.net used to use a perl script to do some > >transformations before loading data into the database. IIRC, when we > >switched to using C we saw 100x improvement in speed, so I suspect that > >if you want performance perl isn't the way to go. I think you can > >compile perl into C, so maybe that would help some. > > Like Craig mentioned, I have never seen those sorts of improvements > going from perl->C, and developer efficiency is primo for me. I've > profiled most of the stuff, and have used XS modules and Inline::C on > the appropriate, often used functions, but I still think that it comes > down to my using CSV and Text::CSV_XS. Even though its XS, CSV is > still a pain in the ass. > > >Ultimately, you might be best of using triggers instead of rules for the > >partitioning since then you could use copy. Or go to raw insert commands > >that are wrapped in a transaction. > > Eh, I've put the partition loading logic in the loader, which seems to > work out pretty well, especially since I keep things sorted and am the > only one inserting into the DB and do so with bulk loads. But I'll > keep this in mind for later use. Well, given that perl is using an entire CPU, it sounds like you should start looking either at ways to remove some of the overhead from perl, or to split that perl into multiple processes. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)