--------------------

pg_autovacuum is a libpq client program that monitors all the

databases associated with apostgresql server. It uses thestats

databases associated with aPostgreSQL server. It uses thestatistics

collector to monitor insert, update and delete activity.

When a table exceeds its insert or delete threshold (more detail

on thresholds below) then that table will be vacuumed or analyzed.

When a table exceeds a insert or delete threshold (for more detail on

thresholds, see "Vacuum and Analyze" below) then that table will be

vacuumed and/or analyzed.

This allows postgresql to keep the fsm and table statistics up to

date, and eliminates the need to schedule periodic vacuums.

This allows PostgreSQL to keep the FSM (Free Space Map) and table

statistics up to date, and eliminates the need to schedule periodic

vacuums.

The primary benefit of pg_autovacuum is that the FSM and table

statistic information are updated as needed. When a table is actively

changing, pg_autovacuum will perform the necessary vacuums and

analyzes, whereas if a table remains static, no cycles will be wasted

performing unnecessary vacuums/analyzes.

statistic information are updated more nearly as frequently as needed.

When a table is actively changing, pg_autovacuum will perform the

VACUUMs and ANALYZEs that such a table needs, whereas if a table

remains static, no cycles will be wasted performing this

unnecessarily.

A secondary benefit of pg_autovacuum is that it ensures that a

database wide vacuum is performed prior toxid wraparound. This is an

database wide vacuum is performed prior toXID wraparound. This is an

important, if rare, problem, as failing to do so can result in major

data loss.

