From: Dimitri Fontaine <dfontaine@hi-media.com>
To: pgsql-performance@postgresql.org
Subject: Re: Improve COPY performance for large data sets
Date: Wed, 10 Sep 2008 19:17:37 +0200
Message-ID: <200809101917.40204.dfontaine@hi-media.com> (raw)
In-Reply-To: <48C7FA69.1080507@brightbuilders.com>
References: <48C7FA69.1080507@brightbuilders.com>
Hi,
Le mercredi 10 septembre 2008, Ryan Hansen a écrit :
> 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 to
writing data. You could try raising checkpoint_segments to 64 or more, but a
single SATA disk won't give you high perfs for IOs. You're getting what you
payed for...
You could maybe ease the disk load by launching the COPY from a remote (local
netword) machine, and while at it if the file is big, try parallel loading
with pgloader.
Regards,
--
dim
Attachments:
signature.asc (application/pgp-signature, 196B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dfontaine@hi-media.com
Subject: Re: Improve COPY performance for large data sets
In-Reply-To: <200809101917.40204.dfontaine@hi-media.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox