NUMA Architecture Tuning for MySQL & PostgreSQL on Dedicated Servers

When enterprise organizations in Pakistan upgrade mission-critical MySQL or PostgreSQL databases to dual-socket bare-metal dedicated servers (powered by dual Intel Xeon Scalable or dual AMD EPYC processors with 128 GB to 512 GB RAM), they anticipate massive linear performance gains. Instead, database administrators frequently encounter an baffling symptom: unexplained query latency spikes, sudden I/O pauses, and Linux kernel swapping—even when the server reports 50 GB of free physical memory.

This operational anomaly is known as the NUMA Latency Trap.

In multi-socket hardware architectures, memory access is not uniform. If your database daemon and its multi-gigabyte buffer pool are not explicitly tuned for Non-Uniform Memory Access (NUMA), the Linux kernel’s memory allocator can starve local CPU sockets, trigger aggressive zone reclaims, and route memory requests across high-latency cross-socket interconnects.

In this deep-dive systems engineering manual, we dissect NUMA hardware topologies, diagnose memory imbalances using numastat, and apply production tuning for MySQL and PostgreSQL.

---

1. Architectural Anatomy: The NUMA Topology

On single-socket servers, all CPU cores access system RAM via a shared memory bus with identical latency (Uniform Memory Access - UMA).

On dual-socket enterprise servers, physical RAM is divided into discrete NUMA Nodes, directly wired to the memory controllers of specific CPU sockets:

          [ CPU Socket 0 ] (NUMA Node 0)                [ CPU Socket 1 ] (NUMA Node 1)
          ┌────────────────────────────┐                ┌────────────────────────────┐
          │  32 Cores / 64 Threads     │                │  32 Cores / 64 Threads     │
          └─────────────┬──────────────┘                └─────────────┬──────────────┘
                        │                                             │
               Local Bus│ ~75ns Latency                      Local Bus│ ~75ns Latency
                        ▼                                             ▼
          ┌────────────────────────────┐                ┌────────────────────────────┐
          │  Node 0 RAM (128 GB DDR5)  │                │  Node 1 RAM (128 GB DDR5)  │
          └────────────────────────────┘                └────────────────────────────┘
                        ▲                                             ▲
                        │                                             │
                        └─────────[ Cross-Socket Interconnect ]───────┘
                                   Intel UPI / AMD Infinity Fabric
                                     Latency: ~190ns - 240ns (3x Slower!)

• Local Access: When Core 4 on Socket 0 accesses memory on Node 0, latency is approximately 75 nanoseconds.
• Remote Access: When Core 4 on Socket 0 accesses memory on Node 1, requests must cross the Intel Ultra Path Interconnect (UPI) or AMD Infinity Fabric. Latency jumps to 190 to 240 nanoseconds—a 300% performance degradation.

---

2. The NUMA Swapping Trap: Zone Reclaim Mode

By default, the Linux kernel operates on a “first-touch” policy: memory pages are allocated on the NUMA node of the CPU thread that first touches them.

When a database daemon initializes a massive 64 GB buffer pool during startup, the initial initialization thread might allocate all pages onto Node 0. As traffic surges:

1. Node 0’s physical memory fills up completely.
2. Even though Node 1 has 64 GB of completely unused RAM, the kernel evaluates Node 0 as exhausted.
3. If the kernel’s zone_reclaim_mode is enabled or misconfigured, the kernel aggressively attempts to reclaim local pages by flushing dirty database buffers to disk and paging to swap space, freezing active SQL transactions.

Verifying NUMA Topology via CLI

# Inspect physical NUMA nodes and memory distribution
numactl --hardware

# Example Output:
# available: 2 nodes (0-1)
# node 0 cpus: 0-31 64-95
# node 0 size: 130842 MB
# node 0 free: 4210 MB
# node 1 cpus: 32-63 96-127
# node 1 size: 130988 MB
# node 1 free: 89412 MB   <-- Extreme Imbalance!

---

3. Real-Time Telemetry: Diagnosing Memory Starvation with numastat

Monitor memory locality metrics for your database process:

# For MySQL
numastat -c mysqld

# For PostgreSQL
numastat -c postgres

Look closely at the following counters:

• numa_hit: Memory successfully allocated on the local node (ideal).
• numa_miss: Memory intended for local node that was forced to allocate remotely.
• numa_foreign: Memory allocated on this node because another node was full.

If numa_miss and numa_foreign exceed 10% of total allocations, your database is suffering from severe NUMA cross-talk penalty.

---

4. Production Tuning for MySQL and PostgreSQL

Step 1: Disable Kernel Zone Reclaim Globally

Ensure the Linux kernel allocates remote memory before ever attempting zone swapping:

# Check current setting
sysctl vm.zone_reclaim_mode

# Permanently disable zone reclaim (/etc/sysctl.d/99-numa.conf)
echo "vm.zone_reclaim_mode = 0" | sudo tee /etc/sysctl.d/99-numa.conf
sudo sysctl -p /etc/sysctl.d/99-numa.conf

Step 2: Configure MySQL InnoDB NUMA Interleaving

MySQL 8.0 and 8.4 feature native support for NUMA interleaving. When enabled, MySQL instructs the operating system to round-robin memory allocation across all available NUMA nodes evenly:

Edit your MySQL configuration file (/etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):

[mysqld]
# Enable native round-robin memory interleaving across all NUMA sockets
innodb_numa_interleave=ON

# Size buffer pool appropriately for multi-node distribution
innodb_buffer_pool_size=96G
innodb_buffer_pool_instances=16

Restart MySQL:

sudo systemctl restart mysqld

Step 3: Configure PostgreSQL NUMA Interleaving via Systemd

PostgreSQL does not currently have a native postgresql.conf parameter for NUMA. Instead, enforce memory interleaving at the process supervisor layer:

Create a systemd override for the PostgreSQL service:

sudo systemctl edit postgresql.service

Add the following configuration:

[Service]
ExecStart=
ExecStart=/usr/bin/numactl --interleave=all /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf

Reload and restart:

sudo systemctl daemon-reload
sudo systemctl restart postgresql

With --interleave=all, PostgreSQL distributes shared memory allocations evenly across Node 0 and Node 1. Memory bandwidth doubles because both memory bus controllers are active simultaneously, eliminating remote latency hot spots.

---

5. Storage and Hardware Synergies in Pakistan

NUMA optimization is only half the battle. To extract maximum throughput from your database nodes:

• Pair memory balancing with low-overhead CoW storage pools as covered in our guide on Btrfs vs ZFS on Bare-Metal Storage Servers.
• Protect multi-gigabyte memory arrays against bit-flip data corruption as detailed in ECC Memory & Multi-Bit Parity Errors in Dedicated Servers.

For fintech firms, e-commerce giants, and telecommunications databases in Pakistan requiring uncompromised query throughput, Nextgen delivers customized multi-socket Dedicated Servers in Pakistan and global enterprise Dedicated Servers optimized from the BIOS to the kernel layer.