Re: db size and VACUUM ANALYZE - Mailing list 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.