pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <gsstark@mit.edu>
Cc: Bryan Vest <bvest@bright.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Config review
Date: Tue, 07 Dec 2004 10:07:54 -0500
Message-ID: <15131.1102432074@sss.pgh.pa.us> (raw)
In-Reply-To: <87r7m2zgar.fsf@stark.xeocode.com>
References: <41B46F52.4000607@bright.net>
	<87r7m2zgar.fsf@stark.xeocode.com>

Greg Stark <gsstark@mit.edu> writes:
> Bryan Vest <bvest@bright.net> writes:
>> Currently the database is at a size of 87.6Gig. A Vacuum Analyze runs every
>> night and has been taking 4 or 5 hours to complete. Everything seems to run
>> fine for a while, then at some point the load goes through the roof and the
>> iowait % also goes way up. It will recover after a little bit and then do the
>> same thing all over again.

> While others have pointed out problems with the config I don't think any of
> them explains this irregular behaviour.

As Greg says, it might be checkpoints or a background query.  If it
actually is the vacuum itself causing the variation in load, the theory
that comes to mind is that the performance tanks when the vacuum run
switches from find-dead-tuples to clean-indexes mode; clean-indexes is
usually a lot more I/O intensive.

ISTM it actually doesn't matter much which of these explanations is
correct, because all three imply the same thing: not enough disk I/O
bandwidth.  The disk is near saturation already and any increase in
demand drives response time over the knee of the curve.

If you are using a RAID configuration it might just be that you need
to adjust the configuration (IIRC, there are some RAID setups that
are not very write-friendly).  Otherwise you may have little alternative
but to buy faster disks.

			regards, tom lane



view thread (4+ messages)  latest in thread

Message-ID: <15131.1102432074@sss.pgh.pa.us>
Permalink:  ../15131.1102432074@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/15131.1102432074@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, gsstark@mit.edu, bvest@bright.net
  Subject: Re: Config review
  In-Reply-To: <15131.1102432074@sss.pgh.pa.us>

* 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