From: Rural Hunter <ruralhunter@gmail.com>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Julio Leyva <jcleyva@hotmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: plsql gets "out of memory"
Date: Mon, 29 Aug 2011 22:35:44 +0800
Message-ID: <4E5BA3C0.7080909@gmail.com> (raw)
In-Reply-To: <4E5B518002000025000407E5@gw.wicourts.gov>
References: <4E5B8FF7.4040007@gmail.com>
<BAY154-W12A115156AED066911581DA1140@phx.gbl>
<4E5B9604.5070602@gmail.com>
<4E5B518002000025000407E5@gw.wicourts.gov>
yes, the size of my database(around 200g) is very close to yours. How
long did it take you to migrate the data(100-200G)? I thought about to
write java code to transfer the data but it's a bit trivial. It would be
the best if I can complete this with regular export/import way. if that
won't work, then writing java code to do the task will be the choice.
Another problem is, from my test, the performance of bulk insert looks
not very good. I estimated the importing of 30G data would take about
10-20 hours based on the progress. I already did some tweaks such as:
fsync=off
archive_mode=off
Increase checkpoint_segments
drop indexes and primary keys
I have about 5G memory free on the server and have these memory settings:
shared_buffers = 1GB
work_mem = 8MB
maintenance_work_mem = 1GB
effective_cache_size = 4GB
Not sure if there is any room to increase the performance of bulk
insert. I monitored the server when the import running. The utilization
of CPU/disk is very low. The memory usage seems no much change when
pgsql is up or down. So looks the performance bottleneck is not on
cpu/disk/memory. I'm a bit lost on this and have no idea what to
check/change.
δΊ 2011/8/29 21:44, Kevin Grittner ει:
> Rural Hunter<ruralhunter@gmail.com> wrote:
>
>> it's a problem of migrating vast data from mysql to pgsql.
>
> I don't know how helpful you'll find this, but when we migrated to
> PostgreSQL a few years ago, we had good luck with using Java to
> stream from one database to another. Be sure to include a lot of
> inserts in each database transactions. We used prepared statements
> for the inserts.
>
> I don't know whether you would consider our data "vast" -- we had
> about 100 production databases. At the time the largest was 200 GB.
>
> -Kevin
>
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: ruralhunter@gmail.com, Kevin.Grittner@wicourts.gov, jcleyva@hotmail.com
Subject: Re: plsql gets "out of memory"
In-Reply-To: <4E5BA3C0.7080909@gmail.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