From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Dimitri Fontaine <dfontaine(at)hi-media(dot)com> |
Cc: | Ben Brehmer <benbrehmer(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org, Thom Brown <thombrown(at)gmail(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, craig_james(at)emolecules(dot)com, kbuckham(at)applocation(dot)net, scott(dot)lists(at)enterprisedb(dot)com |
Subject: | Re: Load experimentation |
Date: | 2009-12-08 09:28:28 |
Message-ID: | dcc563d10912080128n204062cvad5279036f81ce5e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Tue, Dec 8, 2009 at 2:08 AM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> Hi,
>
> Ben Brehmer <benbrehmer(at)gmail(dot)com> writes:
>> By "Loading data" I am implying: "psql -U postgres -d somedatabase -f sql_file.sql". The sql_file.sql contains table creates and insert statements. There are no
>> indexes present nor created during the load.
>>
>> OS: x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-44)
>>
>> PostgreSQL: I will try upgrading to latest version.
>>
>> COPY command: Unfortunately I'm stuck with INSERTS due to the nature
>> this data was generated (Hadoop/MapReduce).
>
> What I think you could do is the followings:
>
> - switch to using 8.4
> - load your files in a *local* database
> - pg_dump -Fc
> - now pg_restore -j X on the amazon setup
>
> That way you will be using COPY rather than INSERTs and parallel loading
> built-in pg_restore (and optimisations of when to add the indexes and
> constraints). The X is to choose depending on the IO power and the
> number of CPU...
That's a lot of work to get to COPY. It might be enough to drop all
FK relations and indexes on the destination db in the cloud, load the
data in a few (or one) transaction(s), then recreate indexes and FK
relationships.
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Fontaine | 2009-12-08 09:37:15 | Re: Load experimentation |
Previous Message | Dimitri Fontaine | 2009-12-08 09:08:45 | Re: Load experimentation |