Re: VACUUM degrades performance significantly. Database

If you are always looking for exact matches, I would suggest
experimenting with a hashed index.

The character fields of your index are very long, and it may be
beneficial to try hashing as an alternative.

Of course, if you need to do greater than, less than, between sorts of
queries, the hashed index simply won't work.

> -----Original Message-----
> From: Stephen [mailto:jleelim(at)xxxxxx(dot)com]
> Sent: Wednesday, October 15, 2003 12:27 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index
> scan if your
> joining column's datatypes do not match
>