From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ulf Lohbrügge <ulf.lohbruegge@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: High CPU Usage of "SET ROLE"
Date: Mon, 22 Oct 2018 09:57:48 -0400
Message-ID: <82967.1540216668@sss.pgh.pa.us> (raw)
In-Reply-To: <CABZYQRJDKytuyyL-th+Kq2_D4fS-rrb3sgDG2Y4qsNT__GGD8Q@mail.gmail.com>
References: <CABZYQRJDKytuyyL-th+Kq2_D4fS-rrb3sgDG2Y4qsNT__GGD8Q@mail.gmail.com>
=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbruegge@gmail.com> writes:
> I'm running PostgreSQL 9.6.10 on Debian and reported some performance
> issues with "SET ROLE" a while ago:
> https://www.postgresql.org/message-id/CABZYQR%2BKu%2BiLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ%40mail...
> ...
> The setup is the same as reported in the above mentioned post: I use more
> than a thousand roles per PostgreSQL instance and set the role for every
> connection before executing actual statements. My pg_class consists
> of 1,557,824 rows as every role has its own schema with more than 300
> tables.
It seems plausible to guess that you've hit some behavior that's O(N^2)
in the number of objects (for some object type or other). Perhaps "perf"
or a similar tool would give some insight into where the bottleneck is.
https://wiki.postgresql.org/wiki/Profiling_with_perf
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ulf.lohbruegge@gmail.com
Subject: Re: High CPU Usage of "SET ROLE"
In-Reply-To: <82967.1540216668@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox