Thread: Vacuum full considered useful ;)
Hi,
Something I'd like to share.
I switched to postgres about 4 months ago.
The perfomance after a while got worse.
I posted a message here, where the result was that my IO was the
problem.
I run vacuum every night. I never used vacuum full because it is not
explicitly recommended and I read somewhere in the archives a mail that
the consistency of the db suffered after a vacuum full run.
Yesterday I switched from 8.1 to 8.2. So I needed to dump the dbase
and reimport it. The dbase after 4 months of running without "vacuum
full"
reached 60 gigabyte of diskspace. Now after a fresh import it only
has 5 gigabyte!
No wonder, I got IO problems with such a fragmentation.
For people not very familiar with postgres especially those coming
from mysql,
i'd recommend paying attention to this.
regards,
patric de waha
> No wonder, I got IO problems with such a fragmentation.
>
> For people not very familiar with postgres especially those coming
> from mysql,
> i'd recommend paying attention to this.
Definitely. The problem here is that you just aren't vacuuming enough,
not that you didn't vacuum full. I would suggest reviewing autovacuum
and seeing if that will help you.
Joshua D. Drake
>
> regards,
> patric de waha
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Patric de Waha <lists@p-dw.com> writes:
> Yesterday I switched from 8.1 to 8.2. So I needed to dump the dbase
> and reimport it. The dbase after 4 months of running without "vacuum
> full"
> reached 60 gigabyte of diskspace. Now after a fresh import it only
> has 5 gigabyte!
> No wonder, I got IO problems with such a fragmentation.
Indeed, but routine VACUUM FULL is not the best answer. What this
suggests is that you don't have the FSM size (max_fsm_pages and possibly
max_fsm_relations) set high enough for your DB size. If it isn't
big enough then you'll "leak" reusable space over time. Also, if
you are using manual rather than autovacuum you might need to be
vacuuming more often.
regards, tom lane
Joshua D. Drake a écrit : > >> No wonder, I got IO problems with such a fragmentation. >> >> For people not very familiar with postgres especially those coming >> from mysql, >> i'd recommend paying attention to this. > > Definitely. The problem here is that you just aren't vacuuming enough, > not that you didn't vacuum full. I would suggest reviewing autovacuum > and seeing if that will help you. > And paying attention to the max_fsm_pages setting. A value too low won't help vacuum's work. Regards. -- Guillaume. http://www.postgresqlfr.org http://docs.postgresqlfr.org
On Jul 14, 2007, at 11:50 AM, Patric de Waha wrote: > Yesterday I switched from 8.1 to 8.2. So I needed to dump the > dbase > and reimport it. The dbase after 4 months of running without > "vacuum full" > reached 60 gigabyte of diskspace. Now after a fresh import it > only has 5 gigabyte! After a couple more months running 8.2, compare your index sizes to what they are now relative to the table sizes. My bet is that if you just reindexed some of your tables that would have cleared out much of that bloat. A short while back I reindexed some tables on my primary production server and shaved off about 20Gb of disk space. The table itself was not bloated. A dump/reload to another server resulted in a table of roughly the same size.