VACUUM degrades performance significantly. Database becomes unusable!

Hello,

Is it normal for plain VACUUM on large table to degrade performance by over
9 times? My database becomes unusable when VACUUM runs. From reading
newsgroups, I thought VACUUM should only slow down by 10% to 15%. Other MVCC
databases like MySQL InnoDB can even VACUUM discretely (runs internally). Is
it my Linux system or is it PostgreSQL?

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. VACUUM takes 5m20s to
complete. I repeatedly ran the following tests while system is idling:

In normal operation:
====================
tsdb=# explain analyze select * from table1 where id =
'33a4e9b6eae09634f4ff3e6fa9280f6e';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=346)
(actual time=25.30..25.31 rows=1 loops=1)
Index Cond: (id = '33a4e9b6eae09634f4ff3e6fa9280f6e'::character varying)
Total runtime: 25.52 msec
(3 rows)

When VACUUM runs:
=================
tsdb=# explain analyze select * from table1 where id =
'336139b47b7faf09fc4d4f03680a4ce5';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=346)
(actual time=2290.07..2290.10 rows=1 loops=1)
Index Cond: (id = '336139b47b7faf09fc4d4f03680a4ce5'::character varying)
Total runtime: 2290.22 msec
(3 rows)

VACUUM output:
==============
tsdb=# VACUUM VERBOSE table1;
INFO: --Relation public.table1--
INFO: Pages 5887: Changed 0, Empty 0; Tup 132672: Vac 0, Keep 0, UnUsed
144.
Total CPU 0.28s/0.01u sec elapsed 36.08 sec.
INFO: --Relation pg_toast.pg_toast_12437088--
INFO: Pages 40495: Changed 0, Empty 0; Tup 197587: Vac 0, Keep 0, UnUsed
235.
Total CPU 1.73s/0.20u sec elapsed 233.91 sec.
VACUUM

vmstat while VACUUM'ing:
========================
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy
id
0 1 1 74420 6520 30616 405128 0 0 1280 0 287 487 0 1
99
0 1 0 74420 6520 30620 405168 0 0 1196 0 271 436 0 0
100
0 1 1 74420 6520 30620 405120 0 0 1496 4 289 491 0 3
97
0 1 1 74420 6520 30620 405208 0 0 1280 0 268 466 0 0
100
1 0 1 74420 6520 30620 405208 0 0 1280 0 288 482 0 1
99
1 0 1 74420 6520 30632 405200 0 0 1416 8 277 441 1 2
97
3 1 1 74416 6520 30632 405196 4 0 1284 0 284 473 0 3
97

PostgreSQL configuration (the only changes made):
=================================================
max_connections = 1024
shared_buffers = 2800
sort_mem = 8192
vacuum_mem = 8192
effective_cache_size = 32000

System:
=======
Hardware: AMD 1.2GHz Athlon 512MB SDRAM
OS: Redhat Linux 9.0 (kernel 2.4.20-8)
FS: EXT3 with Journalling mounted with noatime, UDMA5
Disk1 (Linux): IDE 20GB 7200 RPM Western Digital
Disk2 (PostgreSQL): IDE 120GB 7200 RPM Western Digital
PostgreSQL: 7.3.4

hdparm:
=======
/dev/hda: (Linux partition)
multcount = 16 (on)
IO_support = 1 (32-bit)
unmaskirq = 1 (on)
using_dma = 1 (on)
keepsettings = 0 (off)
readonly = 0 (off)
readahead = 8 (on)
geometry = 2498/255/63, sectors = 40132503, start = 0

/dev/hdc: (PostgreSQL partition)
multcount = 16 (on)
IO_support = 1 (32-bit)
unmaskirq = 1 (on)
using_dma = 1 (on)
keepsettings = 1 (on)
readonly = 0 (off)
readahead = 8 (on)
geometry = 232581/16/63, sectors = 234441648, start = 0

Schema:
=======
CREATE TABLE table1 (
id varchar(32) DEFAULT '' NOT NULL,
colname1 varchar(10) DEFAULT 'http' NOT
NULL,
colname2 varchar(300) DEFAULT '' NOT NULL,
colname3 varchar(5) DEFAULT '' NOT
NULL,
colname4 varchar(300) DEFAULT '' NOT
NULL,
colname5 varchar(300) DEFAULT '' NOT NULL,
colname6 integer DEFAULT 0 NOT
NULL,
colname7 integer DEFAULT 0 NOT
NULL,
colname8 integer DEFAULT 200 NOT NULL,
colname9 varchar(10) DEFAULT '' NOT
NULL,
colname10 varchar(10) DEFAULT '' NOT NULL,
colname11 varchar(100) DEFAULT '' NOT
NULL,
colname12 varchar(100) DEFAULT '' NOT NULL,
colname13 varchar(100) DEFAULT '' NOT NULL,
colname14 varchar(20) DEFAULT '' NOT NULL,
colname15 integer DEFAULT 640 NOT
NULL,
colname16 integer DEFAULT 480 NOT
NULL,
colname17 integer DEFAULT 120 NOT
NULL,
colname18 integer DEFAULT 90 NOT
NULL,
colname19 timestamp DEFAULT CURRENT_TIMESTAMP NOT
NULL,
colname20 timestamp DEFAULT CURRENT_TIMESTAMP NOT
NULL,
colname21 integer DEFAULT 0 NOT NULL,
colname22 integer DEFAULT 0 NOT NULL,
colname23 timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
colname24 integer DEFAULT 0 NOT
NULL,
colname25 integer DEFAULT 0 NOT NULL,
colname26 varchar(10) DEFAULT '' NOT
NULL,
colname28 varchar(10) DEFAULT '' NOT NULL,
colname29 varchar(10) DEFAULT 'jpeg' NOT
NULL,
colname30 varchar(20) DEFAULT '' NOT NULL,
colname31 bytea ,
PRIMARY KEY (id)
) WITHOUT OIDS

CREATE UNIQUE INDEX table1_idx_1 ON table1 (colname1, colname2, colname3,
colname4, colname5)

Sigh, :-(

Stephen