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
>