problem with autovacuum processes

Hi,

We are using PostgreSQL 8.3.7 version and our DB size is around 200GB and its OLTP database. We are having partition tables and maintains for 120 days data.

But from couple of days we are getting the processes slow down and i noticed the following processes like:
procpid | usename | query_start | run_time | waiting | current_query
---------+----------+---------------------+-----------------+---------+---------------------------------------------------------------------------------------------------------------------
14971 | postgres | 2009-11-07 16:17:49 | 5 days 13:15:51 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_21 (to prevent wraparound)
19674 | postgres | 2009-11-11 09:43:15 | 1 day 19:50:25 | f | autovacuum: VACUUM test.tr_stg_hr_dimension_2009_08_18 (to prevent wraparound)
28064 | postgres | 2009-11-10 12:26:26 | 2 days 17:07:14 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_18 (to prevent wraparound)
17044 | postgres | 2009-11-12 17:03:06 | 12:30:34 | f | autovacuum: VACUUM ANALYZE test.dn_rule_data (to prevent wraparound)
25602 | postgres | 2009-11-11 22:12:49 | 1 day 07:20:51 | f | autovacuum: VACUUM test.tr_td_dimension_2009_08_02 (to prevent wraparound)

We enabled autovacuum for every 2million records.... but these processes are runnning for days and which is causing for the partition tables creation and delinking failures and also the drastic performance failure.

Is there any way where we can reduce the time of running of these autovacuum processes.

--Venkat

How much data are each of these tables using? ISTM that you may need to be vacuuming them more regularly to avoid this issue.

Is there any way where we can reduce the time of running of these autovacuum processes.

Have you set-up a vacuum_cost_delay or an autovacuum_cost_delay? If you have, try setting it to 0, then re-running the vacuum. You'll use more I/O, but should complete the vacuum faster.

This message was edited 1 time. Last update was at 18 Nov 2009 01:59:24

Scottie,

we set vacuum_cost_delay and autovacuum_cost_delay to 1000.I'll check by making it to zero. Thanks for that.

Also the dataset we have for those tables are like 4 to 6 millions.

ISTM that you may need to be vacuuming them more regularly to avoid this issue.

Can you please explain ISTM means and how it works in resolving the issue.

Thanks in advance.

--Venkat

Venkat_B wrote:

ISTM that you may need to be vacuuming them more regularly to avoid this issue.

Can you please explain ISTM means and how it works in resolving the issue.

ISTM is simply an acronym for:

It Seems To Me

Oh......

There is no luck even after changing the autovacuum and vacuum_cost_delay to zero.

--Venkat

How long did you wait?

we enable for 5 autovacuum process. There are few running from past 8hrs and 2 processes running from past 2 days stiil not completed even after changing the parameters.

--Venkat

After resetting your delays, do a manual 'VACUUM VERBOSE ANALYZE'

Don't let autovacuum do it, just do it yourself and monitor.

Also, after you changed the settings, did you reload or restart the database? You can check and make sure that the settings have taken by running "show vacuum_cost_delay"