On Fri, Feb 1, 2013 at 2:34 PM, Andres Freund <and...@2ndquadrant.com> wrote: > On 2013-02-01 14:05:46 -0800, Jeff Janes wrote:
>> As far as I can tell this bug kicks in when your cluster gets to be >> older than freeze_min_age, and then lasts forever after. After that >> point pretty much every auto-vacuum inspired by update/deletion >> activity will get promoted to a full table scan. (Which makes me >> wonder how much field-testing the vm-only vacuum has received, if it >> was rarely happening in practice due to this bug.) > > I think you're misreading the code. freezeTableLimit is calculated by >> > limit = ReadNewTransactionId() - freezetable; > which is always relative to the current xid. The bug was that > freezetable had the wrong value in autovac due to freeze_min_age being > used instead of freeze_table_age. Right. Since freeze_min_age was mistakenly being used, the limit would be 50 million in the past (rather than 150 million) under defaults. But since the last full-table vacuum, whenever that was, used freeze_min_age for its intended purpose, that means the 50 million in the past *at the time of that last vacuum* is the highest that relfrozenxid can be. And that is going to be further back than 50 million from right now, so the vacuum will always be promoted to a full scan. I am not entirely sure of my logic above[1], but I'm depending on empirical observation for my conclusion. The attached patch emits a log entry telling if scan_all is being used, and it always is used (under the bug) once the database gets old enough. Or at least, I've never seen it not use scan_all after that point. As an aside, it does seem like log_autovacuum_min_duration=0 should log whether a scan_all was done, and if so what relfrozenxid got set to. But looking at where the log message is generated, I don't know where to retrieve that info. [1] I don't know why it is that a scan_all vacuum with a freeze_min_age of 50m (or a freezeLimit of 50 million ago) will not set relfrozenxid to a higher value than that if it discovers that it can, but it doesn't seem to. Cheers, Jeff
autovac_log.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
