pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Baker <max@warped.org>
To: pgsql-performance@postgresql.org
Subject: Vacuum takes a really long time, vacuum full required
Date: Tue, 19 Oct 2004 11:38:21 -0400
Message-ID: <20041019153821.GA21258@warped.org> (raw)

Hi Folks,

This is my _4th_ time trying to post this, me and the mailing list software
are fighting.  I think it's because of the attachments so I'll just put
links to them instead.  All apologies if this gets duplicated.

I've been having problems maintaining the speed of the database in the
long run.  VACUUMs of the main tables happen a few times a day after maybe
50,000 or less rows are added and deleted (say 6 times a day).

I have a whole lot (probably too much) indexing going on to try to speed
things up. 

Whatever the case, the database still slows down to a halt after a month or
so, and I have to go in and shut everything down and do a VACUUM FULL by
hand.  One index (of many many) takes 2000 seconds to vacuum.  The whole
process takes a few hours.

I would love suggestions on what I can do either inside my application, or
from a dba point of view to keep the database maintained without having to
inflict downtime.  This is for 'Netdisco' -- an open source network
management software by the way.  I'ld like to fix this for everyone who uses
it.


Sys Info :

$ uname -a
    FreeBSD xxxx.ucsc.edu 4.10-STABLE FreeBSD 4.10-STABLE #0: Mon Aug 16
14:56:19 PDT 2004     root@xxxx.ucsc.edu:/usr/src/sys/compile/xxxx  i386

$ pg_config --version
    PostgreSQL 7.3.2

$ cat postgresql.conf
    max_connections = 32
    shared_buffers = 3900           # 30Mb - Bsd current kernel limit
    max_fsm_relations = 1000        # min 10, fsm is free space map, ~40 bytes
    max_fsm_pages = 10000           # min 1000, fsm is free space map, ~6 bytes
    max_locks_per_transaction = 64  # min 10
    wal_buffers = 8                 # min 4, typically 8KB each

The log of the vacuum and the db schema could not be attached, so they are
at : 
    http://netdisco.net/db_vacuum.txt
    http://netdisco.net/pg_all.input

Thanks for any help!
-m



view thread (5+ messages)  latest in thread

Message-ID: <20041019153821.GA21258@warped.org>
Permalink:  ../20041019153821.GA21258@warped.org/
Also on:    postgresql.org/message-id/20041019153821.GA21258@warped.org

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: max@warped.org
  Subject: Re: Vacuum takes a really long time, vacuum full required
  In-Reply-To: <20041019153821.GA21258@warped.org>

* 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