Re: Database size Vs performance degradation - Mailing list pgsql-performance

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: July 30, 2008 8:28 AM
To: Dave North
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Database size Vs performance degradation

Dave North wrote:
> Morning folks,
>     Long time listener, first time poster.

Hi Dave

> Postgres 8.1.8
>     shared_buffers = 2000
>     max_fsm_pages = 400000
> Redhat Enterprise 4
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1 Also running

> on the server is a tomcat web server and other ancillaries

The value of 2000 seems a bit low for shared_buffers perhaps. Oh, and
8.1.13 seems to be the latest bugfix for 8.1 too.

DN: Yeah, I was thinking the same.  I spent several hours reading info
on this list and other places and it's highly inconclusive about having
high or low shared buffs Vs letting the OS disk cache handle it.

> Now, the problem.  We have an application that continually writes a
> bunch of data to a few tables which is then deleted by a batch job
> each night.  We're adding around 80,000 rows to one table per day and
> removing around 75,000 that are deemed to be "unimportant".
[snip]
> We had this problem around a month ago and again yesterday.  Because
> the application needs reasonably high availability, we couldn't full
> vacuum so what we did was a dump and load to another system.  What I
> found here was that after the load, the DB size was around 2.7GB - a
> decrease of 5GB.  Re-loading this back onto the main system, and the
world is good.

Well, that's pretty much the definition of bloat. Are you sure you're
vacuuming enough?

DN: Well, the auto-vac is kicking off pretty darn frequently...around
once every 2 minutes.  However, you just made me think of the obvious -
is it actually doing anything?!  The app is pretty darn write intensive
so I wonder if it's actually able to vacuum the tables?

 I don't have an 8.1 to hand at the moment, but a "vacuum verbose" in
8.2+ gives some details at the end about how many free-space slots need
to be tracked. Presumably you're not tracking enough of them, or your
vacuuming isn't actually taking place.

DN: I think you've hit it.  Now the next obvious problem is how to make
the vac actually vac while maintaining a running system?

Check the size of your database every night. It will rise from 2.7GB,
but it should stay roughly static (apart from whatever data you add of
course). If you can keep it so that most of the working-set of your
database fits in RAM speed will stay just fine.

DN: Yep, I'm just implementing a size tracker now to keep a track on it.
It grew from the 2.5GB to 7GB in around a month so it's pretty easy to
see big jumps I'd say.  Does the auto-vac log it's results somewhere by
any chance do you know?

Fantastic post, thanks so much.

Dave

> Yes, I know we need to upgrade to 8.3 but that's going to take some
> time
> :)

I think you'll like some of the improvements, but it's probably more
important to get 8.1.13 installed soon-ish.

--
   Richard Huxton
   Archonet Ltd