Thread: pg_autovacuum
select version() "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)" I am using PG_Autovacuum with postgresql 8.01 and it doesn't seem to be doing anything. I've seen it work on other machines where a log item is entered each time a table is touched. I tried starting it with pg_autovacumm -d 1 -U postgres (I purposely didn't daemonize it because I wanted to see what it was doing) and it showed that it was adding all the tables, but then it just sat there for 2 hours without doing anything else. The stats_collector and stats_row_level are both set to true. Neither the postgresql.log file nor the pg_autovacuum.log file have anything about it in them.
On Wed, Dec 07, 2005 at 12:11:22PM +0200, Sim Zacks wrote: > select version() > "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC > i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, > pie-8.7.7.1)" > > I am using PG_Autovacuum with postgresql 8.01 and it doesn't seem to be > doing anything. I've seen it work on other machines where a log item is > entered each time a table is touched. > > I tried starting it with pg_autovacumm -d 1 -U postgres > (I purposely didn't daemonize it because I wanted to see what it was > doing) and it showed that it was adding all the tables, but then it just > sat there for 2 hours without doing anything else. > > The stats_collector and stats_row_level are both set to true. > > > Neither the postgresql.log file nor the pg_autovacuum.log file have > anything about it in them. Try -d 2; it should give enough info to tell if it's at least checking tables. -- 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
This is a sampling of the debug output. [2005-12-13 09:43:47 IST] DEBUG: 33 All DBs checked in: 278300 usec, will sleep for 300 secs. [2005-12-13 09:48:47 IST] DEBUG: 34 All DBs checked in: 171112 usec, will sleep for 300 secs. [2005-12-13 09:53:47 IST] DEBUG: updating the database list Does that mean that it is vacuuming the databases? One major reason that I think it is not, is that after a while the system starts getting sluggish and when I run vacuum analyze then it picks up again. That leads me to believe that it is not running vacuum analyze. Thank You Sim Jim C. Nasby wrote: > On Wed, Dec 07, 2005 at 12:11:22PM +0200, Sim Zacks wrote: >> select version() >> "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC >> i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, >> pie-8.7.7.1)" >> >> I am using PG_Autovacuum with postgresql 8.01 and it doesn't seem to be >> doing anything. I've seen it work on other machines where a log item is >> entered each time a table is touched. >> >> I tried starting it with pg_autovacumm -d 1 -U postgres >> (I purposely didn't daemonize it because I wanted to see what it was >> doing) and it showed that it was adding all the tables, but then it just >> sat there for 2 hours without doing anything else. >> >> The stats_collector and stats_row_level are both set to true. >> >> >> Neither the postgresql.log file nor the pg_autovacuum.log file have >> anything about it in them. > > Try -d 2; it should give enough info to tell if it's at least checking > tables.
On Tue, Dec 13, 2005 at 10:43:14AM +0200, Sim Zacks wrote: > This is a sampling of the debug output. > > [2005-12-13 09:43:47 IST] DEBUG: 33 All DBs checked in: 278300 usec, > will sleep for 300 secs. > [2005-12-13 09:48:47 IST] DEBUG: 34 All DBs checked in: 171112 usec, > will sleep for 300 secs. > [2005-12-13 09:53:47 IST] DEBUG: updating the database list > > Does that mean that it is vacuuming the databases? > One major reason that I think it is not, is that after a while the > system starts getting sluggish and when I run vacuum analyze then it > picks up again. > > That leads me to believe that it is not running vacuum analyze. That means that it's most likely just not hitting the statistic targets, which by default are set so that the table can bloat quite a bit. Try adding -V 0.2 to your pg_autovacuum command line. I think if you up the verbosity one more level it'll give you information about the stats on the tables it's looking at. -- 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
Where can I change the statistic targets? These details for each table are written at the beginning, when I start it. [2005-12-13 12:38:45 IST] INFO: table name: sales."public"."productdetails" [2005-12-13 12:38:45 IST] INFO: relid: 9451256; relisshared: 0 [2005-12-13 12:38:45 IST] INFO: reltuples: 0.000000; relpages: 0 [2005-12-13 12:38:45 IST] INFO: curr_analyze_count: 0; curr_vacuum_count: 0 [2005-12-13 12:38:45 IST] INFO: last_analyze_count: 0; last_vacuum_count: 0 [2005-12-13 12:38:45 IST] INFO: analyze_threshold: 500; vacuum_threshold: 1000 [2005-12-13 12:38:45 IST] DEBUG: added table: sales."public"."productdetails" Jim C. Nasby wrote: > On Tue, Dec 13, 2005 at 10:43:14AM +0200, Sim Zacks wrote: >> This is a sampling of the debug output. >> >> [2005-12-13 09:43:47 IST] DEBUG: 33 All DBs checked in: 278300 usec, >> will sleep for 300 secs. >> [2005-12-13 09:48:47 IST] DEBUG: 34 All DBs checked in: 171112 usec, >> will sleep for 300 secs. >> [2005-12-13 09:53:47 IST] DEBUG: updating the database list >> >> Does that mean that it is vacuuming the databases? >> One major reason that I think it is not, is that after a while the >> system starts getting sluggish and when I run vacuum analyze then it >> picks up again. >> >> That leads me to believe that it is not running vacuum analyze. > > That means that it's most likely just not hitting the statistic targets, > which by default are set so that the table can bloat quite a bit. Try > adding -V 0.2 to your pg_autovacuum command line. > > I think if you up the verbosity one more level it'll give you > information about the stats on the tables it's looking at.
On Tue, Dec 13, 2005 at 01:24:36PM +0200, Sim Zacks wrote: > Where can I change the statistic targets? See the readme for pg_autovacuum. As I mentioned, -V is one of the parameters you want to track. > These details for each table are written at the beginning, when I start it. > > [2005-12-13 12:38:45 IST] INFO: table name: > sales."public"."productdetails" > [2005-12-13 12:38:45 IST] INFO: relid: 9451256; relisshared: 0 > [2005-12-13 12:38:45 IST] INFO: reltuples: 0.000000; relpages: 0 > [2005-12-13 12:38:45 IST] INFO: curr_analyze_count: 0; > curr_vacuum_count: 0 > [2005-12-13 12:38:45 IST] INFO: last_analyze_count: 0; > last_vacuum_count: 0 > [2005-12-13 12:38:45 IST] INFO: analyze_threshold: 500; > vacuum_threshold: 1000 > [2005-12-13 12:38:45 IST] DEBUG: added table: > sales."public"."productdetails" I guess it only prints that info out again when it does a vacuum then. If you look at the statistics for a table where data is changing and look at the formulas used to decide when to run an analyze or a vacuum, you can see what tables should be vacuumed soon. In any case, there's nothing that leads me to believe autovac is broken; I think you just need to set it to be more aggresive (such as using -V 0.2). -- 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