Auto Vacuum Daemon (again...)

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

Bye
Shridhar

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

Bye
Shridhar

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

Bye
Shridhar

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

Bye
Shridhar

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

Bye
Shridhar

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