Re: VACUUM degrades performance significantly. Database - Mailing list pgsql-general

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
>