pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: ogjunk-pgjedan@yahoo.com
To: pgsql-admin@postgresql.org
Subject: Re: VACUUMing for 30 minutes
Date: Wed, 22 Dec 2004 08:38:43 -0800 (PST)
Message-ID: <20041222163844.93592.qmail@web12707.mail.yahoo.com> (raw)
In-Reply-To: <4284.1103732076@sss.pgh.pa.us>

Hello,

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> <ogjunk-pgjedan@yahoo.com> writes:
> > VACUUMing this DB takes about 30 minutes, and during that time the
> DB
> > is pretty unresponsive, although the PG process is not using a lot
> of
> > CPU (load ~ 1) nor memory (~20MB for the VACUUM process).
> 
> How big is the DB physically ("du $PGDATA" results)?

4.2 GB:

# du -h ~postgres/data
3.6M    /var/lib/pgsql/data/base/1
3.6M    /var/lib/pgsql/data/base/16975
4.0K    /var/lib/pgsql/data/base/16976/pgsql_tmp
4.1G    /var/lib/pgsql/data/base/16976
4.1G    /var/lib/pgsql/data/base
152K    /var/lib/pgsql/data/global
129M    /var/lib/pgsql/data/pg_xlog
1.1M    /var/lib/pgsql/data/pg_clog
4.2G    /var/lib/pgsql/data

> If you've been lax
> about vacuuming or not had your FSM parameters set high enough, there
> could be a whole lot of dead space for VACUUM to scan through.

I've been vacuuming every night, like a good DBwife.

> If so,
> VACUUM FULL or possibly CLUSTER would be the best way to re-compact
> the
> tables.  (VACUUM VERBOSE on your larger tables would be another way
> to
> investigate this.)

I will try VACUUM VERBOSE on the biggest (and most active) table
tonight and report the findings.

> The other possibility is that you have a seriously slow disk drive
> :-(

It looks like I have an ATA-6 drive with only 2MB cache, and the
following throughput:

# /sbin/hdparm -tT /dev/hda
                                                                       
                                                     
/dev/hda:
 Timing buffer-cache reads:   128 MB in  0.56 seconds =228.57 MB/sec
 Timing buffered disk reads:  64 MB in  1.18 seconds = 54.24 MB/sec

Not SCSI, not RAID, but not the slowest HDD on the continent.

Thanks,
Otis




view thread (7+ messages)  latest in thread

Message-ID: <20041222163844.93592.qmail@web12707.mail.yahoo.com>
Permalink:  ../20041222163844.93592.qmail@web12707.mail.yahoo.com/
Also on:    postgresql.org/message-id/20041222163844.93592.qmail@web12707.mail.yahoo.com

 · 

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-admin@postgresql.org
  Cc: ogjunk-pgjedan@yahoo.com
  Subject: Re: VACUUMing for 30 minutes
  In-Reply-To: <20041222163844.93592.qmail@web12707.mail.yahoo.com>
  References: <4284.1103732076@sss.pgh.pa.us> <20041222163844.93592.qmail@web12707.mail.yahoo.com>

* 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