Horton
Menu

Thread

  1. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Mark Kirkwood <markir@slingshot.co.nz> — 2002-08-27T22:39:28Z

    (mass snippage)
>The ever-growing tables
> that people are experiencing is somehow related to the new
> non-blocking vacuum and the value of the free space map settings...
>But if I understand now what you're saying, it's this: the vacuum
> _does_ get everything....

There is also a complicating effect when rows have toasted storage, which can prevent the space being freed (I think),
see hackers thread entitled something like "unbounded database growth".

I have set myself the task of examining this - but wish I had picked an easier 1st project :-(
- I am having to learn about vacuum, toasting ,freespace, and generally get to grips with the Pg code as well - just as well I have some free time at the moment...:-) 

best wishes

Mark

  2. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-27T22:45:05Z

    Mark Kirkwood <markir@slingshot.co.nz> writes:
> There is also a complicating effect when rows have toasted storage, which can prevent the space being freed (I think),
> see hackers thread entitled something like "unbounded database growth".

> I have set myself the task of examining this -

It doesn't need a lot of examination in my mind: the cause is surely
growth of the index on the toast table.  Since that index's first column
is the OID assigned to the toast item, the range of indexed values tends
to shift over time, causing the leftmost parts of the index btree to
become sparsely populated and eventually empty.  Since we don't
currently have code to collapse out empty pages in a btree, the index
grows during continued update activity, even if the total amount of data
you're storing isn't growing.  This is quite unrelated to the free space
map --- indexes don't use the FSM.

REINDEXing the toast index is the only available workaround at the
moment.

Eventually we need code to collapse out free space in indexes.

			regards, tom lane

  3. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Mark Kirkwood <markir@slingshot.co.nz> — 2002-08-27T23:10:44Z

    Tom Lane wrote:

>It doesn't need a lot of examination in my mind: the cause is surely
>growth of the index on the toast table.  Since that index's first column
>is the OID assigned to the toast item, the range of indexed values tends
>to shift over time, causing the leftmost parts of the index btree to
>become sparsely populated and eventually empty.  Since we don't
>currently have code to collapse out empty pages in a btree, the index
>grows during continued update activity, even if the total amount of data
>you're storing isn't growing.  This is quite unrelated to the free space
>map --- indexes don't use the FSM.
>
>  
>
hmmm, good point - I thought the toast table was growing too, but I will 
check this.

regards

Mark

  4. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Alvaro Herrera <alvherre@atentus.com> — 2002-08-27T23:19:46Z

    En Tue, 27 Aug 2002 18:45:05 -0400
Tom Lane <tgl@sss.pgh.pa.us> escribió:

> Eventually we need code to collapse out free space in indexes.

Maybe when some lower limit is reached in two consecutive pages they
should be merged?

Maybe this can be one of my projects when I start really working on
Postgres.  I have yet to pick the items that I'll do. I don't know how
difficult it is though.

-- 
Alvaro Herrera (<alvherre[a]atentus.com>)
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiánse", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)

  5. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-28T04:01:43Z

    Alvaro Herrera <alvherre@atentus.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> escribi:
>> Eventually we need code to collapse out free space in indexes.

> Maybe when some lower limit is reached in two consecutive pages they
> should be merged?

Yup.  The tricky part is to do this without causing problems for
concurrent readers of the index.

The Lehman-Yao paper that is the basis of our existing code explains how
readers can operate concurrently with splits of index pages.  It doesn't
address doing page merges concurrently with reader scans.  There are
newer papers in the literature that explain how to do that ... but no
one's gotten round to trying to implement it for Postgres.

			regards, tom lane

  6. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Mark Kirkwood <markir@slingshot.co.nz> — 2002-08-29T09:26:56Z

    Tom Lane wrote:

>It doesn't need a lot of examination in my mind: the cause is surely
>growth of the index on the toast table.
>  
>
You are indeed correct !

A quick check of my test case shows that the toast table growth tails 
off ( provided max_fsm_pages is suitably set), and the unbounded growth 
comes entirely from the toast index.
 
Applying REINDEX brings the space usage down to a level that compares 
with a non-toasted example.

With respect to the REINDEX workaround -  having to re-start your server 
single process for it is a bit fatal if you are  24/7 shop - I think 
altering those high hit tables to have attributes detoasted might be 
better (row length permitting).

Anyway that has *toasted* the need for my currrent little investigation 
(quite enjoyed my romp through the code, so I am not too worried).  I 
still have some free time (I am off work after knee operation) so I'm 
now looking for another project....

regards

Mark

  7. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Martijn van Oosterhout <kleptog@svana.org> — 2002-08-29T10:46:56Z

    Eh? Stopping server to do a REINDEX? I use REINDEX all the time and you sure
as hell don't need to stop the server. Unless it's for a system table that
is.

(BTW, 7.0 certainly seems to have had some dodgy index code. 7.2 seems much
better. Fortunatly it only seems to hit multicolumn indexes.)

On Thu, Aug 29, 2002 at 09:26:56PM +1200, Mark Kirkwood wrote:
> Tom Lane wrote:
> 
> >It doesn't need a lot of examination in my mind: the cause is surely
> >growth of the index on the toast table.
> >  
> >
> You are indeed correct !
> 
> A quick check of my test case shows that the toast table growth tails 
> off ( provided max_fsm_pages is suitably set), and the unbounded growth 
> comes entirely from the toast index.
>  
> Applying REINDEX brings the space usage down to a level that compares 
> with a non-toasted example.
> 
> With respect to the REINDEX workaround -  having to re-start your server 
> single process for it is a bit fatal if you are  24/7 shop - I think 
> altering those high hit tables to have attributes detoasted might be 
> better (row length permitting).
> 
> Anyway that has *toasted* the need for my currrent little investigation 
> (quite enjoyed my romp through the code, so I am not too worried).  I 
> still have some free time (I am off work after knee operation) so I'm 
> now looking for another project....
> 
> regards
> 
> Mark
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

  8. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Mark Kirkwood <markir@slingshot.co.nz> — 2002-08-29T12:05:47Z

    Martijn van Oosterhout wrote:
> Eh? Stopping server to do a REINDEX? I use REINDEX all the time and you sure
> as hell don't need to stop the server. Unless it's for a system table that
> is.
> 
The toast index 'pg_toast_xxxxxxx_idx' counts as a system table - not 
immediately obvious I grant you....

cheers

Mark

  9. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-29T13:29:38Z

    Mark Kirkwood <markir@slingshot.co.nz> writes:
> With respect to the REINDEX workaround -  having to re-start your server 
> single process for it is a bit fatal if you are  24/7 shop

You don't have to do that for a TOAST table, do you?  It should be okay
to do the reindex in multiuser mode.

(Now that I think about it, the code might think that a toast table is a
system table, in which case we ought to loosen the check a little.)

			regards, tom lane

  10. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-29T15:57:53Z

    I said:
> Mark Kirkwood <markir@slingshot.co.nz> writes:
>> With respect to the REINDEX workaround -  having to re-start your server 
>> single process for it is a bit fatal if you are  24/7 shop

> You don't have to do that for a TOAST table, do you?  It should be okay
> to do the reindex in multiuser mode.

> (Now that I think about it, the code might think that a toast table is a
> system table, in which case we ought to loosen the check a little.)

REINDEX did think that.  CVS tip doesn't think it anymore ;-)

			regards, tom lane

  11. Re: Free space mapping (was Re: Multi-Versions and Vacuum)

    Barry Lind <barry@xythos.com> — 2002-08-30T06:23:55Z

    I have run into this problem before.  Both the toast index and toast
table are considered internal objects because they begin with pg_, and
thus you can't run reindex on them directly without running a single
process server.  The workaround it to run reindex on the table that owns
the toast index/table, this will cause the toast index to also be rebuilt.

thanks,
--Barry

Tom Lane wrote:

 >Mark Kirkwood <markir@slingshot.co.nz> writes:
 >
 >
 >>With respect to the REINDEX workaround -  having to re-start your server
 >>single process for it is a bit fatal if you are  24/7 shop
 >>
 >>
 >
 >You don't have to do that for a TOAST table, do you?  It should be okay
 >to do the reindex in multiuser mode.
 >
 >(Now that I think about it, the code might think that a toast table is a
 >system table, in which case we ought to loosen the check a little.)
 >
 > 
		regards, tom lane
 >
 >---------------------------(end of broadcast)---------------------------
 >TIP 6: Have you searched our list archives?
 >
 >http://archives.postgresql.org
 >
 >
 >