Re: VACUUM vs. REINDEX

On 7/7/06, William Scott Jordan <wsjordan(at)brownpapertickets(dot)com> wrote:
>
> Hi Jeff,
>
> Ah, okay. I see what information you were looking for. Doing a
> VACUUM on the full DB, we get the following results:
>
> ----------------------------
> INFO: free space map: 885 relations, 8315 pages stored; 177632 total
> pages needed
> DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB
> shared memory.
> ----------------------------
>
> -William

William,

You need to increase your fsm settings. The database is telling you it is
trying to store 177K+ pages, but you have only provided it with 20K. Since
these pages are cheap, I would set your fsm up with at least the following.

max_fsm_pages 500000
max_fsm_relations 5000

This should provide PostgreSQL with enough space to work. You still might
need to run one more vacuum full once you change the setting so that you can
recover the space that was lost due to your fsm begin to small. Keep an eye
on these last couple of lines from vacuum and adjust your setting
accordingly. It may take a couple of tries to get PostgreSQL happy. Once
your fsm is large enough, you should be able to dispense with the vacuum
fulls and reindexes and just do normal vacuuming.

Also in regards to the vacuum vs reindex. Reindexing is great and gives you
nice clean "virgin" indexes, however, if you do not run an analyze (or
vacuum analyze), the database will not have statistics for the new indexes.
This will cause the planner to make bad choices.

What I used to do before upgrading to 8.1 was run a vacuum full, reindexdb,
vacuum analyze every weekend (we were on 7.3.4). This gave me pristine
indexes and tables for Monday's start of the week.

If you can, look hard at upgrading to 8.1.x as it will fix a lot of the
issues you are having with autovacuum (along with a ton of other
improvements).

HTH,

Chris