Thread: Autovacuum Question
PostgreSQL 8.1.3
Question on autovacuum.
autovacuum_naptime (integer)
Specifies the delay between activity rounds for the autovacuum subprocess. In each round the subprocess examines one database and issues VACUUM and ANALYZE commands as needed for tables in that database. The delay is measured in seconds, and the default is 60. This option can be set at server start or in the postgresql.conf file.
Does this mean that each run of autovacuum will vacuum/analyze all tables that need work in the database that autovacuum is looking at?
The reason I'm asking is that I have ~172 non system tables in each database, so if autovacuum only does one table per run, I don't think it will ever get caught up (since there are almost 35k tables in total).
Also, with over 200 databases, does this mean that a database will only get checked once about every 3.5 hours, or does autovacuum run on the database that needs the vacuuming the most?
Thanks,
Chris
Question on autovacuum.
autovacuum_naptime (integer)
Specifies the delay between activity rounds for the autovacuum subprocess. In each round the subprocess examines one database and issues VACUUM and ANALYZE commands as needed for tables in that database. The delay is measured in seconds, and the default is 60. This option can be set at server start or in the postgresql.conf file.
Does this mean that each run of autovacuum will vacuum/analyze all tables that need work in the database that autovacuum is looking at?
The reason I'm asking is that I have ~172 non system tables in each database, so if autovacuum only does one table per run, I don't think it will ever get caught up (since there are almost 35k tables in total).
Also, with over 200 databases, does this mean that a database will only get checked once about every 3.5 hours, or does autovacuum run on the database that needs the vacuuming the most?
Thanks,
Chris
>Does this mean that each run of autovacuum will vacuum/analyze all tables
>that need work in the database that autovacuum is looking at?
Yes, it will do all tables that satisfy the criteria.
>Also, with over 200 databases, does this mean that a database will only get
>checked once about every 3.5 hours, or does autovacuum run on the database
>that needs the vacuuming the most?
If, you have it set to 60 seconds, then yes. It hits one db each minute. Actually, to be more clear, it sleeps 1 minute between each check. So, if, on average, it takes 60 seconds to perform the necessary vacuum/analyzes, then it there will be, on average, 2 minutes between each db, or 400 minutes to get through all 200 dbs.
I just happened to be reading this page from the 8.1 docs:
"The autovacuum daemon, when enabled, runs every autovacuum_naptime seconds and determines which database to process. Any database which is close to transaction ID wraparound is immediately processed. In this case, autovacuum issues a database-wide VACUUM call, or VACUUM FREEZE if it's a template database, and then terminates. If no database fulfills this criterion, the one that was least recently processed by autovacuum is chosen. In this case each table in the selected database is checked, and individual VACUUM or ANALYZE commands are issued as needed."
-----Original Message-----PostgreSQL 8.1.3
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Chris Hoover
Sent: Tuesday, May 16, 2006 1:27 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Autovacuum Question
Question on autovacuum.
autovacuum_naptime (integer)
Specifies the delay between activity rounds for the autovacuum subprocess. In each round the subprocess examines one database and issues VACUUM and ANALYZE commands as needed for tables in that database. The delay is measured in seconds, and the default is 60. This option can be set at server start or in the postgresql.conf file.
Does this mean that each run of autovacuum will vacuum/analyze all tables that need work in the database that autovacuum is looking at?
The reason I'm asking is that I have ~172 non system tables in each database, so if autovacuum only does one table per run, I don't think it will ever get caught up (since there are almost 35k tables in total).
Also, with over 200 databases, does this mean that a database will only get checked once about every 3.5 hours, or does autovacuum run on the database that needs the vacuuming the most?
Thanks,
Chris
On Tue, May 16, 2006 at 10:30:26AM -0700, Mark Liberman wrote: > >Does this mean that each run of autovacuum will vacuum/analyze all tables > >that need work in the database that autovacuum is looking at? > > Yes, it will do all tables that satisfy the criteria. > > >Also, with over 200 databases, does this mean that a database will only get > >checked once about every 3.5 hours, or does autovacuum run on the database > >that needs the vacuuming the most? > > If, you have it set to 60 seconds, then yes. It hits one db each minute. Actually, to be more clear, it sleeps 1 minutebetween each check. So, if, on average, it takes 60 seconds to perform the necessary vacuum/analyzes, then it therewill be, on average, 2 minutes between each db, or 400 minutes to get through all 200 dbs. The sleep time is so that the daemon doesn't just sit there hammering the database, checking to see if it needs to vacuum anything. If you've got 200 tables in one cluster, you should probably drop the sleep time way down; probably closer to 10 or even 1. Ideally, every time the daemon runs, there would be exactly one database that needed vacuuming. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461