Re: [HACKERS] VACUUM's ancillary tasks

[Alvaro Herrera]

Thomas Munro wrote:

> About BRIN indexes:  I couldn't find an explanation of why BRIN
> indexes don't automatically create new summary tuples when you insert
> a new tuple in an unsummarised page range.  Is it deferred until
> VACUUM time in order to untangle some otherwise unresolvable
> interlocking or crash safety problem, or could that one day be done?

The reason is performance for the bulk insert case, which we don't want
to slow down; the range summarization is done at a later time by a
background process so that the inserting process is not slowed down by
having to repeatedly re-compute the summary tuple for each heap
insertion.  I think the ideal mechanism would be that a summarization is
signalled somehow (to another process) as soon as an insertion occupies
a block just past the previous unsummarized range.  (If there are many
readers, perhaps it's better to summarize when the range is half full or
something like that.)

We could have a reloption that switches from this behavior to the other
obvious possibility which is to insert a new summary tuple upon the
first heap insertion to an unsummarized range, but ISTM that that
behavior is pessimal.

> Counting inserts seems slightly bogus because you can't tell whether
> those were inserts into an existing summarised block which is
> self-maintaining or not.  At first glance it looks a bit like
> unsummarised ranges can only appear at the end of the table, is that
> right?  If so, couldn't you detect the number of unsummarised BRIN
> blocks just by comparing the highest summarised BRIN block and the
> current heap size?

We don't have mechanism to invalidate the summary of a range thus far,
so yeah we could try to detect it directly as you suggest.

I would like to be able to invalidate these tuples though, for the case
where many tuples are removed from a range (a fresh summarization could
produce tighter limits).  I think this problem does not necessarily
invalidate the idea above.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] VACUUM's ancillary tasks

[Haribabu Kommi]

On Wed, Oct 19, 2016 at 2:44 AM, Robert Haas  wrote:

> On Sun, Oct 16, 2016 at 3:35 PM, Jeff Janes  wrote:
> > On Fri, Oct 7, 2016 at 6:14 AM, Robert Haas 
> wrote:
> >> On Thu, Oct 6, 2016 at 8:40 PM, Jeff Janes 
> wrote:
> >> > In commit 37484ad2aacef5ec7, you changed the way that frozen tuples
> were
> >> > represented, so that we could make freezing more aggressive without
> >> > losing
> >> > forensic evidence.  But I don't think we ever did anything to actually
> >> > make
> >> > the freezing more aggressive.
> >>
> >> See 3cff1879f8d03cb729368722ca823a4bf74c0cac.  The objection to doing
> >> it in other cases is that it adds write-ahead log volume which might
> >> cause its own share of problems.  There might be some way of getting
> >> ahead of that, though.  I think if we piggyback on an existing WAL
> >> record like XLOG_HEAP2_CLEAN or XLOG_HEAP2_VISIBLE the impact might be
> >> minimal, but I haven't been dedicated enough to try to write the
> >> patch.
> >>
> >> > When I applied the up-thread patch so that pgbench_history gets
> autovac,
> >> > those autovacs don't actually cause any pages to get frozen until the
> >> > wrap
> >> > around kicks in, even when all the tuples on the early pages should be
> >> > well
> >> > beyond vacuum_freeze_min_age.
> >>
> >> If the pages are already all-visible, they'll be skipped until
> >> vacuum_freeze_table_age is reached.
> >
> > So if I set vacuum_freeze_min_age to zero, then they should become
> > all-visible and all-frozen at the same time, and avoid that problem?
>
> Hmm.  I *think* so...
>
> > From the docs on vacuum_freeze_min_age: "Increasing this setting may
> avoid
> > unnecessary work if the rows that would otherwise be frozen will soon be
> > modified again".  How much work is that? Presumably they are already
> getting
> > marked visible, is marking them frozen too a meaningful amount of extra
> > work?  Is it just the extra WAL record?
>
> Yeah, the extra WAL record is the main thing, I think.


Closed in 2016-11 commitfest with "returned with feedback" status as per my
understanding from the recent mails on the thread.

Please feel free to change the status if the current status is doesn't
reflect the
actual status.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] VACUUM's ancillary tasks

[Robert Haas]

On Sun, Oct 16, 2016 at 3:35 PM, Jeff Janes  wrote:
> On Fri, Oct 7, 2016 at 6:14 AM, Robert Haas  wrote:
>> On Thu, Oct 6, 2016 at 8:40 PM, Jeff Janes  wrote:
>> > In commit 37484ad2aacef5ec7, you changed the way that frozen tuples were
>> > represented, so that we could make freezing more aggressive without
>> > losing
>> > forensic evidence.  But I don't think we ever did anything to actually
>> > make
>> > the freezing more aggressive.
>>
>> See 3cff1879f8d03cb729368722ca823a4bf74c0cac.  The objection to doing
>> it in other cases is that it adds write-ahead log volume which might
>> cause its own share of problems.  There might be some way of getting
>> ahead of that, though.  I think if we piggyback on an existing WAL
>> record like XLOG_HEAP2_CLEAN or XLOG_HEAP2_VISIBLE the impact might be
>> minimal, but I haven't been dedicated enough to try to write the
>> patch.
>>
>> > When I applied the up-thread patch so that pgbench_history gets autovac,
>> > those autovacs don't actually cause any pages to get frozen until the
>> > wrap
>> > around kicks in, even when all the tuples on the early pages should be
>> > well
>> > beyond vacuum_freeze_min_age.
>>
>> If the pages are already all-visible, they'll be skipped until
>> vacuum_freeze_table_age is reached.
>
> So if I set vacuum_freeze_min_age to zero, then they should become
> all-visible and all-frozen at the same time, and avoid that problem?

Hmm.  I *think* so...

> From the docs on vacuum_freeze_min_age: "Increasing this setting may avoid
> unnecessary work if the rows that would otherwise be frozen will soon be
> modified again".  How much work is that? Presumably they are already getting
> marked visible, is marking them frozen too a meaningful amount of extra
> work?  Is it just the extra WAL record?

Yeah, the extra WAL record is the main thing, I think.

-- 
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] VACUUM's ancillary tasks

[Jeff Janes]

On Fri, Oct 7, 2016 at 6:14 AM, Robert Haas  wrote:

> On Thu, Oct 6, 2016 at 8:40 PM, Jeff Janes  wrote:
> > In commit 37484ad2aacef5ec7, you changed the way that frozen tuples were
> > represented, so that we could make freezing more aggressive without
> losing
> > forensic evidence.  But I don't think we ever did anything to actually
> make
> > the freezing more aggressive.
>
> See 3cff1879f8d03cb729368722ca823a4bf74c0cac.  The objection to doing
> it in other cases is that it adds write-ahead log volume which might
> cause its own share of problems.  There might be some way of getting
> ahead of that, though.  I think if we piggyback on an existing WAL
> record like XLOG_HEAP2_CLEAN or XLOG_HEAP2_VISIBLE the impact might be
> minimal, but I haven't been dedicated enough to try to write the
> patch.
>
> > When I applied the up-thread patch so that pgbench_history gets autovac,
> > those autovacs don't actually cause any pages to get frozen until the
> wrap
> > around kicks in, even when all the tuples on the early pages should be
> well
> > beyond vacuum_freeze_min_age.
>
> If the pages are already all-visible, they'll be skipped until
> vacuum_freeze_table_age is reached.
>

So if I set vacuum_freeze_min_age to zero, then they should become
all-visible and all-frozen at the same time, and avoid that problem?

>From the docs on vacuum_freeze_min_age: "Increasing this setting may avoid
unnecessary work if the rows that would otherwise be frozen will soon be
modified again".  How much work is that? Presumably they are already
getting marked visible, is marking them frozen too a meaningful amount of
extra work?  Is it just the extra WAL record?

Cheers,

Jeff

Re: [HACKERS] VACUUM's ancillary tasks

[Robert Haas]

On Thu, Oct 6, 2016 at 8:40 PM, Jeff Janes  wrote:
> In commit 37484ad2aacef5ec7, you changed the way that frozen tuples were
> represented, so that we could make freezing more aggressive without losing
> forensic evidence.  But I don't think we ever did anything to actually make
> the freezing more aggressive.

See 3cff1879f8d03cb729368722ca823a4bf74c0cac.  The objection to doing
it in other cases is that it adds write-ahead log volume which might
cause its own share of problems.  There might be some way of getting
ahead of that, though.  I think if we piggyback on an existing WAL
record like XLOG_HEAP2_CLEAN or XLOG_HEAP2_VISIBLE the impact might be
minimal, but I haven't been dedicated enough to try to write the
patch.

> When I applied the up-thread patch so that pgbench_history gets autovac,
> those autovacs don't actually cause any pages to get frozen until the wrap
> around kicks in, even when all the tuples on the early pages should be well
> beyond vacuum_freeze_min_age.

If the pages are already all-visible, they'll be skipped until
vacuum_freeze_table_age is reached.

-- 
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] VACUUM's ancillary tasks

[Masahiko Sawada]

On Fri, Oct 7, 2016 at 6:46 AM, Robert Haas  wrote:
> On Thu, Oct 6, 2016 at 3:56 PM, Jeff Janes  wrote:
>>> Sure, I could handle each case separately, but the goal of this patch
>>> (as hinted at in the Subject) is to generalize all the different tasks
>>> we've been giving to VACUUM.  The only missing piece is what the first
>>> patch addresses; which is insert-mostly tables never getting vacuumed.
>>
>> I don't buy the argument that we should do this in order to be "general".
>> Those other pieces are present to achieve a specific job, not out of
>> generality.
>
> +1.
>
>> If we want to have something to vacuum insert-mostly tables for the sake of
>> the index-only-scans, then I don't think we can ignore the fact that the
>> visibility map is page based, not tuple based.  If we insert 10,000 tuples
>> into a full table and they all go into 100 pages at the end, that is very
>> different than inserting 10,000 tuples which each go into a separate page
>> (because each page has that amount of freespace).  In one case you have
>> 10,000 tuples not marked as all visible, in the other case you have
>> 1,000,000 not marked as all visible.
>
> +1.
>
>> Also, I think that doing more counts which get amalgamated into the same
>> threshold, rather than introducing another parameter, is a bad thing.  I
>> have insert-mostly, most of the time, tables which are never going to
>> benefit from index-only-scans, and I don't want to pay the cost of them
>> getting vacuumed just because of some inserts, when I will never get a
>> benefit out of it.  I could turn autovacuum off for those tables, but then I
>> would have to remember to manually intervene on the rare occasion they do
>> get updates or deletes.  I want to be able to manually pick which tables I
>> sign up for this feature (and then forget it), not manually pick which ones
>> to exempt from it and have to constantly review that.
>
> Of course, if you do that, then what will happen is eventually it will
> be time to advance relfrozenxid for that relation, and you'll get a
> giant soul-crushing VACUUM of doom, rather than a bunch of small,
> hopefully-innocuous VACUUMs.  I've been wondering if would make sense
> to trigger vacuums based on inserts based on a *fixed* number of
> pages, rather than a percentage of the table.  Say, for example,
> whenever we have 64MB of not-all-visible pages, we VACUUM.

+1

>
> There are some difficulties:
>
> 1. We don't want to vacuum too early.  For example, a bulk load may
> vastly inflate the size of a table, but vacuuming it while the load is
> in progress will be useless.  You can maybe avoid that problem by
> basing this on statistics only reported at the end of the transaction,
> but there's another, closely-related issue: vacuuming right after the
> transaction completes may be useless, too, if there are old snapshots
> still in existence that render the newly-inserted tuples
> not-all-visible.

If the dummy xid can be generated for vacuum before starting vacuum
for maintenance vm which is triggered by the amount of the cleared vm
page, that vacuum could wait for old transaction finishes.


> 2. We don't want to trigger index vacuuming for a handful of dead
> tuples, or at least not too often.  I've previously suggested
> requiring a certain minimum number of dead index tuples that would be
> required before index vacuuming occurs; prior to that, we'd just
> truncate to dead line pointers and leave those for the next vacuum
> cycle.  This might need some refinement - should it be page-based? -
> but the basic idea still seems sound.
>

Where do we leave dead line pointers at?

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

Re: [HACKERS] VACUUM's ancillary tasks

[Masahiko Sawada]

On Fri, Oct 7, 2016 at 9:40 AM, Jeff Janes  wrote:
> On Thu, Oct 6, 2016 at 2:46 PM, Robert Haas  wrote:
>>
>>
>> > Also, I think that doing more counts which get amalgamated into the same
>> > threshold, rather than introducing another parameter, is a bad thing.  I
>> > have insert-mostly, most of the time, tables which are never going to
>> > benefit from index-only-scans, and I don't want to pay the cost of them
>> > getting vacuumed just because of some iOn Thu, Oct 6, 2016 at 3:56 PM,
>> > Jeff Janes  wrote:
>> >> Sure, I could handle each case separately, but the goal of this patch
>>
>> nserts, when I will never get a
>> > benefit out of it.  I could turn autovacuum off for those tables, but
>> > then I
>> > would have to remember to manually intervene on the rare occasion they
>> > do
>> > get updates or deletes.  I want to be able to manually pick which tables
>> > I
>> > sign up for this feature (and then forget it), not manually pick which
>> > ones
>> > to exempt from it and have to constantly review that.
>>
>> Of course, if you do that, then what will happen is eventually it will
>> be time to advance relfrozenxid for that relation, and you'll get a
>> giant soul-crushing VACUUM of doom, rather than a bunch of small,
>> hopefully-innocuous VACUUMs.
>
>
> I think I will get the soul-crushing vacuum of doom anyway, if the database
> lasts that long.  For one reason, in my case while updates and deletes are
> rare, they are common enough that the frozen vm bits from early vacuums will
> be mostly cleared before the vacuum of doom comes around.
>
> For a second reason, I don't think the frozen bit in the vm actually gets
> set by much of anything other than a autovacuum-for-wraparound or a manual
> VACUUM FREEZE.

Yeah, the freeze map would be effective especially for static table.
Since we can skip to vacuum frozen page and the freezing tuples is not
big pain usually, we might want to change autovacuum more aggressive
to freeze the page by reducing default value of vacuum_freeze_min_age.

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

Re: [HACKERS] VACUUM's ancillary tasks

[Jeff Janes]

On Thu, Oct 6, 2016 at 2:46 PM, Robert Haas  wrote:

>
> > Also, I think that doing more counts which get amalgamated into the same
> > threshold, rather than introducing another parameter, is a bad thing.  I
> > have insert-mostly, most of the time, tables which are never going to
> > benefit from index-only-scans, and I don't want to pay the cost of them
> > getting vacuumed just because of some iOn Thu, Oct 6, 2016 at 3:56 PM,
> Jeff Janes  wrote:
> >> Sure, I could handle each case separately, but the goal of this patch
>
> nserts, when I will never get a
> > benefit out of it.  I could turn autovacuum off for those tables, but
> then I
> > would have to remember to manually intervene on the rare occasion they do
> > get updates or deletes.  I want to be able to manually pick which tables
> I
> > sign up for this feature (and then forget it), not manually pick which
> ones
> > to exempt from it and have to constantly review that.
>
> Of course, if you do that, then what will happen is eventually it will
> be time to advance relfrozenxid for that relation, and you'll get a
> giant soul-crushing VACUUM of doom, rather than a bunch of small,
> hopefully-innocuous VACUUMs.


I think I will get the soul-crushing vacuum of doom anyway, if the database
lasts that long.  For one reason, in my case while updates and deletes are
rare, they are common enough that the frozen vm bits from early vacuums
will be mostly cleared before the vacuum of doom comes around.

For a second reason, I don't think the frozen bit in the vm actually gets
set by much of anything other than a autovacuum-for-wraparound or a manual
VACUUM FREEZE.

In commit 37484ad2aacef5ec7, you changed the way that frozen tuples were
represented, so that we could make freezing more aggressive without losing
forensic evidence.  But I don't think we ever did anything to actually make
the freezing more aggressive.

When I applied the up-thread patch so that pgbench_history gets autovac,
those autovacs don't actually cause any pages to get frozen until the wrap
around kicks in, even when all the tuples on the early pages should be well
beyond vacuum_freeze_min_age.

Cheers,

Jeff

Re: [HACKERS] VACUUM's ancillary tasks

[Robert Haas]

On Thu, Oct 6, 2016 at 3:56 PM, Jeff Janes  wrote:
>> Sure, I could handle each case separately, but the goal of this patch
>> (as hinted at in the Subject) is to generalize all the different tasks
>> we've been giving to VACUUM.  The only missing piece is what the first
>> patch addresses; which is insert-mostly tables never getting vacuumed.
>
> I don't buy the argument that we should do this in order to be "general".
> Those other pieces are present to achieve a specific job, not out of
> generality.

+1.

> If we want to have something to vacuum insert-mostly tables for the sake of
> the index-only-scans, then I don't think we can ignore the fact that the
> visibility map is page based, not tuple based.  If we insert 10,000 tuples
> into a full table and they all go into 100 pages at the end, that is very
> different than inserting 10,000 tuples which each go into a separate page
> (because each page has that amount of freespace).  In one case you have
> 10,000 tuples not marked as all visible, in the other case you have
> 1,000,000 not marked as all visible.

+1.

> Also, I think that doing more counts which get amalgamated into the same
> threshold, rather than introducing another parameter, is a bad thing.  I
> have insert-mostly, most of the time, tables which are never going to
> benefit from index-only-scans, and I don't want to pay the cost of them
> getting vacuumed just because of some inserts, when I will never get a
> benefit out of it.  I could turn autovacuum off for those tables, but then I
> would have to remember to manually intervene on the rare occasion they do
> get updates or deletes.  I want to be able to manually pick which tables I
> sign up for this feature (and then forget it), not manually pick which ones
> to exempt from it and have to constantly review that.

Of course, if you do that, then what will happen is eventually it will
be time to advance relfrozenxid for that relation, and you'll get a
giant soul-crushing VACUUM of doom, rather than a bunch of small,
hopefully-innocuous VACUUMs.  I've been wondering if would make sense
to trigger vacuums based on inserts based on a *fixed* number of
pages, rather than a percentage of the table.  Say, for example,
whenever we have 64MB of not-all-visible pages, we VACUUM.

There are some difficulties:

1. We don't want to vacuum too early.  For example, a bulk load may
vastly inflate the size of a table, but vacuuming it while the load is
in progress will be useless.  You can maybe avoid that problem by
basing this on statistics only reported at the end of the transaction,
but there's another, closely-related issue: vacuuming right after the
transaction completes may be useless, too, if there are old snapshots
still in existence that render the newly-inserted tuples
not-all-visible.

2. We don't want to trigger index vacuuming for a handful of dead
tuples, or at least not too often.  I've previously suggested
requiring a certain minimum number of dead index tuples that would be
required before index vacuuming occurs; prior to that, we'd just
truncate to dead line pointers and leave those for the next vacuum
cycle.  This might need some refinement - should it be page-based? -
but the basic idea still seems sound.

-- 
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] VACUUM's ancillary tasks

[Jeff Janes]

On Sat, Oct 1, 2016 at 1:34 PM, Vik Fearing  wrote:

>
> Sure, I could handle each case separately, but the goal of this patch
> (as hinted at in the Subject) is to generalize all the different tasks
> we've been giving to VACUUM.  The only missing piece is what the first
> patch addresses; which is insert-mostly tables never getting vacuumed.
>

I don't buy the argument that we should do this in order to be "general".
Those other pieces are present to achieve a specific job, not out of
generality.

If we want to have something to vacuum insert-mostly tables for the sake of
the index-only-scans, then I don't think we can ignore the fact that the
visibility map is page based, not tuple based.  If we insert 10,000 tuples
into a full table and they all go into 100 pages at the end, that is very
different than inserting 10,000 tuples which each go into a separate page
(because each page has that amount of freespace).  In one case you have
10,000 tuples not marked as all visible, in the other case you have
1,000,000 not marked as all visible.

Also, I think that doing more counts which get amalgamated into the same
threshold, rather than introducing another parameter, is a bad thing.  I
have insert-mostly, most of the time, tables which are never going to
benefit from index-only-scans, and I don't want to pay the cost of them
getting vacuumed just because of some inserts, when I will never get a
benefit out of it.  I could turn autovacuum off for those tables, but then
I would have to remember to manually intervene on the rare occasion they do
get updates or deletes.  I want to be able to manually pick which tables I
sign up for this feature (and then forget it), not manually pick which ones
to exempt from it and have to constantly review that.

Cheers,

Jeff

Re: [HACKERS] VACUUM's ancillary tasks

[Thomas Munro]

On Sun, Oct 2, 2016 at 9:34 AM, Vik Fearing  wrote:
> On 10/01/2016 09:28 AM, Thomas Munro wrote:
>> On Mon, Aug 29, 2016 at 1:26 PM, Vik Fearing  wrote:
>>> The attached two patches scratch two itches I've been having for a
>>> while.  I'm attaching them together because the second depends on the first.
>>>
>>> Both deal with the fact that [auto]vacuum has taken on more roles than
>>> its original purpose.
>>>
>>>
>>> Patch One: autovacuum insert-heavy tables
>>>
>>> If you have a table that mostly receives INSERTs, it will never get
>>> vacuumed because there are no (or few) dead rows.  I have added an
>>> "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
>>> the same way as "changes_since_analyze" does.
>>>
>>> The reason such a table needs to be vacuumed is currently twofold: the
>>> visibility map is not updated, slowing down index-only scans; and BRIN
>>> indexes are not maintained, rendering them basically useless.
>>
>> I'm aware of those two problems, but not very familiar with the
>> details.  I don't feel qualified to say whether insert counting is the
>> best approach to the problem at this point.  I looked into it a little
>> bit however, and had the following thoughts:
>>
>> About BRIN indexes:  I couldn't find an explanation of why BRIN
>> indexes don't automatically create new summary tuples when you insert
>> a new tuple in an unsummarised page range.  Is it deferred until
>> VACUUM time in order to untangle some otherwise unresolvable
>> interlocking or crash safety problem, or could that one day be done?
>> Assuming that it must be deferred for some technical reason and there
>> is no way around it, then I wonder if there is a more direct and
>> accurate way to figure out when it's necessary than counting inserts.
>> Counting inserts seems slightly bogus because you can't tell whether
>> those were inserts into an existing summarised block which is
>> self-maintaining or not.  At first glance it looks a bit like
>> unsummarised ranges can only appear at the end of the table, is that
>> right?  If so, couldn't you detect the number of unsummarised BRIN
>> blocks just by comparing the highest summarised BRIN block and the
>> current heap size?
>>
>> About visibility maps:  How crazy would it be to estimate the number
>> of not-all-visible pages instead?  It would be less work to count that
>> since it would only increase when the *first* tuple is inserted into a
>> page that is currently all visible (ie when the bit is cleared), not
>> for every tuple inserted into any page like your inserts_since_vacuum
>> counter.  Another difference is that inserts_since_vacuum is reset
>> even if vacuum finds that it *can't* set the all-visible bit for a
>> given page yet because of some concurrent transaction.  In that case
>> the bit is still not set but autovacuum has no reason to be triggered
>> again.
>
> Sure, I could handle each case separately, but the goal of this patch
> (as hinted at in the Subject) is to generalize all the different tasks
> we've been giving to VACUUM.  The only missing piece is what the first
> patch addresses; which is insert-mostly tables never getting vacuumed.

Yeah, that makes sense.  I just wanted to discuss what the ideal
launch conditions would be for those particular ancillary jobs, and
then figure out whether the difference matters.  Generally, I think
changes to autovacuum heuristics need some consensus-building
discussion, especially in light of other related ideas from Jeff
Janes, and from people involved with BRIN and visibility map design,
including Simon who signed up as a reviewer.  Since we're out of time
I'm going to move this to the November CF, and let's hear from them.

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
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] VACUUM's ancillary tasks

[Vik Fearing]

On 10/01/2016 09:28 AM, Thomas Munro wrote:
> On Mon, Aug 29, 2016 at 1:26 PM, Vik Fearing  wrote:
>> The attached two patches scratch two itches I've been having for a
>> while.  I'm attaching them together because the second depends on the first.
>>
>> Both deal with the fact that [auto]vacuum has taken on more roles than
>> its original purpose.
>>
>>
>> Patch One: autovacuum insert-heavy tables
>>
>> If you have a table that mostly receives INSERTs, it will never get
>> vacuumed because there are no (or few) dead rows.  I have added an
>> "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
>> the same way as "changes_since_analyze" does.
>>
>> The reason such a table needs to be vacuumed is currently twofold: the
>> visibility map is not updated, slowing down index-only scans; and BRIN
>> indexes are not maintained, rendering them basically useless.
> 
> I'm aware of those two problems, but not very familiar with the
> details.  I don't feel qualified to say whether insert counting is the
> best approach to the problem at this point.  I looked into it a little
> bit however, and had the following thoughts:
> 
> About BRIN indexes:  I couldn't find an explanation of why BRIN
> indexes don't automatically create new summary tuples when you insert
> a new tuple in an unsummarised page range.  Is it deferred until
> VACUUM time in order to untangle some otherwise unresolvable
> interlocking or crash safety problem, or could that one day be done?
> Assuming that it must be deferred for some technical reason and there
> is no way around it, then I wonder if there is a more direct and
> accurate way to figure out when it's necessary than counting inserts.
> Counting inserts seems slightly bogus because you can't tell whether
> those were inserts into an existing summarised block which is
> self-maintaining or not.  At first glance it looks a bit like
> unsummarised ranges can only appear at the end of the table, is that
> right?  If so, couldn't you detect the number of unsummarised BRIN
> blocks just by comparing the highest summarised BRIN block and the
> current heap size?
> 
> About visibility maps:  How crazy would it be to estimate the number
> of not-all-visible pages instead?  It would be less work to count that
> since it would only increase when the *first* tuple is inserted into a
> page that is currently all visible (ie when the bit is cleared), not
> for every tuple inserted into any page like your inserts_since_vacuum
> counter.  Another difference is that inserts_since_vacuum is reset
> even if vacuum finds that it *can't* set the all-visible bit for a
> given page yet because of some concurrent transaction.  In that case
> the bit is still not set but autovacuum has no reason to be triggered
> again.

Sure, I could handle each case separately, but the goal of this patch
(as hinted at in the Subject) is to generalize all the different tasks
we've been giving to VACUUM.  The only missing piece is what the first
patch addresses; which is insert-mostly tables never getting vacuumed.

As for the second patch, I would like to withdraw it and redesign it,
based on your comments.  The redesign I have in mind will no longer be
dependent on the first patch, so I'll submit it separately.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] VACUUM's ancillary tasks

[Thomas Munro]

On Mon, Aug 29, 2016 at 1:26 PM, Vik Fearing  wrote:
> The attached two patches scratch two itches I've been having for a
> while.  I'm attaching them together because the second depends on the first.
>
> Both deal with the fact that [auto]vacuum has taken on more roles than
> its original purpose.
>
>
> Patch One: autovacuum insert-heavy tables
>
> If you have a table that mostly receives INSERTs, it will never get
> vacuumed because there are no (or few) dead rows.  I have added an
> "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
> the same way as "changes_since_analyze" does.
>
> The reason such a table needs to be vacuumed is currently twofold: the
> visibility map is not updated, slowing down index-only scans; and BRIN
> indexes are not maintained, rendering them basically useless.

I'm aware of those two problems, but not very familiar with the
details.  I don't feel qualified to say whether insert counting is the
best approach to the problem at this point.  I looked into it a little
bit however, and had the following thoughts:

About BRIN indexes:  I couldn't find an explanation of why BRIN
indexes don't automatically create new summary tuples when you insert
a new tuple in an unsummarised page range.  Is it deferred until
VACUUM time in order to untangle some otherwise unresolvable
interlocking or crash safety problem, or could that one day be done?
Assuming that it must be deferred for some technical reason and there
is no way around it, then I wonder if there is a more direct and
accurate way to figure out when it's necessary than counting inserts.
Counting inserts seems slightly bogus because you can't tell whether
those were inserts into an existing summarised block which is
self-maintaining or not.  At first glance it looks a bit like
unsummarised ranges can only appear at the end of the table, is that
right?  If so, couldn't you detect the number of unsummarised BRIN
blocks just by comparing the highest summarised BRIN block and the
current heap size?

About visibility maps:  How crazy would it be to estimate the number
of not-all-visible pages instead?  It would be less work to count that
since it would only increase when the *first* tuple is inserted into a
page that is currently all visible (ie when the bit is cleared), not
for every tuple inserted into any page like your inserts_since_vacuum
counter.  Another difference is that inserts_since_vacuum is reset
even if vacuum finds that it *can't* set the all-visible bit for a
given page yet because of some concurrent transaction.  In that case
the bit is still not set but autovacuum has no reason to be triggered
again.

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
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] VACUUM's ancillary tasks

[Thomas Munro]

On Tue, Sep 27, 2016 at 2:33 AM, Tom Lane  wrote:
> Thomas Munro  writes:
>> I noticed that ATExecAlterColumnType does this:
>>  * Drop any pg_statistic entry for the column, since it's now wrong type
>
>> What if there is no rewrite, because the type is binary compatible
>> (ATColumnChangeRequiresRewrite returns false)?  Then I think your patch
>> won't attract an autovacuum daemon to ANALYZE the table and produce new
>> statistics, because it won't touch changes_since_analyze.  I wonder if we
>> should simply keep the stats if no rewrite is required.  Aren't the
>> statistical properties of binary-compatible types also compatible?
>
> Not necessarily: the type semantics might be different --- in fact,
> probably are different, else why would there be distinct types in the
> first place?  It would be unwise to keep the old stats IMO.
>
> If you need a concrete example, consider OID vs int4.  They're
> binary-compatible, but since int4 is signed while OID is unsigned,
> stats for one would be wrong for the other.  This is the same reason
> why ALTER COLUMN TYPE has to rebuild indexes even in binary-compatible
> cases.

Ah, right.  Then I think this patch should somehow bump
changes_since_analyze in the no-rewrite case if it's going to do it in
the rewrite case.  It would be surprising and weird if altering a
column's type *sometimes* resulted in new statistics being
automatically generated to replace those that were dropped, depending
on the technical detail of whether a rewrite was necessary.

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
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] VACUUM's ancillary tasks

[Tom Lane]

Thomas Munro  writes:
> I noticed that ATExecAlterColumnType does this:
>  * Drop any pg_statistic entry for the column, since it's now wrong type

> What if there is no rewrite, because the type is binary compatible
> (ATColumnChangeRequiresRewrite returns false)?  Then I think your patch
> won't attract an autovacuum daemon to ANALYZE the table and produce new
> statistics, because it won't touch changes_since_analyze.  I wonder if we
> should simply keep the stats if no rewrite is required.  Aren't the
> statistical properties of binary-compatible types also compatible?

Not necessarily: the type semantics might be different --- in fact,
probably are different, else why would there be distinct types in the
first place?  It would be unwise to keep the old stats IMO.

If you need a concrete example, consider OID vs int4.  They're
binary-compatible, but since int4 is signed while OID is unsigned,
stats for one would be wrong for the other.  This is the same reason
why ALTER COLUMN TYPE has to rebuild indexes even in binary-compatible
cases.

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

Re: [HACKERS] VACUUM's ancillary tasks

[Thomas Munro]

On Mon, Aug 29, 2016 at 1:26 PM, Vik Fearing  wrote:
> While doing this, I noticed that ALTER TABLE should also re-analyze the
> table for obvious reasons, so I have that one set
> "changes_since_analyze" to the number of rows rewritten as well.

I noticed that ATExecAlterColumnType does this:

/*

 * Drop any pg_statistic entry for the column, since it's now wrong
type
 */

RemoveStatistics(RelationGetRelid(rel), attnum);

What if there is no rewrite, because the type is binary compatible
(ATColumnChangeRequiresRewrite returns false)?  Then I think your patch
won't attract an autovacuum daemon to ANALYZE the table and produce new
statistics, because it won't touch changes_since_analyze.  I wonder if we
should simply keep the stats if no rewrite is required.  Aren't the
statistical properties of binary-compatible types also compatible?
Alternatively, we should consider bumping changes_since_analyze in this
case too, if you're going to do it in the rewrite case.

-- 
Thomas Munro
http://www.enterprisedb.com

[HACKERS] VACUUM's ancillary tasks

[Masahiko Sawada]

On Monday, 29 August 2016, Andres Freund > wrote:

