Re: Load experimentation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Dec 8, 2009 at 2:08 AM, Dimitri Fontaine <dfontaine@xxxxxxxxxxxx> wrote:
> Hi,
>
> Ben Brehmer <benbrehmer@xxxxxxxxx> 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.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux