Обсуждение: Simple improvements to freespace allocation

Current freesapce code gives a new block insert target (NBT) from
anywhere in table. That isn't very useful with bigger tables and it
would be useful to be able to specify different algorithms for
producing NBTs.

ALTER TABLE foo WITH (freespace = XXXX);

Three simple and useful models come to mind

* CONCURRENT
This is the standard/current model. Naming it likes this emphasises
why we pick NBTs in the way we do.

* PACK
We want the table to be smaller, so rather than run a VACUUM FULL we
want to force the table to choose an NBT at start of table, even at
the expense of concurrency. By avoiding putting new data at the top of
the table we allow the possibility that VACUUM will shrink table size.
This is same as current except we always reset the FSM pointer to zero
and re-seek from there. This takes some time to have an effect, but is
much less invasive than VACUUM FULL.

* RECENT
For large tables that are append-mostly use case it would be easier to
prefer NBTs from the last two 1GB segments of a table, allowing them
to be more easily cached. This is same as current except when we wrap
we don't go to block 0 we go to first block of penultimate (max - 1)
segment. For tables <= 2 segments this is no change from existing
algorithm. For larger tables it would focus updates/inserts into a
much reduced and yet still large area and allow better cacheing.

These are small patches.

...other possibilities, though more complex are...

* IN-MEMORY
A large table may only have some of its blocks in memory. It would be
useful to force a NBT to be a block already in shared_buffers IFF a
table is above a certain size (use same threshold as seq scans, i.e.
25% of shared_buffers). That may be difficult to achieve in practice,
so not sure about this one. Like it? Any ideas?

We might also allow a custom NBT policy though allowing user code at
that point could be dangerous.

Thoughts?

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

On 01/08/2014 08:56 AM, Simon Riggs wrote:
> Current freesapce code gives a new block insert target (NBT) from
> anywhere in table. That isn't very useful with bigger tables and it
> would be useful to be able to specify different algorithms for
> producing NBTs.

I've actually been surprised how little demand there has been for 
alternative algorithms. When I wrote the current FSM implementation, I 
expected people to start coming up with all kinds of wishes, but it 
didn't happen. There has been very few complaints, everyone seems to be 
satisfied with the way it works now. So I'm not convinced there's much 
need for this.

> ALTER TABLE foo WITH (freespace = XXXX);
>
> Three simple and useful models come to mind
>
> * CONCURRENT
> This is the standard/current model. Naming it likes this emphasises
> why we pick NBTs in the way we do.
>
> * PACK
> We want the table to be smaller, so rather than run a VACUUM FULL we
> want to force the table to choose an NBT at start of table, even at
> the expense of concurrency. By avoiding putting new data at the top of
> the table we allow the possibility that VACUUM will shrink table size.
> This is same as current except we always reset the FSM pointer to zero
> and re-seek from there. This takes some time to have an effect, but is
> much less invasive than VACUUM FULL.

We already reset the FSM pointer to zero on vacuum. Would the above 
actually make any difference in practice?

> * RECENT
> For large tables that are append-mostly use case it would be easier to
> prefer NBTs from the last two 1GB segments of a table, allowing them
> to be more easily cached. This is same as current except when we wrap
> we don't go to block 0 we go to first block of penultimate (max - 1)
> segment. For tables <= 2 segments this is no change from existing
> algorithm. For larger tables it would focus updates/inserts into a
> much reduced and yet still large area and allow better cacheing.

Umm, wouldn't that bloat the table with no limit? Putting my 
DBA/developer hat on, I don't understand when I would want to use that 
setting.

> These are small patches.
>
> ...other possibilities, though more complex are...
>
> * IN-MEMORY
> A large table may only have some of its blocks in memory. It would be
> useful to force a NBT to be a block already in shared_buffers IFF a
> table is above a certain size (use same threshold as seq scans, i.e.
> 25% of shared_buffers). That may be difficult to achieve in practice,
> so not sure about this one. Like it? Any ideas?

Yeah, that seems nice, although I have feeling that it's not worth the 
complexity.

There's one policy that I'd like to see: maintaining cluster order. When 
inserting a new tuple, try to place it close to other tuples with 
similar keys, to keep the table clustered.

In practice, CLUSTER CONCURRENTLY might be more useful, though.

> We might also allow a custom NBT policy though allowing user code at
> that point could be dangerous.