> Hi,
>
> On 2016-08-29 03:26:06 +0200, Vik Fearing wrote:
> > The attached two patches scratch two itches I've been having for a
> > while.  I'm attaching them together because the second depends on the
> first.
> >
> > Both deal with the fact that [auto]vacuum has taken on more roles than
> > its original purpose.
> >
> >
> > Patch One: autovacuum insert-heavy tables
> >
> > If you have a table that mostly receives INSERTs, it will never get
> > vacuumed because there are no (or few) dead rows.  I have added an
> > "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
> > the same way as "changes_since_analyze" does.
> >
> > The reason such a table needs to be vacuumed is currently twofold: the
> > visibility map is not updated, slowing down index-only scans; and BRIN
> > indexes are not maintained, rendering them basically useless.
>
> It might be worthwhile to look at
> http://archives.postgresql.org/message-id/CAMkU%3D1zGu5Oshfz
> xKBqDmxxKcoDJu4pJux8UAo5h7k%2BGA_jS3Q%40mail.gmail.com
> there's definitely some overlap.
>
>
> > Patch Two: autovacuum after table rewrites
> >
> > This patch addresses the absurdity that a standard VACUUM is required
> > after a VACUUM FULL because the visibility map gets blown away.  This is
> > also the case for CLUSTER and some versions of ALTER TABLE that rewrite
> > the table.
>
> I think this should rather fixed by maintaining the VM during
> cluster.


>
+1


>
> IIRC there was an attempt late in the 9.5 cycle, but Bruce
>
(IIRC) ran out of steam. And nobody picked it up again ... :(
>
>
It may be worth to look at
https://www.postgresql.org/message-id/CAMkU%3D1w3osJJ2FneELhhNRLxfZitDgp9FPHee08NT2FQFmz_pQ%40mail.gmail.com

 I've updated this patch to apply to current HEAD, can propose it to pg10.

Regards,

--
Masahiko Sawada


-- 
Regards,

--
Masahiko Sawada

Re: [HACKERS] VACUUM's ancillary tasks

[Andres Freund]

Hi,

On 2016-08-29 03:26:06 +0200, Vik Fearing wrote:
> The attached two patches scratch two itches I've been having for a
> while.  I'm attaching them together because the second depends on the first.
> 
> Both deal with the fact that [auto]vacuum has taken on more roles than
> its original purpose.
> 
> 
> Patch One: autovacuum insert-heavy tables
> 
> If you have a table that mostly receives INSERTs, it will never get
> vacuumed because there are no (or few) dead rows.  I have added an
> "inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
> the same way as "changes_since_analyze" does.
> 
> The reason such a table needs to be vacuumed is currently twofold: the
> visibility map is not updated, slowing down index-only scans; and BRIN
> indexes are not maintained, rendering them basically useless.

It might be worthwhile to look at
http://archives.postgresql.org/message-id/CAMkU%3D1zGu5OshfzxKBqDmxxKcoDJu4pJux8UAo5h7k%2BGA_jS3Q%40mail.gmail.com
there's definitely some overlap.


> Patch Two: autovacuum after table rewrites
> 
> This patch addresses the absurdity that a standard VACUUM is required
> after a VACUUM FULL because the visibility map gets blown away.  This is
> also the case for CLUSTER and some versions of ALTER TABLE that rewrite
> the table.

I think this should rather fixed by maintaining the VM during
cluster. IIRC there was an attempt late in the 9.5 cycle, but Bruce
(IIRC) ran out of steam. And nobody picked it up again ... :(

Greetings,

Andres Freund


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

[HACKERS] VACUUM's ancillary tasks

[Vik Fearing]

The attached two patches scratch two itches I've been having for a
while.  I'm attaching them together because the second depends on the first.

Both deal with the fact that [auto]vacuum has taken on more roles than
its original purpose.


Patch One: autovacuum insert-heavy tables

If you have a table that mostly receives INSERTs, it will never get
vacuumed because there are no (or few) dead rows.  I have added an
"inserts_since_vacuum" field to PgStat_StatTabEntry which works exactly
the same way as "changes_since_analyze" does.

The reason such a table needs to be vacuumed is currently twofold: the
visibility map is not updated, slowing down index-only scans; and BRIN
indexes are not maintained, rendering them basically useless.


Patch Two: autovacuum after table rewrites

This patch addresses the absurdity that a standard VACUUM is required
after a VACUUM FULL because the visibility map gets blown away.  This is
also the case for CLUSTER and some versions of ALTER TABLE that rewrite
the table.

I thought about having those commands do the same work themselves, but
it seems better to have them simply trigger autovacuum than
quadruplicate the work.  I do this by having them fill in the
"inserts_since_vacuum" field added in Patch One with the number of rows
rewritten.  This assumes that autovacuum_vacuum_scale_factor is < 1.0
which hopefully is a safe assumption.

While doing this, I noticed that ALTER TABLE should also re-analyze the
table for obvious reasons, so I have that one set
"changes_since_analyze" to the number of rows rewritten as well.


I have not included any kind of test suite here because I don't really
have any ideas how to go about it in a sane way.  Suggestions welcome.

Attention reviewer: Please note that some of the documentation in the
first patch gets removed by the second patch, in case they both don't
get committed.


I have added this to the imminent commitfest.  These patches are rebased
as of 26fa446.
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


autovac_01_v01.patch
Description: invalid/octet-stream


autovac_02_v01.patch
Description: invalid/octet-stream

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