Date: Tue, 7 Nov 2017 11:45:17 -0800
From: Andres Freund <andres@anarazel.de>
To: Ulf =?iso-8859-1?Q?Lohbr=FCgge?= <ulf.lohbruegge@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow execution of SET ROLE, SET search_path and RESET
 ROLE
Message-ID: <20171107194517.pabhi4czbtiasb2f@alap3.anarazel.de>
References: 
 <CABZYQR+Ku+iLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ@mail.gmail.com>
 <20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de>
 <CABZYQR+GE7mV7qiTFGO3EqxdqV_TmSQyzY3ghSE1OfHabZcG7g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CABZYQR+GE7mV7qiTFGO3EqxdqV_TmSQyzY3ghSE1OfHabZcG7g@mail.gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 2017-11-07 18:48:14 +0100, Ulf Lohbrügge wrote:
> Hi,
> 
> 2017-11-07 16:11 GMT+01:00 Andres Freund <andres@anarazel.de>:
> 
> > 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 (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance