Thread: Auto Vacuum Daemon (again...)

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.

On 26 Nov 2002 at 21:54, Matthew T. O'Connor wrote:
> 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)

Since you have rewritten in C, I think it can be safely added to contrib, after 
core team agrees. It is a good place for such things.

> 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).

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?

> 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.

Good to know that it works.

I would like to comment w.r.t to my original effort.

1) I intentionally left vacuum full to admin. Disk space is cheap and we all 
know that but IMO no application should lock a table without admin knowing it. 
This is kinda microsoftish assumption of user friendliness to make decision on 
behalf of users. Of course, sending admin a notigication is a good idea..

2)In a cluster if there are many databases and time taken for serial vacuum is 
more than time gap between two wake-up intervals of AVD, it would get into a 
continous vacuum. At some point of time, we are going to need one connection 
per database in separate process/thread.

Thanks for your work..

ByeShridhar

--
Distinctive, adj.:    A different color or shape than our competitors.

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.

On Thu, 2002-11-28 at 01:58, Shridhar Daithankar wrote:
> 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..

Well this is a debate that can probably only be solved after doing some
legwork, but I was envisioning something that just monitored the same
messages that get send to the stats collector, I would think that would
be pretty lightweight, or even perhaps extending the stats collector to
also fire off the vacuum processes since it already has all the
information we are polling for.

> 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..

agreed

> 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..

interesting thought.  I think this boils down to how many knobs do we
need to put on this system. It might make sense to say allow upto X
concurrent vacuums, a 4 processor system might handle 4 concurrent
vacuums very well.  I understand what you are saying about starvation, I
was erring on the conservative side by only allowing one vacuum at a
time (also simplicity of code :-) Where the worst case scenario is that
you "suffer some performance hit" but the hit would be finite since
vacuum will get to it fairly soon.

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

already sent.

"Matthew T. O'Connor" <matthew@zeut.net> writes:
> interesting thought.  I think this boils down to how many knobs do we
> need to put on this system. It might make sense to say allow upto X
> concurrent vacuums, a 4 processor system might handle 4 concurrent
> vacuums very well.

This is almost certainly a bad idea.  vacuum is not very
processor-intensive, but it is disk-intensive.  Multiple vacuums running
at once will suck more disk bandwidth than is appropriate for a
"background" operation, no matter how sexy your CPU is.  I can't see
any reason to allow more than one auto-scheduled vacuum at a time.
        regards, tom lane

On 28 Nov 2002 at 10:45, Tom Lane wrote:

> "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > interesting thought.  I think this boils down to how many knobs do we
> > need to put on this system. It might make sense to say allow upto X
> > concurrent vacuums, a 4 processor system might handle 4 concurrent
> > vacuums very well.
> 
> This is almost certainly a bad idea.  vacuum is not very
> processor-intensive, but it is disk-intensive.  Multiple vacuums running
> at once will suck more disk bandwidth than is appropriate for a
> "background" operation, no matter how sexy your CPU is.  I can't see
> any reason to allow more than one auto-scheduled vacuum at a time.

Hmm.. We would need to take care of that as well.. 

ByeShridhar

--
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)

On Thursday 28 November 2002 23:26, Shridhar Daithankar wrote:
> On 28 Nov 2002 at 10:45, Tom Lane wrote:
> > "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > > interesting thought.  I think this boils down to how many knobs do we
> > > need to put on this system. It might make sense to say allow upto X
> > > concurrent vacuums, a 4 processor system might handle 4 concurrent
> > > vacuums very well.
> >
> > This is almost certainly a bad idea.  vacuum is not very
> > processor-intensive, but it is disk-intensive.  Multiple vacuums running
> > at once will suck more disk bandwidth than is appropriate for a
> > "background" operation, no matter how sexy your CPU is.  I can't see
> > any reason to allow more than one auto-scheduled vacuum at a time.
>
> Hmm.. We would need to take care of that as well..

Not sure what you mean by that, but it sounds like the behaviour of my AVD
(having it block until the vacuum command completes) is fine, and perhaps
preferrable.

