Re: How much RAM is too much ?

Scott Marlowe wrote:
> On some architectures, as you add memory the memory itself becomes
> slower....Nowadays it's nothing so drastic.

Last week I saw an Intel system where the measured memory speed dropped
about 30% when going from half filled (36GB) to completely filled
(72GB).  So it can still be pretty drastic.  That only turned into about
a 10% drop in actual throughput running the database though, which was
more than made up for by reduced reading from the disk due to the extra
caching.

Stepping back from that trivia, generally adding memory helps only when
the working size of the data you access frequently didn't fit in there
already.  The working set and the total database size are loosely
related, but not directly.  Other than these weird cases where memory
slows down when there's too much of it due to motherboard limitations,
there are few situations where adding more RAM hurts performance.

However, note that the amount of memory you dedicated to the
database--what the shared_buffers tunable controls--has been reported by
multiple people I've talked with to top out at somewhere around 8GB of
space.  If you increase that parameter beyond that size, it could end up
hurting performance rather than helping.  Since PostgreSQL also uses the
operating system cache, this doesn't make for a real limit in the amount
of memory the database can use.  You just shouldn't give much more than
this amount to the database.

A second concern to be aware of is that if you're using Linux in
particular, it's easy to get the operating system in a state where it
has cached way too many writes, which causes upticks in latency when the
database issues its periodic checkpoints.  I can easily make a Linux
system with 72GB of RAM pause regularly for >30 seconds if I try to
trigger this behavior.  If you write heavily to the database, it's
something to be concerned about with that OS and large memory systems.

In news I don't consider unrelated, FreeBSD is now working on adding
DTrace support:
http://freebsdfoundation.blogspot.com/2010/06/dtrace-userland-project.html
which will give me yet another reason to consider deploying on that OS
instead of Linux.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us