PostgreSQL and NUMA, Part 4 of 4

This series covers the specifics of running PostgreSQL on large systems with many processors. My experience is that people spend months often learning the basics when confronted with the problem. This series tries to dispel these difficulties by providing a clear background into the topics in question. The hope is that future generations of database engineers and administrators don’t have to spend months figuring things out through trial and error.This article builds on the content of the previous articles in the series. It assumes both a general working knowledge of NUMA and how NUMA affects PostgreSQL in particular with regard to the specific PostgreSQL version you are running.Docker, Kubernetes, and other containerized technologies pose unique challenges for running PostgreSQL on NUMA systems, particularly on public cloud providers. This final article in this series covers the problems of doing so and a number of common solutions to making the most of PostgreSQL on such systems.Important Note: This article primarily covers Linux containers, whether on Docker, Kubernetes, or similar technologies. Other free UNIX-type operating systems, such as SmartOS, handle NUMA slightly differently and these may require some adjustment of the practices discussed here, though all such operating systems are broadly similar in these respects.

Quick Recap

NUMA architectures have CPU cores divided into “NUMA nodes” which are typically implemented by single memory controllers. These controllers divide the system memory among themselves. Cores which request memory in their local node go through their local memory controller. When they request memory outside their local node, their memory controller makes a request to the secondary node.On Linux (and many other operating systems), internal memory allocations are 4k in size by default and these are individually managed in terms of their memory space by the kernel. Huge pages are 2MB by default. As PostgreSQL’s shared buffers use 8kb pages (unless compiled with different options), a single shared buffer page is split across two operating system pages by default. If these allocations are interleaved, this means that each shared buffer page will be split between two different NUMA nodes.The Linux scheduler can move processes or threads to be local to frequently accessed memory or move frequently accessed memory to the process or thread’s NUMA node.PostgreSQL 18 has a number of optimizations for NUMA systems but these must be paired with the use of huge pages in order to get optimal performance.

Virtualization, VMs, and Containers

For many decades, technologies have existed to partition large computing systems into smaller computing systems. These technologies have typically existed either as virtualized hardware or virtualized software. The former case is familiar to anyone who has run virtual machines, while Linux containers, Illumos zones, and other technologies provide a virtualized software environment.Virtual environments provide a number of challenges in security, and in mapping local virtualized resources to the underlying physical resources. In general, it can be assumed that virtualized environments lose the ability to directly manage NUMA optimizations in PostgreSQL or other applications. That doesn’t mean that the problems don’t exist or that tuning isn’t required.Not sure how your PostgreSQL configuration holds up on cloud VMs or containers? A PostgreSQL Health Check reviews your configuration and performance and gives you a report with clear recommendations.In cloud environments it is common to run both forms of virtualization together. Often virtual machines running Linux are used to run containers. In this case a clear understanding of the limits of each recommendation is necessary.

Hypervisors and Hardware Virtualization

Hypervisors, such as KVM, provide a logical view of hardware to an independently running kernel, which then can run on what looks like an arbitrary hardware device. The hardware is dynamically mapped to the underlying hardware, with any necessary transformations. If the hardware can be directly mapped, it is. Hypervisors are capable of providing an arbitrary NUMA configuration to the hosted virtual machine, and so the NUMA topology on the virtual machine does not necessarily relate to the topology of the underlying virtual machine.Most cloud providers expose NUMA architectures on their larger virtual machines to the virtual machine in order to allow NUMA optimizations to be made by the application (in this case Postgres) as necessary. Small VMs may see only one NUMA node but the underlying scheduler may move both memory allocations and processes around, meaning that local access is not assured.

Containers and Operating System Virtualization

Instead of virtualizing the hardware system, we can virtualize an operating system and allow that operating system access to some portion of the system resources. In Linux this is handled by control groups (cgroups) and namespaces, and orchestrated by container management software such as containerd, Docker, and Kubernetes. Typically the namespace system allows a virtualized operating system to see a given set of files as its filesystem.Software solutions like Docker allow a layered approach to filesystem images, which allow operating system images to be composed by superimposing different filesystem images on top of each other.

Container Security

Virtualized operating systems, as we see in Linux Containers, share a running kernel. This poses important security challenges, as traditionally the root user may be able to do things like alter the kernel’s binary at runtime, such as live patching. If containers can live-patch the underlying kernel, they can escape and perhaps affect other containers or even perhaps install a rootkit on the underlying host.For this reason, container orchestration systems typically do not allow full root access from inside the container.

What are Capabilities?

Prior to the Linux Kernel 2.2 series, the root user was treated as a special case. Normal users had restrictions imposed on their use of the system which were bypassed by the root user. Starting with the 2.2 series in the late 1990s, the Linux kernel removed the special cases for the root user, instead providing the POSIX-standard “capabilities” system for managing special system permissions.With the advent of capability-based security, the root user ceased to be special in the eyes of the Linux kernel. Instead the root user simply was granted all capabilities by convention, though these could be removed from the root user or added to other users as needed.Capability-based security solves the problem of container escape through the use of the root user. Consequently, all container technologies restrict various POSIX capabilities in order to protect the system from a malicious user or container.The key capability on Linux relevant to setting NUMA policies is CAP_SYS_NICE which allows the ability to set CPU affinity, NUMA allocation policies, and related memory management functions.Note: on Illumos or SmartOS, the term “privileges” is used instead of “capabilities”.

Best Practices

On the VM Level

Always use Huge Pages if in the Cloud or in a VM

Because of the possible mismatch of underlying hardware and the view of it in the kernel, memory locality is harder to ensure by hypervisors. Using huge_pages in PostgreSQL is therefore always likely to lead to significant improvements in performance.

Check if NUMA is Exposed on the VM

Larger VMs often expose multiple NUMA nodes. In these cases, treat the VM like you would any other NUMA system. Often these VMs are pinned to specific NUMA nodes and therefore the architecture maps to the underlying architecture directly.

On the Container Level

Consider Pinning to NUMA Nodes

If you are running many databases in a virtual machine via Kubernetes or Docker, then consider pinning these to specific NUMA nodes if the virtual machine exposes them. This will ensure the best possible performance if your memory and processes fit within that node.

Consider enabling CAP_SYS_NICE

The CAP_SYS_NICE capability provides the ability to manage scheduling policies generally including setting scheduling priority, CPU affinity, and memory page location for arbitrary properties. In theory this is pretty safe since namespacing should prevent the container from seeing outside processes, but it does represent an additional possible security attack point should other vulnerabilities exist.CAP_SYS_NICE can be enabled on Docker by using the --cap-add flag to docker run or adding the capability to the cap_add list in the docker compose file.On Kubernetes, the additional capability is specified in the manifest.This concludes this series on NUMA and PostgreSQL. We hope you have found it useful.Running PostgreSQL on large VMs or in containers and not sure NUMA is working in your favour? Our PostgreSQL performance optimization team can review your setup.