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