Experimental patch for inter-page delay in VACUUM

Tom Lane wrote:

Attached is an extremely crude prototype patch for making VACUUM delay
by a configurable amount between pages,

Cool!

Assuming that this is found to be useful, the following issues would
have to be dealt with before the patch would be production quality:

2. I only bothered to insert delays in the processing loops of plain
VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree
indexes aren't done yet.

I thought we didn't want the delay in vacuum full since it locks things
down, we want vacuum full to finish ASAP. As opposed to normal vacuum
which would be fired by the autovacuum daemon.

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Tom Lane wrote:

2. I only bothered to insert delays in the processing loops of plain
VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree
indexes aren't done yet.

I thought we didn't want the delay in vacuum full since it locks things
down, we want vacuum full to finish ASAP. As opposed to normal vacuum
which would be fired by the autovacuum daemon.

My thought was that it'd be up to the user to set vacuum_page_delay
appropriately for what he is doing. It might or might not ever make
sense to use a nonzero delay in VACUUM FULL, but the facility should be
there. (Since plain and full VACUUM share the same index cleanup code,
it would take some klugery to implement a policy of "no delays for
VACUUM FULL" anyway.)

Best practice would likely be to leave the default vacuum_page_delay at
zero, and have the autovacuum daemon set a nonzero value for vacuums it
issues.

regards, tom lane

Great! I haven't tried it yet, but I love the thought of it already :-)
I've been waiting for something like this for the past 2 years and now it's
going to make my multi-gigabyte PostgreSQL more usable and responsive. Will
the delay be tunable per VACUUM invocation? This is needed for different
tables that require different VACUUM priorities (eg. For small tables that
are rarely used, I rather vacuum with zero delay. For big tables, I'd set a
reasonable delay in vacuum and let it run through the day & nite).

Regards,

Stephen

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:7473.1067579594@sss.pgh.pa.us...

Tom Lane wrote:

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Tom Lane wrote:

2. I only bothered to insert delays in the processing loops of plain
VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree
indexes aren't done yet.

I thought we didn't want the delay in vacuum full since it locks things
down, we want vacuum full to finish ASAP. As opposed to normal vacuum
which would be fired by the autovacuum daemon.

My thought was that it'd be up to the user to set vacuum_page_delay
appropriately for what he is doing. It might or might not ever make
sense to use a nonzero delay in VACUUM FULL, but the facility should be
there. (Since plain and full VACUUM share the same index cleanup code,
it would take some klugery to implement a policy of "no delays for
VACUUM FULL" anyway.)

Best practice would likely be to leave the default vacuum_page_delay at
zero, and have the autovacuum daemon set a nonzero value for vacuums it
issues.

What is the advantage of delaying vacuum per page vs. just doing vacuum
less frequently?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

What is the advantage of delaying vacuum per page vs. just doing vacuum
less frequently?

The point is the amount of load VACUUM poses while it's running. If
your setup doesn't have a lot of disk bandwidth to spare, a background
VACUUM can hurt the performance of your foreground applications quite
a bit. Running it less often doesn't improve this issue at all.

regards, tom lane

Bruce Momjian wrote:

Tom Lane wrote:

"Matthew T. O'Connor" <matthew@zeut.net> writes:

Tom Lane wrote:

2. I only bothered to insert delays in the processing loops of plain
VACUUM and btree index cleanup. VACUUM FULL and cleanup of non-btree
indexes aren't done yet.

I thought we didn't want the delay in vacuum full since it locks things
down, we want vacuum full to finish ASAP. As opposed to normal vacuum
which would be fired by the autovacuum daemon.

My thought was that it'd be up to the user to set vacuum_page_delay
appropriately for what he is doing. It might or might not ever make
sense to use a nonzero delay in VACUUM FULL, but the facility should be
there. (Since plain and full VACUUM share the same index cleanup code,
it would take some klugery to implement a policy of "no delays for
VACUUM FULL" anyway.)

Best practice would likely be to leave the default vacuum_page_delay at
zero, and have the autovacuum daemon set a nonzero value for vacuums it
issues.

What is the advantage of delaying vacuum per page vs. just doing vacuum
less frequently?

It gives regular backends more time to "retouch" the pages they actually
need before they fall off the end of the LRU list.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

pgman@candle.pha.pa.us (Bruce Momjian) writes:

Tom Lane wrote:

Best practice would likely be to leave the default vacuum_page_delay at
zero, and have the autovacuum daemon set a nonzero value for vacuums it
issues.

What is the advantage of delaying vacuum per page vs. just doing vacuum
less frequently?

If the vacuum is deferred, that merely means that you put off the
"slow to a crawl" until a bit later. It is a given that the system
will slow to a crawl for the duration of the vacuum; you are merely
putting it off a bit.

The advantage of the per-page delay is that performance is not being
"totally hammered" by the vacuum. If things are so busy that it's an
issue, the system is liable to "limp somewhat," but that's not as bad
as what we see now, where VACUUM and other activity are 'dueling' for
access to I/O. Per-page delay means that VACUUM mostly defers to the
other activity, limiting how badly it hurts other performance.
--
output = reverse("ofni.smrytrebil" "@" "enworbbc")
<http://dev6.int.libertyrms.com/&gt;
Christopher Browne
(416) 646 3304 x124 (land)

"Stephen" <jleelim@xxxxxx.com> writes:

Great! I haven't tried it yet, but I love the thought of it already :-)
I've been waiting for something like this for the past 2 years and now it's
going to make my multi-gigabyte PostgreSQL more usable and responsive. Will
the delay be tunable per VACUUM invocation?

As the patch is set up, you just do "SET vacuum_page_delay = n" and
then VACUUM.

regards, tom lane

Christopher Browne <cbbrowne@libertyrms.info> writes:

The advantage of the per-page delay is that performance is not being
"totally hammered" by the vacuum. If things are so busy that it's an
issue, the system is liable to "limp somewhat," but that's not as bad
as what we see now, where VACUUM and other activity are 'dueling' for
access to I/O. Per-page delay means that VACUUM mostly defers to the
other activity, limiting how badly it hurts other performance.

... or that's the theory, anyway. The point of putting up this patch
is for people to experiment to find out if it really helps.

regards, tom lane

I tried the Tom Lane's patch on PostgreSQL 7.4-BETA-5 and it works
fantastically! Running a few short tests show a significant improvement in

responsiveness on my RedHat 9 Linux 2.4-20-8 (IDE 120GB 7200RPM UDMA5).

I didn't feel any noticeable delay when vacuum_page_delay is set to 5ms, 10
ms. Vacuum takes 15 to 24 times longer to complete (as expected)

but I don't mind at all. Vmstat BI/BO load is reduced by 5 times when
vacuum_page_delay = 1ms. Load average reduced significantly

also as there are less processes waiting to complete. I find a value of 1ms
to 5ms is quite good and will keep system responsive. Going from 10ms to 1ms
didn't seem to reduce the total vacuum time by much and I'm not sure why.

Any chance we can get this patched into 7.4 permanently?

I cannot say how well it would work on a heavy load, but on a light load
this patch is highly recommended for 24/7 large DB systems. The

database is mostly read-only. There are 133,000 rows and each row is about
2.5kB in size (mostly due to the bytea column holding a binary

image). The long row causes system to TOAST the table. I repeatedly ran the
following tests while system is idling:

Normal operation with no VACUUM
===============================

tsdb=# explain analyze select * from table1 where id =
'0078997ac809877c1a0d1f76af753608';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=19.030..19.036 rows=1 loops=1)
Index Cond: ((id)::text = '0078997ac809877c1a0d1f76af753608'::text)
Total runtime: 19.206 ms
(3 rows)

VACUUM at vacuum_page_delay = 0
===============================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 1 0 176844 3960 17748 146704 0 0 1408 0 296 556 0 1
99
0 1 0 176844 3960 17748 146264 0 0 1536 0 285 546 0 2
98

tsdb=# explain analyze select * from table1 where id =
'00e5ae5f4fddab371f7847f7da65eebb';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=298.028..298.047 rows=1 loops=1)
Index Cond: ((id)::text = '0036edc4a92b6afd41304c6c8b76bc3c'::text)
Total runtime: 298.275 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'0046751ac3ec290b9f66ea1d66431923';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=454.727..454.746 rows=1 loops=1)
Index Cond: ((id)::text = '0046751ac3ec290b9f66ea1d66431923'::text)
Total runtime: 454.970 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00a74e6885579a2d50487f5a1dceba22';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=344.483..344.501 rows=1 loops=1)
Index Cond: ((id)::text = '00a74e6885579a2d50487f5a1dceba22'::text)
Total runtime: 344.700 ms
(3 rows)

VACUUM at vacuum_page_delay = 1
===============================

procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4292 23700 137416 0 0 384 0 127 302 0 0
100
0 0 0 176840 4220 23700 137116 0 0 512 0 118 286 0 0
100
1 0 0 176840 4220 23700 136656 0 0 384 0 132 303 0 1
99

tsdb=# explain analyze select * from table1 where id =
'003d5966f8b9a06e4b0fff9fa8e93be0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=74.575..74.584 rows=1 loops=1)
Index Cond: ((id)::text = '003d5966f8b9a06e4b0fff9fa8e93be0'::text)
Total runtime: 74.761 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00677fe46cd0af3d98564068f34db1cf';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=31.779..31.785 rows=1 loops=1)
Index Cond: ((id)::text = '00677fe46cd0af3d98564068f34db1cf'::text)
Total runtime: 31.954 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00b7c3e2fffdf39ff4ac50add04336b7';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=78.974..78.989 rows=1 loops=1)
Index Cond: ((id)::text = '00b7c3e2fffdf39ff4ac50add04336b7'::text)
Total runtime: 79.172 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'008d49c007f711d5f5ec48b67a8e58f0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=30.143..30.148 rows=1 loops=1)
Index Cond: ((id)::text = '008d49c007f711d5f5ec48b67a8e58f0'::text)
Total runtime: 30.315 ms
(3 rows)

