Search Postgresql Archives

Re: VACUUM touching file but not updating relation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux