public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Andres Freund <andres@anarazel.de>
To: Ulf Lohbrügge <ulf.lohbruegge@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow execution of SET ROLE, SET search_path and RESET ROLE
Date: Tue, 7 Nov 2017 07:11:08 -0800
Message-ID: <20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de> (raw)
In-Reply-To: <CABZYQR+Ku+iLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ@mail.gmail.com>
References: <CABZYQR+Ku+iLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
On 2017-11-07 11:11:36 +0100, Ulf Lohbrügge wrote:
> I'm using PostgreSQL 9.5.9 on Debian and experience slow execution of some
> basic SET statements.
>
> I created about 1600 roles and use that setup for a multi tenancy
> application:
Hm. How often do you drop/create these roles? How many other
roles/groups is one role a member of?
> My application solely uses the role 'admin' to connect to the database.
> When performing sql statements for a specific tenant (e.g. tenant1337), a
> connection with user 'admin' is established and the following commands are
> executed:
>
> SET ROLE 1337;
> SET search_path = tenant1337;
>
> Then the application uses that connection to perform various statements in
> the database.
Just to be sure: You realize bad application code could escape from
that, right?
> My application is a web service that approximately executes some hundred
> statements per second.
>
> I set "log_min_duration_statement = 200ms" and I get about 2k to 4k lines
> per day with statements like "SET ROLE"", "SET search_path ..." and "RESET
> ROLE":
>
> --snip--
> 2017-11-07 09:44:30 CET [27760]: [3-1] user=admin,db=mydb LOG: duration:
> 901.591 ms execute <unnamed>: SET ROLE "tenant762"
> 2017-11-07 09:44:30 CET [27659]: [4-1] user=admin,db=mydb LOG: duration:
> 1803.971 ms execute <unnamed>: SET ROLE "tenant392"
That is weird.
> Besides those peaks in statement duration, my application performs (i.e.
> has acceptable response times) most of the time.
>
> Is there anything I can do to improve performance here?
> Any help is greatly appreciated!
Can you manually reproduce the problem? What times do you get if you
manually run the statement?
Greetings,
Andres Freund
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
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: andres@anarazel.de, ulf.lohbruegge@gmail.com
Subject: Re: Slow execution of SET ROLE, SET search_path and RESET ROLE
In-Reply-To: <20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox