On Nov 14, 2007 2:26 PM, Tena Sakai <tsakai@gallo.ucsf.edu> wrote:
>
> Hi Scott,
>
> Many thanks for your suggestion. I have issued a
> bit heavier command prior to reading your mail.
>
> At psql prompt, I (as superuser) typed:
> # vacuum full verbose analyse;
OK, I had just wanted vacuum verbose.
With the full we get a slightly different output than what I was
looking for. Without the full, you'd get a report at the end telling
you how many fsm pages you'd need to track all your dead tuples.
But we can still use this.
>
> INFO: vacuuming "public.allele"
> INFO: "allele": found 2518282 removable, 1257262 nonremovable row versions
> in 31511 pages
OK, what this is telling you is that the allele table had 2518282 dead
rows, and 1257262 live ones. That's about 2 dead rows for every 1
live one, so that's a fair amount of bloat.
> Total free space (including removable row versions) is 161940948 bytes.
This tells us that vacuum full reclaimed 162Megs or so of free space.
> I would appreciate it if you could give me a bit of
> interpretation from psql.
It looks to me like your tables were bloated. After running vacuum
full your cron should run faster now.
HOWEVER, running vacuum full is more of a recovery procedure than a
normal maintenance operation. Normally what you do is run the
autovacuum daemon and let it vacuum your tables automagically when
needed. If the autovacuum daemon isn't running, then you can cron up
some vacuum analyze (no full) jobs to run every x minutes or hours and
that should keep the bloat at bay.
Try running your report again now and see how it does.
After you get pg_autovacuum running, run a vacuum verbose (just
verbose :) ) about a day after the system's been up and read the
bottom 10 or so lines from that (or post them here) to see how many
pages you need for fsm. For a db with as many rows as you have,
100,000 is a good minimum starting point. Numbers into the millions
aren't all that uncommon on larger servers.
