Jacek Trzmiel <sc0rp@hot.pl> writes:
> I have a problem with growing toast table in pgsql 8.3.5. Probably
> raising max_fsm_pages and running autovacuum more aggresively will fix
> it, but during investigation I've got this puzzling output from VACUUM
> ANALYZE VERBOSE:
>> INFO: "pg_toast_16505": found 756385 removable, 23003704 nonremovable row versions in 28446864 pages
>> DETAIL: 14586 dead row versions cannot be removed yet.
>> There were 92404502 unused item pointers.
>> 22769942 pages contain useful free space.
>> 42 pages are entirely empty.
> So there are 23M rows in 28.5M pages. Assuming worst case of 1 row per
> page, there should be 5.5M completely free pages. But vacuum says that
> only 42 are entriely empty. Am I missing something obvious here?
In this context "entirely empty" suggests that the page not only doesn't
contain any rows, but never has contained any rows --- because it hasn't
got any unused item pointers either. This is an unusual situation,
which is why VACUUM troubles to point it out.
You've definitely got a bad bloat problem there, but the numbers don't
seem inherently inconsistent. Adding removable+nonremovable+unused
gives 116164591 which is just about 4 times the number of pages, so
it appears that the toast stuff is packing 4 rows per page as expected.
But an awful lot of them have been deleted over time without the space
getting re-used, which implies that the FSM is too small to remember
the space. Also, the fact that so many rows were removable this time
suggests it's been too long since the last vacuum. So your plan sounds
about right.
regards, tom lane
