From: | Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | HouseKeeping and vacuum Questions |
Date: | 2007-12-14 03:13:40 |
Message-ID: | 1197602020.2590.11.camel@neuromancer.home.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Johanson | 2007-12-14 04:14:51 | Re: RETURNING clause: how to specifiy column indexes? |
Previous Message | Kris Jurka | 2007-12-14 02:08:38 | Re: RETURNING clause: how to specifiy column indexes? |