From: Dimitri Fontaine <dfontaine@hi-media.com>
Organization: Hi-Media
To: pgsql-performance@postgresql.org
Subject: Re: Improve COPY performance for large data sets
Date: Wed, 10 Sep 2008 19:17:37 +0200
User-Agent: KMail/1.9.9
References: <48C7FA69.1080507@brightbuilders.com>
In-Reply-To: <48C7FA69.1080507@brightbuilders.com>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1291707.uRBvZ8eSTv";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200809101917.40204.dfontaine@hi-media.com>

--nextPart1291707.uRBvZ8eSTv
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,

Le mercredi 10 septembre 2008, Ryan Hansen a =E9crit=A0:
> One thing I'm experiencing some trouble with is running a COPY of a
> large file (20+ million records) into a table in a reasonable amount of
> time.  Currently it's taking about 12 hours to complete on a 64 bit
> server with 3 GB memory allocated (shared_buffer), single SATA 320 GB
> drive.  I don't seem to get any improvement running the same operation
> on a dual opteron dual-core, 16 GB server.

You single SATA disk is probably very busy going from reading source file t=
o=20
writing data. You could try raising checkpoint_segments to 64 or more, but =
a=20
single SATA disk won't give you high perfs for IOs. You're getting what you=
=20
payed for...

You could maybe ease the disk load by launching the COPY from a remote (loc=
al=20
netword) machine, and while at it if the file is big, try parallel loading=
=20
with pgloader.

Regards,
=2D-=20
dim

--nextPart1291707.uRBvZ8eSTv
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEABECAAYFAkjIATEACgkQlBXRlnbh1blVmgCgrG2074cfSQRAHC5fLGCl6Is4
UaMAn0245z4RGkqfWrEz0E8nexTcZ8Xz
=Uva2
-----END PGP SIGNATURE-----

--nextPart1291707.uRBvZ8eSTv--