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.klaver@aklaver.com