From: marc.hsiao <marc.hsiao@cobrasonic.com>
To: 'Scott Marlowe' <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to turn autovacuum prevent wrap around run faster?
Date: Mon, 3 Jan 2011 10:01:38 +0800
Message-ID: <000601cbaaea$28a5e120$79f1a360$@hsiao@cobrasonic.com> (raw)
In-Reply-To: <AANLkTi=39eEqo-5j4DxGTLq4akygP2_Uh-+ZfAmf1MPJ@mail.gmail.com>
References: <-121201471744211051@unknownmsgid>
<AANLkTi=39eEqo-5j4DxGTLq4akygP2_Uh-+ZfAmf1MPJ@mail.gmail.com>
Hi Scot Marlowe:
> Set an autovacuum max cost much higher than the vacuum max cost
> (100000 or so) and drop autovac cost delay to 0, then restart
> autovacuum.
I had set " autovacuum_vacuum_cost_delay=0".
BTW, how to set " autovacuum max cost" ?
Regards
Marc
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Tuesday, December 28, 2010 2:12 PM
To: marc.hsiao
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to turn autovacuum prevent wrap around run
faster?
On Mon, Dec 27, 2010 at 10:53 PM, marc.hsiao <marc.hsiao@cobrasonic.com>
wrote:
> 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:
> maintenance_work_mem = 192MB # min 1MB
>
> max_stack_depth = 4MB # min 100kB
>
>
>
> vacuum_cost_delay = 50ms # 0-100 milliseconds
That's a pretty high regular vacuum cost delay. Just sayin, autovac
doesn't use it.
> vacuum_cost_page_hit = 6 # 0-10000 credits
>
> vacuum_cost_limit = 1000 # 1-10000 credits
And that's pretty low.
> # 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
Set an autovacuum max cost much higher than the vacuum max cost
(100000 or so) and drop autovac cost delay to 0, then restart
autovacuum.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, scott.marlowe@gmail.com
Subject: Re: How to turn autovacuum prevent wrap around run faster?
In-Reply-To: <000601cbaaea$28a5e120$79f1a360$@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