Re: Postgresql Automatic vacuum - Mailing list pgsql-hackers

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
reasonis, 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..
ByeShridhar

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