pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Bill Moran <wmoran@collaborativefusion.com>
To: Rafael Martinez <r.m.guerrero@usit.uio.no>
Cc: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Need to run CLUSTER to keep performance
Date: Thu, 8 Nov 2007 10:17:11 -0500
Message-ID: <20071108101711.71bd7a31.wmoran@collaborativefusion.com> (raw)
In-Reply-To: <47332200.9050705@usit.uio.no>
References: <4732E6B4.1030907@usit.uio.no>
	<4732EF07.3060807@enterprisedb.com>
	<47332200.9050705@usit.uio.no>

In response to Rafael Martinez <r.m.guerrero@usit.uio.no>:

> Heikki Linnakangas wrote:
> > Rafael Martinez wrote:
> 
> >> The tables with this 'problem' are not big, so CLUSTER finnish very fast
> >> and it does not have an impact in the access because of locking. But we
> >> wonder why this happens.
> > 
> > 2 seconds for seq scanning 12 MB worth of data sounds like a lot. Have
> > you increased shared_buffers from the default? Which operating system
> > are you using? Shared memory access is known to be slower on Windows.
> > 
> 
> This is a server with 8GB of ram, we are using 25% as shared_buffers.
> Linux RHELAS4 with a 2.6.9-55.0.9.ELsmp kernel / x86_64.
> 
> > On a small table like that you could run VACUUM every few minutes
> > without much impact on performance. That should keep the table size in
> > check.
> > 
> 
> Ok, we run VACUUM ANALYZE only one time a day, every night. But we would
> espect the performance to get ok again after running vacuum, and it
> doesn't. Only CLUSTER helps.

If you have a large value for max_fsm_pages, but only vacuum once a day,
you could end up with considerable bloat on a small table, but not enough
to exceed max_fsm_pages (thus you wouldn't see any warning/errors)

I recommend either:
a) autovaccum, with aggressive settings for that table
b) a more aggressive schedule for that particular table, maybe a cron
   that vacuums that table every 5 minutes.

You could also do a combination, i.e. enable autovacuum with conservative
settings and set a cron to vacuum the table every 10 minutes.

Vacuuming once a day is usually only enough if you have very minimal
updates.

-- 
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023



view thread (21+ messages)  latest in thread

Message-ID: <20071108101711.71bd7a31.wmoran@collaborativefusion.com>
Permalink:  ../20071108101711.71bd7a31.wmoran@collaborativefusion.com/
Also on:    postgresql.org/message-id/20071108101711.71bd7a31.wmoran@collaborativefusion.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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: wmoran@collaborativefusion.com, r.m.guerrero@usit.uio.no, heikki@enterprisedb.com
  Subject: Re: Need to run CLUSTER to keep performance
  In-Reply-To: <20071108101711.71bd7a31.wmoran@collaborativefusion.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