PostgreSQL and NUMA, Part 3 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.

This entry covers the libnuma patch set by Tomas Vondra which was committed for PostgreSQL 18 and how this can be expected to improve performance further on NUMA systems.

Quick Recap

NUMA provides an approach to parallelizing memory access by dividing memory up between memory controllers, each of which assures serial access to the memory in question. Memory controllers can request memory from each other and some operating systems, such as Linux, support moving memory pages between memory controllers. One way to think about NUMA is that it sets up memory access as a sort of network between groups of processors. For more on how NUMA works, see “An Introduction to NUMA” earlier in this series.

On NUMA systems, we usually want to run PostgreSQL with huge pages enabled in order to allow the checkpointer to move to the shared memory portions it is working on. We then usually prefer to use an interleave NUMA policy for the database as a whole since this maximizes throughput and reduces contention for specific buffer pages. For more on this topic, please see “NUMA, Linux, and PostgreSQL before libnuma Support” also from this series.

The Problem of the Interleave Policy

The interleave NUMA policy has two important downsides, one of which is addressed by Tomas’s patch. The first is that  local memory allocations will be distributed around the NUMA system, meaning that a process which allocates memory only for its own use is likely to have a performance penalty in accessing that memory.

The second problem is that the difference in page sizes means that interleaving the shared buffers splits buffer pages between NUMA nodes unless huge pages are used. This imposes extra performance penalties on all processes, but has a particularly bad impact on the checkpointer, and the checkpointer in turn can slow down the entire PostgreSQL instance.

The General Approach

Tomas Vondra’s patches do basically two things: they micromanage NUMA policies for certain sets of allocations, and they break up at least one large shared resource into smaller elements which can be spread around the memory of the system.

Shared resources are interleaved, while localized resources are set to a local allocation policy.

Additionally a new system view called pg_shmem_allocations_numa was added by Jakub Wartak in a separate patch set to show where memory is stored, but this may be subject to an observer effect and therefore should be treated with care.

Interleaving of Shared Resources

Because shared resources are accessed by a large number of different processes, running on different NUMA nodes, they need to be interleaved. Otherwise a single memory controller will have to govern all access, and many processes will have to access the memory indirectly.

Shared Buffers and Freelists

Tomas’s patch explicitly interleaves shared buffers. This means that regardless of what NUMA policy is in effect when PostgreSQL starts, if it is built with libnuma support, the buffers will be interleaved across all available nodes.

As discussed in previous articles in this series, this has some very unpleasant side effects if huge pages are not used because buffer pages span Linux pages and therefore will get split and stored on memory controlled by different memory controllers. However this can be solved by using huge pages so that groups of buffer pages are instead grouped in a single OS level allocation.

Tomas’s patch also broke up the freelist into smaller lists, and distributed them on NUMA nodes more evenly. These store which buffer pages are available for being evicted from the shared buffers and replaced with new pages on demand. This also reduces lock contention and improves performance on NUMA systems, as the freelist chunks are also interleaved.

ProcArray (renamed as PGProc and no longer an array)

The procarray structure has been moved from an array to a linked list so that it can be spread out across different NUMA nodes and interleaved.

This data structure contains a number of pieces of information relating to the currently running backend. This includes information about the earliest visible transaction ID (needed for vacuuming tables) and the fastpath locking, and other information that may need to be viewed by other running backends (much of the information in pg_stat_activity comes from this shared resource).

Of these, fastpath locking demands some explanation. The fastpath lock system, introduced in 9.2, allows weak locks (AccessShare locks and row locks) to be stored outside the global lock table as long as there is no conflicting strong lock requested. This reduces the amount of weak locks that must be checked during read-heavy operations. Moving these to a variable length chunked hash table removes several important bottlenecks in the fastpath handling, but it also means that initially the fastpath allocations will not be on the same NUMA node as the PGPROC entry, and this further complicates performance.

If connections are long-standing or PGPROC entries are mostly used, we would expect page migrations to eventually move the fastpath allocations to the same NUMA node as the PGPROC allocation. Since backends make an effort to choose a PGPROC entry on their NUMA node, this should over time improve performance. However large numbers of short-lived connections or very bursty workloads may not take advantage of these page migrations and therefore it is even more important to prefer the use of connection poolers over bursty workloads.

Localization of Backend Memory

A second major area of Tomas’s patch set is the localization of backend local allocations.  Without libnuma support (and always before version 18), interleaving allocations would mean that backend allocations are unlikely to be allocated on the same NUMA node as the running backend, and this adds a performance penalty on memory access until pages and/or processes are migrated to be next to each other.

This eliminates the worst of the problems with setting the NUMA policy to interleave.

Recommendations

The previous recommendations continue to apply but with modified explanations. Several new recommendations are added for PostgreSQL 18 as well.

Use Interleave Policy

While Tomas’s patches address the most important shared resources, there may be other behavior not covered by his patches. In the mean time, it is still prudent to start PostgreSQL with an interleave policy on Linux systems. Until more testing and experience can be had, it is prudent to continue this same policy as before, though I do not have any specific knowledge as to how these may be affected and have not tested how processes are distributed across NUMA nodes with his patches.

Use libnuma builds where available

The libnuma support addresses some important bottlenecks when running Postgres on large systems. In particular the ability to distribute shared resources while localizing backend-local allocations should improve performance dramatically.

Enable Huge Pages

With libnuma support, buffer pages are always distributed, and since buffer pages span multiple kernel pages by default, without huge pages being used, they will be split across NUMA nodes.  Therefore it is even more critical to use huge pages on PostgreSQL 18 with libnuma support than it was before anytime Postgres is running on a system with multiple NUMA nodes.

Once again, this is not the same as the Linux kernel feature of Transparent Huge Pages which is generally discouraged due to some poor performance reports and so should be used cautiously if at all.

Prefer Long-Running Connections

Long-running, frequently used connections will help ensure that eventually the PGPROC and the related fastpath allocations will eventually be on the same NUMA node and thus optimize for access latency.

Connection poolers, such as pgbouncer, can be helpful in managing this aspect to the system, as can application connection pools, and more.

The next article in this series will cover running PostgreSQL on Docker containers spanning multiple NUMA domains. That article adds an additional dimension to this problem and builds on this and previous articles in this series. Stay tuned.