Re: how big shmmax is good for Postgres...

I just wanted to add to my previous post that shared_memory generally
has a performance envelope of quickly increasing performance as you
first increase share_memory, then a smaller performance step with each
increase in shared_memory.  Once all of the working set of your data
fits, the return starts to fall off quickly.  Assuming you were on a
machine with infinite memory and there were no penalties for a large
shared_buffers, then you would go until you were comfortably in the
level area.  If your working set were 1G on a dedicated machine with
64G, you could assume that memory was functionally unlimited.
Somewhere around 1.1 Gigs of memory and you get no performance
increase.

In real life, maintaining a cache has costs too.  The kernel of most
OSes now caches data very well.  and it does it very well for large
chunks of data.  So on a machine that big, the OS would be caching the
whole dataset as well.

What we're interested in is the working set size.  If you typically
call up a couple k of data, band it around some and commit, then never
hit that section again for hours, and all your calls do that,  and the
access area is evenly spread across the database, then your working
set is the whole thing.  This is a common access pattern when dealing
with transactional systems.

If you commonly have 100 transactions doing that at once, then you
multiply much memory they use times 100 to get total buffers in use,
and the rest is likely NEVER going to get used.

In these systems, what seems like a bad idea, lowering the
buffer_size, might be the exact right call.

For session servers and large transactional systems, it's often best
to let the OS do the best caching of the most of the data, and have
enough shared buffers to handle 2-10 times the in memory data set
size.  This will result in a buffer size of a few hundred megabytes.

The advantage here is that the OS doesn't have to spend a lot of time
maintaining a large buffer pool and checkpoints are cheaper.  With
spare CPU the background writer can use spare I/O cycles to write out
the smaller number of dirty pages in shared_memory and the system runs
faster.

Same is true of session servers.  If a DB is just used for tracking
logged in users, it only needs a tiny amount of shared_buffers.

Conversely, when you need large numbers of shared_buffers is when you
have something like a large social networking site.  A LOT of people
updating a large data set at the same time likely need way more
shared_buffers to run well.  A user might be inputing data for several
minutes or even hours.  The same pages are getting hit over and over
too.  For this kind of app, you need as much memory as you can afford
to throw at the problem, and a semi fast large RAID array.  A large
cache means your RAID controller / array only have to write, on
average, as fast as the database commits it.

When you can reach the point where shared_buffers is larger than 1/2
your memory you're now using postgresql for caching more so than the
kernel.  As your shared_buffers goes past 75% of available memory you
now have three times as much cache under postgesql'l control than
under the OS's control.  This would mean you've already testing this
and found that postgresql's caching works better for you than the OS's
caching does.  Haven't seen that happen a lot.  Maybe some large
transactional systems would work well that way.

My point here, and I have one, is that larger shared_buffers only
makes sense if you can use them.  They can work against you in a few
different ways that aren't obvious up front.  Checkpointing, Self DOS
due to swap storm, using up memory that the kernel might be better at
using as cache, etc.

So the answer really is, do some realistic testing, with an eye
towards anamalous behavior.