Thread: autovacumm not working ?
Hi everybody,
I have problem with frequently updated table (around 30.000 records and 1000 updates/minute, few indexes).
After a while all queries to that table become much slower then at the begining
(the number of records in the table is quite stable all the time).
I can see that autovacuum is executed (select * from pg_stat_all_tables) so it should update statistics and free unused space.
But when after a while when I run VACUUM VERBOSE ANALYZE I saw strange results:
-----------
INFO: vacuuming "ais.t_ais_position"
INFO: scanned index "t_ais_position_pkey" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.81 sec.
INFO: scanned index "ix_t_ais_position_update_time" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 144.73 sec.
INFO: scanned index "idx_ais_position" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 281.09 sec.
INFO: "t_ais_position": removed 552875 row versions in 8611 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 211.54 sec.
INFO: index "t_ais_position_pkey" now contains 30445 row versions in 367 pages
DETAIL: 0 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 30445 row versions in 18524 pages
DETAIL: 0 index row versions were removed.
4789 index pages have been deleted, 4789 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "idx_ais_position" now contains 30445 row versions in 35981 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 281.12 sec.
INFO: "t_ais_position": found 552875 removable, 30445 nonremovable row versions in 24525 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1229773 unused item pointers.
24478 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 1112.89 sec.
INFO: analyzing "ais.t_ais_position"
INFO: "t_ais_position": scanned 3000 of 24525 pages, containing 3721 live rows and 0 dead rows; 3000 rows in sample, 30419 estimated total rows
Total query runtime: 1136688 ms.
----------
There are 30445 live rows in the table/indexes, but also 552875! rows to be removed (in table and indexes).
I would expect that autovacuum would free unused rows much quicker, so the number of rows to be removed never would be so huge!.
It is not surprising now that queries are very slow: 30445 rows are located in 24525 pages (after I run VACUUM FULL it occupied just 400 pages!) and and some indexes are located in 18524 pages (after I run REINDEX it occupy just 120 pages).
Ofcourse after I run VACUUM and REINDEX manually all queries are much quicker.
Is there any bug in autovacuum or I did something wrong ?
Should I run VACUUM/REINDEX periodically anyway ?
Tomasz
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
port = 5432 # (change requires restart)
max_connections = 50 # (change requires restart)
shared_buffers = 32MB # min 128kB or max_connections*16kB
work_mem = 1MB # min 64kB
maintenance_work_mem = 16MB # min 1MB
max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 150 # 0-1000 milliseconds
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each
effective_cache_size = 128MB
log_destination = 'stderr' # Valid values are combinations of
redirect_stderr = on # Enable capturing of stderr into log
log_line_prefix = '%t ' # Special values:
stats_start_collector = on # needed for block or row stats
stats_row_level = on
autovacuum = on # enable autovacuum subprocess?
datestyle = 'iso, mdy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
I have problem with frequently updated table (around 30.000 records and 1000 updates/minute, few indexes).
After a while all queries to that table become much slower then at the begining
(the number of records in the table is quite stable all the time).
I can see that autovacuum is executed (select * from pg_stat_all_tables) so it should update statistics and free unused space.
But when after a while when I run VACUUM VERBOSE ANALYZE I saw strange results:
-----------
INFO: vacuuming "ais.t_ais_position"
INFO: scanned index "t_ais_position_pkey" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 2.81 sec.
INFO: scanned index "ix_t_ais_position_update_time" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 144.73 sec.
INFO: scanned index "idx_ais_position" to remove 552875 row versions
DETAIL: CPU 0.00s/0.00u sec elapsed 281.09 sec.
INFO: "t_ais_position": removed 552875 row versions in 8611 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 211.54 sec.
INFO: index "t_ais_position_pkey" now contains 30445 row versions in 367 pages
DETAIL: 0 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 30445 row versions in 18524 pages
DETAIL: 0 index row versions were removed.
4789 index pages have been deleted, 4789 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "idx_ais_position" now contains 30445 row versions in 35981 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 281.12 sec.
INFO: "t_ais_position": found 552875 removable, 30445 nonremovable row versions in 24525 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 1229773 unused item pointers.
24478 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 1112.89 sec.
INFO: analyzing "ais.t_ais_position"
INFO: "t_ais_position": scanned 3000 of 24525 pages, containing 3721 live rows and 0 dead rows; 3000 rows in sample, 30419 estimated total rows
Total query runtime: 1136688 ms.
----------
There are 30445 live rows in the table/indexes, but also 552875! rows to be removed (in table and indexes).
I would expect that autovacuum would free unused rows much quicker, so the number of rows to be removed never would be so huge!.
It is not surprising now that queries are very slow: 30445 rows are located in 24525 pages (after I run VACUUM FULL it occupied just 400 pages!) and and some indexes are located in 18524 pages (after I run REINDEX it occupy just 120 pages).
Ofcourse after I run VACUUM and REINDEX manually all queries are much quicker.
Is there any bug in autovacuum or I did something wrong ?
Should I run VACUUM/REINDEX periodically anyway ?
Tomasz
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
port = 5432 # (change requires restart)
max_connections = 50 # (change requires restart)
shared_buffers = 32MB # min 128kB or max_connections*16kB
work_mem = 1MB # min 64kB
maintenance_work_mem = 16MB # min 1MB
max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 150 # 0-1000 milliseconds
checkpoint_segments = 30 # in logfile segments, min 1, 16MB each
effective_cache_size = 128MB
log_destination = 'stderr' # Valid values are combinations of
redirect_stderr = on # Enable capturing of stderr into log
log_line_prefix = '%t ' # Special values:
stats_start_collector = on # needed for block or row stats
stats_row_level = on
autovacuum = on # enable autovacuum subprocess?
datestyle = 'iso, mdy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when.
Tomasz Rakowski wrote: > I have problem with frequently updated table (around 30.000 records and > 1000 updates/minute, few indexes). > After a while all queries to that table become much slower then at the > begining > (the number of records in the table is quite stable all the time). > > I can see that autovacuum is executed (select * from pg_stat_all_tables) > so it should update statistics and free unused space. Sounds like autovacuum is running, but not often enough to keep up with this highly active table. You may be able to get better results by setting table specific autovacuum thresholds for this table so that it get vacuumed more often. However if your table is *very* active then autovacuum may not be able to keep up even with the more aggressive settings, this is a known problem which is hopefully addressed in 8.3, some people solve this by turning off autovacuum for the highly active table and using a cron script to vacuum a table every minute or so.
Matthew,
Thank you for reply.
I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
Then waited for autovacuum to be activated for given table
(watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long).
When autovacuum was done I started VACUUM VERBOSE manually (to be more precise 6 sec later).
I expected that VACUUM VERBOSE will show me just few records to be removed (maybe around hundred), but in fact it reported 3200 record to be removed. It seems that autovacuum was started (anyway I expected it to be activated little bit earlier) but haven't do anything.
Any suggestions why ?
Do you know what is a difference between acivated autovacuum and VACUUM for given table ?
Tomasz
Thank you for reply.
I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
Then waited for autovacuum to be activated for given table
(watching Server status window; about 1000 updates/min, 30.000 rows in the table, so didn't have to wait too long).
When autovacuum was done I started VACUUM VERBOSE manually (to be more precise 6 sec later).
I expected that VACUUM VERBOSE will show me just few records to be removed (maybe around hundred), but in fact it reported 3200 record to be removed. It seems that autovacuum was started (anyway I expected it to be activated little bit earlier) but haven't do anything.
Any suggestions why ?
Do you know what is a difference between acivated autovacuum and VACUUM for given table ?
Tomasz
----- Original Message ----
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
Be a better Heartthrob. Get better relationship answers from someone who knows.
Yahoo! Answers - Check it out.
Tomasz Rakowski wrote: > Matthew, > > Thank you for reply. > > I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01). > Then waited for autovacuum to be activated for given table > (watching Server status window; about 1000 updates/min, 30.000 rows > in the table, so didn't have to wait too long). Did you reload (pg_ctl reload) after changing the postgresql.conf settings? Also note that you can alter values for a specific table by putting them in the pg_autovacuum table. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "I am amazed at [the pgsql-sql] mailing list for the wonderful support, and lack of hesitasion in answering a lost soul's question, I just wished the rest of the mailing list could be like this." (Fotis) (http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)
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 ?
Tomasz
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 ?
Tomasz
----- 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 2:50:40 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 2:50:40 PM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> Matthew,
>
> Thank you for reply.
>
> I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
> Then waited for autovacuum to be activated for given table
> (watching Server status window; about 1000 updates/min, 30.000 rows
> in the table, so didn't have to wait too long).
Did you reload (pg_ctl reload) after changing the postgresql.conf
settings? Also note that you can alter values for a specific table by
putting them in the pg_autovacuum table.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)
> Matthew,
>
> Thank you for reply.
>
> I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
> Then waited for autovacuum to be activated for given table
> (watching Server status window; about 1000 updates/min, 30.000 rows
> in the table, so didn't have to wait too long).
Did you reload (pg_ctl reload) after changing the postgresql.conf
settings? Also note that you can alter values for a specific table by
putting them in the pg_autovacuum table.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)
Pinpoint customers who are looking for what you sell.
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 see autovacuum process starting in avarage every 5 minutes
(table contains 30.000 records, and update rate is about 1000records /min).
But what is strange is that the number of pages allocated to the table are constant (at least for this hour: 500)
and number of pages allocated to indexes are constantly growing (109 -> 145, 92 -> 250!!!, 194->256)
But as I stated in first post after a while it gets worse and worse (thousands of allocated pages for the same number of records..)
Tomasz
I see autovacuum process starting in avarage every 5 minutes
(table contains 30.000 records, and update rate is about 1000records /min).
But what is strange is that the number of pages allocated to the table are constant (at least for this hour: 500)
and number of pages allocated to indexes are constantly growing (109 -> 145, 92 -> 250!!!, 194->256)
But as I stated in first post after a while it gets worse and worse (thousands of allocated pages for the same number of records..)
Tomasz
----- 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
Got a little couch potato?
Check out fun summer activities for kids.
Tomasz Rakowski wrote: > I see autovacuum process starting in avarage every 5 minutes > (table contains 30.000 records, and update rate is about 1000records /min). > > But what is strange is that the number of pages allocated to the table > are constant (at least for this hour: 500) > and number of pages allocated to indexes are constantly growing (109 -> > 145, 92 -> 250!!!, 194->256) > > But as I stated in first post after a while it gets worse and worse > (thousands of allocated pages for the same number of records..) You might want to reduce the autovacuum naptime. Every 5 min may not be frequent enough for you. Also, you might want make sure your FSM settings are high enough.
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.
How restart of database server influances autovacuum process ?
I think that somewhere on this mailing list I read that autovacuum in such case looses some important information
and after database server restart will not behave as expected until VACUUM ANALYZE is executed.
Is it true ?
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 2:50:40 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 2:50:40 PM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> Matthew,
>
> Thank you for reply.
>
> I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
> Then waited for autovacuum to be activated for given table
> (watching Server status window; about 1000 updates/min, 30.000 rows
> in the table, so didn't have to wait too long).
Did you reload (pg_ctl reload) after changing the postgresql.conf
settings? Also note that you can alter values for a specific table by
putting them in the pg_autovacuum table.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)
> Matthew,
>
> Thank you for reply.
>
> I was trying to configure autovacuum for given table to be more aggressive (min=100, scale factor=0.01).
> Then waited for autovacuum to be activated for given table
> (watching Server status window; about 1000 updates/min, 30.000 rows
> in the table, so didn't have to wait too long).
Did you reload (pg_ctl reload) after changing the postgresql.conf
settings? Also note that you can alter values for a specific table by
putting them in the pg_autovacuum table.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
(http://archives.postgresql.org/pgsql-sql/2006-06/msg00265.php)
Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
Tomasz Rakowski <mourawi@yahoo.com> writes: > The strange thing is that number of pages allocated for "t_ais_position" table and "t_ais_position_pkey" index haven'tchanged > (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 .... I think you may be running into one of the usage patterns that btree vacuuming doesn't clean up very well. Do you initially create a lot of rows with nearby update_times, and eventually remove all but a few? The worst case is where you're leaving only one live row per index page. The current VACUUM code can only recycle index pages that have become totally empty ... regards, tom lane
Tomasz Rakowski wrote: > > How restart of database server influances autovacuum process ? > > I think that somewhere on this mailing list I read that autovacuum in > such case looses some important information and after database server > restart will not behave as expected until VACUUM ANALYZE is executed. > Is it true ? No, this was only true in versions previous to 8.1. Unless you stop the database in "immediate" mode. If you do that, then all the information that autovacuum uses is lost. If you use "smart" or "fast" it should work fine. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Porque francamente, si para saber manejarse a uno mismo hubiera que rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)
Mathew,
For frequently updated tables I set vac_scale_factor to 0.01.
I also changed cost_delay from 150 to 10
Now it seems to work much better. Autovacuum is executed much quicker and still doesn't influence regular operations very much (and that was my concern when it was set to 0).
I guess that cost_delay = 150 was much too high and was probably the main reason of my problems. One of the frequently updated tables was much bigger then others : about 400.000 records and vacuuming that table was really taking a lot of time, so from time to time there was bigger delay between vacuuming of other tables.
If problems will continue probably I will turn off autovacum for this bigger table and will vacuum it from cron. The rest of tables I will leave for autovacuum (updated much more frequently but much smaller in size: around 40.000 records)
Does it seem reasonable ?
Tomasz Rakowski
For frequently updated tables I set vac_scale_factor to 0.01.
I also changed cost_delay from 150 to 10
Now it seems to work much better. Autovacuum is executed much quicker and still doesn't influence regular operations very much (and that was my concern when it was set to 0).
I guess that cost_delay = 150 was much too high and was probably the main reason of my problems. One of the frequently updated tables was much bigger then others : about 400.000 records and vacuuming that table was really taking a lot of time, so from time to time there was bigger delay between vacuuming of other tables.
If problems will continue probably I will turn off autovacum for this bigger table and will vacuum it from cron. The rest of tables I will leave for autovacuum (updated much more frequently but much smaller in size: around 40.000 records)
Does it seem reasonable ?
Tomasz Rakowski
----- Original Message ----
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
From: Matthew T. O'Connor <matthew@zeut.net>
To: Tomasz Rakowski <mourawi@yahoo.com>
Cc: pgsql-general@postgresql.org
Sent: Wednesday, June 27, 2007 9:50:42 AM
Subject: Re: [GENERAL] autovacumm not working ?
Tomasz Rakowski wrote:
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
> I have problem with frequently updated table (around 30.000 records and
> 1000 updates/minute, few indexes).
> After a while all queries to that table become much slower then at the
> begining
> (the number of records in the table is quite stable all the time).
>
> I can see that autovacuum is executed (select * from pg_stat_all_tables)
> so it should update statistics and free unused space.
Sounds like autovacuum is running, but not often enough to keep up with
this highly active table. You may be able to get better results by
setting table specific autovacuum thresholds for this table so that it
get vacuumed more often. However if your table is *very* active then
autovacuum may not be able to keep up even with the more aggressive
settings, this is a known problem which is hopefully addressed in 8.3,
some people solve this by turning off autovacuum for the highly active
table and using a cron script to vacuum a table every minute or so.
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.