Обсуждение: Re: Free space mapping (was Re: Multi-Versions and Vacuum)
(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 wellI have some free time at the moment...:-) best wishes Mark
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
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
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)
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
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
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.
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
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
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
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
>
>
>