From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-patches@postgresql.org
Subject: Re: pg_dump additional options for performance
Date: Sun, 27 Jul 2008 10:31:48 +0100
Message-ID: <1217151108.3894.1218.camel@ebony.2ndQuadrant> (raw)
In-Reply-To: <24456.1217094974@sss.pgh.pa.us>
References: <20080720214335.GB18846@tamriel.snowman.net>
<1216591180.19656.848.camel@ebony.2ndQuadrant>
<20080721011829.GC18846@tamriel.snowman.net>
<20080721014942.GC30869@sonic.net>
<20080721015850.GD18846@tamriel.snowman.net>
<18415.1216611275@sss.pgh.pa.us>
<1216621040.19656.867.camel@ebony.2ndQuadrant>
<20080721115602.GG18846@tamriel.snowman.net>
<1216831237.3894.676.camel@ebony.2ndQuadrant>
<1216832987.3894.678.camel@ebony.2ndQuadrant>
<20080724032047.GI18846@tamriel.snowman.net>
<1216885031.3894.739.camel@ebony.2ndQuadrant>
<21687.1216886041@sss.pgh.pa.us>
<1216889809.3894.747.camel@ebony.2ndQuadrant>
<2818.1217027784@sss.pgh.pa.us>
<1217063104.3894.1100.camel@ebony.2ndQuadrant>
<23540.1217089204@sss.pgh.pa.us>
<1217091402.3894.1185.camel@ebony.2ndQuadrant>
<24456.1217094974@sss.pgh.pa.us>
On Sat, 2008-07-26 at 13:56 -0400, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > I want to dump tables separately for performance reasons. There are
> > documented tests showing 100% gains using this method. There is no gain
> > adding this to pg_restore. There is a gain to be had - parallelising
> > index creation, but this patch doesn't provide parallelisation.
>
> Right, but the parallelization is going to happen sometime, and it is
> going to happen in the context of pg_restore.
I honestly think there is less benefit that way than if we consider
things more as a whole:
To do data dump quickly we need to dump different tables to different
disks simultaneously. By its very nature, that cannot end with just a
single file. So the starting point for any restore must be potentially
more than one file.
There are two ways of dumping: either multi-thread pg_dump, or allow
multiple pg_dumps to work together. Second option much less work, same
result. (Either way we also need a way for multiple concurrent sessions
to share a snapshot.)
When restoring, we can then just use multiple pg_restore sessions to
restore the individual data files. Or again we can write a
multi-threaded pg_restore to do the same thing - why would I bother
doing that when I already can? It gains us nothing.
Parallelising the index creation seems best done using concurrent psql.
We've agreed some mods to psql to put multi-sessions in there. If we do
that right, then we can make pg_restore generate a psql script with
multi-session commands scattered appropriately throughout.
Parallel pg_restore is a lot of work for a narrow use case. Concurrent
psql provides a much wider set of use cases.
So fully parallelising dump/restore can be achieved by
* splitting dump into pieces (this patch)
* allowing sessions to share a common snapshot
* concurrent psql
* changes to pg_restore/psql/pg_dump to allow commands to be inserted
which will use concurrent psql features
If we do things this way then we have some useful tools that can be used
in a range of use cases, not just restore.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: simon@2ndquadrant.com, tgl@sss.pgh.pa.us, sfrost@snowman.net
Subject: Re: pg_dump additional options for performance
In-Reply-To: <1217151108.3894.1218.camel@ebony.2ndQuadrant>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox