pg_dump and thousands of schemas

Claudio Freire < > writes:
> On Thu, May 31, 2012 at 11:50 AM, Tom Lane < > 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

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Thu, May 31, 2012 at 12:25 PM, Tom Lane < > 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

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Wed, May 30, 2012 at 2:06 AM, Tatsuo Ishii < > wrote:
Could you rebase this?  I tried doing it myself, but must have messed
it up because it got slower rather than faster.

Thanks,

Jeff

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Sun, Jun 10, 2012 at 4:47 PM, Jeff Janes < > wrote:
OK, I found the problem.  In fixing a merge conflict, I had it execute
the query every time it appended a table, rather than just at the end.

With my proposed patch in place, I find that for a full default dump
your patch is slightly faster with < 300,000 tables, and slightly
slower with > 300,000.  The differences are generally <2% in either
direction.  When it comes to back-patching and partial dumps, I'm not
really sure what to test.

For the record, there is still a quadratic performance on the server,
albeit with a much smaller constant factor than the Reassign one.  It
is in get_tabstat_entry.  I don't know if is worth working on that in
isolation--if PG is going to try to accommodate 100s of thousands of
table, there probably needs to be a more general way to limit the
memory used by all aspects of the rel caches.

Cheers,

Jeff

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

I would like to test your patch and w/without my patch. Could you
please give me the patches? Or do you have your own git repository?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Tue, Jun 12, 2012 at 1:54 AM, Tatsuo Ishii < > wrote: The main patch is in the commit fest as "Resource Owner reassign Locks
for the sake of pg_dump"

My re-basing of your patch is attached.

Cheers,

Jeff


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

I tested your patches with current master head. The result was pretty
good. Before it took 125 minutes (with 9.2 devel) to dump 100k empty
tables and now it takes only less than 4 minutes!

$ time pg_dump test >/dev/null

real3m56.412s
user0m12.059s
sys0m3.571s

Good job!

Now I applied rebased pg_dump patch.

real4m1.779s
user0m11.621s
sys0m3.052s

Unfortunately I see no improvement. Probably my patch's value is for
dumping against older backend.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Hi guys,

I just want to let you know that we have created our own solution to dump and restore our databases. The tool was written in java and the source is now on Github (MIT license): https://github.com/tig100/JdbcPgBackup

The main approach was to cache all database objects - schemas, tables, indexes, etc., and instead of having postgres do joins between the pg_catalog tables (which include thousands of schemas in pg_namespace and millions of columns in pg_attribute), we do full table scans and then find which schema or table an object belongs to by looking it up in a hash map in java, based on schema and table oid's. The dump is not transactionally safe, so it should be performed on a replica db only (WAL importing disabled), not on a live db. Some statistics:

Dump 11,000 schemas = 3 hours.
Dump 28,000 schemas = 8 hours.

You can read more about the tool on the github page.

Best regards,
Hugo

On Thu, May 31, 2012 at 09:20:43AM +0900, Tatsuo Ishii wrote:
Was this applied?

--
  Bruce Momjian  < >         http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Bruce Momjian < > writes:
> On Thu, May 31, 2012 at 09:20:43AM +0900, Tatsuo Ishii wrote:
>>> Ok, I modified the part of pg_dump where tremendous number of LOCK
>>> TABLE are issued. I replace them with single LOCK TABLE with multiple
>>> tables. With 100k tables LOCK statements took 13 minutes in total, now
>>> it only takes 3 seconds. Comments?

>> Shall I commit to master and all supported branches?

> Was this applied?

No, we fixed the server side instead.

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Thu, Aug 30, 2012 at 04:51:56PM -0400, Tom Lane wrote:
Again, thanks.  I knew we fixed the server, but wasn't clear that made
the client changes unnecessary, but I think I now do remember discussion
about that.

--
  Bruce Momjian  < >         http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Thu, Aug 30, 2012 at 4:51 PM, Tom Lane < > wrote:
But only for 9.2, right?  So people running back branches are still screwed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Robert Haas < > writes:
> On Thu, Aug 30, 2012 at 4:51 PM, Tom Lane < > wrote:
>> Bruce Momjian < > writes:
>>> On Thu, May 31, 2012 at 09:20:43AM +0900, Tatsuo Ishii wrote:
>>>> Ok, I modified the part of pg_dump where tremendous number of LOCK
>>>> TABLE are issued. I replace them with single LOCK TABLE with multiple
>>>> tables. With 100k tables LOCK statements took 13 minutes in total, now
>>>> it only takes 3 seconds. Comments?

