[HACKERS] Re: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[Masahiko Sawada]

On Fri, Dec 2, 2016 at 3:50 AM, Robert Haas  wrote:
> On Thu, Dec 1, 2016 at 1:39 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> I think that the indexes only need to be scanned if the VACUUM finds
>>> dead tuples.  But even 1 dead tuple will cause a complete scan of
>>> every index.  I've complained about this before and I think there's
>>> room for improvement here, but nobody's been motivated enough to
>>> pursue this yet.
>>
>> The thing that's been speculated about in the past is having some
>> threshold larger than 1 on the minimum number of dead tuples needed
>> to cause a cleanup pass.
>
> Agreed.
>
>> It wouldn't be hard to implement, if you
>> could get consensus on what the threshold should be.
>
> Also agreed.
>
>> I'd think
>> some algorithm similar to the autovacuum thresholds might be
>> appropriate.  It's not quite clear how this would interact with
>> HOT pruning, though.
>
> What's the relevance of HOT pruning here?
>
> I was thinking that the relevant metric might be how many pages
> contain dead tuples, because what we really want to do to reduce the
> cost of future vacuuming and future index-only scans is get pages
> marked all-visible.  Say, if less than 2% of the pages in the table
> contain dead tuples and the space required to store the TIDs is less
> than 50% of maintenance_work_mem, skip the index scans.  The first of
> those thresholds, at least, would probably need to be configurable,
> but that kind of idea.

I think that this idea is better. If the number of pages containing
dead tuple is less than threshold (e.g.
vacuum_index_cleanup_scale_factor), we can skip  the cleanup index
scans.
I will write the patch and submit to next CF.

> The alternative that's been proposed is to do something based on the
> number of dead tuples but, as somebody pointed out in a previous
> discussion of this topic, one dead tuple per page throughout the whole
> table is a LOT worse than same number of dead tuples all on the same
> pages.  You don't want to keep scanning large chunks of the heap
> because you're too lazy to visit the indexes.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: 答复: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[Robert Haas]

On Thu, Dec 1, 2016 at 9:54 PM, xu jian  wrote:
> Thanks every for your help. I am not familiar with the internal of the
> vacuum freeze, just curious if there is no row change on the table(in other
> words, all pages are frozen), why could index page have dead tuple?

It can't.   If the *entire* table is frozen, then I would think it
shouldn't be scanning the indexes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] 答复: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[xu jian]

Thanks every for your help. I am not familiar with the internal of the vacuum 
freeze, just curious if there is no row change on the table(in other words, all 
pages are frozen), why could index page have dead tuple?

is it possible to scan data page first, if all data page are frozen, skipping 
the index page scan step. Perhaps there is other reason vacuum freeze does 
index page first, then is it possible to provide a option to skip index page 
scan step in vacuum freeze command? thanks


James


发件人: Robert Haas <robertmh...@gmail.com>
发送时间: 2016年12月1日 13:50:49
收件人: Tom Lane
抄送: xu jian; Masahiko Sawada; pgsql-hackers@postgresql.org
主题: Re: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 
vacuum freeze skip page on index?

On Thu, Dec 1, 2016 at 1:39 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Robert Haas <robertmh...@gmail.com> writes:
>> I think that the indexes only need to be scanned if the VACUUM finds
>> dead tuples.  But even 1 dead tuple will cause a complete scan of
>> every index.  I've complained about this before and I think there's
>> room for improvement here, but nobody's been motivated enough to
>> pursue this yet.
>
> The thing that's been speculated about in the past is having some
> threshold larger than 1 on the minimum number of dead tuples needed
> to cause a cleanup pass.

Agreed.

> It wouldn't be hard to implement, if you
> could get consensus on what the threshold should be.

Also agreed.

> I'd think
> some algorithm similar to the autovacuum thresholds might be
> appropriate.  It's not quite clear how this would interact with
> HOT pruning, though.

What's the relevance of HOT pruning here?

I was thinking that the relevant metric might be how many pages
contain dead tuples, because what we really want to do to reduce the
cost of future vacuuming and future index-only scans is get pages
marked all-visible.  Say, if less than 2% of the pages in the table
contain dead tuples and the space required to store the TIDs is less
than 50% of maintenance_work_mem, skip the index scans.  The first of
those thresholds, at least, would probably need to be configurable,
but that kind of idea.

The alternative that's been proposed is to do something based on the
number of dead tuples but, as somebody pointed out in a previous
discussion of this topic, one dead tuple per page throughout the whole
table is a LOT worse than same number of dead tuples all on the same
pages.  You don't want to keep scanning large chunks of the heap
because you're too lazy to visit the indexes.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[HACKERS] Re: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[Robert Haas]

