Re: Issue in autovacuum

On 03/28/2017 03:15 AM, Agarwal, Prateek wrote:
> I had 9.3.0 previously where auto vacuum was working fine where it was
> able to reclaim space by removing dead tuples.
>
> Version: PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc
> (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
>
> When I upgrade my db to 9.4.6 (my application remains same and hardly
> any code change w.r.t transaction declarations), it started complaining
> about dead rows below. I am not sure if it is something to do with
> upgrade or not.

The most current release of 9.4 is 9.4.11.

Given that your problem seems similar to this post:

https://www.postgresql.org/message-id/CAJH0_Xt2%3DqrXo8niU1q5yZS0T03U6ND3KnYSUKUquojEYQCqCA%40mail.gmail.com

upgrading to 9.4.11 will get you this fix from 9.4.10:

https://www.postgresql.org/docs/9.6/static/release-9-4-10.html

"Properly initialize replication slot state when recycling a
previously-used slot (Michael Paquier)

This failure to reset all of the fields of the slot could prevent VACUUM
from removing dead tuples."

So are you using replication slots?

>
> One thing, that was happening on my system was there were continuous
> updates to few tables around 10 million times over a period of 2-3 days.
>
>
> 2017-03-02 19:50:52 GMT [30498]: [41-1] [0] user=,db= LOG: automatic
> vacuum of table "myschema.mytable": index scans: 0
> pages: 0 removed, 23045 remain
> tuples: 0 removed, 902169 remain, 900511 are dead but not yet removable
> buffer usage: 23060 hits, 26263 misses, 0 dirtied
> avg read rate: 2.331 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.26s/0.38u sec elapsed 88.02 sec
>
> Not just mytable, even vaccum not able to claim dead rows on postgres
> internal tables like pg_class
>
> 2017-03-02 20:42:02 GMT [15592]: [3-1] [0] user=,db= LOG: automatic
> vacuum of table "mydb.pg_catalog.pg_class": index scans: 0
> pages: 0 removed, 407 remain
> tuples: 0 removed, 18876 remain, 16568 are dead but not yet removable
> buffer usage: 411 hits, 696 misses, 0 dirtied
> avg read rate: 2.424 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.00s/0.01u sec elapsed 2.24 sec
>
> CentOS 6.7
>
>
> My Vacuum configuration:
> #------------------------------------------------------------------------------
> # AUTOVACUUM PARAMETERS
> #------------------------------------------------------------------------------
>
> autovacuum = on # Enable autovacuum subprocess? 'on'
> # requires track_counts to also be on.
> log_autovacuum_min_duration = 100 # -1 disables, 0 logs all actions and
> # their durations, > 0 logs only
> # actions running at least this number
> # of milliseconds.
> #autovacuum_max_workers = 3 # max number of autovacuum subprocesses
> # (change requires restart)
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50 # min number of row updates before
> # vacuum
> #autovacuum_analyze_threshold = 50 # min number of row updates before
> # analyze
> autovacuum_vacuum_scale_factor = 0.05 # fraction of table size before
> vacuum
> autovacuum_analyze_scale_factor = 0.05 # fraction of table size
> before analyze
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before
> forced vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
> # autovacuum, in milliseconds;
> # -1 means use vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
> # autovacuum, -1 means use
> # vacuum_cost_limit
>
>
> Any possible root cause? Is there a known issue in 9.4.6?
>
> Thank you for your time!
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com