On 7/7/06, William Scott Jordan <wsjordan@brownpapertickets.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