Thread: 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.

Ow Mun Heng,


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!

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.