Re: 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.