What is NUMA locality impact on DB performance?
In the architecture of a modern multi-socket server, not all memory is created equal. If you are running a high-performance database, understanding NUMA (Non-Uniform Memory Access) is the difference between a lightning-fast system and one that feels inexplicably sluggish despite having top-tier hardware.
In a dual-socket server, each CPU "owns" a specific set of local memory slots. This grouping of a CPU and its physically attached RAM is called a NUMA Node.
Local Access: When CPU 0 accesses RAM in its own node, the data travels a very short distance.
Remote Access: When CPU 0 needs data stored in the RAM slots owned by CPU 1, it must travel across an interconnect (like Intel’s UPI or AMD’s Infinity Fabric).
The impact of NUMA on database performance comes down to two factors: Latency and Bandwidth.
The Latency Hit: Remote memory access can be 30% to 100% slower than local access. For a database performing millions of memory lookups per second, these nanoseconds add up to a massive bottleneck.
Interconnect Congestion: The "bridge" between CPUs has limited bandwidth. If both CPUs are constantly reaching across the bridge to grab data from each other's memory, the interconnect becomes saturated, slowing down the entire system.
Databases like Oracle, SQL Server, and PostgreSQL are "memory-hungry" by nature. They use a large shared memory area (like the Oracle SGA or SQL Server Buffer Pool).
If the OS isn't "NUMA-aware," it might place the entire database buffer on Node 0, while the CPU cores on Node 1 are doing half the work. This forces Node 1 to perform Remote Access for every single data block it processes.
If one NUMA node runs out of local memory, the OS might start "swapping" memory to disk—even if there is plenty of free RAM available on the other NUMA node. This is a common cause of "mystery" performance drops where the server seems to have free RAM but is still sluggish.
To minimize the NUMA impact, you need to ensure Locality: keeping the data as close to the processing core as possible.
BIOS Settings: Most servers allow you to enable "Node Interleaving." (Generally, for databases, you want this OFF so the DB can see the distinct nodes and make smart placement decisions).
Processor Affinity: You can "pin" specific database processes to specific CPU cores and their associated memory nodes.
Oracle Instance Caging: In Oracle environments, you can use parameters to limit an instance to a specific NUMA node, ensuring its memory and CPU stay "local" to each other.
Modern Database Features: SQL Server and Oracle have built-in NUMA awareness that attempts to partition the buffer cache across nodes automatically.
NUMA locality is the "hidden" layer of database tuning. As core counts increase and memory channels become more complex, ensuring your database processes aren't "reaching across the aisle" for their data is essential for maintaining high throughput and low latency.