Not coincidentally, Microsoft's primary use case for 1GB pages on Windows is MS SQL server.
It is automatically enabled, but only if you have the "Lock pages in memory" privilege assigned to the database engine service account...
... which the default SYSTEM account has enabled by default...
... but not if you set up a typical cluster with a domain user account.
So in other words, the scenario where you would want this enabled the most -- large expensive enterprise clusters -- is where it is accidentally disabled with no warning.
I use this as a free 10-20% performance boost. Add in a few other similar tuning settings and I can get a 50% pref improvement on just about any "enterprise" cluster without having to get clever.
Turbo buttons are fun to press.
Have you ever written up some of the things you do to improve performance, or is that something you're unable to share?
- Enable the "Lock pages in memory" privilege.
- Enable the "Perform volume maintenance" privilege.
- Enable the "Ad hoc query optimisation" setting.
- If on-prem, set power management to "High Performance", ideally in the hardware BIOS, but failing that, at the hypervisor level.
- Upgrade the OS and DB engine version if this is compatible with the apps.
- Run sp_Blitz and implement any critical recommendations.
The below recommendations apply to cloud-hosted servers only:
- Upgrade to the latest gen DB-optimised VM sizes. In Azure I use Ebds_v5 and Eads_v5.
- Use the "temporary storage" SSD volume for tempdb.
- Consolidate storage. Striped a single large logical volume across multiple 1 TB disks. NEVER use the 1990s era many-small-volumes scheme of having separate data, log, tempdb, and templog drives!
- Move the DB VM and the App VMs into a single "Proximity Placement Group" or the equivalent construct.
In my experience the above list generally yields a total performance improvement of anywhere from 40% to 300% with minimal risk.
If you sprinkle some light tuning on top, such as judiciously creating a handful of indexes with a high predicted performance benefit can take you even further.
Past that, you have to get elbow deep into the schema, which is typically only possible with "in house developed" databases.
https://www.sqlshack.com/category/sql-server-performance-tun...
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.
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.
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.
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.
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).
It was requested by the Oracle DB team
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
I'd expect it to benefit postgres substantially.
I am eagerly awaiting its release as a DB enthusiast and lifelong Postgres user
However, you're right that multiple processes maintaining separate page-tables where each is containing mappings to the same physical memory regions can be wasteful.
Still, due to how huge pages are represented in the page-table data structure, I don't see what difference would it make to switch to 2MB or 1GB page, unless you're running a recentish kernel (~3 years) that has been specifically optimized for exact this case, e.g. to reduce the memory footprint of huge-page representation in page-tables. Specifically, a single 2MB huge page is now represented by 8 page entries (512 bytes) instead of 512 page entries (2MB). And there was another patch proposing to cut 8 page entries to only 2 page entries.
In any case, that's a dramatic cut of memory footprint when compared to how it used be and which is why I believe that this is the actual reason why OP sees the difference.
That would make sense, in retrospect: the OOMs almost always occurred at the point of highest client load.
I have only the most basic knowledge of hugepages - picked up on it being a necessity for stable performance in VMs, but don't understand why it would matter for Postgres as well.
And do you end up having to configure Postgres to use hugepages, or does it pick them up automatically?
Postgresql needs to be reconfigured to use them properly. The shared_buffers setting needs to be adjusted to allocate in [huge page size] units.
@menaerus actually has a great comment on this that digs deeper into it, looking forward to seeing what comes out of that thread as well.
hugepagesz=1G hugepages=940
huge_pages = on
huge_page_size = 1GB