On 9/6/13 2:13 PM, Bruce Momjian wrote:
On Fri, Sep 6, 2013 at 01:01:59PM -0400, Bruce Momjian wrote:
This December 2012 thread by Andrew Dunstan shows he wasn't aware that a
manual VACUUM was required for index-only scans. That thread ended with
us realizing that pg_upgrade's ANALYZE runs will populate
pg_class.relallvisible.
What I didn't see in that thread is an analysis of what cases are going
to require manual vacuum, and I have seen no work in 9.3 to improve
that. I don't even see it on the TODO list.
OK, let's start the discussion then. I have added a TODO list:
Improve setting of visibility map bits for read-only and insert-only
workloads
So, what should trigger an auto-vacuum vacuum for these workloads?
Rather than activity, which is what normally drives autovacuum, it is
lack of activity that should drive it, combined with a high VM cleared
bit percentage.
It seems we can use these statistics values:
n_tup_ins | bigint
n_tup_upd | bigint
n_tup_del | bigint
n_tup_hot_upd | bigint
n_live_tup | bigint
n_dead_tup | bigint
n_mod_since_analyze | bigint
last_vacuum | timestamp with time zone
last_autovacuum | timestamp with time zone
Particilarly last_vacuum and last_autovacuum can tell us the last time
of vacuum. If the n_tup_upd/n_tup_del counts are low, and the VM set
bit count is low, it might need vacuuming, though inserts into existing
pages would complicate that.
Something else that might be useful to look at is if there are any FSM entries
or not. True insert only shouldn't have any FSM.
That said, there's definitely another case to think about... tables that see
update activity on newly inserted rows but not on older rows. A work queue that
is not pruned would be an example of that:
INSERT new work item
UPDATE work item SET status = 'In process';
UPDATE work item SET completion = '50%';
UPDATE work item SET sattus = 'Complete", completion = '100%';
In this case I would expect most of the pages in the table (except the very
end) to be all visible.
--
Jim C. Nasby, Data Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
