Re: [PERFORMANCE] Buying hardware

david(at)lang(dot)hm wrote:
> On Mon, 26 Jan 2009, David Rees wrote:
>
>> And yes, the more memory you can squeeze into the machine, the better,
>> though you'll find that after a certain point, price starts going up
>> steeply. Of course, if you only have a 15GB database, once you reach
>> 16GB of memory you've pretty much hit the point of diminishing
>> returns.
>
> actually, you need more memory than that. besides the data itself you
> would want memory for several other things, among them:
>
> 1. your OS
> 2. your indexes
> 3. you per-request memory allocations (for sorting, etc)
> this is highly dependant on your workload (type and number of parallel
> requests)
> 4. 'dead' tuples in your table (that will be cleared by a vaccum, but
> haven't been yet)
>
> and probably other things as well.
>
> I don't know how large a database will fit in 16G of ram, but I suspect
> it's closer to 8G than 15G.
>
> any experts want to throw out a rule-of-thumb here?
>
> David Lang
>

It depends on what else the server is doing. If you're running the whole
LAPP stack on a single box, for example, the PHP interpreter will need
space for intermediate data. Apache and the Linux kernel will use less
space.

If PostgreSQL is the only thing running on the server, though, assuming
64-bit Linux, most of the RAM in a box that large should be in either
memory you've deliberately set aside for internal PostgreSQL data
structures or the Linux page cache.

There are starting to be some tools built that will show you how RAM is
allocated, now that recent kernels (2.6.25+) do a better job of
accounting for RAM pages. So I would expect the total memory dedicated
to the database functionality to be much closer to 15 GB than 8 GB.

Given large amounts of RAM and only PostgreSQL running in the server,
the interesting trade-offs become

a. How little memory can you buy without putting your service level
agreements at risk?

b. How do you allocate the PostgreSQL-specific memory buffers at the
expense of the Linux page cache for optimum performance?
--
M. Edward (Ed) Borasky

I've never met a happy clam. In fact, most of them were pretty steamed.