VACUUM degrades performance significantly. Database becomes unusable! - Mailing list pgsql-general

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