Обсуждение: Autovac vs manual with analyze
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?
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?
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
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
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
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