Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CF4592E3F4B for ; Wed, 6 Feb 2008 03:38:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36002-03-2 for ; Wed, 6 Feb 2008 03:38:35 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.183]) by postgresql.org (Postfix) with ESMTP id A1A2C2E3F6F for ; Wed, 6 Feb 2008 03:38:35 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so3865046waf.5 for ; Tue, 05 Feb 2008 23:38:34 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=rFIKeY5kOYYRISW6Kcz6WLOuwd0ptcVu57jwnHxp8Mc=; b=ddKrdnCwpAJ7XKxK2V8nzWRn0XBY12g+fbbthTA3AZwMRkrXmSGhMYj268Jgym0H4OAFtKeWhc0kh80MHgy1yJ8d0ZPqwYYxUQfqthMIPYOxXu2Uifc8Zw3RSuQcfAA+KEiOIRdacdc3Qd+ZXIkY5ZQgtn3nhQzF2ylBjlv31Lk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=inDKt6/DbVPNlGUwwajA5HkRAv0j6pvivQZHQFUyiaQNvbaHSHYJNGdMGeyT3Mml6nJ+qazwzsFuz1ufcQHcpvBqs4VN7SkHs9Woz86ytePAmxtKQNGRybaFzns726yQNo+zIcMSqw+Vjk82x2pkvjCUERjiHZwwoAVj7XGdG10= Received: by 10.114.121.1 with SMTP id t1mr4158368wac.67.1202283514353; Tue, 05 Feb 2008 23:38:34 -0800 (PST) Received: by 10.114.166.9 with HTTP; Tue, 5 Feb 2008 23:38:34 -0800 (PST) Message-ID: Date: Wed, 6 Feb 2008 13:08:34 +0530 From: NikhilS To: "Greg Smith" Subject: Re: Benchmark Data requested Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5134_2689597.1202283514348" References: <1202150236.4252.521.camel@ebony.site> <200802051506.51581.dfontaine@hi-media.com> <1202221496.4252.680.camel@ebony.site> <200802051815.25800.dfontaine@hi-media.com> <47A8C1B9.10200@enterprisedb.com> <47A8CC1B.1050000@sun.com> <1202248803.4252.895.camel@ebony.site> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/57 X-Sequence-Number: 28599 ------=_Part_5134_2689597.1202283514348 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, On Feb 6, 2008 9:05 AM, Greg Smith wrote: > On Tue, 5 Feb 2008, Simon Riggs wrote: > > > On Tue, 2008-02-05 at 15:50 -0500, Jignesh K. Shah wrote: > >> > >> Even if it is a single core, the mere fact that the loading process > will > >> eventually wait for a read from the input file which cannot be > >> non-blocking, the OS can timeslice it well for the second process to > use > >> those wait times for the index population work. > > > > If Dimitri is working on parallel load, why bother? > > pgloader is a great tool for a lot of things, particularly if there's any > chance that some of your rows will get rejected. But the way things pass > through the Python/psycopg layer made it uncompetative (more than 50% > slowdown) against the straight COPY path from a rows/second perspective > the last time (V2.1.0?) I did what I thought was a fair test of it (usual > caveat of "with the type of data I was loading"). Maybe there's been some > gigantic improvement since then, but it's hard to beat COPY when you've > got an API layer or two in the middle. > I think, its time now that we should jazz COPY up a bit to include all the discussed functionality. Heikki's batch-indexing idea is pretty useful too. Another thing that pg_bulkload does is it directly loads the tuples into the relation by constructing the tuples and writing them directly to the physical file corresponding to the involved relation, bypassing the engine completely (ofcourse the limitations that arise out of it are not supporting rules, triggers, constraints, default expression evaluation etc). ISTM, we could optimize the COPY code to try to do direct loading too (not necessarily as done by pg_bulkload) to speed it up further in certain cases. Another thing that we should add to COPY is the ability to continue data load across errors as was discussed recently on hackers some time back too. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com ------=_Part_5134_2689597.1202283514348 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi,

On Feb 6, 2008 9:05 AM, Greg Smith <gsmith@gregsmith.com> wrote:
On Tue, 5 Feb 2008, Simon Riggs wrote:

> On Tue, 2008-02-05 at 15:50 -0500, Jignesh K. Shah wrote:
>>
>> Even if it is a single core, the mere fact that the loading process will
>> eventually wait for a read from the input file which cannot be
>> non-blocking, the OS can timeslice it well for the second process to use
>> those wait times for the index population work.
>
> If Dimitri is working on parallel load, why bother?

pgloader is a great tool for a lot of things, particularly if there's any
chance that some of your rows will get rejected.  But the way things pass
through the Python/psycopg layer made it uncompetative (more than 50%
slowdown) against the straight COPY path from a rows/second perspective
the last time (V2.1.0?) I did what I thought was a fair test of it (usual
caveat of "with the type of data I was loading").  Maybe there's been some
gigantic improvement since then, but it's hard to beat COPY when you've
got an API layer or two in the middle.

I think, its time now that we should jazz COPY up a bit to include all the discussed functionality. Heikki's batch-indexing idea is pretty useful too. Another thing that pg_bulkload does is it directly loads the tuples into the relation by constructing the tuples and writing them directly to the physical file corresponding to the involved relation, bypassing the engine completely (ofcourse the limitations that arise out of it are not supporting rules, triggers, constraints, default expression evaluation etc). ISTM, we could optimize the COPY code to try to do direct loading too (not necessarily as done by pg_bulkload) to speed it up further in certain cases.

Another thing that we should add to COPY is the ability to continue data load across errors as was discussed recently on hackers some time back too.

Regards,
Nikhils
--
EnterpriseDB               http://www.enterprisedb.com ------=_Part_5134_2689597.1202283514348--