Skip site navigation (1) Skip section navigation (2)

parallel restore test results

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: parallel restore test results
Date: 2008-10-03 03:42:03
Message-ID: 48E5948B.9010601@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers
I have just completed a test of the patch I posted a few days ago.

The test is a 2Gb dump file that restores to a 22Gb database. The 
database is very complex, with some 28,000 objects.

The baseline test was run in a single transaction:

    pg_restore --use-list tlist -1 -d mdata ../ned-int.pz

The parallel test was run with 8 concurrent threads, truncating the data 
members before load:

    pg_restore --use-list tlist -m 8 --truncate-before-load -d mdatap 
../ned-int.pz

The server is an 8-way machine running 2.66 Ghz Xeons running OpenSuse 
11.0. with Linux kernel 2.6.25. with 16gB of physical RAM.

Server settings:

    max_connections = 100                   # (change requires restart)
    shared_buffers = 1GB                    # min 128kB
    work_mem = 100MB                                # min 64kB
    maintenance_work_mem = 200MB            # min 1MB
    fsync = off                             # turns forced 
synchronization on or off
    synchronous_commit = off                # immediate fsync at commit
    full_page_writes = off                  # recover from partial page 
writes
    checkpoint_segments = 100               # in logfile segments, min 
1, 16MB each
    autovacuum = off                        # Enable autovacuum 
subprocess?  'on'

Server version is CVS HEAD from yesterday some time.

Overall result: baseline: 4h32m  parallel: 0h 54m.

Breaking that down:

                      baseline         parallel
    pre-data         35m                  16m
    data load        38m                  20m*
    post-data       3h29m                30m*

* these two steps overlap.


I am not sure why the pre-data load took so much longer in the baseline. 
I can only assume that there is a small penalty per object from doing it 
all in a single transaction. 

There was a single error on the parallel run, for which I am currently 
unable to account:

ERROR:  relation "foo" does not exist
    Command was:
ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_role_fk FOREIGN KEY (foo_key, role_key) ....


But relation "foo" does indeed exist, and in fact three out of four of 
its FK constraints got created successfully.

cheers

andrew







Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2008-10-03 05:29:34
Subject: using pgfarm for other projects
Previous:From: Andrew DunstanDate: 2008-10-02 23:07:55
Subject: Re: [PATCHES] Infrastructure changes for recovery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group