autovacuum has been running on 2 tables for > 5 hours. There tables are
not huge (see below). For the past ~1 hour, I've shut off all other
activity on this database. The other table being vacuumed has more rows
(1897810). Anyone have any ideas about why this is taking so long?
Thanks,
Brian
[root@rdl64xeoserv01 log]# fgrep autov /var/lib/pgsql/data/postgresql.conf
autovacuum = on # enable autovacuum subprocess?
autovacuum_naptime = 60s # time between autovacuum runs,
in secs
autovacuum_vacuum_threshold = 200 # min # of tuple updates before
autovacuum_analyze_threshold = 50 # min # of tuple updates before
autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovac, -1 means use
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
cemdb=# select procpid,query_start,current_query from pg_stat_activity;
procpid | query_start |
current_query
---------+-------------------------------+-----------------------------------------------------------------
24866 | 2009-05-29 13:50:11.251397-07 | autovacuum: VACUUM
public.ts_user_sessions_map
24869 | 2009-05-29 11:46:54.221713-07 | autovacuum: VACUUM ANALYZE
public.ts_stats_transet_user_daily
24872 | 2009-05-29 11:31:28.324954-07 | autovacuum: VACUUM ANALYZE
public.ts_stats_transet_user_weekly
28097 | 2009-05-29 15:58:49.24832-07 | select
procpid,query_start,current_query from pg_stat_activity;
(4 rows)
cemdb=# select count(*) from ts_stats_transet_user_daily;
count
--------
558321
(1 row)
cemdb=# select count(*) from ts_stats_transet_user_weekly;
count
--------
333324
(1 row)
cemdb=# select c.oid,c.relname,l.pid,l.mode,l.granted from pg_class c
join pg_locks l on c.oid=l.relation order by l.pid;
oid | relname |
pid | mode | granted
----------+-------------------------------------------------------+-------+--------------------------+---------
26612062 | ts_user_sessions_map |
24866 | ShareUpdateExclusiveLock | t
26613644 | ts_user_sessions_map_interimsessionidindex |
24866 | RowExclusiveLock | t
26613645 | ts_user_sessions_map_sessionidindex |
24866 | RowExclusiveLock | t
26612846 | ts_user_sessions_map_appindex |
24866 | RowExclusiveLock | t
26612417 | ts_user_sessions_map_pkey |
24866 | RowExclusiveLock | t
27208308 | ts_stats_transet_user_daily_userindex |
24869 | RowExclusiveLock | t
27208305 | ts_stats_transet_user_daily_transetincarnationidindex |
24869 | RowExclusiveLock | t
27208310 | ts_stats_transet_user_daily_yearindex |
24869 | RowExclusiveLock | t
27208307 | ts_stats_transet_user_daily_userincarnationidindex |
24869 | RowExclusiveLock | t
27208302 | ts_stats_transet_user_daily_lastaggregatedrowindex |
24869 | RowExclusiveLock | t
27208309 | ts_stats_transet_user_daily_weekindex |
24869 | RowExclusiveLock | t
26612320 | ts_stats_transet_user_daily_pkey |
24869 | RowExclusiveLock | t
27208306 | ts_stats_transet_user_daily_transetindex |
24869 | RowExclusiveLock | t
26611722 | ts_stats_transet_user_daily |
24869 | ShareUpdateExclusiveLock | t
27208303 | ts_stats_transet_user_daily_monthindex |
24869 | RowExclusiveLock | t
27208304 | ts_stats_transet_user_daily_starttimeindex |
24869 | RowExclusiveLock | t
27208300 | ts_stats_transet_user_daily_dayindex |
24869 | RowExclusiveLock | t
27208301 | ts_stats_transet_user_daily_hourindex |
24869 | RowExclusiveLock | t
26612551 | ts_stats_transet_user_weekly_lastaggregatedrowindex |
24872 | RowExclusiveLock | t
26612558 | ts_stats_transet_user_weekly_yearindex |
24872 | RowExclusiveLock | t
26612326 | ts_stats_transet_user_weekly_pkey |
24872 | RowExclusiveLock | t
26612554 | ts_stats_transet_user_weekly_transetindex |
24872 | RowExclusiveLock | t
26612555 | ts_stats_transet_user_weekly_userincarnationidindex |
24872 | RowExclusiveLock | t
26611743 | ts_stats_transet_user_weekly |
24872 | ShareUpdateExclusiveLock | t
26612556 | ts_stats_transet_user_weekly_userindex |
24872 | RowExclusiveLock | t
26612553 | ts_stats_transet_user_weekly_starttimeindex |
24872 | RowExclusiveLock | t
26612557 | ts_stats_transet_user_weekly_weekindex |
24872 | RowExclusiveLock | t
26612550 | ts_stats_transet_user_weekly_hourindex |
24872 | RowExclusiveLock | t
26612552 | ts_stats_transet_user_weekly_monthindex |
24872 | RowExclusiveLock | t
26612549 | ts_stats_transet_user_weekly_dayindex |
24872 | RowExclusiveLock | t
2663 | pg_class_relname_nsp_index |
28097 | AccessShareLock | t
10969 | pg_locks |
28097 | AccessShareLock | t
1259 | pg_class |
28097 | AccessShareLock | t
2662 | pg_class_oid_index |
28097 | AccessShareLock | t
(34 rows)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
