From: =?UTF-8?B?VG9yc3RlbiBaw7xobHNkb3JmZg==?= <foo@meisterderspiele.de>
Subject: Re: How to insert a bulk of data with unique-violations
 very fast
Date: Mon, 07 Jun 2010 15:21:13 +0200
Organization: A noiseless patient Spider
Lines: 54
Message-ID: <huiroa$sn6$1@news.eternal-september.org>
References: <hu37gm$qrl$1@news.eternal-september.org>
	<AANLkTimwLi6PkFYltlyXecsDQaI6yD3y22WLmWdgwe_R@mail.gmail.com>
	<4C07E418.80103@meisterderspiele.de>
	<AANLkTilj2xHAZMnQ-To3lod3mMPw1DBU_8EkRwTC29qb@mail.gmail.com>
	<hug2od$nu4$1@news.eternal-september.org>
	<AANLkTim36aTPinMgAQ9bUcf0AD4S1YKRrXfApHERRd7U@mail.gmail.com>
	<op.vdwg04j0eorkce@apollo13>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Mon, 7 Jun 2010 13:21:14 +0000 (UTC)
Injection-Info: mx02.eternal-september.org;
	posting-host="AnkPUomeHXvuN7UdHvIkog";
	logging-data="29414"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX1+C4kCG7iroJLt9cG96jC96"
User-Agent: Mozilla/5.0 (X11; U; Linux i686; de;
	rv:1.8.1.24) Gecko/20100228 Thunderbird/2.0.0.24 Mnenhy/0.7.5.0
In-Reply-To: <op.vdwg04j0eorkce@apollo13>
Cancel-Lock: sha1:I0UAh0BYM5PJS7lWIF0+EA9Oqpw=
To: pgsql-performance@postgresql.org

Pierre C schrieb:
> Since you have lots of data you can use parallel loading.
> 
> Split your data in several files and then do :
> 
> CREATE TEMPORARY TABLE loader1 ( ... )
> COPY loader1 FROM ...
> 
> Use a TEMPORARY TABLE for this : you don't need crash-recovery since if 
> something blows up, you can COPY it again... and it will be much faster 
> because no WAL will be written.

That's a good advice, thank yo :)

> If your disk is fast, COPY is cpu-bound, so if you can do 1 COPY process 
> per core, and avoid writing WAL, it will scale.
> 
> This doesn't solve the other half of your problem (removing the 
> duplicates) which isn't easy to parallelize, but it will make the COPY 
> part a lot faster.
> 
> Note that you can have 1 core process the INSERT / removing duplicates 
> while the others are handling COPY and filling temp tables, so if you 
> pipeline it, you could save some time.
> 
> Does your data contain a lot of duplicates, or are they rare ? What 
> percentage ?

Within the data to import most rows have 20 till 50 duplicates. Sometime 
much more, sometimes less.

But over 99,1% of the rows to import are already know. This percentage 
is growing, because there is a finite number of rows i want to know.

In my special case i'm collection domain-names. Till now it's completly 
for private interests and with normal pc-hardware. I'm collecting them 
by crawling known sites and checking them for new hosts. Maybe i will 
build later an expired domain service or an reverse ip database or 
something like that. But now i'm just interested in the connection of 
the sites and the structure people choose domain-names.

(Before someone ask: Till now i have more rows than domains (nearly) 
exists, because i collect subdomain of all levels too and do not delete 
entries)

Thanks everyone for your advices. This will help me a lot!

Greetings from Germany,
Torsten
-- 
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.