On 3 Sep 2002 at 9:49, Tom Lane wrote:
> In terms of what might happen for 7.4 or beyond, what I'd personally
> like to see is some "auto vacuum" facility that would launch background
> vacuums automatically every so often. This could (eventually) be made
> self-tuning so that it would vacuum heavily-updated tables more often
> than seldom-updated ones --- while not forgetting the
> every-billion-transactions rule...
OK, I plan to work on this. Here is my brief idea
1)Create a table (vacuum_info) that stores table name and auto vacuum defaults.
Since I am planning this in contrib, I would not touch pg_class.
The table will store
- table names
- number of transactions to trigger vacuum analyze(default 1K)
- number of transactions to trigger full vacuum(default 10K)
A trigger on pg_class i.e. table creation should add a row in this table as
well.
2)Write a trigger on tables that updates statistics on table activity. I see
-pg_stat_all_tables
-pg_stat_sys_tables
-pg_stat_user_tables.
The columns are
-n_tup_ins
-n_tup_upd
-n_tup_del
Of course it will ignore it's own updates and inserts to avoid infinite loops.
This will update the pseudo statistics in vacuum_info table
Another trigger on vacuum_info will trigger vacuum if required. Ideally I would
write it in external multithreaded library to trigger vacuum in background
without blocking operations on vacuum_info table.
I need to know the following..
1)Is this sounds like a workable solution?
2)Is this as simple as I have put here or am I missing some vital components?
3)Is there some kind of rework involved?
4)Is use of threads sounds portable enough? I just need to trigger a thread in
background and return. No locking, nothing is required. Will there be any
problem for postgres invoking such an external trigger?
5)When I create a function in a .so, is it possible to invoke init/startup
routines? I can create and destroy thread in these routine to avoid thread
creation overhead. If postgres is using dlopen, I can use _init, _fini.
6)such a 'daemon' would be on per back-end basis if I am guessing correctly.
Would locking things in transactions for vacuum_info be sufficient?
I hope I am making a sensible proposal/design(My first attempt to contribute to
postgres). Please let me know your comments.
Bye
Shridhar
--
Blast medicine anyway! We've learned to tie into every organ in thehuman body
but one. The brain! The brain is what life is all about. -- McCoy, "The
Menagerie", stardate 3012.4
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
