Below is a patch for the lazy vacuum. It implements a simple I/O throttle so
boxen arnt killed for hours a day when VACUUM runs. Patch includes a
paragraph for the manual. The new setting is VACUUM_THROTTLE. It can be SET
from a client connection, too.

The usleep() could be replaced with a select() call with a timeout an no
fd_set's to aid portability..

The intention is I can simply startup a daemon like shellscript that spends
its whole life doing VACUUM, like:

$ while :; do echo "SET vacuum_throttle = 20; VACUUM ANALYZE VERBOSE"; done

It is against version 7.3.2 with a bunch of Debian specific patches applied,
hopefully it will apply fine you people. [Debian's promiscuous patching of
everything always irks me]

Now, some things I noticed while writing this patch:

- Is it correct that the database backend appears to have absolutely no idea
what tables have free space until VACUUM runs for the first time?

- If a table is VACUUM'd multiple times simultaneously, what happens? [Can
this even happen? I didn't look for per-vacuum locks]. From what I can
see, this is a dangerous thing to do...

- Would this patch be more useful as a general I/O throttle for reading
pages in from disk? [Down in ReadBuffer() somewhere I guess, but I didnt
look to close (yet] I didn't do it this way, because I didnt want sleeps
forced upon processes that could have important locks held.

Note that I'm fairly noob to these database thingys, and comments are
appreciated.

- Guy


diff -bBur postgresql-7.3.2/doc/src/sgml/runtime.sgml postgresql-7.3.2-guy/doc/src/sgml/runtime.sgml
--- postgresql-7.3.2/doc/src/sgml/runtime.sgml 2003-01-11 05:04:26.000000000 +0000
+++ postgresql-7.3.2-guy/doc/src/sgml/runtime.sgml 2003-08-28 04:16:28.000000000 +0000
@@ -2045,6 +2045,23 @@
</varlistentry>

<varlistentry>
+ <term><varname>VACUUM_THROTTLE</varname> (<type>integer</type>)</term>
+ <listitem>
+ <para>
+ Optionally throttle the rate at which the lazy
+ <command>VACUUM</command> will scan database pages. The value
+ specified is either 0 to disable the throttle (the default) or the
+ number of pages/second <command>VACUUM</command> is permitted to
+ look at. If you are having problems with <command>VACUUM</command>
+ nuking your I/O subsystem, try tuning this parameter. Values larger
+ than your OS scheduling frequency will probably not be useful. This
+ does not affect <command>VACUUM FULL</command> or
+ <command>ANALYZE</command>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><varname>VIRTUAL_HOST</varname> (<type>string</type>)</term>
<listitem>
<para>
diff -bBur postgresql-7.3.2/src/backend/commands/vacuumlazy.c postgresql-7.3.2-guy/src/backend/commands/vacuumlazy.c
--- postgresql-7.3.2/src/backend/commands/vacuumlazy.c 2002-09-20 19:56:01.000000000 +0000
+++ postgresql-7.3.2-guy/src/backend/commands/vacuumlazy.c 2003-08-28 03:34:27.000000000 +0000
@@ -204,6 +205,7 @@
bool did_vacuum_index = false;
int i;
VacRUsage ru0;
+ int page_delay = 0;

vac_init_rusage(&ru0);

@@ -221,6 +223,9 @@

lazy_space_alloc(vacrelstats, nblocks);

+ if (VacuumThrottle > 0)
+ page_delay = 1000000 / VacuumThrottle;
+
for (blkno = 0; blkno < nblocks; blkno++)
{
Buffer buf;
@@ -232,6 +237,9 @@
hastup;
int prev_dead_count;

+ if(page_delay > 0)
+ usleep(page_delay);
+
CHECK_FOR_INTERRUPTS();

/*
diff -bBur postgresql-7.3.2/src/backend/utils/init/globals.c postgresql-7.3.2-guy/src/backend/utils/init/globals.c
--- postgresql-7.3.2/src/backend/utils/init/globals.c 2002-10-03 17:07:53.000000000 +0000
+++ postgresql-7.3.2-guy/src/backend/utils/init/globals.c 2003-08-28 02:55:16.000000000 +0000
@@ -70,4 +70,5 @@
bool allowSystemTableMods = false;
int SortMem = 1024;
int VacuumMem = 8192;
+int VacuumThrottle = 0;
int NBuffers = DEF_NBUFFERS;
diff -bBur postgresql-7.3.2/src/backend/utils/misc/guc.c postgresql-7.3.2-guy/src/backend/utils/misc/guc.c
--- postgresql-7.3.2/src/backend/utils/misc/guc.c 2003-01-28 18:04:13.000000000 +0000
+++ postgresql-7.3.2-guy/src/backend/utils/misc/guc.c 2003-08-28 03:07:27.000000000 +0000
@@ -602,6 +602,11 @@
},

{
+ {"vacuum_throttle", PGC_USERSET}, &VacuumThrottle,
+ 0, 0, INT_MAX, NULL, NULL
+ },
+
+ {
{"max_files_per_process", PGC_BACKEND}, &max_files_per_process,
1000, 25, INT_MAX, NULL, NULL
},
diff -bBur postgresql-7.3.2/src/include/c.h postgresql-7.3.2-guy/src/include/c.h
--- postgresql-7.3.2/src/include/c.h 2002-10-24 03:11:05.000000000 +0000
+++ postgresql-7.3.2-guy/src/include/c.h 2003-08-28 03:17:03.000000000 +0000
@@ -58,6 +58,7 @@
#include <string.h>
#include <stddef.h>
#include <stdarg.h>
+#include <unistd.h>
#ifdef HAVE_STRINGS_H
#include <strings.h>
#endif
diff -bBur postgresql-7.3.2/src/include/miscadmin.h postgresql-7.3.2-guy/src/include/miscadmin.h
--- postgresql-7.3.2/src/include/miscadmin.h 2002-10-03 17:07:53.000000000 +0000
+++ postgresql-7.3.2-guy/src/include/miscadmin.h 2003-08-28 03:12:25.000000000 +0000
@@ -165,6 +165,7 @@
extern bool allowSystemTableMods;
extern DLLIMPORT int SortMem;
extern int VacuumMem;
+extern int VacuumThrottle;

/*
* A few postmaster startup options are exported here so the

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Guy Thornley <guy (AT) esphion (DOT) com> writes:
Wasn't this idea tried and rejected already? You haven't given us any
information about actual performance.

usleep is not portable, AFAIR.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Tom Lane wrote:
What would be more valuable would be if VACUUM ran only when it know it
could reap a certain number of tuples, or there where FSM need for those
tuples.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Mon, Sep 01, 2003 at 09:05:33AM -0400, Tom Lane wrote:
tuning vacuums, memory usage, etc, and people griping about the way it nukes
the I/O system. I'm new here.

What sort of performance numbers are you looking for? Without the throttle,
I/O is nuked and other database activity takes an age, and with it, its much
happier?

More seriously, this patch isnt meant to actually deal with vacuumed tuples.
The application being developed by the company I am working for requires
24x7x365 unattended operation. Even if vacuum ran every 6 months, for the
transaction renumbering stuff, the way it nukes I/O is not acceptable.
Especially on (potentially) several-hundred gig databases.

We are beginning to learn that "DBMS" and "unattended" dont belong in the
same sentence.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Guy Thornley <guy (AT) esphion (DOT) com> writes:
Some people say that VACUUM nukes their performance, and some don't
find it to be a problem. AFAICT, it's only an issue if you have little
reserve disk bandwidth, which in itself is a dangerous situation for a
database that you don't want to pay attention to.

I don't want to sound like I'm rejecting your patch out of hand. What
I do want is to get some idea of its range of usefulness.

"Unattended" and "running on the edge of your resources" don't play nice
together, for sure.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly