Thread: How to turn autovacuum prevent wrap around run faster?
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:
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
shared_buffers = 4096MB # min 128kB
temp_buffers = 384MB # min 800kB
max_prepared_transactions = 100 # zero disables the feature
work_mem = 100MB # min 64kB
maintenance_work_mem = 192MB # min 1MB
max_stack_depth = 4MB # min 100kB
vacuum_cost_delay = 50ms # 0-100 milliseconds
vacuum_cost_page_hit = 6 # 0-10000 credits
vacuum_cost_limit = 1000 # 1-10000 credits
#------------------------------------------------------------------------------
# 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
Regards.
Marc Hsiao
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.
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@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott Marlowe Sent: Tuesday, December 28, 2010 2:12 PM To: marc.hsiao Cc: pgsql-performance@postgresql.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.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. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Hi All: The autovacuum (prevent wraparound) still run more than 36 hours, I can not drop the partition table after adjust the autovacuum parameters. If a table is running autovacuum (prevent wraparound), can I purge this table? If not, what else I can do for clean this partition table? If the table is running autovacuum (prevent wraparound), can I use pg_dump to backup it? Will the Transaction ID Wraparound Failures happen while table has been restored into new DB? Regards Marc
Hi All: The autovacuum (prevent wraparound) still run more than 36 hours, I can not drop the partition table after adjust the autovacuum parameters. If a table is running autovacuum (prevent wraparound), can I purge this table? If not, what else I can do for clean this partition table? If the table is running autovacuum (prevent wraparound), can I use pg_dump to backup it? Will the Transaction ID Wraparound Failures happen while table has been restored into new DB? Regards Marc -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-turn-autovacuum-prevent-wrap-around-run-faster-tp3319984p3331421.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Hi All:
My Server list below
postgres=# select version();
version
----------------------------------------------------------------------------
---------------------------------------
PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-44), 64-bit
(1 row)
I use another way to solve this issue.
The table is partition table, I will create this partition table before
insert data.
The freezemaxid will be smaller than others, so that the partition table
will be last one that been vacuum.
Use this sql to check
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r' order by
2;
I also adjust the postgresql.conf three parameters.
This will cause that my partition table will not reach the max_age in a
short time.
autovacuum_freeze_max_age = 2000000000
vacuum_freeze_min_age = 10000000
vacuum_freeze_table_age = 150000000
As far as now that my partition table drop run normal, without autovacuum
prevent wraparound interrupt.
Regards
Marc
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of marc.hsiao
Sent: Friday, January 07, 2011 4:15 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to turn autovacuum prevent wrap around run
faster?
Hi All:
The autovacuum (prevent wraparound) still run more than 36 hours, I can not
drop the partition table after adjust the autovacuum parameters.
If a table is running autovacuum (prevent wraparound), can I purge this
table?
If not, what else I can do for clean this partition table?
If the table is running autovacuum (prevent wraparound), can I use pg_dump
to backup it?
Will the Transaction ID Wraparound Failures happen while table has been
restored into new DB?
Regards
Marc
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance