[PERFORM] 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-1 credits vacuum_cost_limit = 1000# 1-1 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
Re: [PERFORM] How to turn autovacuum prevent wrap around run faster?
Hi Scot Marlowe: > Set an autovacuum max cost much higher than the vacuum max cost > (10 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-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 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-1 credits > > vacuum_cost_limit = 1000 # 1-1 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 (10 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
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
Re: [PERFORM] How to turn autovacuum prevent wrap around run faster?
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 = 20 vacuum_freeze_min_age = 1000 vacuum_freeze_table_age = 15000 As far as now that my partition table drop run normal, without autovacuum prevent wraparound interrupt. Regards Marc -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