Yeah, I don't think there's much need for that. Overall,

- Heikki

On 8 January 2014 07:43, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
> On 01/08/2014 08:56 AM, Simon Riggs wrote:
>>
>> Current freesapce code gives a new block insert target (NBT) from
>> anywhere in table. That isn't very useful with bigger tables and it
>> would be useful to be able to specify different algorithms for
>> producing NBTs.
>
>
> I've actually been surprised how little demand there has been for
> alternative algorithms. When I wrote the current FSM implementation, I
> expected people to start coming up with all kinds of wishes, but it didn't
> happen. There has been very few complaints, everyone seems to be satisfied
> with the way it works now. So I'm not convinced there's much need for this.

That would require someone to conduct detailed analysis on problems,
which few people are capable of doing at a level that we would accept.
No doubt I will soon be challenged to prove beyond doubt that anything
here is required, which becomes chicken and egg. For the vast majority
of cases, the general approach we have works well enough - this area
has had lots of very useful attention ove the years.

The problem is tables have multiple use cases and we support only one,
with no easy way for people to experiment with alternatives in
production.

Its been on my list for years... but its not been a top priority, for sure.


>> ALTER TABLE foo WITH (freespace = XXXX);
>>
>> Three simple and useful models come to mind
>>
>> * CONCURRENT
>> This is the standard/current model. Naming it likes this emphasises
>> why we pick NBTs in the way we do.
>>
>> * PACK
>> We want the table to be smaller, so rather than run a VACUUM FULL we
>> want to force the table to choose an NBT at start of table, even at
>> the expense of concurrency. By avoiding putting new data at the top of
>> the table we allow the possibility that VACUUM will shrink table size.
>> This is same as current except we always reset the FSM pointer to zero
>> and re-seek from there. This takes some time to have an effect, but is
>> much less invasive than VACUUM FULL.
>
>
> We already reset the FSM pointer to zero on vacuum. Would the above actually
> make any difference in practice?

The Pack algo would emphasise tight packing over assigning concurrent
blocks. It would be useful if that also included not doing HOT updates
in favour of migrating rows to an earlier block in the table. Emphasis
on avoiding VACUUM FULL in certain cases, not for general use.


>> * RECENT
>> For large tables that are append-mostly use case it would be easier to
>> prefer NBTs from the last two 1GB segments of a table, allowing them
>> to be more easily cached. This is same as current except when we wrap
>> we don't go to block 0 we go to first block of penultimate (max - 1)
>> segment. For tables <= 2 segments this is no change from existing
>> algorithm. For larger tables it would focus updates/inserts into a
>> much reduced and yet still large area and allow better cacheing.
>
>
> Umm, wouldn't that bloat the table with no limit? Putting my DBA/developer
> hat on, I don't understand when I would want to use that setting.

That would depend on the use case; no algo suggested works for all or
even general cases.

If you have a large table, allocating freespace in blocks unlikely to
be accessed by queries means we introduce additional cache pressure.
If we allocate NBTs from newer blocks we will more likely find them in
cache.

Allowing older data to become write-seldom allows us to consider
things like compressing particular segments or moving them onto
cheaper storage.


(Another suggestion might be to use the VM so that we tend to add data
to already dirty blocks.)

> There's one policy that I'd like to see: maintaining cluster order. When
> inserting a new tuple, try to place it close to other tuples with similar
> keys, to keep the table clustered.

Agreed. Do you have a particular algorithm in mind? I can think of a few.

> In practice, CLUSTER CONCURRENTLY might be more useful, though.

I think we want both wholesale and retail. Certainly in the absence of
the former, the latter seems good addition.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> On 01/08/2014 08:56 AM, Simon Riggs wrote:
>> * IN-MEMORY
>> A large table may only have some of its blocks in memory. It would be
>> useful to force a NBT to be a block already in shared_buffers IFF a
>> table is above a certain size (use same threshold as seq scans, i.e.
>> 25% of shared_buffers). That may be difficult to achieve in practice,
>> so not sure about this one. Like it? Any ideas?

> Yeah, that seems nice, although I have feeling that it's not worth the 
> complexity.

Not only would that be rather expensive to do, but I think it would be
self-defeating.  Pages that are in memory would be particularly likely
to have been modified by someone else recently, so that the FSM's info
about their available space is stale, and thus once you actually got
to the page it'd be more likely to not have the space you need.

