Search Postgresql Archives

Re: autovacumm not working ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <alvherre@xxxxxxxxxxxxxxxxx>
To: Tomasz Rakowski <mourawi@xxxxxxxxx>
Cc: Matthew T. O'Connor <matthew@xxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
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



Be a PS3 game guru.
Get your game face on with the latest PS3 news and previews at Yahoo! Games.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux