From: marc.hsiao <marc.hsiao@cobrasonic.com>
To: pgsql-performance@postgresql.org
Subject: How to turn autovacuum prevent wrap around run faster?
Date: Tue, 28 Dec 2010 13:53:48 +0800
Message-ID: <001b01cba653$9910e9f0$cb32bdd0$@hsiao@cobrasonic.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: marc.hsiao@cobrasonic.com
Subject: Re: How to turn autovacuum prevent wrap around run faster?
In-Reply-To: <001b01cba653$9910e9f0$cb32bdd0$@hsiao@cobrasonic.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox