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.
