Message-ID: <4E5BA3C0.7080909@gmail.com>
Date: Mon, 29 Aug 2011 22:35:44 +0800
From: Rural Hunter <ruralhunter@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
 rv:6.0) Gecko/20110812 Thunderbird/6.0
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: Julio Leyva <jcleyva@hotmail.com>, pgsql-admin@postgresql.org
Subject: Re: plsql gets "out of memory"
References: <4E5B8FF7.4040007@gmail.com>
	<BAY154-W12A115156AED066911581DA1140@phx.gbl>
	<4E5B9604.5070602@gmail.com>
	<4E5B518002000025000407E5@gw.wicourts.gov>
In-Reply-To: <4E5B518002000025000407E5@gw.wicourts.gov>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
>