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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> 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/


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match