From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Avinash Kumar <avinash.vallarapu@gmail.com>
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, 08 May 2020 08:53:41 +0200
Message-ID: <8f67073e5a6818a17eef5c44b087640db8939f7d.camel@cybertec.at> (raw)
In-Reply-To: <CAN0TujdKpqFQPVLtWDNagv3Y-LgyrMrzCKpr3fib7sKgq_w5uQ@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>
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.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
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: laurenz.albe@cybertec.at, avinash.vallarapu@gmail.com, 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: <8f67073e5a6818a17eef5c44b087640db8939f7d.camel@cybertec.at>
* 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