Re: VACUUM degrades performance significantly. Database

Dann,

I already hashed the 5-way index under the column "id". Removing the 5-way
index didn't improve responsiveness, but setting elvtune on Linux did! The
5-way index is still needed for my purposes.

Thanks anyway.

Stephen :-)

""Dann Corbit"" <DCorbit(at)connx(dot)com> wrote in message
news:D90A5A6C612A39408103E6ECDD77B8294CE21E(at)voyager(dot)corporate(dot)connx(dot)com(dot)(dot)(dot)
> 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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>