Re: autovacumm not working ? - Mailing list pgsql-general
| From | Tomasz Rakowski |
|---|---|
| Subject | Re: autovacumm not working ? |
| Date | |
| Msg-id | 95068.6400.qm@web37112.mail.mud.yahoo.com Whole thread Raw |
| In response to | autovacumm not working ? (Tomasz Rakowski <mourawi@yahoo.com>) |
| Responses |
Re: autovacumm not working ?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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
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@commandprompt.com>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 3:54:14 PM
Subject: Re: [GENERAL] autovacumm not working ?
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>; 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
> 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.
pgsql-general by date: