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:

/messages/by-id/CAJH0_Xt2=qrXo8niU1q5yZS0T03U6ND3KnYSUKUquojEYQCqCA@mail.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 03/28/2017 03:15 AM, Agarwal, Prateek wrote:

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.

So are you using replication slots?

Or prepared transactions? Or maybe you just have a really old open
transaction?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for your responses!
We are not using prepared transactions and not using replication slots as well.
We do have a lot of transactions getting created and closed on account of executing the update queries.

Thanks,
Prateek
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, March 28, 2017 11:54 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Agarwal, Prateek <prateeka@hpe.com>; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Issue in autovacuum

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 03/28/2017 03:15 AM, Agarwal, Prateek wrote:

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.

So are you using replication slots?

Or prepared transactions? Or maybe you just have a really old open transaction?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general