Thread: Re: [PERFORM] pg_dump and thousands of schemas
>>> We recently fixed a couple of O(N^2) loops in pg_dump, but those covered >>> extremely specific cases that might or might not have anything to do >>> with what you're seeing. The complainant was extremely helpful about >>> tracking down the problems: >>> http://archives.postgresql.org/pgsql-general/2012-03/msg00957.php >>> http://archives.postgresql.org/pgsql-committers/2012-03/msg00225.php >>> http://archives.postgresql.org/pgsql-committers/2012-03/msg00230.php >> >> I'm wondering if these fixes (or today's commit) include the case for >> a database has ~100 thounsands of tables, indexes. One of my customers >> has had troubles with pg_dump for the database, it takes over 10 >> hours. > > So I did qucik test with old PostgreSQL 9.0.2 and current (as of > commit 2755abf386e6572bad15cb6a032e504ad32308cc). In a fresh initdb-ed > database I created 100,000 tables, and each has two integer > attributes, one of them is a primary key. Creating tables were > resonably fast as expected (18-20 minutes). This created a 1.4GB > database cluster. > > pg_dump dbname >/dev/null took 188 minutes on 9.0.2, which was pretty > long time as the customer complained. Now what was current? Well it > took 125 minutes. Ps showed that most of time was spent in backend. > > Below is the script to create tables. > > cnt=100000 > while [ $cnt -gt 0 ] > do > psql -e -p 5432 -c "create table t$cnt(i int primary key, j int);" test > cnt=`expr $cnt - 1` > done > > p.s. You need to increate max_locks_per_transaction before running > pg_dump (I raised to 640 in my case). Just for record, I rerun the test again with my single-LOCK patch, and now total runtime of pg_dump is 113 minutes. 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). So far, I'm glad to see 40% time savings at this point. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: > Just for record, I rerun the test again with my single-LOCK patch, and > now total runtime of pg_dump is 113 minutes. > 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). > > So far, I'm glad to see 40% time savings at this point. I see only 9.6% savings (100 * (113/125 - 1)). What am I missing? Cheers robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
> On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> Just for record, I rerun the test again with my single-LOCK patch, and >> now total runtime of pg_dump is 113 minutes. >> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). >> >> So far, I'm glad to see 40% time savings at this point. > > I see only 9.6% savings (100 * (113/125 - 1)). What am I missing? What I meant was (100 * (113/188 - 1)). -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
On Thu, May 31, 2012 at 4:07 PM, Tatsuo Ishii <ishii@postgresql.org> wrote: >> On Thu, May 31, 2012 at 10:45 AM, Tatsuo Ishii <ishii@postgresql.org> wrote: >>> Just for record, I rerun the test again with my single-LOCK patch, and >>> now total runtime of pg_dump is 113 minutes. >>> 188 minutes(9.0)->125 minutes(git master)->113 minutes(with my patch). >>> >>> So far, I'm glad to see 40% time savings at this point. >> >> I see only 9.6% savings (100 * (113/125 - 1)). What am I missing? > > What I meant was (100 * (113/188 - 1)). OK, my fault was to assume you wanted to measure only your part, while apparently you meant overall savings. But Tom had asked for separate measurements if I understood him correctly. Also, that measurement of your change would go after the O(N^2) fix. It could actually turn out to be much more than 9% because the overall time would be reduced even more dramatic. So it might actually be good for your fix to wait a bit. ;-) Kind regards robert -- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/
On Thu, May 31, 2012 at 11:17 AM, Robert Klemme <shortcutter@googlemail.com> wrote: > > OK, my fault was to assume you wanted to measure only your part, while > apparently you meant overall savings. But Tom had asked for separate > measurements if I understood him correctly. Also, that measurement of > your change would go after the O(N^2) fix. It could actually turn out > to be much more than 9% because the overall time would be reduced even > more dramatic. So it might actually be good for your fix to wait a > bit. ;-) It's not clear whether Tom is already working on that O(N^2) fix in locking. I'm asking because it doesn't seem like a complicated patch, contributors may want to get working if not ;-)
Claudio Freire <klaussfreire@gmail.com> writes:
> It's not clear whether Tom is already working on that O(N^2) fix in locking.
I'm not; Jeff Janes is. But you shouldn't be holding your breath
anyway, since it's 9.3 material at this point.
regards, tom lane
On Thu, May 31, 2012 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Claudio Freire <klaussfreire@gmail.com> writes: >> It's not clear whether Tom is already working on that O(N^2) fix in locking. > > I'm not; Jeff Janes is. But you shouldn't be holding your breath > anyway, since it's 9.3 material at this point. I agree we can't back-patch that change, but then I think we ought to consider back-patching some variant of Tatsuo's patch. Maybe it's not reasonable to thunk an arbitrary number of relation names in there on one line, but how about 1000 relations per LOCK statement or so? I guess we'd need to see how much that erodes the benefit, but we've certainly done back-branch rearrangements in pg_dump in the past to fix various kinds of issues, and this is pretty non-invasive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, May 31, 2012 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm not; Jeff Janes is. �But you shouldn't be holding your breath
>> anyway, since it's 9.3 material at this point.
> I agree we can't back-patch that change, but then I think we ought to
> consider back-patching some variant of Tatsuo's patch. Maybe it's not
> reasonable to thunk an arbitrary number of relation names in there on
> one line, but how about 1000 relations per LOCK statement or so? I
> guess we'd need to see how much that erodes the benefit, but we've
> certainly done back-branch rearrangements in pg_dump in the past to
> fix various kinds of issues, and this is pretty non-invasive.
I am not convinced either that this patch will still be useful after
Jeff's fix goes in, or that it provides any meaningful savings when
you consider a complete pg_dump run. Yeah, it will make the lock
acquisition phase faster, but that's not a big part of the runtime
except in very limited scenarios (--schema-only, perhaps).
The performance patches we applied to pg_dump over the past couple weeks
were meant to relieve pain in situations where the big server-side
lossage wasn't the dominant factor in runtime (ie, partial dumps).
But this one is targeting exactly that area, which is why it looks like
a band-aid and not a fix to me.
regards, tom lane
On Thu, May 31, 2012 at 10:50:51AM -0400, Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > On Thu, May 31, 2012 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> I'm not; Jeff Janes is. �But you shouldn't be holding your breath > >> anyway, since it's 9.3 material at this point. > > > I agree we can't back-patch that change, but then I think we ought to > > consider back-patching some variant of Tatsuo's patch. Maybe it's not > > reasonable to thunk an arbitrary number of relation names in there on > > one line, but how about 1000 relations per LOCK statement or so? I > > guess we'd need to see how much that erodes the benefit, but we've > > certainly done back-branch rearrangements in pg_dump in the past to > > fix various kinds of issues, and this is pretty non-invasive. > > I am not convinced either that this patch will still be useful after > Jeff's fix goes in, or that it provides any meaningful savings when > you consider a complete pg_dump run. Yeah, it will make the lock > acquisition phase faster, but that's not a big part of the runtime > except in very limited scenarios (--schema-only, perhaps). FYI, that is the pg_upgrade use-case, and pg_dump/restore time is reportedly taking the majority of time in many cases. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, May 31, 2012 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > The performance patches we applied to pg_dump over the past couple weeks > were meant to relieve pain in situations where the big server-side > lossage wasn't the dominant factor in runtime (ie, partial dumps). > But this one is targeting exactly that area, which is why it looks like > a band-aid and not a fix to me. No, Tatsuo's patch attacks a phase dominated by latency in some setups. That it's also becoming slow currently because of the locking cost is irrelevant, with locking sped up, the patch should only improve the phase even further. Imagine the current timeline: * = locking . = waiting *.*.**.**.***.***.****.****.*****.**** Tatsuo's patch converts it to: *.************** The locking fix would turn the timeline into: *.*.*.*.*.*.* Tatsuo's patch would turn that into: ******* And, as noted before, pg_dump --schema-only is a key bottleneck in pg_upgrade.
On Thu, May 31, 2012 at 10:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, May 31, 2012 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I'm not; Jeff Janes is. But you shouldn't be holding your breath >>> anyway, since it's 9.3 material at this point. > >> I agree we can't back-patch that change, but then I think we ought to >> consider back-patching some variant of Tatsuo's patch. Maybe it's not >> reasonable to thunk an arbitrary number of relation names in there on >> one line, but how about 1000 relations per LOCK statement or so? I >> guess we'd need to see how much that erodes the benefit, but we've >> certainly done back-branch rearrangements in pg_dump in the past to >> fix various kinds of issues, and this is pretty non-invasive. > > I am not convinced either that this patch will still be useful after > Jeff's fix goes in, ... But people on older branches are not going to GET Jeff's fix. > or that it provides any meaningful savings when > you consider a complete pg_dump run. Yeah, it will make the lock > acquisition phase faster, but that's not a big part of the runtime > except in very limited scenarios (--schema-only, perhaps). That is not a borderline scenario, as others have also pointed out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Thu, May 31, 2012 at 11:04:12AM -0400, Robert Haas wrote: > On Thu, May 31, 2012 at 10:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Robert Haas <robertmhaas@gmail.com> writes: > >> On Thu, May 31, 2012 at 10:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >>> I'm not; Jeff Janes is. But you shouldn't be holding your breath > >>> anyway, since it's 9.3 material at this point. > > > >> I agree we can't back-patch that change, but then I think we ought to > >> consider back-patching some variant of Tatsuo's patch. Maybe it's not > >> reasonable to thunk an arbitrary number of relation names in there on > >> one line, but how about 1000 relations per LOCK statement or so? I > >> guess we'd need to see how much that erodes the benefit, but we've > >> certainly done back-branch rearrangements in pg_dump in the past to > >> fix various kinds of issues, and this is pretty non-invasive. > > > > I am not convinced either that this patch will still be useful after > > Jeff's fix goes in, ... > > But people on older branches are not going to GET Jeff's fix. FYI, if it got into Postgres 9.2, everyone upgrading to Postgres 9.2 would benefit because pg_upgrade uses the new cluster's pg_dumpall. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Claudio Freire <klaussfreire@gmail.com> writes:
> On Thu, May 31, 2012 at 11:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The performance patches we applied to pg_dump over the past couple weeks
>> were meant to relieve pain in situations where the big server-side
>> lossage wasn't the dominant factor in runtime (ie, partial dumps).
>> But this one is targeting exactly that area, which is why it looks like
>> a band-aid and not a fix to me.
> No, Tatsuo's patch attacks a phase dominated by latency in some
> setups.
No, it does not. The reason it's a win is that it avoids the O(N^2)
behavior in the server. Whether the bandwidth savings is worth worrying
about cannot be proven one way or the other as long as that elephant
is in the room.
regards, tom lane
On Thu, May 31, 2012 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> No, Tatsuo's patch attacks a phase dominated by latency in some >> setups. > > No, it does not. The reason it's a win is that it avoids the O(N^2) > behavior in the server. Whether the bandwidth savings is worth worrying > about cannot be proven one way or the other as long as that elephant > is in the room. > > regards, tom lane I understand that, but if the locking is fixed and made to be O(N) (and hence each table locking O(1)), then latency suddenly becomes the dominating factor. I'm thinking, though, pg_upgrade runs locally, contrary to pg_dump backups, so in that case latency would be negligible and Tatsuo's patch inconsequential. I'm also thinking, whether the ResourceOwner patch you've proposed would get negated by Tatsuo's patch, because suddenly a "portal" (IIRC) has a lot more locks than ResourceOwner could accomodate, forcing a reversal to O(N²) behavior. In that case, that patch would in fact be detrimental... huh... way to go 180