max_fsm_pages

If you increase max_fsm_pages to a large enough number then the next
vacuum analyze will make all the pages with free space available to be
reused. A normal VACUUM does not actually reclaim space (unless it's at
the end of the table I think), it only marks the space as reuseable.
VACUUM FULL will reclaim space immediately.

Matthew

wespvp@syntegra.com wrote:

On Thu, 2004-07-01 at 11:45, wespvp@syntegra.com wrote:

If max_fsm_pages is too small and I have space not reclaimed by vacuum, if I
increase max_fsm_pages and restart postmaster, will the next VACUUM ANALYZE
relcaim all overlooked pages or must I do a VACUUM FULL?

Let's say you have a table with 1,000 rows, but you've deleted 1,000,000
over the past year, and most of those are unclaimed. Regular vacuum
will put those 900,000 odd pages into the FSM, and the database can use
them. However, scans on this table will still be mostly reading empty
space.

So, to collapse the table back down to something reasonable, you'll need
to do a vacuum full, then regular vacuums should keep things tight from
then on.

On 7/1/04 4:00 PM, "Scott Marlowe" <smarlowe@qwest.net> wrote:

Let's say you have a table with 1,000 rows, but you've deleted 1,000,000
over the past year, and most of those are unclaimed. Regular vacuum
will put those 900,000 odd pages into the FSM, and the database can use
them. However, scans on this table will still be mostly reading empty
space.

So, to collapse the table back down to something reasonable, you'll need
to do a vacuum full, then regular vacuums should keep things tight from
then on.

Right. But let's say max_fsm_pages is set to the default (20,000) and that
is enough for 100,000 rows and I do a VACUUM ANALYZE. If I understand it
right, only 100,000 will be marked as available for reuse (I probably should
not have used the word 'reclaim' in the original email). If I run VACUUM
ANALYZE again, will there still be only 100,000 available for reuse, or will
an additional 100,000 be made available for a total of 200,000?

If I bump up max_fsm_pages to 1,000,000 and run VACUUM ANALYZE, then would
all 900,000 rows then be available for reused? Based on what Chris said,
the answer to the second question is 'yes' - On any given VACUUM ANALYZE it
will be able to free up to the current max_fsm_pages worth of rows. I'm not
sure about the first question, though. I'm guessing that 800,000 would
remain unavailable regardless of how many times you ran VACUUM ANALYZE -
that max_fsm_pages is the total amount of free space it will track at one
time?

Wes

<wespvp@syntegra.com> writes:

If I bump up max_fsm_pages to 1,000,000 and run VACUUM ANALYZE, then would
all 900,000 rows then be available for reused?

VACUUM will fill whatever FSM space is available. You do have to
restart the postmaster to get the increased FSM size to be allocated,
but you don't have to do any magic pushups beyond that.

regards, tom lane

"TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> VACUUM will fill whatever FSM space is available. You do have to
TL> restart the postmaster to get the increased FSM size to be allocated,
TL> but you don't have to do any magic pushups beyond that.

Do you need a full restart or will a simple reload configuration
suffice? Downtime is hard for me ;-)

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Vivek Khera <khera@kcilink.com> writes:

"TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:
TL> VACUUM will fill whatever FSM space is available. You do have to
TL> restart the postmaster to get the increased FSM size to be allocated,
TL> but you don't have to do any magic pushups beyond that.

Do you need a full restart or will a simple reload configuration
suffice? Downtime is hard for me ;-)

I said "restart the postmaster", and "restart the postmaster" is
what I meant...

regards, tom lane