pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Nagler <nagler@bivio.biz>
To: pgsql-performance@postgresql.org
Subject: vacuum locking
Date: Fri, 17 Oct 2003 08:48:30 -0600
Message-ID: <16272.318.870000.93584@gargle.gargle.HOWL> (raw)

It seems a simple "vacuum" (not full or analyze) slows down the
database dramatically.  I am running vacuum every 15 minutes, but it
takes about 5 minutes to run even after a fresh import.  Even with
vacuuming every 15 minutes, I'm not sure vacuuming is working
properly.

There are a lot of updates.  The slowest relation is the primary key
index, which is composed of a sequence.  I've appended a csv with the
parsed output from vacuum.  The page counts are growing way too fast
imo.  I believe this is caused by the updates, and index pages not
getting re-used.  The index values aren't changing, but other values
in the table are.

Any suggestions how to make vacuuming more effective and reducing the
time it takes to vacuum?  I'd settle for less frequent vacuuming or
perhaps index rebuilding.  The database can be re-imported in about an
hour.

Rob
----------------------------------------------------------------
Spacing every 15 minutes
Pages,Tuples,Deleted
7974,1029258,1536
7979,1025951,4336
7979,1026129,52
7979,1025618,686
7979,1025520,152
7980,1025583,28
7995,1028008,6
8004,1030016,14
8010,1026149,4965
8012,1026684,6
8014,1025910,960
8020,1026812,114
8027,1027642,50
8031,1027913,362
8040,1028368,784
8046,1028454,1143
8049,1029155,6
8053,1029980,10
8065,1031506,24
8084,1029134,4804
8098,1031004,346
8103,1029412,3044
8118,1029736,1872
8141,1031643,1704
8150,1032597,286
8152,1033222,6
8159,1029436,4845
8165,1029987,712
8170,1030229,268
8176,1029568,1632
8189,1030136,1540
8218,1030915,3963
8255,1033049,4598
8297,1036583,3866
8308,1031412,8640
8315,1031987,1058
8325,1033892,6
8334,1030589,4625
8350,1031709,1040
8400,1033071,5946
8426,1031555,8368
8434,1031638,2240
8436,1031703,872
8442,1031891,612





view thread (45+ messages)  latest in thread

Message-ID: <16272.318.870000.93584@gargle.gargle.HOWL>
Permalink:  ../16272.318.870000.93584@gargle.gargle.HOWL/
Also on:    postgresql.org/message-id/16272.318.870000.93584@gargle.gargle.HOWL

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nagler@bivio.biz
  Subject: Re: vacuum locking
  In-Reply-To: <16272.318.870000.93584@gargle.gargle.HOWL>
  References: <16272.318.870000.93584@gargle.gargle.HOWL>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox