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

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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Scott Marlowe
Sent: Tuesday, December 28, 2010 2:12 PM
To: marc.hsiao
Cc: pgsql-performance(at)postgresql(dot)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(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.