Обсуждение: Re: Unbounded (Possibly) Database Size Increase - Toasting

<markir@slingshot.co.nz> writes:
> The toast table gets about 90 percent of the growth, followed by the toast 
> index at about 9 percent. The actual table + primary key stay at about 2M each.

Odd.  I wonder whether you are looking at an unintended behavior of the
free space map's thresholding mechanism.  The toast table will generally
have large tuples of consistent size (about 2K each).  This will cause
the FSM threshold for whether to remember a page to approach 2K, which
probably will mean that we forget about pages that could still hold one
toast tuple.  That might be enough to cause the growth.  It may be
worth playing around with the details of the threshold-setting policy.

In particular, I'd suggest altering the code in GetPageWithFreeSpace
and RecordAndGetPageWithFreeSpace (in
src/backend/storage/freespace/freespace.c) to make the threshold
converge towards something less than the average request size, perhaps
average/2, which you could do with

-        cur_avg += ((int) spaceNeeded - cur_avg) / 32;
+        cur_avg += (((int) spaceNeeded)/2 - cur_avg) / 32;

Possibly the initial threshold set in create_fsm_rel also needs to be
smaller than it is.  Not sure about that though.

Let me know how that affects your results ...
        regards, tom lane

On Tue, 21 May 2002 11:10:04 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Odd.  I wonder whether you are looking at an unintended behavior of the
>free space map's thresholding mechanism.  The toast table will generally
>have large tuples of consistent size (about 2K each).

So we have 4 tuples per page?

>This will cause
>the FSM threshold for whether to remember a page to approach 2K, which
>probably will mean that we forget about pages that could still hold one
>toast tuple.

I thought I was able to follow you up to here.

>That might be enough to cause the growth.

Here I'm lost.  The effect you mention explains growth up to a state
where each toast table page holds 3 instead of 4 tuples (1.33 *
initial size).  Now with each UPDATE we get pages with significantly
more free space than 2K.  Even if we add a few 1.000 pages being added
before the next VACUUM, we still reach a stable size.  Of course this
only holds if there are enough FSM slots, which Mark claims to have.

So IMHO there have to be additional reasons causing *unbounded*
growth.  Or am I missing something?

Just my 0.02.
ServusManfred

Manfred Koizar <mkoi-pg@aon.at> writes:
> Here I'm lost.  The effect you mention explains growth up to a state
> where each toast table page holds 3 instead of 4 tuples (1.33 *
> initial size).  Now with each UPDATE we get pages with significantly
> more free space than 2K.

Good point, it should still stabilize with at worst 33% overhead.  So
maybe I'm barking up the wrong tree.

Still, the FSM code is new in 7.2 and I'm quite prepared to believe that
the effect Mark is seeing indicates some problem in it.  Anyone care to
sit down and read through freespace.c?  It's pretty liberally commented.
        regards, tom lane