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

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
>