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)
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
