Several months ago tried to implement a special postgres backend as an Auto Vacuum Daemon (AVD), somewhat like the stats collector. I failed due to my lack of experience with the postgres source.
On Sep 23, Shridhar Daithankar released an AVD written in C++ that acted as a client program rather than part of the backend. I rewrote it in C, and have been playing with it ever since. At this point I need feedback and direction from the hacker group. First: Do we want AVD integrated into the main source tree, or should it remain a "tool" that can be downloaded from gborg. I would think it should be controlled by the postmaster, and configured from GUC (at least basic on off settings) Second: Assuming we want it integrated into the source tree, can it remain a client app? Can a non backend program that connects to the postmaster using libpq be a child of the postmaster that the postmaster can control (start and stop). Third: If a special backend version is preferred, I don't personally know how to have a backend monitor and vacuum multiple databases. I guess it could be similar to the client app and fire up new back everytime a database needs to be vacuumed. Fourth: I think AVD is a feature that is needed in some form or fashion. I am willing to work on it, but if it needs to be a backend version I will probably need some help. Anyway for you reading pleasure, I have attached a plot of results from a simple test program I wrote. As you can see from the plot, AVD keeps the file size under control. Also, the first few Xacts are faster in the non AVD case, but after that AVD keeps the average Xact time down. The periodic spikes in the AVD run correspond to when the AVD has fired off a vacuum. Also when the table file gets to approx 450MB performance drops off horribly I assume this is because my system can no longer cache the whole file (I have 512M in my machine). Also, I had been developing against 7.2.3 until recently, and I wound up doing some of these benchmarks against both 7.2.3 and 7.3devel and 7.3 perfoms much better, that is it 7.2 slowed down much sooner under this test. Thanks, Matthew ps, The test program performs the following: create table pgavdtest_table (id int,num numeric(10,2),txt char(512)) while i<1000 insert into pgavdtest_table (id,num,txt) values (i,i.i,'string i') while i<1000 update pgavdtest_table set num=num+i, txt='update string %i' pps, I can post the source (both the AVD and the test progam) to the list, or email it to individuals if they would like.
<<attachment: avdtest.png>>
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly