On Nov 14, 2007 2:26 PM, Tena Sakai <tsakai@xxxxxxxxxxxxxx> 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. ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate