5 ms·

> machine would regularly fill the page table causing an OOM That's quite interesting. How big the page-table must be on your server to directly or indirectly

> machine would regularly fill the page table causing an OOM That's quite interesting. How big the page-table must be on your server to directly or indirectly cause an OOM? 4-level page-table data-structure can address 512^4 page-tables. A single page-table can contain 512 entries. And each page-table entry is 64 bytes large. Unless I missed something, this means that upper bound memory consumption of page-table data-structure is 512^4 * 64B and which is 4TB of RAM so I guess it is theoretically possible to OOM on a 1TB machine. What I don't understand is how huge-pages would help you mitigate this problem because 2MB huge-page entry will essentially be made up from 512 entries from a single table. Linux call them compound pages. And given that all those entries will be vacant, and in-use for that particular huge-page, upper bound memory consumption will remain the same as with 4K pages. It has been recognized that compound pages might be wasteful because all those 511 entries are basically pointing to a head entry [1], so unless you're running recentish version of kernel on your system, you wouldn't see this advantage. [1] https://lwn.net/Articles/839737/ https://lwn.net/Articles/839737/

gnfargbl 4y ago
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.
anarazel 4y ago
I only looked curiously at the relevant Linux code. But my understanding is that compound pages don't show up in the page table of a process. Yes, there's an associated data structure with each physical page, but that's separate from per process tracking. The problem with the page table and postgres is that they're separately maintained for each process, even if all processes share the same shared memory area. With 1GB or 2MB pages that's not too bad. But with 4k pages you can easily end up with dozens to hundreds of MB for each connection. Wasted memory + wasted cycles (tlb misses).
- gavinray 4y ago
  There's a new Linux feature called Shared Page Table Entries that allows processes to share these It was requested by the Oracle DB team https://lwn.net/Articles/919143/ https://lwn.net/Articles/919143/ I recognize your username so you'd know better than most whether Postgres would be able to use this or not though
  - anarazel 4y ago
    I did vaguely follow the effort - last time I checked nothing seems to have been merged yet. Did it get merged since? I'd expect it to benefit postgres substantially.
    - gavinray 4y ago
      It doesn't appear to be merged yet in any of the 6.2 RC's from what I can see =/ I am eagerly awaiting its release as a DB enthusiast and lifelong Postgres user
- gnfargbl 4y ago
  Oh, so what I had assumed was some kind of page table fragmentation was in fact due to the number of postgres clients increasing, meaning that the number of page table entries increased due to duplication between processes? That would make sense, in retrospect: the OOMs almost always occurred at the point of highest client load.
- 4y ago

gnfargbl 4y ago

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.

anarazel 4y ago

I only looked curiously at the relevant Linux code. But my understanding is that compound pages don't show up in the page table of a process. Yes, there's an associated data structure with each physical page, but that's separate from per process tracking. The problem with the page table and postgres is that they're separately maintained for each process, even if all processes share the same shared memory area. With 1GB or 2MB pages that's not too bad. But with 4k pages you can easily end up with dozens to hundreds of MB for each connection. Wasted memory + wasted cycles (tlb misses).

gavinray 4y ago
There's a new Linux feature called Shared Page Table Entries that allows processes to share these It was requested by the Oracle DB team https://lwn.net/Articles/919143/ https://lwn.net/Articles/919143/ I recognize your username so you'd know better than most whether Postgres would be able to use this or not though
- anarazel 4y ago
  I did vaguely follow the effort - last time I checked nothing seems to have been merged yet. Did it get merged since? I'd expect it to benefit postgres substantially.
  - gavinray 4y ago
    It doesn't appear to be merged yet in any of the 6.2 RC's from what I can see =/ I am eagerly awaiting its release as a DB enthusiast and lifelong Postgres user
gnfargbl 4y ago
Oh, so what I had assumed was some kind of page table fragmentation was in fact due to the number of postgres clients increasing, meaning that the number of page table entries increased due to duplication between processes? That would make sense, in retrospect: the OOMs almost always occurred at the point of highest client load.
4y ago