4 ms·

This is a machine which is dedicated to postgres. For the particular workload (heavy update) and filesystem (ZFS), it appears from experimentation that optimal

This is a machine which is dedicated to postgres. For the particular workload (heavy update) and filesystem (ZFS), it appears from experimentation that optimal performance is achieved by dedicating most of the RAM to postgres' shared buffers, and not really relying on the filesystem cache at all. (As an aside, that might be unusual, the "standard" advice seems to be to set shared_buffers to a smaller value and to rely on the OS cache.) So, I have about 95% of the RAM assigned to postgres shared buffers. Without hugepages enabled, this was fine when the database started up, but as (presumably) the buffer cache became fragmented over time, the size of the page tables grew to a point where the machine ran out of RAM. I do have vm.overcommit_memory = 2 (never overcommit), but even so the OOM killer was still invoked because, well, the machine had no RAM left. I can't remember exactly how big the pagetables had gotten in this situation, but I think it was a few tens of GBs. I'm actually using 1GB hugepages, but in all honesty I'm not sure how Linux deals with them or how postgres claims them. From /proc/meminfo it looks like the page table is quite small, with the "Hugetlb" and "DirectMap1G" numbers both around 1TB.

mickeyp 4y ago
Yeah this mirrors my experiences. I, too, find the file cache to be significant performance impediment on high-RAM systems. Worse, its behaviour is non-deterministic (or at least hard to reason about.) It leads to page table fragmentation and that is the ultimate performance destroyer. Huge page tables are a godsend in this case, but you do need a program that is properly optimised to handle them properly. At least most RDBMS do this reasonably well.
menaerus 4y ago
If you assign 95% of the memory to postgres, then yes, it is realistic for page-table to grow several GBs under long and heavy workloads so machine will eventually run into an OOM, especially if you use vm.overcommit_memory = 2 (never overcommit). Mitigating the OOM by switching to hugepages may suggest that you're running a kernel with the optimized page-table hugepage handling because of which there are less page-table entries and consequently the whole page-table ends up being smaller. Currently, I have no other explanation.

mickeyp 4y ago

Yeah this mirrors my experiences. I, too, find the file cache to be significant performance impediment on high-RAM systems. Worse, its behaviour is non-deterministic (or at least hard to reason about.) It leads to page table fragmentation and that is the ultimate performance destroyer. Huge page tables are a godsend in this case, but you do need a program that is properly optimised to handle them properly. At least most RDBMS do this reasonably well.

menaerus 4y ago

If you assign 95% of the memory to postgres, then yes, it is realistic for page-table to grow several GBs under long and heavy workloads so machine will eventually run into an OOM, especially if you use vm.overcommit_memory = 2 (never overcommit). Mitigating the OOM by switching to hugepages may suggest that you're running a kernel with the optimized page-table hugepage handling because of which there are less page-table entries and consequently the whole page-table ends up being smaller. Currently, I have no other explanation.