Re: parallel pg_restore

Le mercredi 24 septembre 2008, Andrew Dunstan a écrit :
> No. The proposal will perform exactly the same set of steps as
> single-threaded pg_restore, but in parallel. The individual steps won't
> be broken up.

Ok, good for a solid trustworthy parallelism restore. Which is exactly what we
want. Just out of curiosity, do you plan to use Postgres-R helper backends
infrastructure?

> Quite apart from anything else, parallel data loading of individual
> tables will defeat clustering, as well as making it impossible to avoid
> WAL logging of the load (which I have made provision for).

Depends whether the different workers are able to work from the same
transaction or not, I imagine. Some work has been done to allow multiple
backends to be working in the exact same transaction (Simon's snapclone and
Postgres-R helper backend infrastructure), so one of them could TRUNCATE the
table and give a go signal to workers to fill the table. In the same
transaction.
Ok, I need to wake up now... :)

> The fact that custom archives are compressed by default would in fact
> make parallel loading of individual tables' data difficult with the
> present format. We'd have to do something like expanding it on the
> client (which might not even have enough disk space) and then split it
> before loading it to the server. That's pretty yucky. Alternatively,
> each loader thread would need to start decompressing the data from the
> start and thow away data until it got to the point it wanted to start
> restoring from. Also pretty yucky.

Another alternative is the round-robin reader implemented in pgloader, where
all the archive reading is done by a single worker, which then split what it
read to any number of coworkers, filling next queue(s) while previous one(s)
are busy COPYing to the server.

> Far better would be to provide for multiple data members in the archive
> and teach pg_dump to split large tables as it writes the archive. Then
> pg_restore would need comparatively little adjustment.

Well, that's another possibility, but I tend to prefer having the parallelism
mecanics into the restore side of things. It may be only an illusion, but
this way I have far more trust into my backups.

> Also, of course, you can split tables yourself by partitioning them.
> That would buy you parallel data load with what I am doing now, with no
> extra work.

And that's excellent :)

> In any case, data loading is very far from being the only problem. One
> of my clients has long running restores where the data load takes about
> 20% or so of the time - the rest is in index creation and the like. No
> amount of table splitting will make a huge difference to them, but
> parallel processing will.

Oh yes, I'm running into this too (not on the same level but still).
Parallel seqscan should help creating indexes in parallel without having the
disks going crazy for read - write - read - write etc sequences, and posix
advices should help a lot here too.
Does the dependancy tracker in pg_restore allows to consider FK creation are
dependant on matching PK being already there?

> As against that, if your problem is in loading
> one huge table, this won't help you much. However, this is not a pattern
> I see much - most of my clients seem to have several large tables plus a
> boatload of indexes. They will benefit a lot.

The use case given by Greg Smith at the time was loading a multi terabyte
table on a raid array with a lot of spindles. It then become impossible for a
single CPU to take full profit of the available write bandwith. No idea how
common this situation is in the field, though.

Regards,
--
dim