All,

I see that an auto vacuum process that has started on April 08 is still continuing till date on a production server that I manage.

Some statistics related to the process are as listed below. The queries are below statistics.

1) ps -aux | grep ^postgres

postgres 24188 0.0 2.4 14038764 809904 ? Ss Apr08 1:05 postgres: autovacuum worker process sbcoasis

2) select * from pg_lock

locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------
relation | 16384 | 18349 | | | | | | | | 5/6082308 | 24188 | ShareUpdateExclusiveLock | t

3) pg_stat_user_tables values

relid | n_live_tup | n_tup_hot_upd | n_dead_tup | n_tup_ins | n_tup_del
-------+------------+---------------+------------+-----------+-----------
18349 | 1245123345 | 0 | 3 | 76479925 | 5

relid | last_autovacuum | last_autoanalyze
-------+-------------------------------+-------------------------------
18349 | 2009-03-29 20:36:59.18137-07 | 2009-04-07 21:48:09.646087-07

4) Postgres Configuration Parameters:

# - Memory -

shared_buffers = 12GB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 2GB # min 64kB
maintenance_work_mem = 1GB # min 1MB
#max_stack_depth = 2MB # min 100kB

# - Free Space Map -

max_fsm_pages = 1500000 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
max_fsm_relations = 500 # min 100, ~70 bytes each
# (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)

# - Cost-Based Vacuum Delay -

vacuum_cost_delay = 5 # 0-1000 milliseconds

5) The server has quad processors with 32 GB RAM.

--------------------------------------------------------------------------------------------------------------------------------------------------------

Queries

From past experience, running a manual vacuum and analyze on the table after a couple of days took around 12 - 16 hours, so the time taken for the current process seems over the top. It also stops me from running reindex routine (which was to happen last week - runs once a month), as the indexing routine waited for the vacuum to be completed and had to be cancelled.

A) Is there a way to know whether the process is still running or is stalled?
B) Can the process be killed safely without causing issues in the autovacuum daemon?
C) Is there a way to know the percentage of autovacuum completed on a table?
D) Can I speed up the autovacuum process in some way? Will increasing the maintenance_work_mem affect other processes?

Please let me know if more information is required to resolve the issue.

Thanks in advance.

Shrirang.

The information contained in this message, including any attachments, is attorney privileged and/or confidential information intended only for the use of the individual or entity named as addressee. The review, dissemination, distribution or copying of this communication by or to anyone other than the intended addressee is strictly prohibited. If you have received this communication in error, please immediately notify the sender by replying to the message and destroy all copies of the original message.