Re: VACUUM degrades performance significantly. Database

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
>