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