Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE

From: Andres Freund
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE
Date: ,
Msg-id: 20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de
(view: Whole thread, Raw)
In response to: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge)
Responses: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge)
List: pgsql-performance

Tree view

[PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE  (Andres Freund, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
   Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Scott Marlowe, )
    Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE  (Andres Freund, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )
   Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
    Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )

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 ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


pgsql-performance by date:

From: Andres Freund
Date:
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE
From: Ulf Lohbrügge
Date:
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE