Re: optimal hardware for postgres?

Linux 2.6 does have NUMA support. But whether it's actually a for
Postgres is debatable due to the architecture.

First let's take a look at how NUMA makes this run faster in a 2x
Opteron system. The idea is that the processes running on CPU0 can
access memory attached to that CPU a lot faster than memory attached to
CPU1. So in a NUMA-aware system, a process running on CPU0 can request
all it's memory be located memory bank0.

Now let's look at how Postgres manages memory. 1 big shared memory block
and an even bigger OS cache pool. Assuming the postgres decides to
follow the NUMA model -- the main process requests a big 150MB chunk of
shared memory on CPU0. All good and dandy at this point. So let's start
making connections to postgres. Now where do you put these processes? Do
you continue to pile these processes onto CPU0 where memory latency is
really low? Or do you use the idle CPU but accept the +40ns latency?

Continue on and think about the cache. One of the design philosphies of
Postgres is that the OS manages the memory. So you've got 7GB of memory
left over -- 3GB is on CPU0 and 4GB on CPU1. When you happen to hit
stuff cached on CPU0, you get extremely fast queries. When you hit stuff
on CPU1, your queries are much slower (although still way faster than
disk).

If your dataset is small and your multi-user load is low (e.g. you have
8GB of RAM but you only need to use 4GB to do all your stuff), Linux can
probably put all your processes onto the "fast" CPU so NUMA could be a
win. But otherwse, what you'd probably end up with is uneven performance
-- sometimes very fast and sometimes very slow. Would you prefer this
kind of behavior over interleaved memory access where everything
performs in the middle? Dunno. Would definitely depend on the server
usage pattern.

Guy Rouillier wrote:
> William Yu wrote:
>
>
>>On other note -- if you are thinking about SMP Opteron, you may
>>actually get better performance from 1x275 (Dual Core 2.2ghz) versus
>>2x248 (2.2ghz). Full duals have twice the bandwidth but without good
>>NUMA support, memory has to be interleaved between CPUs.
>
>
> I thought the 2.6 kernel had good NUMA support? (I realize I just made
> an assumption that the original poster was running PG on Linux 2.6.)
>