autovacuum hung? - Mailing list pgsql-performance

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)