Re: Postgresql on SUN Server

Andrew Sullivan wrote:

> One thing that is very interesting about all of this is that the
> large shared buffers only exact their performance penalty over time.
> My hypothesis is that it has something to do with expiring buffers.
> In one test I performed, I set the buffer to 1G.  I then did a bunch
> of work on a data set that was close to 1G.  Speedy.  But when I
> finally went over 1G, everything slowed to a crawl.  This makes me
> believe that the problem is in the way records are added to or
> expired from the buffer.
>
> It was only one test, mind: I didn't have time to repeat it.  So it's
> just a bit of gossip and not a result.

Interesting. We encountered a similar issue, but on Red Hat Advanced
Server 2.1.

Specs:
Linux 2.4.9-e.3smp (Red Hat's tweaks on Linux 2.4.9)
Dual P4 Xeon 2.4GHz
1.5 GB of RAM
shared_buffers = 32768 (about 256 Megs)
PostgreSQL 7.3.2 compiled from source.
Never used swap.

Performance seems speedy initially, but after, a few days or some large
data migration and processing operations, things slowed to a crawl, esp.
on INSERTs. Little disk or CPU activity. Did the usual dead chicken
waving: VACUUM, ANALYZE, REINDEX, dump&restore, restart postgresql. No
luck. Reboot, and everything's back to normal. Annoying.

It's a repeatable phenomenon, though we can't figure out the cause: the
old-ish O.S., or the shared memory fragmentation(?) or just plain unlucky.

We've kept some vmstat and other details. Bug me if anyone's interested.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
   2:59pm  up 153 days,  5:46, 11 users,  load average: 3.81, 4.73, 5.65