Hello All,

I have written a small daemon that can automatically vacuum PostgreSQL 
database, depending upon activity per table.

It sits on top of postgres statistics collector. The postgres installation 
should have per row statistics collection enabled.

Features are,

* Vacuuming based on activity on the table
* Per table vacuum. So only heavily updated tables are vacuumed.
* multiple databases supported
* Performs 'vacuum analyze' only, so it will not block the database


The project location is 
http://gborg.postgresql.org/project/pgavd/projdisplay.php

Let me know for bugs/improvements and comments.. 

I am sure real world postgres installations has some sort of scripts doing 
similar thing. This is an attempt to provide a generic interface to periodic 
vacuum.


Bye
 Shridhar

--
The Abrams' Principle:	The shortest distance between two points is off the 
wall.


Mon, 23 Sep 2002 19:13:44 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Postgresql Automatic vacuum
On 23 Sep 2002 at 14:50, Lee Kindness wrote:

> Shridhar,
> 
> Might be useful to add a .tag.gz to the downloads, so people do not
> have to use CVS to take a look.

There is a development snapshot..


Bye
 Shridhar

--
In most countries selling harmful things like drugs is punishable.Then howcome 
people can sell Microsoft software and go unpunished?(By hasku@rost.abo.fi, 
Hasse Skrifvars)


Mon, 23 Sep 2002 19:26:57 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Postgresql Automatic vacuum
On 23 Sep 2002 at 13:28, Matthew T. O'Connor wrote:

> On Monday 23 September 2002 09:43 am, Shridhar Daithankar wrote:
> > Hello All,
> >
> > I have written a small daemon that can automatically vacuum PostgreSQL
> > database, depending upon activity per table.
> 
> Hello Shridhar, sorry I didn't respond to the email you sent me a while back.  
> Anyway, I saw this post, and just started taking a look a it.  I wasn't 
> thinking of doing this as a totally separate executable / code base, but 
> perhaps that has advantages I need to think more.  
> 
> A couple of quick questions, you are using C++, but all postgres source code 
> is in C, do you want this to eventually be included as part of the postgres 
> distribution?  If so, I think that C might be a better choice.

Well, I wrote it in C++ because I like it. I have lost habit of writing pure C 
code. Nothing else.

As far as getting into base postgresql distro. I don't mind it rewriting but I 
have some reservations.

1) As it is postgresql source code is huge. Adding functions to it which 
directly taps into it's nervous system e.g. cache, would take far more time to 
perfect in all conditions.

My application as it is is an external client app. It enjoys all the isolation 
provided by postgresql. Besides this is a low priority functionality at 
runtime, unlike real time replication. It would rarely matter it vacuum is 
triggered after 6 seconds instead of configuerd 5 seconds, for example.

Less code, less bugs is my thinking. 

I wanted this functionality out fast. I didn't want to invest in learning 
postgresql source code because I didn't have time. So I wrote a separate app. 
Besides it would run on all previous postgresql versions which supports 
statistics collection. That's a huge plus if you ask me.

2) Consider this. No other database offers built in tool to clean the things.  
Is it that nobody needs it? No everybody needs it. And then you end up cleaning 
database by taking it down.

If people take for granted that postgresql does not need manual cleaning, by 
deploying apps. like pgavd, vacuum will be a big feature of postgres. Clean the 
database without taking it down..


> I will play with it more and give you some more feedback.

Awaiting that.

I am Cc'ing this to Hackers because I am sure some people might have same 
doubts.
Bye
 Shridhar

--
intoxicated, adj.:	When you feel sophisticated without being able to pronounce 
it.


Tue, 24 Sep 2002 11:46:58 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Postgresql Automatic vacuum
Am Dienstag, 24. September 2002 08:16 schrieb Shridhar Daithankar:
>
> > I will play with it more and give you some more feedback.
>
> Awaiting that.
>

IMO there are still several problems with that approach, namely:
* every database will get "polluted" with the autovacuum table, which is undesired 
* the biggest problem is the ~/.pgavrc file. I think it should work like other postgres utils do, e.g. supporting -U, -d, ....
* it's not possible to use without activly administration the config file. it should be able to work without
  adminstrator assistance.

When this is a daemon, why not store the data in memory? Even with several thousands of tables the memory footprint would
 still be small. And it should be possible to use for all databases without modifying a config file.

Two weeks ago I began writing a similar daemon, but had no time yet to finish it. I've tried to avoid using fixed numbers (namely "vacuum table
after 1000 updates") and tried to make my own heuristic based on the statistics data and the size of the table. The reason is, for a large table 1000 entries might be 
a small percentage and vacuum is not necessary, while for small tables 10 updates might be sufficient.

Best regards,
	Mario Weilguni


Tue, 24 Sep 2002 08:42:06 +0200
Mario Weilguni <mweilguni@sime.com>
Re: Postgresql Automatic vacuum
On 24 Sep 2002 at 8:42, Mario Weilguni wrote:

> Am Dienstag, 24. September 2002 08:16 schrieb Shridhar Daithankar:
> IMO there are still several problems with that approach, namely:
> * every database will get "polluted" with the autovacuum table, which is undesired 

I agree. But that was the best alternative I could see. explanation 
follows..Besides I didn't want to touch PG meta data..

> * the biggest problem is the ~/.pgavrc file. I think it should work like other postgres utils do, e.g. supporting -U, -d, ....

Shouldn't be a problem. The config stuff is working and I can add that. I would 
rather term it a minor issue. On personal preference, I would just fire it 
without any arguments. It's not a thing that you change daily. Configure it in 
config file and done..

> * it's not possible to use without activly administration the config file. it should be able to work without
>   adminstrator assistance.

Well. I would call that tuning. Each admin can tune it. Yes it's an effort but 
certainly not an active administration.
 
> When this is a daemon, why not store the data in memory? Even with several thousands of tables the memory footprint would
>  still be small. And it should be possible to use for all databases without modifying a config file.

Well. When postgresql has ability to deal with arbitrary number of rows, it 
seemed redundant to me to duplicate all those functionality. Why write lists 
and arrays again and again? Let postgresql do it.


> Two weeks ago I began writing a similar daemon, but had no time yet to finish it. I've tried to avoid using fixed numbers (namely "vacuum table
> after 1000 updates") and tried to make my own heuristic based on the statistics data and the size of the table. The reason is, for a large table 1000 entries might be 
> a small percentage and vacuum is not necessary, while for small tables 10 updates might be sufficient.

Well, that fixed number is not really fixed but admin tunable, that too per 
database. These are just defaults. Tune it to suit your needs.

The objective of whole exercise is to get rid of periodic vacuum as this app. 
shifts threshold to activity rather than time.

Besides a table should be vacuumed when it starts affecting performance. On an 
installation if a table a 1M rows and change 1K rows affects performance, there 
will be a similar performance hit for a 100K rows table for 1K rows update. 
Because overhead involved would be almost same.(Not disk space. pgavd does not 
target vacuum full but tuple size should matter).

At least me thinks so..

I plan to implement per table threshold in addition to per database thresholds. 
But right now, it seems like overhead to me. Besides there is an item in TODO, 
to shift unit of work from rows to blocks affected. I guess that takes care of 
some of your points..
Bye
 Shridhar

--
Jones' Second Law:	The man who smiles when things go wrong has thought of 
someone	to blame it on.


Tue, 24 Sep 2002 12:32:43 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: Postgresql Automatic vacuum