pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum full considered useful ;)
5+ messages / 5 participants
[nested] [flat]
* Vacuum full considered useful ;)
@ 2007-07-14 15:50 Patric de Waha <lists@p-dw.com>
0 siblings, 3 replies; 5+ messages in thread
From: Patric de Waha @ 2007-07-14 15:50 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full considered useful ;)
@ 2007-07-14 16:25 Joshua D. Drake <jd@commandprompt.com>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 1 reply; 5+ messages in thread
From: Joshua D. Drake @ 2007-07-14 16:25 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
> 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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full considered useful ;)
@ 2007-07-14 16:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-07-14 16:32 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full considered useful ;)
@ 2007-07-14 16:34 Guillaume Lelarge <guillaume@lelarge.info>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 0 replies; 5+ messages in thread
From: Guillaume Lelarge @ 2007-07-14 16:34 UTC (permalink / raw)
To: Patric de Waha <lists@p-dw.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum full considered useful ;)
@ 2007-07-15 02:26 Vivek Khera <vivek@khera.org>
parent: Patric de Waha <lists@p-dw.com>
2 siblings, 0 replies; 5+ messages in thread
From: Vivek Khera @ 2007-07-15 02:26 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-15 02:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-14 15:50 Vacuum full considered useful ;) Patric de Waha <lists@p-dw.com>
2007-07-14 16:25 ` Joshua D. Drake <jd@commandprompt.com>
2007-07-14 16:34 ` Guillaume Lelarge <guillaume@lelarge.info>
2007-07-14 16:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-15 02:26 ` Vivek Khera <vivek@khera.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox