Re: Vacuum verbose output?

>>>>> "TL" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

TL> 100 is definitely too small for max_fsm_relations; we've bumped that
TL> default to 1000 for the next release. Given that you've got a
TL> nearly-100k-page table, 10000 for max_fsm_pages is probably much too
TL> small also.

>> What are the implications/effects of increasing them?

TL> Shared memory usage gets bigger. I think the max_fsm_pages multiplier
TL> is 6 bytes per slot, so you could make it a million, or ten, without
TL> stressing modern machines. I'd try 1000/1000000 for starters.

Just a followup to this tip, Tom...

I implemented this change last week (set max_fsm_relations = 500000),
during my switchover from RAID5 to RAID10. After my dump/reload and
vacuum analyze, things settled down to a good clip. And my nightly
vacuum analyze runs have held to a pretty constant time. Usually
after a full vacuum or reload the nightly vacuums start taking longer
after about 4 or 5 days. (I also vacuum some tables hourly, every few
hours, etc. depending on activity).

So basically, this one optimization has significantly improved my disk
usage by not making so many empty pages, and keeps performance good by
not having sparse tables.

My largest table has 115M rows in it (two integers and a char(4)).

Thanks for the tip!

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/