From: Tom Lane <tgl@sss.pgh.pa.us>
To: Owayss Kabtoul <owayssk@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: High CPU load caused by the autovacuum launcher process
Date: Fri, 08 Jun 2018 11:41:13 -0400
Message-ID: <24641.1528472473@sss.pgh.pa.us> (raw)
In-Reply-To: <CAP-VjpwEo_=LZrp5f7jeh_==Uw5psEQKFcsJp=vO2ZoySmuJCw@mail.gmail.com>
References: <CAP-VjpwEo_=LZrp5f7jeh_==Uw5psEQKFcsJp=vO2ZoySmuJCw@mail.gmail.com>
Owayss Kabtoul <owayssk@gmail.com> writes:
> I ran into an issue where, on Postgres instances that have a very large
> number of databases per cluster (~15K), the autovacuum process seems to
> have a very high impact on CPU usage.
That's, um, not a very reasonable configuration.
> So auto-vacuum never really sleeps. Even changing the autovacuum_naptime
> and setting it to a much higher value (from 1min to 50min) did not have any
> effect at all.
Simple arithmetic says that even at 50min, the launcher will have to
launch one worker every 0.2 seconds in order to visit every database
once per naptime.
You could raise the naptime setting by another factor of 10 and then
the launcher would only have to wake up every other second ... but
these DBs had better all have extremely light workloads, or you're
risking terrible bloat due to tables only getting vacuumed a couple
times per day.
On the whole, I'd revisit why you need so many DBs per cluster.
Quite aside from autovacuum performance issues, you're expending
~100GB just on redundant copies of the system catalogs; that can't
be helping disk cache performance, for instance. (Or, if you've
got a box that's so beefy you just don't care about that, why are
you worried about the launcher?)
regards, tom lane
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, owayssk@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: High CPU load caused by the autovacuum launcher process
In-Reply-To: <24641.1528472473@sss.pgh.pa.us>
* 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