Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eCCwn-0005Ft-Bh for pgsql-performance@arkaria.postgresql.org; Tue, 07 Nov 2017 23:06:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eCCwm-0004AV-Sg for pgsql-performance@arkaria.postgresql.org; Tue, 07 Nov 2017 23:06:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eCCuu-0000qC-Dk for pgsql-performance@postgresql.org; Tue, 07 Nov 2017 23:04:24 +0000 Received: from mail-lf0-x22e.google.com ([2a00:1450:4010:c07::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1eCCuq-0007PW-Oi for pgsql-performance@postgresql.org; Tue, 07 Nov 2017 23:04:23 +0000 Received: by mail-lf0-x22e.google.com with SMTP id 90so936574lfs.13 for ; Tue, 07 Nov 2017 15:04:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=yQkQfJ7zAAcdy/og0DVJkduZBGUsy0xmtB6JRk+t5sI=; b=ZDKCwk2aWhQUNOZ59vmeNXqtGo1kB6hkfEW7K33viuXB87x1rpeKo18kIezWaZAXb9 ZTcaSg+QgGqeYb4fltCJq6nlNHwouTl+3lgHNODhUnG01VICpDRRd9fYVTlzWCm0U2g6 Lu1DQj/uRH4prDiJzxEIjegHfcH5+YJ3r5EefA72637eo0dUus0TneAC0cOEOTaXKEcG eHGTJ1d+An1Hsnuv9V2+spjeNnbKCyjaWWTW/kDHWEWjUSCkl1KVZS8H7jLpChS+HKSc rjSMHUYbEusw6sRJBo08Kbi6zbAKawKfg7MX3j8kMhr9ekna4Z2TJPNmBpUpy4xVoFRv wXug== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=yQkQfJ7zAAcdy/og0DVJkduZBGUsy0xmtB6JRk+t5sI=; b=Bd8rKWwxGxl4fEWM4/WDmV2MLhMXOk/+WUFxG0SFbYSHA/IXiltJqmqJGqiKLk6npa T2jrErZIreHY7RHtjs8Y496PHGvQjVwID4VAWQcuvzVsXEiJF3TahVY8tVTUa4U8f2fV 7eyUF1B0lSbRoFR674IbKAhmOkFCzVrYCou/aNLy2LLzCdZz5ovgu8spFMbuBVLI2vsc DWvtY6AodPuQEOZGvUTzl1cZgzLYqqkO/nEQttzsGYz8E5uV+HNJr+sEvzpJHCoVi+W8 iLSkflY+2es4LCQ2hERSaLXR6lJAqkco8jHAxnlK3GaCyxo+/TmcpzAvvOS3EEW8SIBC RZaw== X-Gm-Message-State: AJaThX5XztcPlbaUqPNMMJGdvQmQBoT6Fy0I/pjIwLW6fPK+xnUPdLHW xJAraOJuT+hldgxWnH/0KWjwNb5BpZQzleYtL5E= X-Google-Smtp-Source: AGs4zMY3SD3JzPmkclm6LdiVfx5o9QLEk5XfA/Zs3W5Z4c/vRET7XL+hJnPLBRuRm9qi5pB+3liDKzhvvplnJzhmxM8= X-Received: by 10.25.92.16 with SMTP id q16mr131387lfb.57.1510095859760; Tue, 07 Nov 2017 15:04:19 -0800 (PST) MIME-Version: 1.0 Received: by 10.25.74.202 with HTTP; Tue, 7 Nov 2017 15:04:18 -0800 (PST) In-Reply-To: References: <20171107151108.3jtmta4xrgmuyrqf@alap3.anarazel.de> <20171107194517.pabhi4czbtiasb2f@alap3.anarazel.de> From: =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= Date: Wed, 8 Nov 2017 00:04:18 +0100 Message-ID: Subject: Re: Slow execution of SET ROLE, SET search_path and RESET ROLE To: Scott Marlowe Cc: Andres Freund , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="94eb2c0d8ea84c3f73055d6c97ac" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --94eb2c0d8ea84c3f73055d6c97ac Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 2017-11-07 22:39 GMT+01:00 Scott Marlowe : > On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbr=C3=BCgge > wrote: > > 2017-11-07 20:45 GMT+01:00 Andres Freund : > >> > >> On 2017-11-07 18:48:14 +0100, Ulf Lohbr=C3=BCgge wrote: > >> > Hi, > >> > > >> > 2017-11-07 16:11 GMT+01:00 Andres Freund : > >> > > >> > > Hi, > >> > > > >> > > On 2017-11-07 11:11:36 +0100, Ulf Lohbr=C3=BCgge wrote: > >> > > > I'm using PostgreSQL 9.5.9 on Debian and experience slow executi= on > >> > > > of > >> > > some > >> > > > basic SET statements. > >> > > > > >> > > > I created about 1600 roles and use that setup for a multi tenanc= y > >> > > > 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? > > > > > > Which table do you mean exactly? pg_catalog.pg_authid? > > > > Sorry, forgot to write that: I delete about 2-3 roles per day. > > I'm gonna take a guess that pg_users or pg_roles has gotten bloated > over time. Try running a vacuum full on both of them. It's also > possible some other pg_xxx table is bloated out here too you might > need to download something like checkpostgres.pl to check for bloat in > system catalog tables. > As pg_user and pg_roles are views: Do you mean pg_authid? That table is just 432kb large: --snip-- postgres=3D# select pg_size_pretty(pg_total_relation_size('pg_authid')); pg_size_pretty ---------------- 432 kB (1 row) --snap-- I don't want to start a vacuum full right now because I'm not quite sure if things will lock up. But I will do it later when there is less traffic. I just ran "check_postgres.pl --action=3Dbloat" and got the following outpu= t: --snip-- POSTGRES_BLOAT OK: DB "postgres" (host:localhost) (db postgres) index pg_shdepend_depender_index rows:? pages:9615 shouldbe:4073 (2.4X) wasted bytes:45400064 (43 MB) | pg_shdepend_depender_index=3D45400064B pg_catalog.pg_shdepend=3D9740288B pg_shdepend_reference_index=3D4046848B pg_depend_reference_index=3D98304B pg_depend_depender_index=3D57344B pg_catalog.pg_class=3D32768B pg_catalog.pg_description=3D16384B pg_amop_fam_strat_index=3D8192B pg_amop_opr_fam_index=3D8192B pg_catalog.pg_amop=3D8192B pg_catalog.pg_depend=3D8192B pg_class_oid_index= =3D0B pg_class_relname_nsp_index=3D0B pg_class_tblspc_relfilenode_index=3D0B pg_description_o_c_o_index=3D0B --snap-- Looks fine, doesn't it? Cheers, Ulf --94eb2c0d8ea84c3f73055d6c97ac Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
2017= -11-07 22:39 GMT+01:00 Scott Marlowe <scott.marlowe@gmail.com>= ;:
On Tue, Nov 7, 2017 at 2:25 PM, Ulf Lohbr=C3=BCgge <ulf.lohbruegge@gmail.com> wrote= :
> 2017-11-07 20:45 GMT+01:00 Andres Freund <andres@anarazel.de>:
>>
>> On 2017-11-07 18:48:14 +0100, Ulf Lohbr=C3=BCgge 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=C3=BCgge wrote:<= br> >> > > > I'm using PostgreSQL 9.5.9 on Debian and experi= ence 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?=C2=A0 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 outpu= t?=C2=A0 Do
>> you ever delete roles?
>
>
> Which table do you mean exactly? pg_catalog.pg_authid?
>
> Sorry, forgot to write that: I delete about 2-3 roles per day.

I'm gonna take a guess that pg_users or pg_roles has gotten bloa= ted
over time. Try running a vacuum full on both of them. It's also
possible some other pg_xxx table is bloated out here too you might
need to download something like checkpostgres.pl to check for bloat in system catalog tables.

As pg_user and p= g_roles are views: Do you mean pg_authid? That table is just 432kb large:

--snip--
postgres=3D# select pg_size_pret= ty(pg_total_relation_size('pg_authid'));
=C2=A0pg_size_pr= etty
----------------
=C2=A0432 kB
(1 row)=C2= =A0
--snap--

I don't want to start a= vacuum full right now because I'm not quite sure if things will lock u= p. But I will do it later when there is less traffic.

<= div>I just ran "check_postgres.pl= =C2=A0--action=3Dbloat" and got the following output:
--snip--
POSTGRES_BLOAT OK: DB "postgres" = (host:localhost) (db postgres) index pg_shdepend_depender_index rows:? page= s:9615 shouldbe:4073 (2.4X) wasted bytes:45400064 (43 MB) | pg_shdepend_dep= ender_index=3D45400064B pg_catalog.pg_shdepend=3D9740288B pg_shdepend_refer= ence_index=3D4046848B pg_depend_reference_index=3D98304B pg_depend_depender= _index=3D57344B pg_catalog.pg_class=3D32768B pg_catalog.pg_description=3D16= 384B pg_amop_fam_strat_index=3D8192B pg_amop_opr_fam_index=3D8192B pg_catal= og.pg_amop=3D8192B pg_catalog.pg_depend=3D8192B pg_class_oid_index=3D0B pg_= class_relname_nsp_index=3D0B pg_class_tblspc_relfilenode_index=3D0B pg_desc= ription_o_c_o_index=3D0B
--snap--

Lo= oks fine, doesn't it?

C= heers,
Ulf
--94eb2c0d8ea84c3f73055d6c97ac--