From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Avinash Kumar <avinash.vallarapu@gmail.com>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Rory Campbell-Lange <rory@campbell-lange.net>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: samhitha g <samhithagarudadri@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
Date: Fri, 8 May 2020 09:01:50 -0400
Message-ID: <4b0c83b7-c592-cd2e-9897-764fde9d748a@sqlexec.com> (raw)
In-Reply-To: <CAN0Tujfc6uLp6B=1mSeCfp=s-voFjL_40Q3+B1Fg3_pZf+VCyg@mail.gmail.com>
References: <CAG5FJBVFp4TtEde-xn2hpYQKByFxPQChXitrLorwyiN71+cPyA@mail.gmail.com>
<CAKFQuwafqFBr+jJ9fGie19CCDoBiGarJejhcwiYHaYyM+kY_kQ@mail.gmail.com>
<CAN0TujdCgR5TA4JW6T5Di6nL5RVShbUAfY+G-7Fc-ABH4U=--g@mail.gmail.com>
<20200507210846.GA17040@campbell-lange.net>
<CAN0TujfLZ22xm0u99B+jsB31R55fQ5OJ2PKgcmr0TZHUPwoTUw@mail.gmail.com>
<17c5b15451fac2ace7a815d1d2c8f86e1d232802.camel@cybertec.at>
<CAN0TujdKpqFQPVLtWDNagv3Y-LgyrMrzCKpr3fib7sKgq_w5uQ@mail.gmail.com>
<8f67073e5a6818a17eef5c44b087640db8939f7d.camel@cybertec.at>
<CAN0Tujfc6uLp6B=1mSeCfp=s-voFjL_40Q3+B1Fg3_pZf+VCyg@mail.gmail.com>
Hi all,
Since we are talking about multi-tenant databases, the citus extension
<https://www.citusdata.com/product/community; fits in neatly with that
using horizontal partitioning/shards.
Regards,
Michael Vitale
Avinash Kumar wrote on 5/8/2020 6:14 AM:
> Hi,
>
> On Fri, May 8, 2020 at 3:53 AM Laurenz Albe <laurenz.albe@cybertec.at
> <mailto:laurenz.albe@cybertec.at>> wrote:
>
> On Fri, 2020-05-08 at 03:47 -0300, Avinash Kumar wrote:
> > > Just set "autovacuum_max_workers" higher.
> >
> > No, that wouldn't help. If you just increase
> autovacuum_max_workers, the total cost limit of
> > autovacuum_vacuum_cost_limit (or vacuum_cost_limit) is shared by
> so many workers and it
> > further delays autovacuum per each worker. Instead you need to
> increase autovacuum_vacuum_cost_limit
> > as well when you increase the number of workers.
>
> True, I should have mentioned that.
>
> > But, if you do that and also increase workers, well, you would
> easily reach the limitations
> > of the disk. I am not sure it is anywhere advised to have 20
> autovacuum_max_workers unless
> > i have a disk with lots of IOPS and with very tiny tables across
> all the databases.
>
> Sure, if you have a high database load, you will at some point
> exceed the limits of
> the machine, which is not surprising. What I am trying to say is
> that you have to ramp
> up the resources for autovacuum together with increasing the
> overall workload.
> You should consider autovacuum as part of that workload.
>
> If your machine cannot cope with the workload any more, you have
> to scale, which
> is easily done by adding more machines if you have many databases.
>
> Agreed. Getting back to the original question asked by Sammy, i think
> it is still bad to create 2000 databases for storing 2000
> clients/(schemas) for a multi-tenant setup.
>
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
> --
> Regards,
> Avinash Vallarapu
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: MichaelDBA@sqlexec.com, avinash.vallarapu@gmail.com, laurenz.albe@cybertec.at, rory@campbell-lange.net, david.g.johnston@gmail.com, samhithagarudadri@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: pg_attribute, pg_class, pg_depend grow huge in count and size with multiple tenants.
In-Reply-To: <4b0c83b7-c592-cd2e-9897-764fde9d748a@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox