Skip site navigation (1) Skip section navigation (2)

autovacuum hung?

From: Brian Cox <brian(dot)cox(at)ca(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: autovacuum hung?
Date: 2009-05-29 23:43:28
Message-ID: 4A207320.5080508@ca.com (view raw or flat)
Thread:
Lists: 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(at)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)


Responses

pgsql-performance by date

Next:From: Alvaro HerreraDate: 2009-05-30 00:02:39
Subject: Re: autovacuum hung?
Previous:From: Robert HaasDate: 2009-05-29 22:15:37
Subject: Re: Unexpected query plan results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group