Postgres Pro
Downloads
Home   >   mailing lists

Thread: Autovac vs manual with analyze

Autovac vs manual with analyze

From
Scott Whitney
Date:
p { margin: 0; }
p { margin: 0; }
I've got about 44GB of data in a few hundred production databases. I'm using PG 8.1.4, but upgrading today (even to the latest 8.1) is not an option. I know, I know. I wish it were, and it's slated here for q2, but I cannot even apply maintenance patches without a full testing cycle.

My auto-vac parameters are:
autovacuum = on                 # enable autovacuum subprocess?
autovacuum_naptime = 3          # time between autovacuum runs, in secs
autovacuum_vacuum_threshold = 400       # min # of tuple updates before vacuum
autovacuum_analyze_threshold = 200      # min # of tuple updates before analyze
autovacuum_vacuum_scale_factor = 0.2    # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1   # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1      # default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for autovac

and auto-vacuum is running.

My problem is that each Saturday at midnight, I have to start a vacuumdb -f -z -a or my pg_clog dir never clears out.

The manual vacuum takes quite some time and impacts weekend customers.

So, my questions are:

a) Is the manual vacuum needed for performance reasons, or is auto-vac sufficient?
b) How do my settings look?
c) Is there a way that the clogs get cleared via autovac, would a full vac of just template1/template0 (if that last is possible) do it?


pg_stop_backup()

From
"Vitaly Burshteyn"
Date:

Out of curiosity,  any idea why every time I run select pg_stop_backup();  it goes into an infinite loop?  While the first part of this select pg_start_backup(‘label’) works without any issues??

 

 

Vitaly Burshteyn

Senior Network Engineer

Broadway.com, Theatre Direct International

729  7th Avenue

New York, New York 10019

Phone: 212.817.9117

Cell# 917-701-5732

 

Re: pg_stop_backup()

From
Bruce Momjian
Date:
Vitaly Burshteyn wrote:
> Out of curiosity,  any idea why every time I run select
> pg_stop_backup();  it goes into an infinite loop?  While the
> first part of this select pg_start_backup(?label?) works without
> any issues??

pg_start_backup() is waiting for the archives to be stored.  I would
look in the server logs to see if something if failing there.  9.0 will
have better feedback when pg_stop_backup hangs like that.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do

Re: pg_stop_backup()

From
Simon Riggs
Date:
On Wed, 2010-03-17 at 10:41 -0400, Vitaly Burshteyn wrote:

> Out of curiosity,  any idea why every time I run select
> pg_stop_backup();  it goes into an infinite loop?

When you say "infinite" do you mean to say it never comes out of the
loop at all? Not sure if you are reporting a bug, or not.

The manual describes what is happening:
http://developer.postgresql.org/pgdocs/postgres/continuous-archiving.html#BACKUP-BASE-BACKUP
Step 5

Is there anything you think should be added to the manual?

--
 Simon Riggs           www.2ndQuadrant.com

Re: Autovac vs manual with analyze

From
Simon Riggs
Date:
On Mon, 2010-03-15 at 13:20 -0500, Scott Whitney wrote:

> My problem is that each Saturday at midnight, I have to start a
> vacuumdb -f -z -a or my pg_clog dir never clears out.

Why is your pg_clog dir a problem?

> So, my questions are:
>
> a) Is the manual vacuum needed for performance reasons, or is auto-vac
> sufficient?

If you don't know why you are running it, then you should skip it and
measure the difference.

--
 Simon Riggs           www.2ndQuadrant.com