Re: [PERFORM] Performance of information_schema with many schemata and tables

[Pritam Baral]

On Wednesday 28 June 2017 05:27 AM, Ulf Lohbrügge wrote:
> Hi all,
>
> we use schemata to separate our customers in a multi-tenant setup (9.5.7, 
> Debian stable). Each tenant is managed in his own schema with all the tables 
> that only he can access. All tables in all schemata are the same in terms of 
> their DDL: Every tenant uses e.g. his own table 'address'. We currently 
> manage around 1200 schemata (i.e. tenants) on one cluster. Every schema 
> consists currently of ~200 tables - so we end up with ~24 tables plus 
> constraints, indexes, sequences et al.
>
> Our current approach is quite nice in terms of data privacy because every 
> tenant is isolated from all other tenants. A tenant uses his own user that 
> gives him only access to the corresponding schema. Performance is great for 
> us - we didn't expect Postgres to scale so well!
>
> But performance is pretty bad when we query things in the information_schema:
>
> SELECT
>   *
> FROM information_schema.tables
> WHERE table_schema = 'foo'
> AND table_name = 'bar';``
>
> Above query results in a large sequence scan with a filter that removes 
> 1305161 rows:
>
>   
>   
> QUERY PLAN
> ---
>  Nested Loop Left Join  (cost=0.70..101170.18 rows=3 width=265) (actual 
> time=383.505..383.505 rows=0 loops=1)
>->  Nested Loop  (cost=0.00..101144.65 rows=3 width=141) (actual 
> time=383.504..383.504 rows=0 loops=1)
>  Join Filter: (nc.oid = c.relnamespace)
>  ->  Seq Scan on pg_class c  (cost=0.00..101023.01 rows=867 width=77) 
> (actual time=383.502..383.502 rows=0 loops=1)
>Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND 
> (((relname)::information_schema.sql_identifier)::text = 'bar'::text) AND 
> (pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT, 
> INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR 
> has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
>Rows Removed by Filter: 1305161
>  ->  Materialize  (cost=0.00..56.62 rows=5 width=68) (never executed)
>->  Seq Scan on pg_namespace nc  (cost=0.00..56.60 rows=5 
> width=68) (never executed)
>  Filter: ((NOT pg_is_other_temp_schema(oid)) AND 
> (((nspname)::information_schema.sql_identifier)::text = 'foo'::text))
>->  Nested Loop  (cost=0.70..8.43 rows=1 width=132) (never executed)
>  ->  Index Scan using pg_type_oid_index on pg_type t  
> (cost=0.42..8.12 rows=1 width=72) (never executed)
>Index Cond: (c.reloftype = oid)
>  ->  Index Scan using pg_namespace_oid_index on pg_namespace nt  
> (cost=0.28..0.30 rows=1 width=68) (never executed)
>Index Cond: (oid = t.typnamespace)
>  Planning time: 0.624 ms
>  Execution time: 383.784 ms
> (16 rows)
>
> We noticed the degraded performance first when using the psql cli. Pressing 
> tab after beginning a WHERE clause results in a query against the 
> information_schema which is pretty slow and ends in "lag" when trying to 
> enter queries.
>
> We also use Flyway (https://flywaydb.org/) to handle our database migrations. 
> Unfortunately Flyway is querying the information_schema to check if specific 
> tables exist (I guess this is one of the reasons information_schema exists) 
> and therefore vastly slows down the migration of our tenants. Our last 
> migration run on all tenants (schemata) almost took 2h because the above 
> query is executed multiple times per tenant. The migration run consisted of 
> multiple sql files to be executed and triggered more than 10 queries on 
> information_schema per tenant.
>
> I don't think that Flyway is to blame because querying the information_schema 
> should be a fast operation (and was fast for us when we had less schemata). I 
> tried to speedup querying pg_class by adding indexes (after enabling 
> allow_system_table_mods) but didn't succeed. The function call 'pg_has_role' 
> is probably not easy to optimize.
>
> Postgres is really doing a great job to handle those many schemata and tables 
> but doesn't scale well when querying information_schema. I actually don't 
> want to change my current multi-tenant setup (one schema per tenant) as it is 
> working great but the slow information_schema is killing our deployments.
>
> Are there any other options besides switching from 
> one-schema-per-tenant-approach? Any help is greatly appreciated!

Have you tried a `REINDEX SYSTEM `?

>
> 

[PERFORM] Performance of information_schema with many schemata and tables

[Ulf Lohbrügge]

Hi all,

we use schemata to separate our customers in a multi-tenant setup (9.5.7,
Debian stable). Each tenant is managed in his own schema with all the
tables that only he can access. All tables in all schemata are the same in
terms of their DDL: Every tenant uses e.g. his own table 'address'. We
currently manage around 1200 schemata (i.e. tenants) on one cluster. Every
schema consists currently of ~200 tables - so we end up with ~24 tables
plus constraints, indexes, sequences et al.

Our current approach is quite nice in terms of data privacy because every
tenant is isolated from all other tenants. A tenant uses his own user that
gives him only access to the corresponding schema. Performance is great for
us - we didn't expect Postgres to scale so well!

But performance is pretty bad when we query things in the
information_schema:

SELECT
  *
FROM information_schema.tables
WHERE table_schema = 'foo'
AND table_name = 'bar';``

Above query results in a large sequence scan with a filter that removes
1305161 rows:



QUERY PLAN
---
 Nested Loop Left Join  (cost=0.70..101170.18 rows=3 width=265) (actual
time=383.505..383.505 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..101144.65 rows=3 width=141) (actual
time=383.504..383.504 rows=0 loops=1)
 Join Filter: (nc.oid = c.relnamespace)
 ->  Seq Scan on pg_class c  (cost=0.00..101023.01 rows=867
width=77) (actual time=383.502..383.502 rows=0 loops=1)
   Filter: ((relkind = ANY ('{r,v,f}'::"char"[])) AND
(((relname)::information_schema.sql_identifier)::text = 'bar'::text) AND
(pg_has_role(relowner, 'USAGE'::text) OR has_table_privilege(oid, 'SELECT,
INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'::text) OR
has_any_column_privilege(oid, 'SELECT, INSERT, UPDATE, REFERENCES'::text)))
   Rows Removed by Filter: 1305161
 ->  Materialize  (cost=0.00..56.62 rows=5 width=68) (never
executed)
   ->  Seq Scan on pg_namespace nc  (cost=0.00..56.60 rows=5
width=68) (never executed)
 Filter: ((NOT pg_is_other_temp_schema(oid)) AND
(((nspname)::information_schema.sql_identifier)::text = 'foo'::text))
   ->  Nested Loop  (cost=0.70..8.43 rows=1 width=132) (never executed)
 ->  Index Scan using pg_type_oid_index on pg_type t
 (cost=0.42..8.12 rows=1 width=72) (never executed)
   Index Cond: (c.reloftype = oid)
 ->  Index Scan using pg_namespace_oid_index on pg_namespace nt
 (cost=0.28..0.30 rows=1 width=68) (never executed)
   Index Cond: (oid = t.typnamespace)
 Planning time: 0.624 ms
 Execution time: 383.784 ms
(16 rows)

We noticed the degraded performance first when using the psql cli. Pressing
tab after beginning a WHERE clause results in a query against the
information_schema which is pretty slow and ends in "lag" when trying to
enter queries.

We also use Flyway (https://flywaydb.org/) to handle our database
migrations. Unfortunately Flyway is querying the information_schema to
check if specific tables exist (I guess this is one of the reasons
information_schema exists) and therefore vastly slows down the migration of
our tenants. Our last migration run on all tenants (schemata) almost took
2h because the above query is executed multiple times per tenant. The
migration run consisted of multiple sql files to be executed and triggered
more than 10 queries on information_schema per tenant.

I don't think that Flyway is to blame because querying the
information_schema should be a fast operation (and was fast for us when we
had less schemata). I tried to speedup querying pg_class by adding indexes
(after enabling allow_system_table_mods) but didn't succeed. The function
call 'pg_has_role' is probably not easy to optimize.

Postgres is really doing a great job to handle those many schemata and
tables but doesn't scale well when querying information_schema. I actually
don't want to change my current multi-tenant setup (one schema per tenant)
as it is working great but the slow information_schema is killing our
deployments.

Are there any other options besides switching from
one-schema-per-tenant-approach? Any help is greatly appreciated!

Regards,
Ulf

[PERFORM] Fwd: Stalled post to pgsql-performance

[Chris Wilson]

Hi Karl and Jeff,

On 26 June 2017 at 22:22, Jeff Janes  wrote:

> Be warned that "explain (analyze)" can substantially slow down and distort
> this type of query, especially when sorting.  You should run "explain
> (analyze, timing off)" first, and then only trust "explain (analyze)" if
> the overall execution times between them are similar.
>

Thanks, I didn't realise that. I will use TIMING OFF from now on.

On 26 June 2017 at 21:32, Karl Czajkowski  wrote:

> > I created the index starting with date and it did make a big
> > difference: down to 10.3 seconds using a bitmap index scan and bitmap
> > heap scan (and then two hash joins as before).
>
> By the way, what kind of machine are you using?  CPU, RAM, backing
> storage?
>
> I tried running your original test code and the query completed in
> about 8 seconds, and adding the index changes and analyze statement
> brought it down to around 2.3 seconds on my workstation with Postgres
> 9.5.7.  On an unrelated development VM with Postgres 9.6.3, the final
> form took around 4 seconds.
>

This is very interesting. I'm using a powerful box:

   - HP ProLiant DL580 G7, Xeon(R) CPU E7- 4850  @ 2.00GHz * 80 cores, 128
   GB RAM, hardware RAID, 3.6 TB SAS array.

  totalusedfree  shared  buff/cache
available
Mem:   125G2.2G834M 30G122G
91G
Swap:  9.3G 98M9.2G


And disk I/O is fast:

$ dd if=/dev/zero of=/local/tmp/bigfile bs=1M count=100k
107374182400 bytes (107 GB) copied, 234.751 s, 457 MB/s


But your question let me to investigate and discover that we were compiling
Postgres with no optimisations! I've built a new one with -O2 and got the
time down to 3.6 seconds (EXPLAIN with either TIMING OFF or BUFFERS,
there's no material difference).

And again, vacuum your tables.  Heap fetches aren't cheap.
>

Sorry, I don't understand, why does VACUUM help on a table with no deleted
rows? Do you mean ANALYZE?


> > work_mem = 100MB
>
> Can you give it more than that?  How many simultaneous connections do you
> expect?
>

Yes, I can and it does help! By increasing work_mem to 200 MB, I managed to
convert the external merge sort (on disk) to a quicksort in memory, and
reached 3.3 seconds.

The cartestian join is slightly faster at 3.0 seconds, but not enough to be
worth the overhead of creating the join table. I still wish I understood
why it helps.

Jeff, thanks for the explanation about hash joins and sorting. I wish I
understood why a hash join wouldn't preserve order in the first table even
if it has to be done incrementally, since I expect that we'd still be
reading records from the first table in order, but just in batches.

Other possible rewrites to try instead of joins:
>
>   -- replace the case statement with a scalar subquery
>
>   -- replace the case statement with a stored procedure wrapping that
> scalar subquery
>  and declare the procedure as STABLE or even IMMUTABLE
>
> These are shots in the dark, but seem easy enough to experiment with and
> might
> behave differently if the query planner realizes it can cache results for
> repeated use of the same ~100 input values.


I hit a jackpot with jsonb_object_agg, getting down to 2.1 seconds (2.8
with BUFFERS and TIMING ):

explain (analyze, timing off)
with metric as (select jsonb_object_agg(id, pos) AS metric_lookup from
metric_pos),
 asset  as (select jsonb_object_agg(id, pos) AS asset_lookup  from
asset_pos)
SELECT metric_lookup->id_metric AS pos_metric, asset_lookup->id_asset AS
pos_asset, date, value
FROM metric_value, metric, asset
WHERE date >= '2016-01-01' and date < '2016-06-01'
AND timerange_transaction @> current_timestamp
ORDER BY metric_value.id_metric, metric_value.id_asset, date;


Which is awesome! Thank you so much for your help, both of you!

Now if only we could make hash joins as fast as JSONB hash lookups :)

Cheers, Chris.