Re: How to turn autovacuum prevent wrap around run faster?

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: "marc(dot)hsiao" <marc(dot)hsiao(at)cobrasonic(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: How to turn autovacuum prevent wrap around run faster?
Date: 2010-12-28 06:11:43
Message-ID: AANLkTi=39eEqo-5j4DxGTLq4akygP2_Uh-+ZfAmf1MPJ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Mon, Dec 27, 2010 at 10:53 PM, marc.hsiao <marc(dot)hsiao(at)cobrasonic(dot)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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message kakarukeys 2010-12-28 07:08:45 adding foreign key constraint locks up table
Previous Message marc.hsiao 2010-12-28 05:53:48 How to turn autovacuum prevent wrap around run faster?