On 29 Nov 2002 at 7:59, Matthew T. O'Connor wrote:

> On Thursday 28 November 2002 23:26, Shridhar Daithankar wrote:
> > On 28 Nov 2002 at 10:45, Tom Lane wrote:
> > > This is almost certainly a bad idea.  vacuum is not very
> > > processor-intensive, but it is disk-intensive.  Multiple vacuums running
> > > at once will suck more disk bandwidth than is appropriate for a
> > > "background" operation, no matter how sexy your CPU is.  I can't see
> > > any reason to allow more than one auto-scheduled vacuum at a time.
> > Hmm.. We would need to take care of that as well..
> Not sure what you mean by that, but it sounds like the behaviour of my AVD 
> (having it block until the vacuum command completes) is fine, and perhaps 
> preferrable. 

Right.. But I will still keep option open for parallel vacuum which is most 
useful for reusing tuples in shared buffers.. And stale updated tuples are what 
causes performance drop in my experience..

You know.. just enough rope to hang themselves..;-)



ByeShridhar

--
Auction:    A gyp off the old block.

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "Matthew T. O'Connor" <matthew@zeut.net>
Sent: Monday, December 02, 2002 11:12 AM
Subject: Re: [HACKERS] Auto Vacuum Daemon (again...)


> On 28 Nov 2002 at 3:02, Matthew T. O'Connor wrote:
> I went thr. it today and I have some comments to make.
>
> 1. The idea of using single database is real great. I really liked that
> idea which keeps configuration simple.

I'm no longer think this is a good idea.  Tom Lane responded to our thread
on the hacker list saying that it would never be a good idea to have more
than one vacuum process running at a time, even on different databases as
vacuum is typically io bound.  Since never want to run more than one vacuum
at a time, it is much simpler to have it all managed by one AVD, rather than
one AVD for each database on a server.

> 2. You are fetching all the statistics in the list. This could get big if
> there are thousands of table or for a hosting companies where there are
tons
> of databases. That is the reason  I put a table in there..
>
> Of course not that it won't work, but by putting a table I thought it
> cause some less code in the app.

I don't see how putting a table in is any different than checking the view.
First I don't like the idea of having to have tables in someones database, I
find that intrusive.  I know that some packages such as PGAdmin do this, and
I never liked it as a developer.  Second, the only reason that it would be
less work for the server is that you may not have an entry in your table for
all tables in the database.  This can be accomplished through some type of
exclusion list that could be part of the configuration system.

> I will hack in a add-on for parallel vacuums by tom. and send you. Just
> put a command line switch(never played with getopt). Basically,after list
of
> database is read, fork a child that sleeps and vacuums only one database.

See comments above.

> Besides I have couple of bugreports which I will check against your
> version as well..

Please let me know what you find, I know it's far from a polished piece of
work yet :-)

> After a thorough look of code, I will come up with more of these but next
> time I will send you patched rather than comments..

I look forward to it.

Also, I wanted to let you know that I am working on integrating it into the
main Postgres source tree right now.  From what I have heard on the hackers
list it seems that they are hoping to have this be a core feature that they
can depend on so that they can guarantee that databases are vacuumed every
so often as required for 24x7 operation.  Basically I will still have it as
a separate executable, but the postmaster will take care of launching it
with proper arguments, restarting it if it dies (much like the stats
collector) and stop the AVD on shutdown.  This should be fairly easy to
do, I still don't know if others think this is a good idea, as I got to
response to that part of my other email, but it is the best idea I have
right now.

> Sorry for late reply. Still fighting with some *very* stupid bugs in my
> daytime jobs ( like 'if (k < 60)' evaluating to false for k=0 in release
version
> only etc..)

Good luck with your work, I hope you find all the bugs quickly, Its not the
fun part of coding.

Thanks again for the feedback, I really want this feature in postgres.

Matthew

On Fri, 2002-11-29 at 06:59, Matthew T. O'Connor wrote:
> On Thursday 28 November 2002 23:26, Shridhar Daithankar wrote:
> > On 28 Nov 2002 at 10:45, Tom Lane wrote:
> > > "Matthew T. O'Connor" <matthew@zeut.net> writes:
> > > > interesting thought.  I think this boils down to how many knobs do we
> > > > need to put on this system. It might make sense to say allow upto X
> > > > concurrent vacuums, a 4 processor system might handle 4 concurrent
> > > > vacuums very well.
> > >
> > > This is almost certainly a bad idea.  vacuum is not very
> > > processor-intensive, but it is disk-intensive.  Multiple vacuums running
> > > at once will suck more disk bandwidth than is appropriate for a
> > > "background" operation, no matter how sexy your CPU is.  I can't see
> > > any reason to allow more than one auto-scheduled vacuum at a time.
> >
> > Hmm.. We would need to take care of that as well..
> 
> Not sure what you mean by that, but it sounds like the behaviour of my AVD 
> (having it block until the vacuum command completes) is fine, and perhaps 
> preferrable. 


I can easily imagine larger systems with multiple CPUs and multiple disk
and card bundles to support multiple databases.  In this case, I have a
hard time figuring out why you'd not want to allow multiple concurrent
vacuums.  I guess I can understand a recommendation of only allowing a
single vacuum, however, should it be mandated that AVD will ONLY be able
to perform a single vacuum at a time?


Greg

On Fri, 2002-11-29 at 07:19, Shridhar Daithankar wrote:
> On 29 Nov 2002 at 7:59, Matthew T. O'Connor wrote:
> 
> > On Thursday 28 November 2002 23:26, Shridhar Daithankar wrote:
> > > On 28 Nov 2002 at 10:45, Tom Lane wrote:
> > > > This is almost certainly a bad idea.  vacuum is not very
> > > > processor-intensive, but it is disk-intensive.  Multiple vacuums running
> > > > at once will suck more disk bandwidth than is appropriate for a
> > > > "background" operation, no matter how sexy your CPU is.  I can't see
> > > > any reason to allow more than one auto-scheduled vacuum at a time.
> > > Hmm.. We would need to take care of that as well..
> > Not sure what you mean by that, but it sounds like the behaviour of my AVD 
> > (having it block until the vacuum command completes) is fine, and perhaps 
> > preferrable. 
> 
> Right.. But I will still keep option open for parallel vacuum which is most 
> useful for reusing tuples in shared buffers.. And stale updated tuples are what 
> causes performance drop in my experience..
> 
> You know.. just enough rope to hang themselves..;-)
> 

Right.  This is exactly what I was thinking about.  If someone shoots
their own foot off, that's their problem.  The added flexibility seems
well worth it.

Greg

> > Not sure what you mean by that, but it sounds like the behaviour of my AVD
> > (having it block until the vacuum command completes) is fine, and perhaps
> > preferrable.
>
> I can easily imagine larger systems with multiple CPUs and multiple disk
> and card bundles to support multiple databases.  In this case, I have a
> hard time figuring out why you'd not want to allow multiple concurrent
> vacuums.  I guess I can understand a recommendation of only allowing a
> single vacuum, however, should it be mandated that AVD will ONLY be able
> to perform a single vacuum at a time?

