postgresql meltdown on PlanetMath.org

Hi all.

As the topic suggests, I am having fairly critical troubles with
postgresql on PlanetMath.org (a site which I run).  You can go there and
try to pull up some entries and you will see the problem: everything is
incredibly slow.

It is hard to pinpoint when this began happening, but I've tried a
variety of optimizations to fix it, all of which have failed.

First: the machine.  The machine is not too spectactular, but it is not
so bad that the performance currently witnessed should be happening.  It
is a dual PIII-650 with 512MB of RAM and a 20gb IDE drive (yes, DMA is
on).  There is plenty of free space on the drive.

Now, the optimisations I have tried:

- Using hash indices everywhere.  A few months ago, I did this, and
  there was a dramatic and instant speed up.  However, this began
  degenerating.  I also noticed in the logs that there was deadlock
  happening all over the place.  The server response time was
  intolerable so I figured the deadlock might have something to do with
  this, and eliminated all hash indices (replaced with normal BTree
  indices).

- Going back to BTrees yielded a temporary respite, but soon enough the
  server was back to half a minute to pull up an already-cached entry,
  which is of course crazy.

- I then tried increasing the machines shared memory max to 75% of the
  physical memory, and scaled postgresql's buffers accordingly.  This
  also sped things up for a while, but again resulted in eventual
  degeneration.  Even worse, there were occasional crashes due to
  running out of memory that (according to my calculations) shouldn't
  have been happening.

- Lastly, I tried reducing the shared memory max and limiting postgresql
  to more conservative values, although still not to the out-of-box
  values.  Right now shared memory max on the system is 128mb,
  postgres's shared buffers are at 64mb, sort_mem is at 16mb, and
  effective cache size is at 10mb.

For perspective, the size of the PlanetMath database dump is 24mb.  It
should be able to fit in memory easily, so I'm not sure what I'm doing
wrong regarding the caching.

For the most trivial request, Postgresql takes up basically all the CPU
for the duration of the request.  The load average of the machine is
over-unity at all times, sometimes as bad as being the 30's.  None of
this happens without postgres running, so it is definitely the culprit.

The site averages about one hit every twenty seconds.  This should not
be an overwhelming load, especially for what is just pulling up cached
information 99% of the time.

Given this scenario, can anyone advise?  I am particularly puzzled as to
why everything I tried initially helped, but always degenerated rather
rapidly to a near standstill.  It seems to me that everything should be
able to be cached in memory with no problem, perhaps I need to force
this more explicitly.

My next step, if I cannot fix this, is to try mysql =(

Anyway, whoever helps would be doing a great service to many who use
PlanetMath =)  It'd be much appreciated.

Aaron Krowne