WIP patch for parallel pg_dump
This is the second patch for parallel pg_dump, now the actual part that
parallelizes the whole thing. More precisely, it adds parallel
backup/restore
to pg_dump/pg_restore for the directory archive format and keeps the
parallel
restore part of the custom archive format. Combined with my archive format
directory patch, which also includes a prototype of the liblzf compression
you
can combine this compression with any of the just mentioned backup/restore
scenarios. This patch is on top of the previous directory patch.
You would add a regular parallel dump with
$ pg_dump -j 4 -Fd -f out.dir dbname
In previous discussions there was a request to add support for multiple
directories, which I have done as well, so that you can also run
$ pg_dump -j 4 -Fd -f dir1:dir2:dir3 dbname
to equally distribute the data among those three directories (we can still
discuss the syntax, I am not all that happy with the colon either...)
The dump would always start with the largest objects, by looking at the
relpages column of pg_class which should give a good estimate. The order of
the
objects to restore is determined by the dependencies among the objects
(which
is already used in the parallel restore of the custom archivetype).
The file test.sh includes some example commands that I have run here as a
kind
of regression test that should give you an impression of how to call it from
the
command line.
One thing that is currently missing is proper support for Windows, this is
the next
thing that I will be working on. Also this version still gives quite a bunch
of debug
information about what the processes are doing, so don't try to pipe the
pg_dump output anywhere (even when not run in parallel), it will probably
just
not work...
The missing part that would make parallel pg_dump work with no strings
attached
is snapshot synchronization. As long as there are no synchronized snapshots,
you would need to stop writing to your database before starting the parallel
pg_dump. However it turns out that most often when you are especially
concerned
about a fast dump, you have shut down your applications anyway (which is the
reason why you are so concerned about speed in the first place). These cases
are typically database migrations from one host/platform to another or
database
upgrades without pg_migrator.
Joachim
Attachments:
pg_dump-parallel.difftext/x-patch; charset=US-ASCII; name=pg_dump-parallel.diffDownload+2202-874
On Sun, Nov 14, 2010 at 6:52 PM, Joachim Wieland <joe@mcknight.de> wrote:
You would add a regular parallel dump with
$ pg_dump -j 4 -Fd -f out.dir dbname
So this is an updated series of patches for my parallel pg_dump WIP
patch. Most importantly it now runs on Windows once you get it to
compile there (I have added the new files to the respective project of
Mkvcbuild.pm but I wondered why the other archive formats do not need
to be defined in that file...).
So far nobody has volunteered to review this patch. It would be great
if people could at least check it out, run it and let me know if it
works and if they have any comments.
I have put all four patches in a tar archive, the patches must be
applied sequentially:
1. pg_dump_compression-refactor.diff
2. pg_dump_directory.diff
3. pg_dump_directory_parallel.diff
4. pg_dump_directory_parallel_lzf.diff
The compression-refactor patch does not include Heikki's latest changes yet.
And the last of the four patches adds LZF compression for whoever
wants to try that out. You need to link against an already installed
liblzf and call it with --compress-lzf.
Joachim
Attachments:
pg_dump_parallel.tar.gzapplication/x-gzip; name=pg_dump_parallel.tar.gzDownload+0-1
On 02.12.2010 07:39, Joachim Wieland wrote:
On Sun, Nov 14, 2010 at 6:52 PM, Joachim Wieland<joe@mcknight.de> wrote:
You would add a regular parallel dump with
$ pg_dump -j 4 -Fd -f out.dir dbname
So this is an updated series of patches for my parallel pg_dump WIP
patch. Most importantly it now runs on Windows once you get it to
compile there (I have added the new files to the respective project of
Mkvcbuild.pm but I wondered why the other archive formats do not need
to be defined in that file...).So far nobody has volunteered to review this patch. It would be great
if people could at least check it out, run it and let me know if it
works and if they have any comments.
That's a big patch..
I don't see the point of the sort-by-relpages code. The order the
objects are dumped should be irrelevant, as long as you obey the
restrictions dictated by dependencies. Or is it only needed for the
multiple-target-dirs feature? Frankly I don't see the point of that, so
it would be good to cull it out at least in this first stage.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
I don't see the point of the sort-by-relpages code. The order the objects
are dumped should be irrelevant, as long as you obey the restrictions
dictated by dependencies. Or is it only needed for the multiple-target-dirs
feature? Frankly I don't see the point of that, so it would be good to cull
it out at least in this first stage.
From the talk at CHAR(10), and provided memory serves, it's an
optimisation so that you're doing largest file in a process and all the
little file in other processes. In lots of case the total pg_dump
duration is then reduced to about the time to dump the biggest files.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Thu, Dec 2, 2010 at 6:19 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
I don't see the point of the sort-by-relpages code. The order the objects
are dumped should be irrelevant, as long as you obey the restrictions
dictated by dependencies. Or is it only needed for the multiple-target-dirs
feature? Frankly I don't see the point of that, so it would be good to cull
it out at least in this first stage.
A guy called Dimitri Fontaine actually proposed the
serveral-directories feature here and other people liked the idea.
http://archives.postgresql.org/pgsql-hackers/2008-02/msg01061.php :-)
The code doesn't change much with or without it, and if people are no
longer in favour of it, I have no problem with taking it out.
As Dimitri has already pointed out, the relpage sorting thing is there
to start with the largest table(s) first.
Joachim
Joachim Wieland <joe@mcknight.de> writes:
A guy called Dimitri Fontaine actually proposed the
serveral-directories feature here and other people liked the idea.
Hehe :)
Reading that now, it could be that I didn't know at the time that given
a powerful enough subsystem disk there's no way to saturate it with one
CPU. So the use case of parralel dump in a bunch or user given locations
would be to use different mount points (disk subsystems) at the same
time. Not sure how releveant it is.
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 12/02/2010 05:50 AM, Dimitri Fontaine wrote:
So the use case of parralel dump in a bunch or user given locations
would be to use different mount points (disk subsystems) at the same
time. Not sure how releveant it is.
I think it will complicate this feature unnecessarily for 9.1.
Personally, I need this patch so much I'm thinking of backporting it.
However, having all the data go to one directory/mount wouldn't trouble
me at all.
Now, if only I could think of some way to write a parallel dump to a set
of pipes, I'd be in heaven.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On 12/02/2010 12:56 PM, Josh Berkus wrote:
On 12/02/2010 05:50 AM, Dimitri Fontaine wrote:
So the use case of parralel dump in a bunch or user given locations
would be to use different mount points (disk subsystems) at the same
time. Not sure how releveant it is.I think it will complicate this feature unnecessarily for 9.1.
Personally, I need this patch so much I'm thinking of backporting it.
However, having all the data go to one directory/mount wouldn't
trouble me at all.Now, if only I could think of some way to write a parallel dump to a
set of pipes, I'd be in heaven.
The only way I can see that working sanely would be to have a program
gathering stuff at the other end of the pipes, and ensuring it was all
coherent. That would be a huge growth in scope for this, and I seriously
doubt it's worth it.
cheers
andrew
Now, if only I could think of some way to write a parallel dump to a
set of pipes, I'd be in heaven.The only way I can see that working sanely would be to have a program
gathering stuff at the other end of the pipes, and ensuring it was all
coherent. That would be a huge growth in scope for this, and I seriously
doubt it's worth it.
Oh, no question. And there's workarounds ... sshfs, for example. I'm
just thinking of the ad-hoc parallel backup I'm running today, which
relies heavily on pipes.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
On Thu, Dec 2, 2010 at 12:56 PM, Josh Berkus <josh@agliodbs.com> wrote:
Now, if only I could think of some way to write a parallel dump to a set of
pipes, I'd be in heaven.
What exactly are you trying to accomplish with the pipes?
Joachim
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
That's a big patch..
Not nearly big enough :-(
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.
regards, tom lane
On 12/02/2010 05:01 PM, Tom Lane wrote:
Heikki Linnakangas<heikki.linnakangas@enterprisedb.com> writes:
That's a big patch..
Not nearly big enough :-(
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.
Well, snapshot cloning should allow that objection to be overcome, no?
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> writes:
On 12/02/2010 05:01 PM, Tom Lane wrote:
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.
Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
(I'm not actually convinced that snapshot cloning is the only problem
here; locking could be an issue too, if there are concurrent processes
trying to take locks that will conflict with pg_dump's. But the
snapshot issue is definitely a showstopper.)
regards, tom lane
Dimitri Fontaine wrote:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
I don't see the point of the sort-by-relpages code. The order the objects
are dumped should be irrelevant, as long as you obey the restrictions
dictated by dependencies. Or is it only needed for the multiple-target-dirs
feature? Frankly I don't see the point of that, so it would be good to cull
it out at least in this first stage.From the talk at CHAR(10), and provided memory serves, it's an
optimisation so that you're doing largest file in a process and all the
little file in other processes. In lots of case the total pg_dump
duration is then reduced to about the time to dump the biggest files.
Seems there should be a comment in the code explaining why this is being
done.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 12/02/2010 05:32 PM, Tom Lane wrote:
Andrew Dunstan<andrew@dunslane.net> writes:
On 12/02/2010 05:01 PM, Tom Lane wrote:
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
Yes, I agree with that.
(I'm not actually convinced that snapshot cloning is the only problem
here; locking could be an issue too, if there are concurrent processes
trying to take locks that will conflict with pg_dump's. But the
snapshot issue is definitely a showstopper.)
Why is that more an issue with parallel pg_dump?
cheers
andrew
On Thu, Dec 2, 2010 at 5:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> writes:
On 12/02/2010 05:01 PM, Tom Lane wrote:
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
Yes, by all means let's allow the perfect to be the enemy of the good.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 12/02/2010 07:13 PM, Robert Haas wrote:
On Thu, Dec 2, 2010 at 5:32 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan<andrew@dunslane.net> writes:
On 12/02/2010 05:01 PM, Tom Lane wrote:
In the past, proposals for this have always been rejected on the grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
Yes, by all means let's allow the perfect to be the enemy of the good.
That seems like a bit of an easy shot. Requiring that parallel pg_dump
produce a dump that is as consistent as non-parallel pg_dump currently
produces isn't unreasonable. It's not stopping us moving forward, it's
just not wanting to go backwards.
And it shouldn't be terribly hard. IIRC Joachim has already done some
work on it.
cheers
andrew
On Thu, Dec 2, 2010 at 7:21 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
In the past, proposals for this have always been rejected on the
grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
Yes, by all means let's allow the perfect to be the enemy of the good.
That seems like a bit of an easy shot. Requiring that parallel pg_dump
produce a dump that is as consistent as non-parallel pg_dump currently
produces isn't unreasonable. It's not stopping us moving forward, it's just
not wanting to go backwards.
I certainly agree that would be nice. But if Joachim thought the
patch were useless without that, perhaps he wouldn't have bothered
writing it at this point. In fact, he doesn't think that, and he
mentioned the use cases he sees in his original post. But even
supposing you wouldn't personally find this useful in those
situations, how can you possibly say that HE wouldn't find it useful
in those situations? I understand that people sometimes show up here
and ask for ridiculous things, but I don't think we should be too
quick to attribute ridiculousness to regular contributors.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 12/02/2010 07:48 PM, Robert Haas wrote:
On Thu, Dec 2, 2010 at 7:21 PM, Andrew Dunstan<andrew@dunslane.net> wrote:
In the past, proposals for this have always been rejected on the
grounds
that it's impossible to assure a consistent dump if different
connections are used to read different tables. I fail to understand
why that consideration can be allowed to go by the wayside now.Well, snapshot cloning should allow that objection to be overcome, no?
Possibly, but we need to see that patch first not second.
Yes, by all means let's allow the perfect to be the enemy of the good.
That seems like a bit of an easy shot. Requiring that parallel pg_dump
produce a dump that is as consistent as non-parallel pg_dump currently
produces isn't unreasonable. It's not stopping us moving forward, it's just
not wanting to go backwards.I certainly agree that would be nice. But if Joachim thought the
patch were useless without that, perhaps he wouldn't have bothered
writing it at this point. In fact, he doesn't think that, and he
mentioned the use cases he sees in his original post. But even
supposing you wouldn't personally find this useful in those
situations, how can you possibly say that HE wouldn't find it useful
in those situations? I understand that people sometimes show up here
and ask for ridiculous things, but I don't think we should be too
quick to attribute ridiculousness to regular contributors.
Umm, nobody has attributed ridiculousness to anyone. Please don't put
words in my mouth. But I think this is a perfectly reasonable discussion
to have. Nobody gets to come along and get the features they want
without some sort of consensus, not me, not you, not Joachim, not Tom.
cheers
andrew
On Dec 2, 2010, at 8:11 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
Umm, nobody has attributed ridiculousness to anyone. Please don't put words in my mouth. But I think this is a perfectly reasonable discussion to have. Nobody gets to come along and get the features they want without some sort of consensus, not me, not you, not Joachim, not Tom.
I'm not disputing that we COULD reject the patch. I AM disputing that we've made a cogent argument for doing so.
...Robert
