Обсуждение: Parallel pg_dump for 9.1
People have been talking about a parallel version of pg_dump a few times already. I have been working on some proof-of-concept code for this feature every now and then and I am planning to contribute this for 9.1. There are two main issues with a parallel version of pg_dump: The first one is that it requires a consistent snapshot among multiple pg_dump clients and the second is that currently the output goes to a single file and it is unclear what to do about multiple processes writing into a single file. - There are ideas on how to solve the issue with the consistent snapshot but in the end you can always solve it by stopping your application(s). I actually assume that whenever people are interested in a very fast dump, it is because they are doing some maintenance task (like migrating to a different server) that involves pg_dump. In these cases, they would stop their system anyway. Even if we had consistent snapshots in a future version, would we forbid people to run parallel dumps against old server versions? What I suggest is to just display a big warning if run against a server without consistent snapshot support (which currently is every version). - Regarding the output of pg_dump I am proposing two solutions. The first one is to introduce a new archive type "directory" where each table and each blob is a file in a directory, similar to the experimental "files" archive type. Also the idea has come up that you should be able to specify multiple directories in order to make use of several physical disk drives. Thinking this further, in order to manage all the mess that you can create with this, every file of the same backup needs to have a unique identifier and pg_restore should have a check parameter that tells you if your backup directory is in a sane and complete state (think about moving a file from one backup directory to another one or trying to restore from two directories which are from different backup sets...). The second solution to the single-file-problem is to generate no output at all, i.e. whatever you export from your source database you import directly into your target database, which in the end turns out to be a parallel form of "pg_dump | psql". In fact, technically this is rather a parallel pg_restore than a pg_dump as you need to respect the dependencies between objects. The good news is that with the parallel pg_restore of the custom archive format we have everything in place already for this dependency checking. The addition is a new archive type that dumps (just-in-time) whatever the dependency-algorithm decides to restore next. This is probably the fastest way that we can copy or upgrade a database when pg_migrator cannot be used (for example when you migrate to a different hardware architecture). As said, I have some working code for the features described (unix only), if anybody would like to give it a try already now, just let me know, I'd be happy to get some early test reports and you could check for the speedup to expect. But before I continue, I'd like to have a discussion about what is what people actually want and what is the best way to go forward here. I am currently not planning to make parallel dumps work with the custom format even though this would be possible if we changed the format to a certain degree. Comments? Joachim
On Mon, Mar 29, 2010 at 04:46:48PM +0200, Joachim Wieland wrote: > People have been talking about a parallel version of pg_dump a few > times already. I have been working on some proof-of-concept code for > this feature every now and then and I am planning to contribute this > for 9.1. > > There are two main issues with a parallel version of pg_dump: > > The first one is that it requires a consistent snapshot among > multiple pg_dump clients Cloning snapshots seems like the way to fix this. I don't know how far this project has drifted from the PostgreSQL code, but you might want to look here: http://pgfoundry.org/projects/snapclone/ > and the second is that currently the output goes to a single file > and it is unclear what to do about multiple processes writing into a > single file. I don't think that's a good idea. Coming up with a directory structure for the new parallel pg_dump seems like a much better idea. Andrew, do you have some notes on this? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Mar 29, 2010 at 10:46 AM, Joachim Wieland <joe@mcknight.de> wrote: > - There are ideas on how to solve the issue with the consistent > snapshot but in the end you can always solve it by stopping your > application(s). I actually assume that whenever people are interested > in a very fast dump, it is because they are doing some maintenance > task (like migrating to a different server) that involves pg_dump. In > these cases, they would stop their system anyway. > Even if we had consistent snapshots in a future version, would we > forbid people to run parallel dumps against old server versions? What > I suggest is to just display a big warning if run against a server > without consistent snapshot support (which currently is every > version). Seems reasonable. > - Regarding the output of pg_dump I am proposing two solutions. The > first one is to introduce a new archive type "directory" where each > table and each blob is a file in a directory, similar to the > experimental "files" archive type. Also the idea has come up that you > should be able to specify multiple directories in order to make use of > several physical disk drives. Thinking this further, in order to > manage all the mess that you can create with this, every file of the > same backup needs to have a unique identifier and pg_restore should > have a check parameter that tells you if your backup directory is in a > sane and complete state (think about moving a file from one backup > directory to another one or trying to restore from two directories > which are from different backup sets...). I think that specifying several directories is a piece of complexity that would be best left alone for a first version of this. But a single directory with multiple files sounds pretty reasonable. Of course we'll also need to support that format in non-parallel mode, and in pg_restore. > The second solution to the single-file-problem is to generate no > output at all, i.e. whatever you export from your source database you > import directly into your target database, which in the end turns out > to be a parallel form of "pg_dump | psql". This is a very interesting idea but you might want to get the other thing merged first, as it's going to present a different set of issues. > I am currently not planning to make parallel dumps work with the > custom format even though this would be possible if we changed the > format to a certain degree. I'm thinking we probably don't want to change the existing formats. ...Robert
Robert Haas wrote: > On Mon, Mar 29, 2010 at 10:46 AM, Joachim Wieland <joe@mcknight.de> wrote: [...] >> - Regarding the output of pg_dump I am proposing two solutions. The >> first one is to introduce a new archive type "directory" where each >> table and each blob is a file in a directory, similar to the >> experimental "files" archive type. Also the idea has come up that you >> should be able to specify multiple directories in order to make use of >> several physical disk drives. Thinking this further, in order to >> manage all the mess that you can create with this, every file of the >> same backup needs to have a unique identifier and pg_restore should >> have a check parameter that tells you if your backup directory is in a >> sane and complete state (think about moving a file from one backup >> directory to another one or trying to restore from two directories >> which are from different backup sets...). > > I think that specifying several directories is a piece of complexity > that would be best left alone for a first version of this. But a > single directory with multiple files sounds pretty reasonable. Of > course we'll also need to support that format in non-parallel mode, > and in pg_restore. > >> The second solution to the single-file-problem is to generate no >> output at all, i.e. whatever you export from your source database you >> import directly into your target database, which in the end turns out >> to be a parallel form of "pg_dump | psql". > > This is a very interesting idea but you might want to get the other > thing merged first, as it's going to present a different set of > issues. I had some prior discussion with joachim (and I suspect I had some influence in him trying to implement that) on that. The reason why this is really needed is that the current pg_restore -j is actually a net loss(vs "pg_dump | psql") in a LOT of scenarios that are basically "duplicate this database to that location" (or any migration really). The example at had is a 240GB production database with around 850 tables, it takes ~145min to dump that database single threaded(completely CPU bound), simply loading the SQL using psql can restore it in ~150min(again CPU bound both for COPY and index creation), -j8 brings that down to ~55min. So if you do the math(and a bit of handwaving): * using pg_dump | psql you get greatest(140,150) -> 150min. * using pg_dump -Z0 -Fc && pg_restore -j8 you get 145+55 -> 200min * using a theoretical parallel pg_dump and the existing parallel restore you would get: 50(just a guess for how fast it might be) + 55 -> 105min * a parallel dump & restore that can pipline would end up at greatest(50,55)->55min So a parallel dump alone would only give you a 50% speedup in total time for doing a migration/upgrade/dump-to-devbox despite the fact that it uses 8x the resources. A piplined solution would result in a ~3x speedup in total time and you don't even have to even think about stuff that might be a problem like having available diskspace on the source/destination to hold a full temporary dump(if you don't you might even have to add some transfer time as well). Stefan
On Mon, Mar 29, 2010 at 1:16 PM, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > Robert Haas wrote: >> >> On Mon, Mar 29, 2010 at 10:46 AM, Joachim Wieland <joe@mcknight.de> wrote: > > [...] >>> >>> - Regarding the output of pg_dump I am proposing two solutions. The >>> first one is to introduce a new archive type "directory" where each >>> table and each blob is a file in a directory, similar to the >>> experimental "files" archive type. Also the idea has come up that you >>> should be able to specify multiple directories in order to make use of >>> several physical disk drives. Thinking this further, in order to >>> manage all the mess that you can create with this, every file of the >>> same backup needs to have a unique identifier and pg_restore should >>> have a check parameter that tells you if your backup directory is in a >>> sane and complete state (think about moving a file from one backup >>> directory to another one or trying to restore from two directories >>> which are from different backup sets...). >> >> I think that specifying several directories is a piece of complexity >> that would be best left alone for a first version of this. But a >> single directory with multiple files sounds pretty reasonable. Of >> course we'll also need to support that format in non-parallel mode, >> and in pg_restore. >> >>> The second solution to the single-file-problem is to generate no >>> output at all, i.e. whatever you export from your source database you >>> import directly into your target database, which in the end turns out >>> to be a parallel form of "pg_dump | psql". >> >> This is a very interesting idea but you might want to get the other >> thing merged first, as it's going to present a different set of >> issues. > > I had some prior discussion with joachim (and I suspect I had some influence > in him trying to implement that) on that. > The reason why this is really needed is that the current pg_restore -j is > actually a net loss(vs "pg_dump | psql") in a LOT of scenarios that are > basically "duplicate this database to that location" (or any migration > really). > The example at had is a 240GB production database with around 850 tables, it > takes ~145min to dump that database single threaded(completely CPU bound), > simply loading the SQL using psql can restore it in ~150min(again CPU bound > both for COPY and index creation), -j8 brings that down to ~55min. > So if you do the math(and a bit of handwaving): > > * using pg_dump | psql you get greatest(140,150) -> 150min. > * using pg_dump -Z0 -Fc && pg_restore -j8 you get 145+55 -> 200min > * using a theoretical parallel pg_dump and the existing parallel restore you > would get: 50(just a guess for how fast it might be) + 55 -> 105min > * a parallel dump & restore that can pipline would end up at > greatest(50,55)->55min > > > So a parallel dump alone would only give you a 50% speedup in total time for > doing a migration/upgrade/dump-to-devbox despite the fact that it uses 8x > the resources. A piplined solution would result in a ~3x speedup in total > time and you don't even have to even think about stuff that might be a > problem like having available diskspace on the source/destination to hold a > full temporary dump(if you don't you might even have to add some transfer > time as well). It's a great idea - but there are two features here. I've seen many patches implementing two features during my relatively short time with the project and if the rejection rate hasn't been 100% it's certainly been close. If Joachim thinks he's got it all working, by all means submit both patches. One can apply over the other if they are interdependent. But I STRONGLY suggest separating this into two pieces - it is MUCH easier to get things applied that way, for good and valid reasons. ...Robert
On 3/29/10 7:46 AM, Joachim Wieland wrote:
> I actually assume that whenever people are interested
> in a very fast dump, it is because they are doing some maintenance
> task (like migrating to a different server) that involves pg_dump. In
> these cases, they would stop their system anyway.
Actually, I'd say that there's a broad set of cases of people who want
to do a parallel pg_dump while their system is active. Parallel pg_dump
on a stopped system will help some people (for migration, particularly)
but parallel pg_dump with snapshot cloning will help a lot more people.
For example, imagine a user who has a 16-core machine on a 14-drive RAID
10, and a 100-table 1TB database. At 2am, this person might reasonaly
want to allocate a large portion of the machine resources to the dump by
giving it 4 threads, without cutting access to the application.
So: if parallel dump in single-user mode is what you can get done, then
do it. We can always improve it later, and we have to start somewhere.But we will eventually need parallel pg_dump on
activesystems, and
that should remain on the TODO list.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> On 3/29/10 7:46 AM, Joachim Wieland wrote:
>> I actually assume that whenever people are interested
>> in a very fast dump, it is because they are doing some maintenance
>> task (like migrating to a different server) that involves pg_dump. In
>> these cases, they would stop their system anyway.
> Actually, I'd say that there's a broad set of cases of people who want
> to do a parallel pg_dump while their system is active. Parallel pg_dump
> on a stopped system will help some people (for migration, particularly)
> but parallel pg_dump with snapshot cloning will help a lot more people.
I doubt that. My thought about it is that parallel dump will suck
enough resources from the source server, both disk and CPU, that you
would never want to use it on a live production machine. Not even at
2am. And your proposed use case is hardly a "broad set" in any case.
Thus, Joachim's approach seems perfectly sane from here. I certainly
don't see that there's an argument for spending 10x more development
effort to pick up such use cases.
Another question that's worth asking is exactly what the use case would
be for parallel pg_dump against a live server, whether the snapshots are
synchronized or not. You will not be able to use that dump as a basis
for PITR, so there is no practical way of incorporating any changes that
occur after the dump begins. So what are you making it for? If it's a
routine backup for disaster recovery, fine, but it's not apparent why
you want max speed and to heck with live performance for that purpose.
I think migration to a new server version (that's too incompatible for
PITR or pg_migrate migration) is really the only likely use case.
regards, tom lane
On Mon, Mar 29, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> writes: >> On 3/29/10 7:46 AM, Joachim Wieland wrote: >>> I actually assume that whenever people are interested >>> in a very fast dump, it is because they are doing some maintenance >>> task (like migrating to a different server) that involves pg_dump. In >>> these cases, they would stop their system anyway. > >> Actually, I'd say that there's a broad set of cases of people who want >> to do a parallel pg_dump while their system is active. Parallel pg_dump >> on a stopped system will help some people (for migration, particularly) >> but parallel pg_dump with snapshot cloning will help a lot more people. > > I doubt that. My thought about it is that parallel dump will suck > enough resources from the source server, both disk and CPU, that you > would never want to use it on a live production machine. Not even at > 2am. And your proposed use case is hardly a "broad set" in any case. > Thus, Joachim's approach seems perfectly sane from here. I certainly > don't see that there's an argument for spending 10x more development > effort to pick up such use cases. > > Another question that's worth asking is exactly what the use case would > be for parallel pg_dump against a live server, whether the snapshots are > synchronized or not. You will not be able to use that dump as a basis > for PITR, so there is no practical way of incorporating any changes that > occur after the dump begins. So what are you making it for? If it's a > routine backup for disaster recovery, fine, but it's not apparent why > you want max speed and to heck with live performance for that purpose. > I think migration to a new server version (that's too incompatible for > PITR or pg_migrate migration) is really the only likely use case. It's completely possible that you could want to clone a server for dev and have more CPU and I/O bandwidth available than can be efficiently used by a non-parallel pg_dump. But certainly what Joachim is talking about will be a good start. I think there is merit to the synchronized snapshot stuff for pg_dump and perhaps other applications as well, but I think Joachim's (well-taken) point is that we don't have to treat it as a hard prerequisite. ...Robert
Robert Haas wrote: > > It's completely possible that you could want to clone a server for dev > and have more CPU and I/O bandwidth available than can be efficiently > used by a non-parallel pg_dump. But certainly what Joachim is talking > about will be a good start. I think there is merit to the > synchronized snapshot stuff for pg_dump and perhaps other applications > as well, but I think Joachim's (well-taken) point is that we don't > have to treat it as a hard prerequisite. > > > Possibly. I think the most useful thing that could be done right now is probably the least controversial, namely creating a directory type of archive, with support for pg_restore, including parallel pg_restore. Personally I think that's worth doing in its own right anyway. cheers andrew
Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> writes: >> On 3/29/10 7:46 AM, Joachim Wieland wrote: >>> I actually assume that whenever people are interested >>> in a very fast dump, it is because they are doing some maintenance >>> task (like migrating to a different server) that involves pg_dump. In >>> these cases, they would stop their system anyway. > >> Actually, I'd say that there's a broad set of cases of people who want >> to do a parallel pg_dump while their system is active. Parallel pg_dump >> on a stopped system will help some people (for migration, particularly) >> but parallel pg_dump with snapshot cloning will help a lot more people. > > I doubt that. My thought about it is that parallel dump will suck > enough resources from the source server, both disk and CPU, that you > would never want to use it on a live production machine. Not even at > 2am. And your proposed use case is hardly a "broad set" in any case. > Thus, Joachim's approach seems perfectly sane from here. I certainly > don't see that there's an argument for spending 10x more development > effort to pick up such use cases. > > Another question that's worth asking is exactly what the use case would > be for parallel pg_dump against a live server, whether the snapshots are > synchronized or not. You will not be able to use that dump as a basis > for PITR, so there is no practical way of incorporating any changes that > occur after the dump begins. So what are you making it for? If it's a > routine backup for disaster recovery, fine, but it's not apparent why > you want max speed and to heck with live performance for that purpose. > I think migration to a new server version (that's too incompatible for > PITR or pg_migrate migration) is really the only likely use case. I really doubt that - on fast systems pg_dump is completely CPU bottlenecked and typical 1-2U typical hardware you get these days has 8-16 cores so simply dedicating a few cores to dumping the database during quieter times is very realistic. Databases are growing larger and larger and the single threaded nature of pg_dump makes it very hard to even stay withing reasonable time limits for doing the backup. Stefan
On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: > on fast systems pg_dump is completely CPU bottlenecked Might be useful to profile why that is. I don't think pg_dump has historically been developed with CPU efficiency in mind.
On Tue, 30 Mar 2010 13:01:54 +0200, Peter Eisentraut <peter_e@gmx.net> wrote: > On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: >> on fast systems pg_dump is completely CPU bottlenecked > > Might be useful to profile why that is. I don't think pg_dump has > historically been developed with CPU efficiency in mind. Already done that (I had posted some WIP patches to speed up COPY, hopefully I'll have time to finish those one day ;) Most of the time spent in the postmaster process during "COPY TO" is in the datum -> string functions.
Peter Eisentraut wrote: > On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: >> on fast systems pg_dump is completely CPU bottlenecked > > Might be useful to profile why that is. I don't think pg_dump has > historically been developed with CPU efficiency in mind. It's not pg_dump that is the problem - it is COPY that is the limit. In my specific case als the fact that a lot of the columns are bytea adds to the horrible CPU overhead (fixed in 9.0). Still our bulk load & unload performance is still way slower on a per core comparision than a lot of other databases :( Stefan
On Mar 30, 2010, at 8:15 AM, Stefan Kaltenbrunner wrote: > Peter Eisentraut wrote: >> On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: >>> on fast systems pg_dump is completely CPU bottlenecked >> Might be useful to profile why that is. I don't think pg_dump has >> historically been developed with CPU efficiency in mind. > > It's not pg_dump that is the problem - it is COPY that is the limit. > In my specific case als the fact that a lot of the columns are bytea > adds to the horrible CPU overhead (fixed in 9.0). Still our bulk > load & unload performance is still way slower on a per core > comparision than a lot of other databases :( > Don't forget the zlib compression used in -Fc (unless you use -Z0) takes a fair amount of cpu too. I did some tests and it turned out that -Z0 actually took longer than - Z1 simply because there was a lot more data to write out, thus I became IO bound not CPU bound. There's a thing called pigz around that is a parallel gzip implementation - wonder how much of that could be adapted to pg_dumps use as compression does use a considerable amount of time (even at - Z1). The biggest problem I can immediately see is that it uses threads. -- Jeff Trout <jeff@jefftrout.com> http://www.stuarthamm.net/ http://www.dellsmartexitin.com/
Jeff wrote: > > On Mar 30, 2010, at 8:15 AM, Stefan Kaltenbrunner wrote: > >> Peter Eisentraut wrote: >>> On tis, 2010-03-30 at 08:39 +0200, Stefan Kaltenbrunner wrote: >>>> on fast systems pg_dump is completely CPU bottlenecked >>> Might be useful to profile why that is. I don't think pg_dump has >>> historically been developed with CPU efficiency in mind. >> >> It's not pg_dump that is the problem - it is COPY that is the limit. >> In my specific case als the fact that a lot of the columns are bytea >> adds to the horrible CPU overhead (fixed in 9.0). Still our bulk load >> & unload performance is still way slower on a per core comparision >> than a lot of other databases :( >> > > Don't forget the zlib compression used in -Fc (unless you use -Z0) takes > a fair amount of cpu too. > I did some tests and it turned out that -Z0 actually took longer than > -Z1 simply because there was a lot more data to write out, thus I became > IO bound not CPU bound. > > There's a thing called pigz around that is a parallel gzip > implementation - wonder how much of that could be adapted to pg_dumps > use as compression does use a considerable amount of time (even at > -Z1). The biggest problem I can immediately see is that it uses threads. all my numbers are with -Z0 and it is the backend (COPY and/or index creation) that is the limit. If you start using compression you are shifting the load to pg_dump. Stefan