How to turn autovacuum prevent wrap around run faster?

From: "marc(dot)hsiao" <marc(dot)hsiao(at)cobrasonic(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: How to turn autovacuum prevent wrap around run faster?
Date: 2010-12-28 05:53:48
Message-ID: 001b01cba653$9910e9f0$cb32bdd0$@hsiao@cobrasonic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi ALL:

My Database is for logs only, I almost have 30G data growth in my database.

I Use partition table to store those data, those tables are partitioned by
time column daily.

My data only keep for three days.

I will dump those data into dump file and drop the partition table after
three days.

My question is :

The partition table that I have to backup and drop is running a long
Prevent-Wraparound-Autovaccuum,

Is any way to let the vacuum faster?

The Prevent-Wraparound-Autovaccuum run very slow, almost 36 hours.

My Disk IO is low.

My Server config for vacuum list below:

#---------------------------------------------------------------------------
---

# RESOURCE USAGE (except WAL)

#---------------------------------------------------------------------------
---

shared_buffers = 4096MB # min 128kB

temp_buffers = 384MB # min 800kB

max_prepared_transactions = 100 # zero disables the feature

work_mem = 100MB # min 64kB

maintenance_work_mem = 192MB # min 1MB

max_stack_depth = 4MB # min 100kB

vacuum_cost_delay = 50ms # 0-100 milliseconds

vacuum_cost_page_hit = 6 # 0-10000 credits

vacuum_cost_limit = 1000 # 1-10000 credits

#---------------------------------------------------------------------------
---

# AUTOVACUUM PARAMETERS

#---------------------------------------------------------------------------
---

autovacuum = on # Enable autovacuum subprocess? 'on'

autovacuum_max_workers = 3 # max number of autovacuum subprocesses

autovacuum_naptime = 1 # time between autovacuum runs

autovacuum_vacuum_scale_factor = 0.01 # fraction of table size before vacuum

autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for

Regards.
Marc Hsiao

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2010-12-28 06:11:43 Re: How to turn autovacuum prevent wrap around run faster?
Previous Message Tom Lane 2010-12-26 17:24:47 Re: encourging bitmap AND