Re: VACUUM not doing its job?

Kristian Eide wrote:
>>You should also search the archives for threads on free space maps. You
>>most likely need to increase yours. In particular, see:
>> http://archives.postgresql.org/pgsql-general/2002-07/msg00972.php
>
>
> Thanks, very helpful, although there does not seem to be much description of
> what the two free space map options in postgresql.conf actually do. Doing a
> VACUUM ANALYZE VERBOSE on my largest table gives me:
>
> NOTICE: Pages 43681: Changed 208, Empty 0; Tup 1921387: Vac 61669, Keep 0,
> UnUsed 1362341.
>
> I will try to increase the 'max_fsm_pages' option from 10000 to 500000 and
> see if that helps.

The default could indeed be a bit low for some installations. With
10,000 pages in the FSM and a page being 8 KB this is only 80 MB at most
(presuming pages are completely free). So whenever you update more than
80 MB between vacuums, you are sure to have a disk space leak, and even
if you update less but the pages are not completely empty you will leak
diskspace.

So the first thing I would do is start with running vacuum more often.
Since it is just a vaccum and not a vacuum full it does not lock the
tables anyway, and the key appears to be whether the amount of updates
between vacuums exceeds max_fsm_pages * blocksize * free space per page.

Also, maybe 500000 is a bit over the top. 500,000 pages of 8 KB each
make 4 GB, and even after 3 weeks your database was 'only' 2.4 GB (of
which half a GB were indexes that aren't touched by vacuum anyway).

How about starting with an hourly vacuum with a max_fsm_pages that you
calculate from the maximum amount of data you expect to be entered
within an hour (with some added safety for pages not being completely
emtpty etc.). Say you expect a maximum insert activity of 80 MB an hour,
that is 10,000 complete pages, with a contingency for pages being only
10% empty that means a max_fsm_pages of 100,000.
(Yet on the other hand, 100,000 can hold a theoretical 800 MB, so it
seems absurd high compared to the total size of your database when it
has just had a vacuum full.)

Also, since as you can see this is just some theoretical exercise (and I
am not a developer, just somebody who reads mailinglists and hopes to
someday convert some Oracle applications to PostgreSQL) and everybody is
desperate for numbers, maybe you can post back the results of whatever
you did in a few weeks?

Jochem