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@connx.com> wrote in message
news:D90A5A6C612A39408103E6ECDD77B8294CE21E@voyager.corporate.connx.com...
> 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@xxxxxx.com]
> > Sent: Wednesday, October 15, 2003 12:27 PM
> > To: pgsql-general@postgresql.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.marlowe@ihs.com> wrote in message
> > news:Pine.LNX.4.33.0310151150580.23393-100000@css120.ihs.com...
> > > 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
>