From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Amarendra Konda <amar.vijaya@gmail.com>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Autovacuum is cleaning very less dead tuples
Date: Fri, 27 Sep 2019 07:55:06 +0200
Message-ID: <b8fcc92f8c8a648ee347fbb7d4db22a81b7caf58.camel@cybertec.at> (raw)
In-Reply-To: <CAJNAD0=XJ1ALpYb6qJtBD1+2AgwJLnmcU0JtGqCH5tD5hEHDUQ@mail.gmail.com>
References: <CAJNAD0=XJ1ALpYb6qJtBD1+2AgwJLnmcU0JtGqCH5tD5hEHDUQ@mail.gmail.com>
On Fri, 2019-09-27 at 11:10 +0530, Amarendra Konda wrote:
> As part of vacuum tuning, We have set the below set of parameters.
>
> > select relname,reloptions, pg_namespace.nspname from pg_class join
> pg_namespace on pg_namespace.oid=pg_class.relnamespace where relname
> IN('process_instance') and pg_namespace.nspname='public';
> relname |
> reloptions
> | nspname
> --------------+----------------------------------------------------
> -------------------------------------------------------------------
> -------------------------------+---------
> process_instance |
> {autovacuum_vacuum_scale_factor=0,autovacuum_vacuum_threshold=20000,a
> utovacuum_vacuum_cost_limit=1000,autovacuum_vacuum_cost_delay=10}
> | public
That's not so much tuning as breaking.
You have set autovacuum to run all the time at a snail's pace.
That way, it will have trouble getting any work done.
Don't touch autovacuum_vacuum_scale_factor and
autovacuum_vacuum_threshold. Don't raise autovacuum_vacuum_cost_limit.
If anything, lower autovacuum_vacuum_cost_delay.
> However n_dead_tup value from pg_stat_user_tables was always showing
> very high value. Most of the time, it is greater than 100K dead
> tuples.
That is only a problem if the number of live tuples is less than
500000.
> Overall, we couldn't able to correlate on why autovacuum was able to
> cleanup only < 2K tuples, even though there are mode dead tuples
> based on the statistics ? Can you please explain on why we are
> notcing huge difference and what steps needs to taken to minimize the
> gap ?
It is questionable if there is a problem at all.
> Log message
>
> 2019-09-25 00:06:31 UTC::@:[80487]:LOG: automatic vacuum of table
> "fc_db_web_2.public.process_instance": index scans: 1
> pages: 0 removed, 854445 remain, 0 skipped due to pins, 774350
> skipped frozen
> tuples: 1376 removed, 16819201 remain, 21 are dead but not yet
> removable
> system usage: CPU 44.57s/33.04u sec elapsed 5088.65 sec
This shows that at least this table has no problem.
Even with your settings, autovacuum finished in 5 seconds and
could clean up almost everything.
> Live and Dead tuples
>
> select relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE
> relname='process_instance';
> relname | n_live_tup | n_dead_tup
> --------------+------------+------------
> conversation | 16841596 | 144202
Perfect. There is no problem at all.
The table has less than 20% dead tuples, so everything is in perfect
order.
Just stop fighting windmills.
Give up your tuning attempts and reset all parameters back to the
default.
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, amar.vijaya@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Autovacuum is cleaning very less dead tuples
In-Reply-To: <b8fcc92f8c8a648ee347fbb7d4db22a81b7caf58.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