Re: Vacuuming big btree indexes without pages with deleted items

> 31 марта 2015 г., в 23:33, Kevin Grittner <kgrittn(at)ymail(dot)com> написал(а):
>
> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> wrote:
>> On 3/27/15 5:15 AM, Vladimir Borodin wrote:
>
>>> Master writes this record to xlog in btvacuumscan function after
>>> vacuuming of all index pages. And in case of no pages with
>>> deleted items xlog record would contain lastBlockVacuumed 0.
>>>
>>> In btree_xlog_vacuum replica reads all blocks from
>>> lastBlockVacuumed to last block of the index while applying this
>>> record because there is no api in the buffer manager to
>>> understand if the page is unpinned.
>>>
>>> So if the index is quite big (200+ GB in described case) it
>>> takes much time to do it.
>
>>> 2. Is it possible not to write to xlog record with
>>> lastBlockVacuumed 0 in some cases? For example, in case of not
>>> deleting any pages.
>
>> Possibly, but that's much higher risk. Without studying it, if we
>> wanted to mess around with that it might actually make more sense
>> to XLOG a set of blkno's that got vacuumed, but I suspect that
>> wouldn't be a win.
>
> I feel pretty confident that it would be a win in some significant
> cases, but it could be worse in some cases by changing sequential
> access to random, unless we use heuristics to protect against
> that. But...
>
>>> Or maybe there are some better ways of improving this situation?
>
> This is a start of a better way:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2ed5b87f96d473962ec5230fd820abfeaccb2069
>
> If we expand on that commit to cover non-MVCC index scans,
> index-only scans, and index scans of non-WAL-logged indexes, then
> this whole aspect of btree vacuum can be eliminated. It seems
> extremely dubious that all of that could be done for 9.5, and it's
> certainly not material for back-patching to any stable branches,
> but it would be a more complete and better-performing fix than the
> alternatives being discussed here.

Kevin, thanks for your work in this direction.

This way seems to be definitely better. It doesn’t matter that it would not be included in 9.5 and back-patched to stable versions. This thread is mostly about what could be done in the future. If other cases (including index-only scans) would be addressed in 9.6, for example, that would be really cool.

>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

--
May the force be with you…
https://simply.name