Skip site navigation (1) Skip section navigation (2)

Re: db size and VACUUM ANALYZE

From: Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>
To: Marcin Krol <mrkafk(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: db size and VACUUM ANALYZE
Date: 2010-02-12 18:46:04
Message-ID: 1266000364.4372.41.camel@bnicholson-desktop (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, 2010-02-12 at 18:41 +0100, Marcin Krol wrote:
> Brad Nicholson wrote:
> 
> First of all, I don't really care about 1G of disk space, the main 
> problem was why the performance degraded so much?

Because you data is now spread across a whole lot more blocks on disk.
It takes more work to find the data than it used to.

> > Are you running autovacuum?  
> 
> Apparently no. I have turned it on in conf and restarted pg, I'll see 
> how that works.
> 
> It should take care of this for you.  You
> > may need to make it more aggressive than the default though.
> 
> Hmm what do you mean by more aggressive? I haven't seen anything in the 
> parameters that would suggest whether it is more likely or less likely 
> to recover dead tuples:

I would start by turning autovacuum on and running it with the defaults.
This will most likely make this problem go away.  If you find that you
still have problems, try lowering autovacuum_vacuum_scale_factor a bit.

>                                          # actions running at least that 
> time.
> #autovacuum_max_workers = 3             # max number of autovacuum 
> subprocesses
> #autovacuum_naptime = 1min              # time between autovacuum runs
> #autovacuum_vacuum_threshold = 50       # min number of row updates before
>                                          # vacuum
> #autovacuum_analyze_threshold = 50      # min number of row updates before
>                                          # analyze
> #autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before 
> vacuum
> #autovacuum_analyze_scale_factor = 0.1  # fraction of table size before 
> analyze
> #autovacuum_freeze_max_age = 200000000  # maximum XID age before forced 
> vacuum
>                                          # (change requires restart)
> #autovacuum_vacuum_cost_delay = 20      # default vacuum cost delay for
>                                          # autovacuum, -1 means use
>                                          # vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
>                                          # autovacuum, -1 means use
>                                          # vacuum_cost_limit
> 
> 
> I don't see anything in here that would suggest equivalent of VACUUM FULL.

Autovaccum does not run VACUUM FULL, it runs a regular VACUUM.

I recommend reading this page in the documentation:

http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html


-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.



In response to

Responses

pgsql-novice by date

Next:From: Brad NicholsonDate: 2010-02-12 19:28:50
Subject: Re: db size and VACUUM ANALYZE
Previous:From: Marcin KrolDate: 2010-02-12 17:41:50
Subject: Re: db size and VACUUM ANALYZE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group