On Wednesday, 6 March 2024, we hosted an insightful session led by our PostgreSQL Principal Consultant, Chris Travers, on Understanding NUMA policies and their impact on PostgreSQL performance.
The Q&A session had intriguing questions asked by the audience that are listed below:
Q1. At what point we need to focus on ensuring huge_pages in PostgreSQL?
There are a couple of factors here. The first is, that if you’re able to show that you have multiple NUMA domains, it will almost always be a win performance-wise. But it becomes critical at the point where you start seeing that the checkpointer is running at 100 percent CPU load, and none of your queries are running at 100 percent CPU load, especially if you don’t have a lot of IO weight. That’s a really good indication that you’ve hit a point where it’s now a heavy bottleneck, and that’s a point where it’s starting to become something where you’re going to see a very large win out of it. Usually, if you have more than eight cores or so, you’re often, not always, but often hitting multiple NUMA domains at that point. It’s good to make sure that you have both interleaves set up and huge pages set up at that point. If you’re setting up NUMA policies, you might as well set up huge pages at the same time. It’ll save you some trouble.
Q2. What are some downsides of disabling NUMA for servers?
Typically, you do have an option to turn NUMA off, in which case it will go back through this Northbridge, I think it’s called the Northbridge memory controller. The problem is once you do that, then, you have a lot more of a possibility of hitting memory contention issues where maybe two cores are trying to hit the same memory at the same time. The other side to it is that the on-chip memory controllers are typically going to be able to parallelize memory access much better than the server, the system board chip will. So, your memory access will be slower and you may have some more locking. It will be something that will hurt your performance. NUMA interleave may seem not great for some things, but it’s much better than the alternatives.
Q3. We often are told we don’t want swap space to be used on database servers. How does NUMA affect that recommendation?
There are numerous instances where you might possess items that won’t be immediately utilized. In such scenarios, having swap space can alleviate certain memory pressure issues to an extent. However, this doesn’t directly impact Non-Uniform Memory Access (NUMA). When a single query consumes a significant amount of memory and begins paging, it’s not ideal, but it’s preferable to experiencing widespread memory pressure across all queries. Furthermore, it’s important to note that memory is segmented into NUMA domains, effectively placing each query within its own semi-isolated memory space. While not entirely isolated, this segmentation offers some degree of separation. Consequently, having swap space is advisable, not only to provide a buffer but also to closely monitor its usage to avoid heavy reliance on it. Although swap space might be used more frequently in such an environment, the negative impact on overall performance is less severe compared to a system with uniform memory access, where memory pressure would be more broadly distributed.
For those who share our sentiments about keeping database performance at optimal health through optimization and were unable to join us live, here’s a recap of the invaluable insights shared during the webinar.
- Overview of NUMA: Introduction of NUMA architecture, emphasizing its relevance and impact.
- The PostgreSQL-NUMA Dynamics: An exploration of how PostgreSQL interacts with system memory within NUMA architectures.
- NUMA Challenges: Practical strategies and solutions to navigate the challenges posed by running PostgreSQL on NUMA-enabled systems for optimal database operation.
- Practical Examples: Through case studies and benchmarks, Chris illustrated the significant effects of NUMA configurations on query performance and strategic database planning, providing tangible examples of the theory in action.
