Обсуждение: How much RAM is too much ?

A J <s5aly@yahoo.com> wrote:

> Cost aside, are there any technical factors to consider before
> increasing RAM (assuming the system can support it) ? Is there a
> sweet spot beyond which more RAM does not help Postgres or
> actually harms ?

I've never heard of putting more RAM in a machine hurting
performance, as long as it's the right type of RAM for that machine.
On the other hand, you can easily configure PostgreSQL to allocate
more RAM for specific purposes than is good.

> If my database is only couple of hundred Gigs, can I not just have
> RAM that big and have read from buffer all the time ?

Sure, as long as you allow enough extra for the OS and all other
processes.  If you're looking at hundreds of GB of RAM on a box,
plan on letting the OS use the vast majority of that for caching.

This doesn't obviate the need for a RAID controller with its own BBU
RAM if you want write performance to be good.

-Kevin

On Thu, Jul 22, 2010 at 2:45 PM, A J <s5aly@yahoo.com> wrote:
> Cost aside, are there any technical factors to consider before increasing
> RAM (assuming the system can support it) ? Is there a sweet spot beyond
> which more RAM does not help Postgres or actually harms ?
> If my database is only couple of hundred Gigs, can I not just have RAM that
> big and have read from buffer all the time ?

On some architectures, as you add memory the memory itself becomes
slower.  In the old days it was Intel's brain dead VX pentium
architecture that could only cache the first 64Megs of ram but could
handle 256M that had issues.  Since windows tended to load things from
the top of memory down, having > 64 Meg could make a machine
noticeably slower.

Nowadays it's nothing so drastic.  Some hardware can only support say
800MHz memory with 2 or 4 banks of DRAM, then 667, then 500 as you
fill them up.  I avoid hardware like that since dbs are often memory
hungry machines.

Most modern machines are actually faster as you add memory because it
can now address more banks at the same time, and with AMD and Intel's
latest creations more banks filled is almost always a win.  To the
point that having the same amount of memory, but using smaller DIMMs
makes the machine faster than using fewer, larger DIMMs.

Generally speaking, unless your hardware is the kind that acts up with
more memory, no, adding memory will almost never hurt.  But once you
can cache your entire db with some room to spare in memory, adding
memory won't usually help.

So, what CPU / Memory architecture are you running?

-- To understand recursion, one must first understand recursion.

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

FreeBSD has good kernel level support for DTrace
This is what it looks like on an amd64 FreeBSD stable-8 server with a
custom kernel and a few extra options. The kernel build is straightforward.

Kernel modules loaded on this box
 5    0xfff... ...   dtraceall.ko
 6    0xfff... ...   profile.ko
 7    0xfff... ...   opensolaris.ko
 8    0xfff... ...   cyclic.ko
 9    0xfff... ...   dtrace.ko
10    0xfff... ...   systrace.ko
11    0xfff... ...   sdt.ko
12    0xfff... ...   lockstat.ko
13    0xfff... ...   fbt.ko
14    0xfff... ...   dtnfsclient.ko
15    0xfff... ...   dtmalloc.ko

DTrace is showing 26218 probes and support for the following providers
dtrace dtmalloc nfsclient fbt lockstat mac mac_framework proc priv
callout_execute vfs syscall profile

Of the syscall probes, of which many are very useful for figuring out
what is going on with say a userland daemon that forks, execs, and
context switches back and forth,  dtrace is publishing entry / return
probes for read, write, and other syscalls.  There are function boundary
tracing probes for fsync in the kernel.

Thought you might find that interesting.

Bryan



On 7/22/10 6:48 PM, Greg Smith wrote:
> 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 <greg@2ndquadrant.com> writes:
> 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.

Well, there's debian/kFreeBSD too, which will be supported in the next
debian stable release I think.

  http://release.debian.org/squeeze/arch_qualify.html

Regards,
--
dim