On Thu, Dec 1, 2016 at 1:39 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I think that the indexes only need to be scanned if the VACUUM finds
>> dead tuples.  But even 1 dead tuple will cause a complete scan of
>> every index.  I've complained about this before and I think there's
>> room for improvement here, but nobody's been motivated enough to
>> pursue this yet.
>
> The thing that's been speculated about in the past is having some
> threshold larger than 1 on the minimum number of dead tuples needed
> to cause a cleanup pass.

Agreed.

> It wouldn't be hard to implement, if you
> could get consensus on what the threshold should be.

Also agreed.

> I'd think
> some algorithm similar to the autovacuum thresholds might be
> appropriate.  It's not quite clear how this would interact with
> HOT pruning, though.

What's the relevance of HOT pruning here?

I was thinking that the relevant metric might be how many pages
contain dead tuples, because what we really want to do to reduce the
cost of future vacuuming and future index-only scans is get pages
marked all-visible.  Say, if less than 2% of the pages in the table
contain dead tuples and the space required to store the TIDs is less
than 50% of maintenance_work_mem, skip the index scans.  The first of
those thresholds, at least, would probably need to be configurable,
but that kind of idea.

The alternative that's been proposed is to do something based on the
number of dead tuples but, as somebody pointed out in a previous
discussion of this topic, one dead tuple per page throughout the whole
table is a LOT worse than same number of dead tuples all on the same
pages.  You don't want to keep scanning large chunks of the heap
because you're too lazy to visit the indexes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[Tom Lane]

Robert Haas  writes:
> I think that the indexes only need to be scanned if the VACUUM finds
> dead tuples.  But even 1 dead tuple will cause a complete scan of
> every index.  I've complained about this before and I think there's
> room for improvement here, but nobody's been motivated enough to
> pursue this yet.

The thing that's been speculated about in the past is having some
threshold larger than 1 on the minimum number of dead tuples needed
to cause a cleanup pass.  It wouldn't be hard to implement, if you
could get consensus on what the threshold should be.  I'd think
some algorithm similar to the autovacuum thresholds might be
appropriate.  It's not quite clear how this would interact with
HOT pruning, though.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: [HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[Robert Haas]

On Thu, Dec 1, 2016 at 9:45 AM, xu jian  wrote:
> Thanks for your reply. Is there any reason to update index
> statistics even if there is no changes on the table?
> or is there any way to disable index statistics update during vacuum freeze?
> thanks

I think that the indexes only need to be scanned if the VACUUM finds
dead tuples.  But even 1 dead tuple will cause a complete scan of
every index.  I've complained about this before and I think there's
room for improvement here, but nobody's been motivated enough to
pursue this yet.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] 答复: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on index?

[xu jian]

Hi Masahiko,

Thanks for your reply. Is there any reason to update index statistics 
even if there is no changes on the table?
or is there any way to disable index statistics update during vacuum freeze? 
thanks

James



发件人: Masahiko Sawada 
发送时间: 2016年12月1日 9:06:15
收件人: xu jian
抄送: pgsql-hackers@postgresql.org
主题: Re: [HACKERS] postgres 1 个(共 2 个) can pg 9.6 vacuum freeze skip page on 
index?

On Thu, Dec 1, 2016 at 1:33 AM, xu jian  wrote:
> Hello,
>
>Please execute me if I am using the wrong mailing list, but I ask the
> question in pgsql-admin, looks like no one know the answer.
>
>
> we upgraded our pg db to 9.6, as we know, pg9.6 doesn't need full table scan
> in vacuum freeze.
>
> http://rhaas.blogspot.com/2016/03/no-more-full-table-vacuums.html
>
>
> so we think if we have run vacuum freeze on the table, and there is no
> change on table which has been vacuum freeze before  it should finish super
> faster.
>
>
> However, it doesn't look like we expect. the next run of vacuum freeze still
> take long time. Then we run vacuum freeze with verbose. we notice it spends
> long time on scanning index.
>
> it seems even all rows are frozen on the data page, vacuum freeze still
> needs to scan all the index pages. if we drop the index, then vacuum freeze
> finishes immediately.
>
>
> Does anyone know if it is true?

Yeah that's true. The vacuum on each index is required in order to
update index statistics even if  no updating on table.

> Btw, our table is large, and has about 40GB index files.  is there anyway to
> make the vacuum freeze faster in this case?

I guess that there is no way.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center