References: 
 <loom.20110310T163020-437@post.gmane.org><AANLkTiks9aTwVyN-JW1Ep2cVoJabBF_fZfkk_43GRcNv@mail.gmail.com><loom.20110311T195504-383@post.gmane.org><AANLkTi=dGuvB_EuU43CbFUeuaG57-WXvypw+A1WKHhZ8@mail.gmail.com><8CDAFB3EE5DFE5F-1AE0-21557@web-mmc-d02.sysops.aol.com><AANLkTimVFRO3FFGo3YXf3tSTVNdXRmLh8STnmJt9eTRr@mail.gmail.com>
	<AANLkTikqxB3EmnvgxYEGPwOg50r0K1JD=u651puWOzqW@mail.gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Tuning massive UPDATES and GROUP BY's?
Date: Mon, 14 Mar 2011 11:54:39 -0400
In-Reply-To: <AANLkTikqxB3EmnvgxYEGPwOg50r0K1JD=u651puWOzqW@mail.gmail.com>
MIME-Version: 1.0
From: runner <runner@winning.com>
Content-Type: multipart/alternative;
	boundary="--------MB_8CDB0774277BB85_27F8_18DE_web-mmc-m04.sysops.aol.com"
Message-Id: <8CDB0773FE92165-27F8-BD4@web-mmc-m04.sysops.aol.com>


----------MB_8CDB0774277BB85_27F8_18DE_web-mmc-m04.sysops.aol.com
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="us-ascii"


=20

=20


=20


> Bulk data imports of this size I've done with minimal pain by simply=20
> breaking the raw data into chunks (10M records becomes 10 files of=20
> 1M records), on a separate spindle from the database, and performing=20
> multiple COPY commands but no more than 1 COPY per server core. =20
> I tested this a while back on a 4 core server and when I attempted 5=20
> COPY's at a time the time to complete went up almost 30%.  I don't=20
> recall any benefit having fewer than 4 in this case but the server was=
=20
> only processing my data at the time.  Indexes were on the target table=
=20
> however I dropped all constraints.  The UNIX split command is handy=20
> for breaking the data up into individual files.

I'm not using COPY.  My dump file is a bunch if INSERT INTO statements. =
 I know it would be faster to use copy.  If I can figure out how to do thi=
s in one hour I will try it.  I did two mysqldumps, one with INSERT INTO=
 and one as CSV to I can try COPY at a later time.   I'm running five para=
llel psql processes to import the data which has been broken out by table.


=20

----------MB_8CDB0774277BB85_27F8_18DE_web-mmc-m04.sysops.aol.com
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"

<font color=3D'black' size=3D'2' face=3D'arial'><font color=3D"black" face=
=3D"arial" size=3D"2">

<div> <br>

</div>


<div> <br>

</div>


<div style=3D"clear: both;"></div>


<div style=3D"font-family: helvetica,arial; font-size: 10pt; color: black;=
"> <br>

<div id=3D"AOLMsgPart_2_c288f977-0db0-4304-8212-8f80f6e47399">
<div class=3D"gmail_quote">
<div><br>

</div>


<div>&gt; Bulk data imports of this size I've done with minimal pain by si=
mply <br>
&gt; breaking the raw data into chunks (10M records becomes 10 files of <b=
r>
&gt; 1M records), on a separate spindle from the database, and performing=
 <br>
&gt; multiple COPY commands but no more than 1 COPY per server core.&nbsp;=
 <br>
&gt; I tested this a while back on a 4 core server and when I attempted 5=
 <br>
&gt; COPY's at a time the time to complete went up almost 30%. &nbsp;I don=
't <br>
&gt; recall any benefit having fewer than 4 in this case but the server wa=
s <br>
&gt; only processing my data at the time. &nbsp;Indexes were on the target=
 table <br>
&gt; however I dropped all constraints. &nbsp;The UNIX split command is ha=
ndy <br>
&gt; for breaking the data up into individual files.</div>


<div><br>
I'm not using COPY.&nbsp; My dump file is a bunch if INSERT INTO statement=
s.&nbsp; I know it would be faster to use copy.&nbsp; If I can figure out=
 how to do this in one hour I will try it.&nbsp; I did two mysqldumps, one=
 with INSERT INTO and one as CSV to I can try COPY at a later time.&nbsp;&=
nbsp; I'm running five parallel psql processes to import the data which ha=
s been broken out by table.<br>

</div>


</div>


</div>

 <!-- end of AOLMsgPart_2_c288f977-0db0-4304-8212-8f80f6e47399 -->

</div>

</font></font>

----------MB_8CDB0774277BB85_27F8_18DE_web-mmc-m04.sysops.aol.com--