VACUUM at vacuum_page_delay = 5
===============================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4228 22668 138212 0 0 512 0 117 276 0 0
100
0 0 0 176840 4220 22668 138212 0 0 384 0 132 296 0 1
99
0 0 0 176840 4220 22668 137764 0 0 384 0 114 276 0 0
100

tsdb=# explain analyze select * from table1 where id =
'000aa16ffe019fa327b68b7e610e5ac0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=14.089..14.094 rows=1 loops=1)
Index Cond: ((id)::text = '000aa16ffe019fa327b68b7e610e5ac0'::text)
Total runtime: 14.252 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00aacc4684577737498df0536be1fac8';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=16.747..16.752 rows=1 loops=1)
Index Cond: ((id)::text = '00aacc4684577737498df0536be1fac8'::text)
Total runtime: 16.910 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00e295f5644d4cb77a5ebc4efbbaa770';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=16.684..16.690 rows=1 loops=1)
Index Cond: ((id)::text = '00e295f5644d4cb77a5ebc4efbbaa770'::text)
Total runtime: 16.886 ms
(3 rows)

VACUUM at vacuum_page_delay = 10
================================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4336 20968 139780 0 0 384 108 121 294 0 0
100
0 0 0 176840 4336 20968 140164 0 0 384 0 130 281 0 1
99

tsdb=# explain analyze select * from table1 where id =
'007841017b9f7c80394f2bb4314ba8c1';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=19.576..19.587 rows=1 loops=1)
Index Cond: ((id)::text = '007841017b9f7c80394f2bb4314ba8c1'::text)
Total runtime: 19.854 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'0070724846c4d0d0dbb8f3e939fd1da4';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=10.616..10.624 rows=1 loops=1)
Index Cond: ((id)::text = '0070724846c4d0d0dbb8f3e939fd1da4'::text)
Total runtime: 10.795 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00fc92bf0f5048d7680bd8fa2d4c6f3a';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=28.007..28.014 rows=1 loops=1)
Index Cond: ((id)::text = '00fc92bf0f5048d7680bd8fa2d4c6f3a'::text)
Total runtime: 28.183 ms
(3 rows)

Tom Lane wrote:

"Stephen" <jleelim@xxxxxx.com> writes:

Great! I haven't tried it yet, but I love the thought of it already :-)
I've been waiting for something like this for the past 2 years and now it's
going to make my multi-gigabyte PostgreSQL more usable and responsive. Will
the delay be tunable per VACUUM invocation?

As the patch is set up, you just do "SET vacuum_page_delay = n" and
then VACUUM.

probably a setting that autovacuum will tweak based on things like table
size etc.... If we can find a way to automatically tweak it that makes
sense.

"Stephen" <jleelim@xxxxxx.com> writes:

also as there are less processes waiting to complete. I find a value of 1ms
to 5ms is quite good and will keep system responsive. Going from 10ms to 1ms
didn't seem to reduce the total vacuum time by much and I'm not sure why.

On most Unixen, the effective resolution of sleep requests is whatever
the scheduler time quantum is --- and 10ms is the standard quantum in
most cases. So any delay less than 10ms is going to be interpreted as
10ms.

I think on recent Linuxen it's possible to adjust the time quantum, but
whether this would be a net win isn't clear; presumably a shorter
quantum would result in more scheduler overhead and more process-swap
penalties.

regards, tom lane

Stephen wrote:

I tried the Tom Lane's patch on PostgreSQL 7.4-BETA-5 and it works
fantastically! Running a few short tests show a significant improvement in

responsiveness on my RedHat 9 Linux 2.4-20-8 (IDE 120GB 7200RPM UDMA5).

I am currently looking at implementing ARC as a replacement strategy. I
don't have anything that works yet, so I can't really tell what the
result would be and it might turn out that we want both features.

All I can say is that the theory looks like an extremely smart and
generalized version of the crude hack I had done. And that one is able
to lower the impact of VACUUM on the foreground clients while increasing
the VACUUM speed. The 7.3.4 version of my crude hack is attached.

Jan

I didn't feel any noticeable delay when vacuum_page_delay is set to 5ms, 10
ms. Vacuum takes 15 to 24 times longer to complete (as expected)

but I don't mind at all. Vmstat BI/BO load is reduced by 5 times when
vacuum_page_delay = 1ms. Load average reduced significantly

also as there are less processes waiting to complete. I find a value of 1ms
to 5ms is quite good and will keep system responsive. Going from 10ms to 1ms
didn't seem to reduce the total vacuum time by much and I'm not sure why.

Any chance we can get this patched into 7.4 permanently?

I cannot say how well it would work on a heavy load, but on a light load
this patch is highly recommended for 24/7 large DB systems. The

database is mostly read-only. There are 133,000 rows and each row is about
2.5kB in size (mostly due to the bytea column holding a binary

image). The long row causes system to TOAST the table. I repeatedly ran the
following tests while system is idling:

Normal operation with no VACUUM
===============================

tsdb=# explain analyze select * from table1 where id =
'0078997ac809877c1a0d1f76af753608';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=19.030..19.036 rows=1 loops=1)
Index Cond: ((id)::text = '0078997ac809877c1a0d1f76af753608'::text)
Total runtime: 19.206 ms
(3 rows)

VACUUM at vacuum_page_delay = 0
===============================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 1 0 176844 3960 17748 146704 0 0 1408 0 296 556 0 1
99
0 1 0 176844 3960 17748 146264 0 0 1536 0 285 546 0 2
98

tsdb=# explain analyze select * from table1 where id =
'00e5ae5f4fddab371f7847f7da65eebb';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=298.028..298.047 rows=1 loops=1)
Index Cond: ((id)::text = '0036edc4a92b6afd41304c6c8b76bc3c'::text)
Total runtime: 298.275 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'0046751ac3ec290b9f66ea1d66431923';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=454.727..454.746 rows=1 loops=1)
Index Cond: ((id)::text = '0046751ac3ec290b9f66ea1d66431923'::text)
Total runtime: 454.970 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00a74e6885579a2d50487f5a1dceba22';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=344.483..344.501 rows=1 loops=1)
Index Cond: ((id)::text = '00a74e6885579a2d50487f5a1dceba22'::text)
Total runtime: 344.700 ms
(3 rows)

VACUUM at vacuum_page_delay = 1
===============================

procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4292 23700 137416 0 0 384 0 127 302 0 0
100
0 0 0 176840 4220 23700 137116 0 0 512 0 118 286 0 0
100
1 0 0 176840 4220 23700 136656 0 0 384 0 132 303 0 1
99

tsdb=# explain analyze select * from table1 where id =
'003d5966f8b9a06e4b0fff9fa8e93be0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=74.575..74.584 rows=1 loops=1)
Index Cond: ((id)::text = '003d5966f8b9a06e4b0fff9fa8e93be0'::text)
Total runtime: 74.761 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00677fe46cd0af3d98564068f34db1cf';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=31.779..31.785 rows=1 loops=1)
Index Cond: ((id)::text = '00677fe46cd0af3d98564068f34db1cf'::text)
Total runtime: 31.954 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00b7c3e2fffdf39ff4ac50add04336b7';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=78.974..78.989 rows=1 loops=1)
Index Cond: ((id)::text = '00b7c3e2fffdf39ff4ac50add04336b7'::text)
Total runtime: 79.172 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'008d49c007f711d5f5ec48b67a8e58f0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=30.143..30.148 rows=1 loops=1)
Index Cond: ((id)::text = '008d49c007f711d5f5ec48b67a8e58f0'::text)
Total runtime: 30.315 ms
(3 rows)

VACUUM at vacuum_page_delay = 5
===============================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4228 22668 138212 0 0 512 0 117 276 0 0
100
0 0 0 176840 4220 22668 138212 0 0 384 0 132 296 0 1
99
0 0 0 176840 4220 22668 137764 0 0 384 0 114 276 0 0
100

tsdb=# explain analyze select * from table1 where id =
'000aa16ffe019fa327b68b7e610e5ac0';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=14.089..14.094 rows=1 loops=1)
Index Cond: ((id)::text = '000aa16ffe019fa327b68b7e610e5ac0'::text)
Total runtime: 14.252 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00aacc4684577737498df0536be1fac8';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=16.747..16.752 rows=1 loops=1)
Index Cond: ((id)::text = '00aacc4684577737498df0536be1fac8'::text)
Total runtime: 16.910 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00e295f5644d4cb77a5ebc4efbbaa770';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=16.684..16.690 rows=1 loops=1)
Index Cond: ((id)::text = '00e295f5644d4cb77a5ebc4efbbaa770'::text)
Total runtime: 16.886 ms
(3 rows)

VACUUM at vacuum_page_delay = 10
================================

-bash-2.05b$ vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 0 0 176840 4336 20968 139780 0 0 384 108 121 294 0 0
100
0 0 0 176840 4336 20968 140164 0 0 384 0 130 281 0 1
99

tsdb=# explain analyze select * from table1 where id =
'007841017b9f7c80394f2bb4314ba8c1';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=19.576..19.587 rows=1 loops=1)
Index Cond: ((id)::text = '007841017b9f7c80394f2bb4314ba8c1'::text)
Total runtime: 19.854 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'0070724846c4d0d0dbb8f3e939fd1da4';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=10.616..10.624 rows=1 loops=1)
Index Cond: ((id)::text = '0070724846c4d0d0dbb8f3e939fd1da4'::text)
Total runtime: 10.795 ms
(3 rows)

tsdb=# explain analyze select * from table1 where id =
'00fc92bf0f5048d7680bd8fa2d4c6f3a';
QUERY PLAN
----------------------------------------------------------------------------
------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344)
(actual time=28.007..28.014 rows=1 loops=1)
Index Cond: ((id)::text = '00fc92bf0f5048d7680bd8fa2d4c6f3a'::text)
Total runtime: 28.183 ms
(3 rows)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

As it turns out. With vacuum_page_delay = 0, VACUUM took 1m20s (80s) to
complete, with vacuum_page_delay = 1 and vacuum_page_delay = 10, both
VACUUMs completed in 18m3s (1080 sec). A factor of 13 times! This is for a
single 350 MB table.

Apparently, it looks like the upcoming Linux kernel 2.6 will have a smaller
quantum:

http://go.jitbot.com/linux2.6-quantum

There is also mention of user-space tweak to get a more accurate time slice
of near 1ms on Linux, but I'm not sure how this works and if it applies to
Unixes:

http://go.jitbot.com/linux-devrtc-quantum

Regards, Stephen

"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:2254.1067713969@sss.pgh.pa.us...

"Stephen" <jleelim@xxxxxx.com> writes:

also as there are less processes waiting to complete. I find a value of

1ms

to 5ms is quite good and will keep system responsive. Going from 10ms to

1ms

didn't seem to reduce the total vacuum time by much and I'm not sure

why.

Jan Wieck <JanWieck@Yahoo.com> writes:

I am currently looking at implementing ARC as a replacement strategy. I
don't have anything that works yet, so I can't really tell what the
result would be and it might turn out that we want both features.

It's likely that we would. As someone (you?) already pointed out,
VACUUM has bad side-effects both in terms of cache flushing and in
terms of sheer I/O load. Those effects require different fixes AFAICS.

One thing that bothers me here is that I don't see how adjusting our
own buffer replacement strategy is going to do much of anything when
we cannot control the kernel's buffer replacement strategy. To get any
real traction we'd have to go back to the "take over most of RAM for
shared buffers" approach, which we already know to have a bunch of
severe disadvantages.

regards, tom lane

Not surprising, I should have thought. Why would you care that much?
The idea as I understand it is to improve the responsiveness of things
happening alongside vacuum ("real work"). I normally run vacuum when I
don't expect anything else much to be happening - but I don't care how
long it takes (within reason), especially if it isn't going to intefere
with other uses.

cheers

andrew

Stephen wrote:

Tom Lane kirjutas P, 02.11.2003 kell 20:00:

Jan Wieck <JanWieck@Yahoo.com> writes:

I am currently looking at implementing ARC as a replacement strategy. I
don't have anything that works yet, so I can't really tell what the
result would be and it might turn out that we want both features.

It's likely that we would. As someone (you?) already pointed out,
VACUUM has bad side-effects both in terms of cache flushing and in
terms of sheer I/O load. Those effects require different fixes AFAICS.

One thing that bothers me here is that I don't see how adjusting our
own buffer replacement strategy is going to do much of anything when
we cannot control the kernel's buffer replacement strategy.

At least for OpenSource/Free OS'es it would probably be possible to
persuade kernel developers to give the needed control to userspace apps.

So the "take over all RAM" is not the only option ;)

Centuries ago, Nostradamus foresaw when "Stephen" <jleelim@xxxxxxx.com> would write:

As it turns out. With vacuum_page_delay = 0, VACUUM took 1m20s (80s)
to complete, with vacuum_page_delay = 1 and vacuum_page_delay = 10,
both VACUUMs completed in 18m3s (1080 sec). A factor of 13 times!
This is for a single 350 MB table.

While it is unfortunate that the minimum quanta seems to commonly be
10ms, it doesn't strike me as an enormous difficulty from a practical
perspective.

Well, actually, the case where it _would_ be troublesome would be
where there was a combination of huge tables needing vacuuming and
smaller ones that are _heavily_ updated (e.g. - account balances),
where pg_autovacuum might take so long on some big tables that it
wouldn't get to the smaller ones often enough.

But even in that case, I'm not sure the loss of control is necessarily
a vital problem. It certainly means that the cost of vacuuming has a
strictly limited "degrading" effect on performance.

It might be mitigated by the VACUUM CACHE notion I have suggested,
where a Real Quick Vacuum would go through just the pages that are
cached in memory, which would likely be quite effective at dealing
with heavily-updated balance tables...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://www3.sympatico.ca/cbbrowne/sap.html
Rules of the Evil Overlord #212. "I will not send out battalions
composed wholly of robots or skeletons against heroes who have qualms
about killing living beings. <http://www.eviloverlord.com/&gt;

Tom Lane wrote:

Attached is an extremely crude prototype patch for making VACUUM delay
by a configurable amount between pages, in hopes of throttling its disk
bandwidth consumption. By default, there is no delay (so no change in
behavior). In some quick testing, setting vacuum_page_delay to 10
(milliseconds) seemed to greatly reduce a background VACUUM's impact
on pgbench timing on an underpowered machine. Of course, it also makes
VACUUM a lot slower, but that's probably not a serious concern for
background VACUUMs.

[SNIP]

The patch is against CVS tip, but should apply cleanly to any recent
7.4 beta. You could likely adapt it to 7.3 without much effort.

Will we have this on 7.4 ?
I tried it and improve a lot the responsiness of my queries just putting
the delay equal to 10 ms.

Regards
Gaetano Mendola