pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: Logan Bowers <logan@datacurrent.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgresql meltdown on PlanetMath.org
Date: Sun, 16 Mar 2003 22:10:11 -0800
Message-ID: <20030317061011.GH23355@perrin.int.nxad.com> (raw)
In-Reply-To: <Pine.LNX.4.53.0303170059580.8767@neo.magick.org>
References: <20030316060125.GD19570@vt.edu>
	<3E741B93.1090203@joeconway.com>
	<20030316075206.GE19570@vt.edu>
	<Pine.LNX.4.53.0303170059580.8767@neo.magick.org>

> I don't know what your definition of "high" is, but I do find that
> turnover can degrade performance over time.  Perhaps one of the devs
> can enlighten me, but I have a database that turns over ~100,000
> rows/day that does appear to slowly get worse.  The updates are done
> in batches and I "VACUUM" and "VACUUM ANALYZE" after each batch
> (three/day) but I found that over time simple queries would start to
> hit the disk more and more.

Creeping index syndrome.  Tom recently fixed this in HEAD.  Try the
latest copy from the repo and see if this solves your problems.

> A "select count(*) FROM tblwordidx" initially took about 1 second to
> return a count of 2 million but after a few months it took several
> minutes of really hard HDD grinding.

That's because there are dead entries in the index that weren't being
reused or cleaned up.  As I said, this has been fixed.

-sc


PS  It's good to see you around again.  :)

-- 
Sean Chittenden



view thread (39+ messages)  latest in thread

Message-ID: <20030317061011.GH23355@perrin.int.nxad.com>
Permalink:  ../20030317061011.GH23355@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030317061011.GH23355@perrin.int.nxad.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-performance@postgresql.org
  Cc: sean@chittenden.org, logan@datacurrent.com
  Subject: Re: postgresql meltdown on PlanetMath.org
  In-Reply-To: <20030317061011.GH23355@perrin.int.nxad.com>
  References: <20030316060125.GD19570@vt.edu> <3E741B93.1090203@joeconway.com> <20030316075206.GE19570@vt.edu> <Pine.LNX.4.53.0303170059580.8767@neo.magick.org> <20030317061011.GH23355@perrin.int.nxad.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