Autovacuum daemon functionality questions

Hi,

I'm using PostgreSQL 8.2.

I want to configure autovacuum daemon process in our production server in an
efficient way and take advantage of this daemon to perform vacuum and
analyze more aggressively.

I also read about autovacuum daemon at
http://www.postgresql.org/docs/8.2/interactive/routine-vacuu ming.html

My questions are:
1. Why need to have 2 parameters (base threshold and scale factor) to define
the threshold value, when either one of the parameter is more than enough to
define the threshold value. Can you explain the significance of having both
parameters. What is the real-time advantage of this?
2. Documentation says ".... If the number of obsolete tuples since the last
VACUUM exceeds the "vacuum threshold", the table is vacuumed ...".
I also know about this table "pg_stat_user_tables" which has columns
n_tup_ins, n_tup_upd, n_tup_del, last_autovaccum and last_autoanalyze.
Since INSERT, UPDATE and DELETE count gets incremented everytime and do not
reset after running autovacuum/autoanalyze, how does autovacuum identifies
obsolete tuples since last VACUUM from this entries.
3. Is there a way to see autovacuum daemon log entries?



--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon functionality questions

Gnanakumar wrote:

> 1. Why need to have 2 parameters (base threshold and scale factor) to define
> the threshold value, when either one of the parameter is more than enough to
> define the threshold value. Can you explain the significance of having both
> parameters. What is the real-time advantage of this?

real-time advantage? They are just the two factors in a linear
equation.

> 2. Documentation says ".... If the number of obsolete tuples since the last
> VACUUM exceeds the "vacuum threshold", the table is vacuumed ...".
> I also know about this table "pg_stat_user_tables" which has columns
> n_tup_ins, n_tup_upd, n_tup_del, last_autovaccum and last_autoanalyze.
> Since INSERT, UPDATE and DELETE count gets incremented everytime and do not
> reset after running autovacuum/autoanalyze, how does autovacuum identifies
> obsolete tuples since last VACUUM from this entries.

There are two separate counters for live and dead tuples, IIRC (though
they may not be exposed in the pg_stat views)

> 3. Is there a way to see autovacuum daemon log entries?

Not in 8.2.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon functionality questions

>> 1. Why need to have 2 parameters (base threshold and scale factor) to
define
>> the threshold value, when either one of the parameter is more than enough
to
>> define the threshold value. Can you explain the significance of having
both
>> parameters. What is the real-time advantage of this?

>real-time advantage? They are just the two factors in a linear
>equation.

>> 2. Documentation says ".... If the number of obsolete tuples since the
last
>> VACUUM exceeds the "vacuum threshold", the table is vacuumed ...".
>> I also know about this table "pg_stat_user_tables" which has columns
>> n_tup_ins, n_tup_upd, n_tup_del, last_autovaccum and last_autoanalyze.
>> Since INSERT, UPDATE and DELETE count gets incremented everytime and do
not
>> reset after running autovacuum/autoanalyze, how does autovacuum
identifies
>> obsolete tuples since last VACUUM from this entries.

>There are two separate counters for live and dead tuples, IIRC (though
>they may not be exposed in the pg_stat views)

I've a stop/start of PostgreSQL service on a daily basis. Since these 2
counters are not stored/saved in tables and not available in pg_stat views
also, will these values be persisted/retained even after stop/start/restart?

>There are two separate counters for live and dead tuples
As per documentation, live tuples can always be obtained from
pg_class.reltuples. So, the question/answer here again would be to have
only counter on dead tuples since last VACUUM. Is my understanding right?
Is there some other/alternative way where I can check obsolete tuples at any
time since last VACUUM?

>> 3. Is there a way to see autovacuum daemon log entries?

>Not in 8.2.

From which next version of PostgreSQL is it available? Any pointers to
relevant documentation are appreciated.

>--
>Alvaro Herrera http://www.CommandPrompt.com/
>PostgreSQL Replication, Consulting, Custom Development, 24x7 support


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Autovacuum daemon functionality questions

Gnanakumar wrote:

> >There are two separate counters for live and dead tuples, IIRC (though
> >they may not be exposed in the pg_stat views)
>
> I've a stop/start of PostgreSQL service on a daily basis. Since these 2
> counters are not stored/saved in tables and not available in pg_stat views
> also, will these values be persisted/retained even after stop/start/restart?

Yes, they persist.

> >There are two separate counters for live and dead tuples
> As per documentation, live tuples can always be obtained from
> pg_class.reltuples. So, the question/answer here again would be to have
> only counter on dead tuples since last VACUUM. Is my understanding right?
> Is there some other/alternative way where I can check obsolete tuples at any
> time since last VACUUM?

reltuples is only updated by VACUUM and ANALYZE, which is why pgstats
keeps track of numbers of tuples inserted/deleted/updated for a more
accurate dead and live tuple counts, for autovacuum purposes.

> >> 3. Is there a way to see autovacuum daemon log entries?
>
> >Not in 8.2.
>
> From which next version of PostgreSQL is it available? Any pointers to
> relevant documentation are appreciated.

8.3
http://www.postgresql.org/docs/8.3/static/runtime-config-aut ovacuum.html

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin