Re: [GENERAL] HouseKeeping and vacuum Questions
On Fri, 2007-12-14 at 09:35 +0100, Harald Armin Massa wrote: > Ow Mun Heng, > > The current issue which prompted me to do such housekeeping is > due to > long database wide vacuum time. (it went from 2 hours to 4 > hours to 7 > hours) > > If vacuum takes to long, you are doing it not often enough. You should > use autovacuum, you should be able to express the delta between two > vacuums in seconds or minutes, not hours or days. autovacuum is already turned on and the threshold is at default settings. Perhaps I need to tune it to be more aggressive? how can I do that? delta between 2 vacuums in seconds or minutes? that would be nice. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] HouseKeeping and vacuum Questions
Ow Mun Heng, The current issue which prompted me to do such housekeeping is due to > long database wide vacuum time. (it went from 2 hours to 4 hours to 7 > hours) > If vacuum takes to long, you are doing it not often enough. You should use autovacuum, you should be able to express the delta between two vacuums in seconds or minutes, not hours or days. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
[GENERAL] HouseKeeping and vacuum Questions
I'm starting to perform some basic housekeeping to try to trim some big tables (~200 million rows - ~50GB+indexes) into separate partitions (via inheritance). The current issue which prompted me to do such housekeeping is due to long database wide vacuum time. (it went from 2 hours to 4 hours to 7 hours) My current strategy is to keep only 1 month of data in the base table and make a job to automatically insert/delete 1 days worth of data into the child partition tables. Currently, I've moved 3 such large tables into separate partitions and my vacuum time is still slow. (I suspect it's because I bulk insert/deleted the tuples from the main_tables in 1 go and then the vacuum had to deal with vacuum off the MVCC for the past few months's data) I'm already batching my housekeep into 6 hours timeframes (eg: insert into foo_child select * from fooo from hour1 to hour6) So, my question is now 1. Should I perform a vacuum after each 6 hour batch? or 2. perform a vacuum after 1 day batch? (4x 6hour sessions) and what should I do with the few tables which I've not started to partition? There's 4 months worth of data which is still sitting in the respective main tables. Appreciate pointers etc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
