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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Scott Marlowe Sent: Tuesday, December 28, 2010 2:12 PM To: marc.hsiao Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: How to turn autovacuum prevent wrap around run faster? On Mon, Dec 27, 2010 at 10:53 PM, marc.hsiao <marc.hsiao@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
