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 11:45:17 -0800
Message-ID: <20171107194517.pabhi4czbtiasb2f@alap3.anarazel.de> (raw)
In-Reply-To: <CABZYQR+GE7mV7qiTFGO3EqxdqV_TmSQyzY3ghSE1OfHabZcG7g@mail.gmail.com>
References: <CABZYQR+Ku+iLFhqwY89QrrnKG9wKxckmssDG2rYKESojiohRgQ@mail.gmail.com>
<20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de>
<CABZYQR+GE7mV7qiTFGO3EqxdqV_TmSQyzY3ghSE1OfHabZcG7g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: <20171107194517.pabhi4czbtiasb2f@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