>>> Was this applied?

>> No, we fixed the server side instead.

> But only for 9.2, right?  So people running back branches are still screwed.

Yeah, but they're screwed anyway, because there are a bunch of O(N^2)
behaviors involved here, not all of which are masked by what Tatsuo-san
suggested.

Six months or a year from now, we might have enough confidence in that
batch of 9.2 fixes to back-port them en masse.  Don't want to do it
today though.

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Thu, Aug 30, 2012 at 8:17 PM, Tom Lane < > wrote:
All of the other ones that I know of were associated with pg_dump
itself, and since it is recommended to run the newer version of
pg_dump against the older version of the server, no back patching
would be necessary to get the benefits of those particular fixes.

> Six months or a year from now, we might have enough confidence in that
> batch of 9.2 fixes to back-port them en masse.  Don't want to do it
> today though.


What would be the recommendation for people trying to upgrade, but who
can't get their data out in a reasonable window?

Putting Tatsuo-san's change into a future pg_dump might be more
conservative than back-porting the server's Lock Table change to the
server version they are trying to get rid of.

Cheers,

Jeff


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

On Sun, Sep 2, 2012 at 5:39 PM, Jeff Janes < > wrote:
What he said.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Denis < > writes:
> I've read all the posts in thread, and as I understood in version 9.2 some
> patches were applied to improve pg_dump speed. I've just installed
> PostgreSQL 9.2.1 and I still have the same problem. I have a database with
> 2600 schemas in it. I try to dump each schema individually, but it takes too
> much time for every schema (about 30-40 seconds per schema, no matter what
> the data size is).

Could you provide a test case for that?  Maybe the output of pg_dump -s,
anonymized as you see fit?

> Also for each schema dump I have a slow query log entry,

Could you provide EXPLAIN ANALYZE output for that query?

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Tom Lane-2 wrote

Denis < > writes:
> I've read all the posts in thread, and as I understood in version 9.2 some
> patches were applied to improve pg_dump speed. I've just installed
> PostgreSQL 9.2.1 and I still have the same problem. I have a database with
> 2600 schemas in it. I try to dump each schema individually, but it takes too
> much time for every schema (about 30-40 seconds per schema, no matter what
> the data size is).

Could you provide a test case for that?  Maybe the output of pg_dump -s,
anonymized as you see fit?

> Also for each schema dump I have a slow query log entry,

Could you provide EXPLAIN ANALYZE output for that query?

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Here is the output of "pg_dump -s" test.dump

And here is the output of EXPLAIN ANALYZE. It took 5 seconds but usually it takes from 10 to 15 seconds when I am doing backup.

Sort  (cost=853562.04..854020.73 rows=183478 width=219) (actual time=5340.477..5405.604 rows=183924 loops=1)
   Sort Key: c.oid
   Sort Method: external merge  Disk: 33048kB
   ->  Hash Left Join  (cost=59259.80..798636.25 rows=183478 width=219) (actual time=839.297..4971.299 rows=183924 loops=1)
         Hash Cond: (c.reltoastrelid = tc.oid)
         ->  Hash Right Join  (cost=29530.77..146976.65 rows=183478 width=183) (actual time=404.959..3261.462 rows=183924 loops=1
)
               Hash Cond: ((d.classid = c.tableoid) AND (d.objid = c.oid) AND (d.refclassid = c.tableoid))
               Join Filter: (c.relkind = 'S'::"char")
               ->  Seq Scan on pg_depend d  (cost=0.00..71403.54 rows=995806 width=20) (actual time=1.137..878.571 rows=998642 lo
ops=1)
                     Filter: ((objsubid = 0) AND (deptype = 'a'::"char"))
                     Rows Removed by Filter: 2196665
               ->  Hash  (cost=21839.91..21839.91 rows=183478 width=175) (actual time=402.947..402.947 rows=183924 loops=1)
                     Buckets: 1024  Batches: 32  Memory Usage: 876kB
                     ->  Seq Scan on pg_class c  (cost=0.00..21839.91 rows=183478 width=175) (actual time=0.017..267.614 rows=183
924 loops=1)
                           Filter: (relkind = ANY ('{r,S,v,c,f}'::"char"[]))
                           Rows Removed by Filter: 383565
         ->  Hash  (cost=18333.79..18333.79 rows=560979 width=40) (actual time=434.258..434.258 rows=567489 loops=1)
               Buckets: 4096  Batches: 32  Memory Usage: 703kB
               ->  Seq Scan on pg_class tc  (cost=0.00..18333.79 rows=560979 width=40) (actual time=0.003..273.418 rows=567489 lo
ops=1)
         SubPlan 1
           ->  Seq Scan on pg_authid  (cost=0.00..1.01 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=183924)
                 Filter: (oid = c.relowner)
                 Rows Removed by Filter: 2
         SubPlan 2
           ->  Seq Scan on pg_tablespace t  (cost=0.00..1.02 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=183924)
                 Filter: (oid = c.reltablespace)
                 Rows Removed by Filter: 2
         SubPlan 3
           ->  Function Scan on unnest x  (cost=0.00..1.25 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=183924)
 Total runtime: 5428.498 ms

