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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
