Re: Experimental patch for inter-page delay in VACUUM

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(at)Yahoo(dot)com #