NUMA aware memory allocations.

In a system with more one NUMA node sometimes best to have all memory allocated on the first NUMA node for performance.

E.g. With 2 NUMA nodes with 300Gb each, pin the lowest number CPU VPs pinned to the first NUMA node and allocate all hugepages on the first NUMA node. As the lowest number CPU VPs are used most, this ensures most memory references are local.

However update statistics can require large amounts of memory for sorting.

If only memory on the first NUMA node is used and the first NUMA node does not contain enough memory the update statistics can generate

large spills to temp e.g. 100GB for some tables.

If memory is allocated on multiple NUMA nodes there is currently no way to control which numa nodes are used for memory allocations.

It would be best if user sessions can be guaranteed to allocate memory from the first NUMA node as normal and only the update statistics

allocates memory from the 2nd NUMA node rather than spilling to temp. This would avoid 100GB spills to temp with associated i/o impact.

With this in mind the following onconfig extensions are suggested:

# Default numa node for memory allocated included user sessions

SHMVIRT_DEFAULT_NUMA_NODE 0

# Control how memory from the virtual portion is allocated across NUMA nodes.

SHMVIRTSIZE 209715200:0,104857600:1

# Control placement of bufferpools on NUMA nodes

BUFFERPOOL size=8k,buffers=262144:0,buffers=131072:1,lrus=16,lru_min_dirty=0.15,lru_max_dirty=0.40

The update statistics can then use the following Sql extension to ensure it's memory is allocated from the 2nd NUMA node:

SET SESSION NUMA_NODE 1