From: Aaron Krowne <akrowne@vt.edu>
To: pgsql-performance@postgresql.org
Cc: akrowne@vt.edu
Subject: postgresql meltdown on PlanetMath.org
Date: Sun, 16 Mar 2003 01:01:25 -0500
Message-ID: <20030316060125.GD19570@vt.edu> (raw)
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
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: akrowne@vt.edu
Subject: Re: postgresql meltdown on PlanetMath.org
In-Reply-To: <20030316060125.GD19570@vt.edu>
References: <20030316060125.GD19570@vt.edu>
* 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