Thread: max_fsm_pages
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? Wes
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: > 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? > > Wes > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match >
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