Autovaccum settings while Bulk Loading data

From: Ambarish Bhattacharya <b(dot)ambarish(at)live(dot)in>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Autovaccum settings while Bulk Loading data
Date: 2010-06-10 08:47:48
Message-ID: BAY141-W24938315EC2639A872BC0C86D80@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


Dear Experts,

I have data about half milllion to 1 million which is populated into the Postgres db using a batch job (A sql script consists of pl/pgsql functions and views) .

I am using PostgreSQL 8.3.5 on windows 2003 64-Bit machine.

It would be helpful if you can suggest me the appropriate Autovacuum settings for handling this large data as my autovacuum setting is hanging the entire process.

As of now I have the below Autovacuum settings in postgresql.conf file.

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on

log_autovacuum_min_duration = 0

autovacuum_max_workers = 5

autovacuum_naptime = 10min

autovacuum_vacuum_threshold = 1000

autovacuum_analyze_threshold = 500

autovacuum_vacuum_scale_factor = 0.2

autovacuum_analyze_scale_factor = 0.1

autovacuum_freeze_max_age = 200000000


#autovacuum_vacuum_cost_delay = 200

#autovacuum_vacuum_cost_limit = -1

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

Please provide you suggestion regarding the same.

Many thanks



_________________________________________________________________
The latest in fashion and style in MSN Lifestyle
http://lifestyle.in.msn.com/

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Heikki Linnakangas 2010-06-10 09:01:25 Re: Autovaccum settings while Bulk Loading data
Previous Message Devrim GÜNDÜZ 2010-06-10 08:29:58 Re: Large (almost 50%!) performance drop after upgrading to 8.4.4?