On Mon, Jan 28, 2013 at 9:09 PM, Josh Berkus <j...@agliodbs.com> wrote: > > Let's do this by example. TableA is a large table which receives an > almost constant stream of individual row updates, inserts, and deletes. > > DEFAULTS: > > XID 1: First rows in TableA are updated. > XID 200m: Anti-wraparound autovac of TableA. > All XIDs older than XID 100m set to FROZENXID. > XID 300m: Anti-wraparound autovac of TableA > All XIDs older than XID 200M set to FROZENXID. > XID 400m: Anti-wraparound autovac of TableA > All XIDs older than XID 300M set to FROZENXID. > XID 500m: Anti-wraparound autovac of TableA > All XIDs older than XID 400M set to FROZENXID. > XID 600m: Anti-wraparound autovac of TableA > All XIDs older than XID 500M set to FROZENXID.
You seem to be assuming the only reason for vacuums to occur on this table is autovacuum_freeze_max_age, which doesn't seem likely to be the case for a busy table. > vacuum_freeze_min_age = 1m > > XID 1: First rows in TableA are updated. > XID 200m: Anti-wraparound autovac of TableA. > All XIDs older than XID 199m set to FROZENXID. > XID 399m: Anti-wraparound autovac of TableA > All XIDs older than XID 398M set to FROZENXID. > XID 598m: Anti-wraparound autovac of TableA > All XIDs older than XID 597M set to FROZENXID. > > > vacuum_freeze_min_age = 1m, autovacuum_freeze_max_age = 500m > > XID 1: First rows in TableA are updated. > XID 500m: Anti-wraparound autovac of TableA. > All XIDs older than XID 499m set to FROZENXID. > > As you can see, the current default settings cause 80% more wraparound > autovacs per table than vacuum_freeze_min_age of 1m would, and almost > 500% more than what I consider sane settings would. You can get nearly all the benefits of your "sane" settings just by increasing autovacuum_freeze_max_age and leaving vacuum_freeze_min_age alone. (Assuming the table doesn't get vacuumed for other reasons) Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
