Re: 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
>