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@xxxxxxxxxxxxxxx www.2ndQuadrant.us -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