Hmm.. CPU time (from what I've seen) isn't an issue.  Strictly disk. The
big problem with multiple vacuums is determining which tables are in
common areas.

Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
PostgreSQL only has a single tablespace at the moment....

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On 10 Dec 2002 at 9:42, Rod Taylor wrote:

> Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
> PostgreSQL only has a single tablespace at the moment....

Sorry I am talking without doing much of it(Stuck to windows for job) But 
actually when I was talking with Matthew offlist, he mentioned that if properly 
streamlined pgavd_c could be in pg sources. But I have these plans of making 
pgavd a central point of management. i.e. where you can vacuum all your 
machines and all databases on them from one place. Like network management 
console.

I hope to finish things fast but can't commit. Still tied here..

ByeShridhar

--
QOTD:    "It's a cold bowl of chili, when love don't work out."

On Tue, 2002-12-10 at 08:42, Rod Taylor wrote:
> > > Not sure what you mean by that, but it sounds like the behaviour of my AVD 
> > > (having it block until the vacuum command completes) is fine, and perhaps 
> > > preferrable. 
> > 
> > I can easily imagine larger systems with multiple CPUs and multiple disk
> > and card bundles to support multiple databases.  In this case, I have a
> > hard time figuring out why you'd not want to allow multiple concurrent
> > vacuums.  I guess I can understand a recommendation of only allowing a
> > single vacuum, however, should it be mandated that AVD will ONLY be able
> > to perform a single vacuum at a time?
> 
> Hmm.. CPU time (from what I've seen) isn't an issue.  Strictly disk. The
> big problem with multiple vacuums is determining which tables are in
> common areas.
> 
> Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
> PostgreSQL only has a single tablespace at the moment....

But tablespace is planned for 7.4 right?  Since tablespace is supposed
to go in for 7.4, I think you've hit the nail on the head.  One AVD per
tablespace sounds just right to me.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting

On Tue, 2002-12-10 at 12:00, Greg Copeland wrote:
> On Tue, 2002-12-10 at 08:42, Rod Taylor wrote:
> > > > Not sure what you mean by that, but it sounds like the behaviour of my AVD
> > > > (having it block until the vacuum command completes) is fine, and perhaps
> > > > preferrable.
> > >
> > > I can easily imagine larger systems with multiple CPUs and multiple disk
> > > and card bundles to support multiple databases.  In this case, I have a
> > > hard time figuring out why you'd not want to allow multiple concurrent
> > > vacuums.  I guess I can understand a recommendation of only allowing a
> > > single vacuum, however, should it be mandated that AVD will ONLY be able
> > > to perform a single vacuum at a time?
> >
> > Hmm.. CPU time (from what I've seen) isn't an issue.  Strictly disk. The
> > big problem with multiple vacuums is determining which tables are in
> > common areas.
> >
> > Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
> > PostgreSQL only has a single tablespace at the moment....
>
> But tablespace is planned for 7.4 right?  Since tablespace is supposed
> to go in for 7.4, I think you've hit the nail on the head.  One AVD per
> tablespace sounds just right to me.

Planned if someone implements it and manages to have it committed prior
to release.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On 10 Dec 2002, Rod Taylor wrote:

> > > Not sure what you mean by that, but it sounds like the behaviour of my AVD 
> > > (having it block until the vacuum command completes) is fine, and perhaps 
> > > preferrable. 
> > 
> > I can easily imagine larger systems with multiple CPUs and multiple disk
> > and card bundles to support multiple databases.  In this case, I have a
> > hard time figuring out why you'd not want to allow multiple concurrent
> > vacuums.  I guess I can understand a recommendation of only allowing a
> > single vacuum, however, should it be mandated that AVD will ONLY be able
> > to perform a single vacuum at a time?
> 
> Hmm.. CPU time (from what I've seen) isn't an issue.  Strictly disk. The
> big problem with multiple vacuums is determining which tables are in
> common areas.
> 
> Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
> PostgreSQL only has a single tablespace at the moment....

But Postgresql can already place different databases on different data 
stores.  I.e. initlocation and all.  If someone was using multiple SCSI 
cards with multiple JBOD or RAID boxes hanging off of a box, they would 
have the same thing, effectively, that you are talking about.

So, someone out there may well be able to use a multiple process AVD right 
now.  Imagine m databases on n different drive sets for large production 
databases.

On Tue, 2002-12-10 at 13:09, scott.marlowe wrote:
> On 10 Dec 2002, Rod Taylor wrote:
> > Perhaps a more appropriate rule would be 1 AVD per tablespace?  Since
> > PostgreSQL only has a single tablespace at the moment....
> 
> But Postgresql can already place different databases on different data 
> stores.  I.e. initlocation and all.  If someone was using multiple SCSI 
> cards with multiple JBOD or RAID boxes hanging off of a box, they would 
> have the same thing, effectively, that you are talking about.
> 
> So, someone out there may well be able to use a multiple process AVD right 
> now.  Imagine m databases on n different drive sets for large production 
> databases.


That's right.  I always forget about that.  So, it seems, regardless of
the namespace effort, we shouldn't be limiting the number of concurrent
AVD's.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting