On 12 November 2011 00:08, Thom Brown <thom@xxxxxxxxx> wrote: > On 11 November 2011 23:28, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Thom Brown <thom@xxxxxxxxx> writes: >>> On 11 November 2011 00:55, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >>>> Thom Brown <thom@xxxxxxxxx> 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. So would you say this is acceptable and normal activity, or is something awry here? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
