Re: Auto Vacuum Daemon (again...) - Mailing list pgsql-hackers

On 27 Nov 2002 at 13:01, Matthew T. O'Connor wrote:

> On Wed, 2002-11-27 at 01:59, Shridhar Daithankar wrote:
> > I would not like postmaster forking into pgavd app. As far as possible, we 
> > should not touch the core. This is a client app. and be it that way. Once we 
> > integrate it into backend, we need to test the integration as well. Why bother?
> 
> I understand and agree that a non-integrated version is simpler, but I
> think there is much to gain by integrating it.  First, the
> non-integrated version has to constantly poll the server for stats
> updates this creates unnecessary over head.  A more integrated version
> could be signaled, or gather the stats information in much the same
> manner as the stats system does.  Also, having the postmaster control
> the AVD is logical since it doesn't make sense to have AVD running when
> the postmaster is not running, also, we what happens when multiple
> postmaster are running on the same machine, I would think each should
> have it's on AVD.  Integrating it in I think would be much better.

There are differences in approach here. The reason I prefer polling rather than 
signalig is IMO vacuum should always be a low priority activity and as such it 
does not deserve a signalling overhead.

A simpler way of integrating would be writing a C trigger on pg_statistics 
table(forgot the exact name). For every insert/update watch the value and 
trigger the vacuum daemon from a separate thread. (Assuming that you can create 
a trigger on view)

But Tom has earlier pointed out that even a couple of lines of trigger on such 
a table/view would be a huge performance hit in general..

I would still prefer polling. It would serve the need for foreseeable future..

> I agree vacuum full should be left to admin, my version does the same.

Good. I just wanted to confirm that we follow same policy. Thanks..

> Well the way I have it running is that the AVD blocks and waits for the
> vacuum process to finish.  This way you are guaranteed to never be
> running more than one vacuum process at a time.  I can send you the code
> if you would like, I am interested in feedback.

The reason I brought up issue of multiple processes/connection is starvation of 
a DB.

Say there are two DBs which are seriously hammered. Now if a DB starts 
vacuuming and takes long, another DB just keeps waiting for his turn for 
vacuuming and by the time vacuum is triggered, it might already have suffered 
some performance hit.

Of course these things are largely context dependent and admin should be abe to 
make better choice but the app. should be able to handle the worst situation..

The other way round is make AVD vacuum only one database. DBA can launch 
multiple instances of AVD for each database as he sees fit. That would be much 
simpler..

Please send me the code offlist. I would go thr. it and get back to you by 
early next week(bit busy, right now)


ByeShridhar

--
union, n.:    A dues-paying club workers wield to strike management.