Hi there, I run VACUUM VERBOSE and the output from it is below: ----------------- INFO: vacuuming "ais.t_ais_position" INFO: scanned index "t_ais_position_pkey" to remove 972 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 0.32 sec. INFO: scanned index "ix_t_ais_position_update_time" to remove 972 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 2.81 sec. INFO: scanned index "idx_ais_position" to remove 972 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 1.29 sec. INFO: "t_ais_position": removed 972 row versions in 305 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.46 sec. INFO: index "t_ais_position_pkey" now contains 26582 row versions in 145 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "ix_t_ais_position_update_time" now contains 26582 row versions in 250 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_ais_position" now contains 26664 row versions in 246 pages DETAIL: 972 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.93 sec. INFO: "t_ais_position": found 972 removable, 26582 nonremovable row versions in 498 pages DETAIL: 22 dead row versions cannot be removed yet. There were 9796 unused item pointers. 498 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.01u sec elapsed 6.81 sec.
Query returned successfully with no result in 6889 ms. ------------------ Then I left system running for several hours. There was about 1 mln updates to the table (1000/min). The number of rows in the table haven't changed much: from 26582 to 26962 rows. Autovacuum was executed on avarage every 5 minutes (scale_factor for this table is 0.01, base_thresh is 100, naptime is default 1 min). Then I run VACUUM VERBOSE one more time: ---------------- INFO: vacuuming "ais.t_ais_position" INFO: scanned index "t_ais_position_pkey" to remove 2387 row versions DETAIL: CPU 0.00s/0.01u sec elapsed 0.32 sec. INFO: scanned index "ix_t_ais_position_update_time" to remove 2387 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 19.22 sec. INFO: scanned index "idx_ais_position" to remove 2387 row versions DETAIL: CPU 0.00s/0.00u sec elapsed 2.17 sec. INFO: "t_ais_position": removed 2387 row versions in 489 pages DETAIL: CPU 0.00s/0.00u sec elapsed 1.54 sec. INFO: index "t_ais_position_pkey" now contains 26962 row versions in 146 pages DETAIL: 2387 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "ix_t_ais_position_update_time" now contains 26962 row versions in 2218 pages DETAIL: 2387 index row versions were removed. 19 index pages have been deleted, 11 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "idx_ais_position" now contains 27306 row versions in 348 pages DETAIL: 2387 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 1.68 sec. INFO: "t_ais_position": found 2387 removable, 26962 nonremovable row versions in 498 pages DETAIL: 19 dead row versions cannot be removed yet. There were 8001 unused item pointers. 498 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.01u sec elapsed 26.06 sec. Query returned successfully with no result in 26101 ms. ------------------------- The strange thing is that number of pages allocated for "t_ais_position" table and "t_ais_position_pkey" index haven't changed (so autovacuum works ok on them) , but the number of pages allocated to "ix_t_ais_position_update_time" index increased from 250 to 2218 (x 9 times). "ix_t_ais_position_update_time" index is created as "create index ix_t_ais_position_update_time on t_ais_position(update_time)", so it is suppose to be updated very frequently (as main table). The other index "t_ais_position_pkey" is on primary key and values doesn't change at all .... Could somebody explain me that ? (something more then autovacuum doesn't keep with updates would be nice) Is it still possible to use autovacuum for such tables or I really should switch to VACUUM run from cron ? Do you think that if I run VACUUM from cron every 5 minutes I would see exactly the same behaviour ? Tomasz Rakowski ----- Original Message ---- From: Alvaro Herrera <[EMAIL PROTECTED]> To: Tomasz Rakowski <[EMAIL PROTECTED]> Cc: Matthew T. O'Connor <[EMAIL PROTECTED]>; pgsql-general@postgresql.org Sent: Wednesday, June 27, 2007 3:54:14 PM Subject: Re: [GENERAL] autovacumm not working ? Tomasz Rakowski wrote: > Alvaro, > > I changed autovacuum parametrs for this specific table in pg_autovacuum > > insert into pg_autovacuum (vacrelid,enabled,vac_base_thresh, > vac_scale_factor, anl_base_thresh, anl_scale_factor, > vac_cost_delay, vac_cost_limit, > freeze_min_age, freeze_max_age) > values ( (select oid from pg_class where relname='t_ais_position'), True, > 100, 0.01 , 100, 0.02, -1, -1, -1, -1 ) > > Should I somehow let autovacuum deamon know about new table > configuration or above insert is enough ? The insert should be enough. You do see the autovacuum process starting on that database, right? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ____________________________________________________________________________________ It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/
