Re: VACUUM degrades performance significantly. Database

Scott,

I dropped the 5 way unique index and the VACUUM improved slightly. I ran
VACUUM, ANALYZE, VACUUM and queries repeatedly. The max response time seem
to have reduced to 1700 msec from 2300 msec. The higher load and vmstat
during VACUUM remained the same. It's still not enough to justify dropping
the index for my purposes.

tsdb=# explain analyze select * from table1 where id =
'3305b141837f065d673aa09cf382d331';
QUERY PLAN
----------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=346)
(actual time=1762.34..1762.37 rows=1 loops=1)
Index Cond: (id = '3305b141837f065d673aa09cf382d331'::character varying)
Total runtime: 1762.50 msec
(3 rows)

Regards,

Stephen

""scott.marlowe"" <scott(dot)marlowe(at)ihs(dot)com> wrote in message
news:Pine(dot)LNX(dot)4(dot)33(dot)0310151150580(dot)23393-100000(at)css120(dot)ihs(dot)com(dot)(dot)(dot)
> It sounds like you might be I/O bound. if you drop the 5 way unique index
> for a test, how do the vacuum and parallel select run?
>
> On Wed, 15 Oct 2003, Stephen wrote:
>
> > 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
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>