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: 20171107194517.pabhi4czbtiasb2f@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, )

On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> Hi,
> 
> 2017-11-07 16:11 GMT+01:00 Andres Freund <>:
> 
> > 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?
> >
> 
> I create between 10-40 roles per day.

Could you VACUUM (VERBOSE, FREEZE) that table and report the output?  Do
you ever delete roles?

> > Can you manually reproduce the problem? What times do you get if you
> > manually run the statement?
> >
> 
> Unfortunately not. Every time I manually execute "SET ROLE ..." the
> statement is pretty fast. I created a simple SQL file that contains the
> following statements:
> 
> --snip--
> SET ROLE tenant382;
> SET ROLE tenant1337;
> SET ROLE tenant2;
> -- repeat the lines above 100k times
> --snap--
> 
> When I execute those statements via 'time psql < set-roles.sql', the call
> lasts 138,7 seconds. So 300k "SET ROLE" statements result in 0,46ms per
> call on average.

And most of that is going to be roundtrip time. Hm. Could it be that
you're just seeing the delays when pgbouncer establishes new pooling
connections and you're attributing that to SET ROLE in your app?

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: Tom Lane
Date:
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE
From: Ulf Lohbrügge
Date:
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE