From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kasia Tuszynska <ktuszynska@esri.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: loading a lot of data on windows is very slow
Date: Wed, 25 Aug 2010 14:37:29 -0400
Message-ID: <23105.1282761449@sss.pgh.pa.us> (raw)
In-Reply-To: <232B5217AD58584C87019E8933556D11021426D18F@redmx2.esri.com>
References: <232B5217AD58584C87019E8933556D11021426D18F@redmx2.esri.com>
Kasia Tuszynska <ktuszynska@esri.com> writes:
> PG: 8.3.8
> 2010-08-18 20:29:42 PDT ERROR: out of memory
> 2010-08-18 20:29:42 PDT DETAIL: Failed on request of size 134217728.
What command is this failure occurring in? There aren't that many that
would try to inhale your full work_mem in one gulp like that.
The short answer here is probably that 32-bit Windows is not the
platform to be trying to do memory-intensive work on, and you ought to
get yourself a real operating system and/or a 64-bit machine. You could
make it run within Windows' restrictions by cutting work_mem,
maintenance_work_mem, and/or shared_buffers, but given that it's already
taking a ridiculously long time, that doesn't seem like a path to a
solution.
Alternatively, you might be able to optimize the data load process a
bit. Have you read
http://www.postgresql.org/docs/8.3/static/populate.html
? I'm suspicious in particular that you might be trying to enforce
foreign keys incrementally, which is usually a bad approach for large
data loads.
Also, I'd advise rethinking the choice of PG 8.3.x if possible.
There are relevant performance improvements in 8.4.x.
> Client: In house application, I checked to see if it is not doing anything silly, it seems to be doing everything as expected, indexes are created after a table has loaded, we commit every 1000 inserts...
Committing after every few inserts is *not* an optimization for
Postgres. Try kicking that number up a few orders of magnitude.
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, ktuszynska@esri.com
Subject: Re: loading a lot of data on windows is very slow
In-Reply-To: <23105.1282761449@sss.pgh.pa.us>
* 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