Denis < > writes:
> Here is the output of EXPLAIN ANALYZE. It took 5 seconds but usually it
> takes from 10 to 15 seconds when I am doing backup.

> Sort  (cost=853562.04..854020.73 rows=183478 width=219) (actual
> time=5340.477..5405.604 rows=183924 loops=1)

Hmmm ... so the problem here isn't that you've got 2600 schemas, it's
that you've got 183924 tables.  That's going to take some time no matter
what.

It does seem like we could make some small changes to optimize that
query a little bit, but they're not going to result in any amazing
improvement overall, because pg_dump still has to deal with all the
tables it's getting back.  Fundamentally, I would ask whether you really
need so many tables.  It seems pretty likely that you have lots and lots
of basically-identical tables.  Usually it would be better to redesign
such a structure into fewer tables with more index columns.

> Here is the output of "pg_dump -s"  test.dump
> < http://postgresql.1045698.n5.nabble.com/file/n5730877/test.dump >  

This dump contains only 1 schema and 43 tables, so I don't think it's
for the database you're having trouble with ...

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

Tom Lane-2 wrote

Denis < > writes:
> Here is the output of EXPLAIN ANALYZE. It took 5 seconds but usually it
> takes from 10 to 15 seconds when I am doing backup.

> Sort  (cost=853562.04..854020.73 rows=183478 width=219) (actual
> time=5340.477..5405.604 rows=183924 loops=1)

Hmmm ... so the problem here isn't that you've got 2600 schemas, it's
that you've got 183924 tables.  That's going to take some time no matter
what.

It does seem like we could make some small changes to optimize that
query a little bit, but they're not going to result in any amazing
improvement overall, because pg_dump still has to deal with all the
tables it's getting back.  Fundamentally, I would ask whether you really
need so many tables.  It seems pretty likely that you have lots and lots
of basically-identical tables.  Usually it would be better to redesign
such a structure into fewer tables with more index columns.

> Here is the output of "pg_dump -s"  test.dump
> < http://postgresql.1045698.n5.nabble.com/file/n5730877/test.dump>  

This dump contains only 1 schema and 43 tables, so I don't think it's
for the database you're having trouble with ...

                        regards, tom lane


--
Sent via pgsql-performance mailing list ( )
To make changes to your subscription:

I wonder why pg_dump has to have deal with all these 183924 tables, if I specified to dump only one scheme: "pg_dump -n schema_name" or even like this to dump just one table "pg_dump -t 'schema_name.comments' "  ?

We have a web application where we create a schema with a number of tables in it for each customer. This architecture was chosen to ease the process of backup/restoring data. Sometimes clients ask us to restore data for the last month or roll back to last week's state. This task is easy to accomplish then the client's data is isolated in a schema/DB. If we put all the clients data in one table - operations of this kind will be much harder to perform. We will have to restore a huge DB with an enormously large tables in it to find the requested data.
Different clients have different activity rate and we can select different backup strategies according to it. This would be impossible in case we keep all the clients data in one table.
Besides all the above mentioned, the probability of massive data corruption (if an error in our web application occurs) is much higher.