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.