The existing FSM algorithm is intentionally designed to hand out pages
that nobody else has tried to insert into lately, with one goal being to
minimize the number of retries needed because of stale info.  (Or at
least, it worked that way originally, and I don't think Heikki's rewrite
changed that aspect.)  I'm concerned that the alternatives Simon proposes
would lead to more processes ganging up on the same pages, with not only a
direct cost in concurrency but an indirect cost in repeated FSM searches
due to believing stale available-space data.  Indeed, a naive
implementation could easily get into infinite loops of handing back the
same page.

I realize that the point is exactly to sacrifice some insertion
performance in hopes of getting better table packing, but it's not clear
to me that there's an easy fix that makes packing better without a very
large hit on the other side.

Anyway, these fears could be proven or disproven with some benchmarking of
a trial patch, and I have no objection if Simon wants to do that
experimentation.  But I'd be hesitant to see such a feature committed
in advance of experimental proof that it's actually useful.
        regards, tom lane

On 1/8/14, 1:43 AM, Heikki Linnakangas wrote:

I've wanted the cluster case for a long time. I also see the use for the RECENT scenario, especially if we had CLUSTER
CONCURRENTthat let you shrink the head of the table as needed.
 

I suspect the in-memory case would only be useful if it could look into the OS cache as well, at least until we can
recommendyou give Postgres 90% of memory instead of 25%. Even then, I'm not sure how useful it would ultimately be...
 

>> * PACK
>> We want the table to be smaller, so rather than run a VACUUM FULL we
>> want to force the table to choose an NBT at start of table, even at
>> the expense of concurrency. By avoiding putting new data at the top of
>> the table we allow the possibility that VACUUM will shrink table size.
>> This is same as current except we always reset the FSM pointer to zero
>> and re-seek from there. This takes some time to have an effect, but is
>> much less invasive than VACUUM FULL.
>
> We already reset the FSM pointer to zero on vacuum. Would the above actually make any difference in practice?

What if your first request is for a large chunk of free space? You could skip a lot of blocks, even if the FSM is
bucketized.

But there's probably a more important point to this one: for you to have any chance of packing you MUST get everything
outof the tail of the table. Resetting to zero on every request is one possible way to do that, though it might be
betterto do something like reset only once the pointer goes past block X. The other thing you'd want is a way to force
tuplesoff the last X pages. Due to a lack of ctid operators that was already hard, and HOT makes that even harder (BTW,
relatedto this you'd ideally want HOT to continue to operate on the front of the table, but not the back.)
 

All that said, I've definitely wanted the ability to shrink tables in the past, though TBH I've wanted that more for
indexes.

Ultimately, what I really want on this front is:

PACK TABLE blah BACKGROUND;
CLUSTER TABLE blah BACKGROUND;
REINDEX INDEX blah BACKGROUND;

where BACKGROUND would respect a throttle setting. (While I'm dreaming, it'd be nice to have DATABASE/TABLESPACE/SCHEMA
alternatespecifications too...)
 
-- 
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net

On 9 January 2014 01:38, Jim Nasby <jim@nasby.net> wrote:
> On 1/8/14, 1:43 AM, Heikki Linnakangas wrote:
>
> I've wanted the cluster case for a long time. I also see the use for the
> RECENT scenario, especially if we had CLUSTER CONCURRENT that let you shrink
> the head of the table as needed.
>

> But there's probably a more important point to this one: for you to have any
> chance of packing you MUST get everything out of the tail of the table.
> Resetting to zero on every request is one possible way to do that, though it
> might be better to do something like reset only once the pointer goes past
> block X. The other thing you'd want is a way to force tuples off the last X
> pages. Due to a lack of ctid operators that was already hard, and HOT makes
> that even harder

Agreed

> (BTW, related to this you'd ideally want HOT to continue to
> operate on the front of the table, but not the back.)

That's a good idea.

> All that said, I've definitely wanted the ability to shrink tables in the
> past, though TBH I've wanted that more for indexes.
>
> Ultimately, what I really want on this front is:
>
> PACK TABLE blah BACKGROUND;
> CLUSTER TABLE blah BACKGROUND;
> REINDEX INDEX blah BACKGROUND;
>
> where BACKGROUND would respect a throttle setting. (While I'm dreaming, it'd
> be nice to have DATABASE/TABLESPACE/SCHEMA alternate specifications too...)

I like the idea of declarative deprioritisation.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services