Thread: 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-vacuuming.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?
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
>> 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
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-autovacuum.html -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.