Re: VACUUM touching file but not updating relation

On 11 November 2011 23:28, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Thom Brown <thom(at)linux(dot)com> writes:
>> On 11 November 2011 00:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Thom Brown <thom(at)linux(dot)com> writes:
>>>> I just noticed that the VACUUM process touches a lot of relations
>>>> (affects mtime) but for one file I looked at, it didn't change.  This
>>>> doesn't always happen, and many relations aren't touched at all.
>
>>> No immmediate ideas as to why the mtime would change if the file
>>> contents didn't.  It seems like there must be a code path that marked
>>> a buffer dirty without having changed it, but we're usually pretty
>>> careful about that.
>
>> I checked all files where the time stamp of the file had changed, but
>> had the same MD5 sum.  I used the list in the query you mentioned and
>> get: [ mostly indexes ]
>
> Hmm, is this on a hot standby master?

It's using a wal_level of hot_standby and has max_wal_senders set to
2, but it's not actually replicating to anywhere else. But if I
comment out both of these, restart, then compare pre-vacuum and
post-vacuum, I get the following results for unchanged but touched
items:

test=# select oid,relname from pg_class where relfilenode in
(11680,11682,11684,11686,11690,16530);
oid | relname
-------+---------------------
2619 | pg_statistic
2840 | pg_toast_2619
2841 | pg_toast_2619_index
16530 | cows2
(4 rows)

The items which didn't match a result in this instance were 11686 and
11690, which is surprising since they both have a visibility map and
free space map, indicating they're some kind of table.

> I observe that _bt_delitems_vacuum() unconditionally dirties the page
> and writes a WAL record, whether it has anything to do or not; and that
> if XLogStandbyInfoActive() then btvacuumscan will indeed call it despite
> there being (probably) nothing useful to do.  Seems like that could be
> improved.  The comment explaining why it's necessary to do that doesn't
> make any sense to me, either.

Well the effect, in the single instances I've checked, is certainly
more pronounced for hot_standby, but there still appears to be some
occurrences for minimal wal_level too.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company