data loss. (See the section in the _Administrator's Guide_ entitled

KNOWN ISSUES:

-------------

pg_autovacuum has been tested under Redhat Linux (by me) and Solaris (by

Christopher B. Browne) and all known bugs have been resolved. Please report

any problems to the hackers list.

pg_autovacuum does not get started automatically by either the postmaster or

by pg_ctl. Along the sames lines, when the postmaster exits no one tells

pg_autovacuum. The result is that at the start of the next loop,

pg_autovacuum fails to connect to the server and exits. Any time it fails

to connect pg_autovacuum exits.

pg_autovacuum requires that the stats system be enabled and reporting row

level stats. The overhead of the stats system has been shown to be

significant under certain workloads. For instance a tight loop of queries

performing "select 1" was nearly 30% slower with stats enabled. However,

in practice with more realistic workloads, the stats system overhead is

usually nominal.

pg_autovacuum has been tested under Redhat Linux (by me) and Debian

GNU/Linux and Solaris (by Christopher B. Browne) and all known bugs

have been resolved. Please report any problems to the hackers list.

pg_autovacuum requires that the statistics system be enabled and

reporting row level stats. The overhead of the stats system has been

shown to be significant costly under certain workloads. For instance,

a tight loop of queries performing "select 1" was found to run nearly

30% slower when stats were enabled. However, in practice, with more

realistic workloads, the stats system overhead is usually nominal.

pg_autovacuum does not get started automatically by either the

postmaster or by pg_ctl. Similarly, when the postmaster exits, no one

tells pg_autovacuum. The result of that is that at the start of the

next loop, pg_autovacuum will fail to connect to the server and

exit(). Any time it fails to connect pg_autovacuum exit()s.

While pg_autovacuum can manage vacuums for as many databases as you

may have tied to a particular PostgreSQL postmaster, it can only

connect to a single PostgreSQL postmaster. Thus, if you have multiple

postmasters on a particular host, you will need multiple pg_autovacuum

instances, and they have no way, at present, to coordinate between one

another to ensure that they do not concurrently vacuum big tables.

TODO:

-----

At present, there are no sample scripts to automatically start up

pg_autovacuum along with the database. It would be desirable to have

a SysV script to start up pg_autovacuum after PostgreSQL has been

started.

Some users have expressed interest in making pg_autovacuum more

configurable so that certain tables known to be inactive could be

excluded from being vacuumed. It would probably make sense to

introduce this sort of functionality by providing arguments to specify

the database and schema in which to find a configuration table.

INSTALL:

--------

As of postgresql v7.4 pg_autovacuum is included in the main source tree

under contrib. Therefore you just make && make install (similar to most other

contrib modules) and it will be installed for you.

As of postgresql v7.4 pg_autovacuum is included in the main source

tree under contrib. Therefore you merely need to "make && make

install" (similar to most other contrib modules) and it will be

installed for you.

If you are using an earlier version ofpostgresql justuncompress the tar.gz

into the contrib directory and modify the contrib/Makefile to include the pg_autovacuum

directory. pg_autovacuum will then be made as part of the standard

postgresql install.

If you are using an earlier version ofPostgreSQL,uncompress the

tar.gz fileinto the contrib directory and modify the contrib/Makefile

to include the pg_autovacuumdirectory. pg_autovacuum will then be

built as part of the standardpostgresql install.

make sure that thefolowing are set in postgresql.conf

make sure that thefollowing are set in postgresql.conf:

stats_start_collector = true

stats_row_level = true

start up the postmaster, then execute the pg_autovacuum executable.

Start up the postmaster, then execute the pg_autovacuum executable.

If you have a script that automatically starts up the PostgreSQL

instance, you might add in, after that, something similar to the

following:

sleep 10 # To give the database some time to start up

$PGBINS/pg_autovacuum -D -s $SBASE -S $SSCALE ... [other arguments]

Command line arguments:

-----------------------

pg_autovacuum has the following optional arguments:

-d debug: 0 silent, 1 basic info, 2 more debug info, etc...

-Ddameonize: Detach from tty and run in background.

-Ddaemonize: Detach from tty and run in background.

-s sleep base value: see "Sleeping" below.

-S sleep scaling factor: see "Sleeping" below.

-v vacuum base threshold: see Vacuum and Analyze.

-U username: Username pg_autovacuum will use to connect with, if not

specified the current username is used.

-P password: Password pg_autovacuum will use to connect with.

-H host: host name or IP to connecttoo.

-H host: host name or IP to connectto.

-p port: port used for connection.

-h help: list of command line options.

All arguments have default values defined in pg_autovacuum.h. At the

time of writing they are:

Numerous arguments have default values defined in pg_autovacuum.h. At

thetime of writing they are:

-d 1

-v 1000

-V 2

-a 500 (half of -vis not specified)

-A 1 (half of -vis not specified)

-a 500 (half of -vif not specified)

-A 1 (half of -vif not specified)

-s 300 (5 minutes)

-S 2

Vacuum and Analyze:

-------------------

pg_autovacuum performs either a vacuum analyze or just analyze depending

on the quantity and type of table activity (insert, update, or delete):

pg_autovacuum performs either a VACUUM ANALYZE or just ANALYZE

depending on the mixture of table activity (insert, update, or

delete):

- If the number of (inserts + updates + deletes) > AnalyzeThreshold, then

only an analyze is performed.

- If the number of (deletes + updates) > VacuumThreshold, then a

- If the number of (deletes + updates) > VacuumThreshold, then a

vacuum analyze is performed.

deleteThreshold is equal to:

analyze_base_value + (analyze_scaling_factor * "number of tuples in the table")

The AnalyzeThreshold defaults to half of the VacuumThreshold since it

represents a much less expensive operation (approx 5%-10% of vacuum), and

running it more often should not substantially degrade system performance.

represents a much less expensive operation (approx 5%-10% of vacuum),

and running ANALYZE more often should not substantially degrade system

performance.

Sleeping:

---------

pg_autovacuum sleeps for a while after it is done checking all the

databases. It does this in order to limit the amount of system

resources it consumes. Thisalsoallows the system administrator to

resources it consumes. This allows the system administrator to

configure pg_autovacuum to be more or less aggressive.

Reducing the sleep time will cause pg_autovacuum to respond more

quickly to changes, whether they be database addition/removal, table

addition/removal, or just normal table activity.

On the other hand, settingpg_autovaccum to sleep valuesto agressivly

(for too shorta periodof time) can have a negative effect on server

performance.Ifa table gets vacuumed 5 times during the course of a

largeupdate, this is likely to takemuch longer than if the table was

vacuumedonly once, at the end.

On the other hand, settingpg_autovacuum to sleep valuestoo

aggressively (to too shortperiodsof time) can have a negative effect

on serverperformance.For instance, ifa table gets vacuumed 5 times

during the course of alargeset of updates, this is likely to takea

lot more work than if the table wasvacuumedjust once, at the end.

The total time it sleeps is equal to:

base_sleep_value + sleep_scaling_factor * "duration of the previous

loop"

Note that timing measurements are made in seconds; specifying

"pg_vacuum -s 1" means pg_autovacuum could poll the database upto 60 times

minute. In a system with large tables where vacuums may run for several

minutes, longer times between vacuums are likely to be appropriate.

"pg_vacuum -s 1" means pg_autovacuum could poll the database up to 60

times minute. In a system with large tables where vacuums may run for

several minutes, rather longer times between vacuums are likely to be

appropriate.

What pg_autovacuum monitors:

----------------------------

pg_autovacuum dynamically generates a list of all databases and tables that

exist on the server. It will dynamically add and remove databases and

tables that are removed from the database server while pg_autovacuum is

running. Overhead is fairly small per object. For example: 10 databases

with 10 tables each appears to less than 10k of memory on my Linux box.

pg_autovacuum dynamically generates a list of all databases and tables

that exist on the server. It will dynamically add and remove

databases and tables that are removed from the database server while

pg_autovacuum is running. Overhead is fairly small per object. For

example: 10 databases with 10 tables each appears to less than 10k of

memory on my Linux box.

new_tbl->reltuples=atoi(PQgetvalue(res,row,PQfnumber(res,"reltuples")));

new_tbl->relpages=atoi(PQgetvalue(res,row,PQfnumber(res,"relpages")));

log_entry(PQgetvalue(res,row,PQfnumber(res,"relisshared")));

if (strcmp("t",PQgetvalue(res,row,PQfnumber(res,"relisshared"))))

new_tbl->relisshared=0;

new_tbl->relisshared=1;

args->analyze_base_threshold+args->analyze_scaling_factor*new_tbl->reltuples;

res=send_query(query_table_stats(dbi),dbi);

res=send_query((char*)TABLE_STATS_QUERY,dbi);

t=PQntuples(res);

{

sprintf(logbuffer," table name: %s.%s",tbl->dbi->dbname,tbl->table_name);

log_entry(logbuffer);

sprintf(logbuffer," relfilenode: %i",tbl->relfilenode);

sprintf(logbuffer," relfilenode: %i; relisshared: %i",tbl->relfilenode,tbl->relisshared);

log_entry(logbuffer);

sprintf(logbuffer," reltuples: %i; relpages: %i",tbl->reltuples,tbl->relpages);

log_entry(logbuffer);

query_table_stats(db_info*dbi)

{

if (!strcmp(dbi->dbname,"template1"))/* Use template1 to

return (char*)TABLE_STATS_ALL;

return (char*)TABLE_STATS_USER;

}

if (NULL==dbi->conn)

returnNULL;

if (args->debug >=4)

log_entry(query);

res=PQexec(dbi->conn,query);

if (!res)

intj=0,

loops=0;

intsleep_secs;

Dllist*db_list;

Dlelem*db_elem,

if (0==xid_wraparound_check(dbs));

{

res=send_query(query_table_stats(dbs),dbs);/* Get an updated

res=send_query(TABLE_STATS_QUERY,dbs);/* Get an updated

for (j=0;j<PQntuples(res);j++)

if ((tbl->curr_vacuum_count-tbl->CountAtLastVacuum) >=tbl->vacuum_threshold)

{

snprintf(buf,sizeof(buf),"VACUUM ANALYZE \"%s\"",tbl->table_name);

if((tbl->relisshared>0)&& (strcmp("template1",dbs->dbname)))

snprintf(buf,sizeof(buf),"ANALYZE %s",tbl->table_name);

snprintf(buf,sizeof(buf),"VACUUM ANALYZE %s",tbl->table_name);

if (args->debug >=1)

{

sprintf(logbuffer,"Performing: %s",buf);

}

elseif ((tbl->curr_analyze_count-tbl->CountAtLastAnalyze) >=tbl->analyze_threshold)

{

snprintf(buf,sizeof(buf),"ANALYZE\"%s\"",tbl->table_name);

snprintf(buf,sizeof(buf),"ANALYZE%s",tbl->table_name);

if (args->debug >=1)

{

sprintf(logbuffer,"Performing: %s",buf);

#defineVACUUM_ANALYZE0

#defineANALYZE_ONLY1

#defineTABLE_STATS_ALL"select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.relfilenode=b.relid"

#defineTABLE_STATS_QUERY"select a.relfilenode,a.relname,a.relnamespace,a.relpages,a.relisshared,a.reltuples,b.schemaname,b.n_tup_ins,b.n_tup_upd,b.n_tup_del from pg_class a, pg_stat_all_tables b where a.relfilenode=b.relid and a.relkind = 'r'"

#definePAGES_QUERY "select relfilenode,reltuples,relpages from pg_class where relfilenode=%i"

#defineFROZENOID_QUERY "select oid,age(datfrozenxid) from pg_database where datname = 'template1'"

*table_name;

intrelfilenode,

reltuples,

relisshared,

relpages;

longanalyze_threshold,

vacuum_threshold;

staticPGconn*db_connect(db_info*dbi);

staticvoiddb_disconnect(db_info*dbi);

staticPGresult*send_query(constchar*query,db_info*dbi);

staticchar*query_table_stats(db_info*dbi);

staticvoiddaemonize(void);