Re: moving to PostgreSQL from MS-SQL and from Oracle, looking for feature comparison information

Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Maxim Boguk (maxim(dot)boguk(at)gmail(dot)com) wrote:

>>> 6. does PostgreSQL support NUMA on Intel based X64 servers and
>>> does it support Hyper-Threading ?
>>
>> No NUMA support. Yes PostgreSQL will work on HT enabled servers
>> (will it be efficient - depend on workload and CPU type).
>
> PostgreSQL doesn't need to directly support NUMA- the Linux
> kernel does. As for if we do anything special when running in a
> NUMA environment, no, not at this time. Ditto with
> hyper-threading.

Since each connection creates its own backend process, each
connection's local data in a NUMA environment tends to be allocated
from a "nearby" memory segment. So NUMA for a connection's private
memory is very efficiently handled by default. Where an issue
could possibly arise is in the shared memory, used for the page
buffers and interprocess communication (such as locks). If
allocations for that become unbalanced, the CPU controlling a
heavily used memory segment could become a bottleneck.

I did some investigation into NUMA issues with PostgreSQL after
seeing some NUMA performance issues on a machine with four memory
segments and a database which fit in about 1/4 of the machine's RAM
*and* the user pre-warmed the cache using a single process. I
found that by programming PostgreSQL to use interleaved mode for
shared memory *and* using an OS cpuset to interleave OS buffers and
cache I was able to get a consistent 2% to 3% performance increase
and prevent occasional (rare) "spikes" of bad performance. Almost
all of that was gained just by using the OS cpuset feature (which
does not require any change to PostgreSQL to do), so we didn't make
any change to PostgreSQL -- a sysadmin can manage interleaved
buffer allocation pretty easily if they need to.

If you were able to find a situation where NUMA issues within
PostgreSQL caused even a 1% hit, we could always revisit the issue.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company