Windows PostgreSQL server for Enterprise data: pagefile utilized ~95% persistently, system managed (8gb currently)
I'm a relative newcomer to postgres but it is working well for us after migrating our data from SQL Server to Postgres about a month ago. Our organization does not have a DBA, so I'm it!
In order to upgrade ArcGIS Enterprise 11.1 to 11.4+ we needed to upgrade our SQL Server licenses but that won't be done for another year from now at least; I had actually been planning the postgres migration for more than a year ago for this reason, and I finally did it. This also gives us (GIS) more isolated administrative control and ability to monitor performance of our data instead of it being squished on a SQL Server instance that is shared with loads of other databases from the organization.
First and foremost, we are not experiencing any noticeable degradation in performance. Everything is running smooth. However, I did notice that Windows' pagefile utilization on the postgres machine is ~95% persistently, so I'm generally just asking if that's normal or expected? Should put it out of my mind, or is this something I need to address?
---
Our postgres server is a virtual server on our local server cluster that was already pretty darn fast, and then we just upgraded our nodes recently, so it's even better. The OS is Windows Server 2019 Datacenter. The machine has 4 vcores and 32gb RAM.
Our postgres database use case is fairly simple and straightforward, and not very intense. We have a few Enterprise Geodatabases, with one main production db that houses, for example, our core utilities and municipal datasets that we generally serve out as map/feature services for internal use. We generally use Traditional versioning rather than Branch. Only myself and my GIS coworker hit the database directly in ArcGIS Pro, or with various automations with Python (mostly using arcpy). We do multiple backups a day with pg_dump (for each db) & pg_dumpall (for global objects only) and I've done disaster recovery testing and restoration using ESRI's recommended process and everything checks out. The only config customizations I did for postgres was to set shared_buffers to 25% of physical RAM, so 8GB, per a lot of general recommendations I could find.
All that is to say that we don't have an extreme burden on the postgres server. It runs pretty cool between 5-20% CPU with a few sustained few spikes to 40-50% (never seems to get saturated).
About 30-40% of RAM is persistently 'In Use' with nearly the rest of the RAM is listed by windows resource monitor as on 'Standby'.
Since so much RAM is available, I'm wondering why Windows (or postgres) isn't 'releasing' the pagefile, if that's the right term? Is it normal for postgres to reserve that pagefile for use? Windows is set to manage the paging file size automatically.
The machine has been through at least one reset recently but the pagefile persistently is 'utilized', according to ArcGIS Monitor metrics. This Monitor alert is how I realized there even may be an issue, otherwise I would not have thought to check on the pagefile since we are not having any performance issues.
Thanks for any info or tips.
Accepted Solutions
@CalvinHarmin wrote:Your note on the dynamic allocation base size is interesting though. It seems in theory, since my utilization remains above 90%, and that I haven't hit a cap of either 1/8 the hard disk volume, or 3x the physical RAM, that it should be automatically expanding the base size in my circumstance? But since there are no System errors being logged, it's not actually hitting a cap in a way that causes issues?
When I mentioned >90%, I was referring to commit charge %, not the pagefile utilization %. In Resource Monitor, go to the Memory tab and check the Commit Charge graph (see reference image below). This shows the total memory (physical RAM + pagefile) that has been reserved by Windows processes. If your commit charge % exceeds 90% and you're seeing high pagefile utilization without the allocated size growing, then you could probably resolve this by either increasing the capacity of your C:, or moving the system-managed pagefile to another volume with greater capacity.
@CalvinHarmin wrote:One big gap in my understanding still is if postgres is holding on to this allocation without using it, or if the OS is independently holding on to it for some other reason/cause. I am not even at a level where I can ask the right questions about postgres' behavior this since this level of software/database architecture goes far beyond my level of understanding.
Postgres doesn't directly manage the pagefile - that's entirely managed by Windows. However, settings like shared_buffers in postgres can indirectly affect the system's memory behavior. If you're not seeing any system errors or performance hits, the system is likely managing the pagefile well.
High pagefile utilization does not necessarily indicate a problem. However, to understand whether it's a concern or expected behavior, it's important to explore how Windows handles system-managed pagefiles and what factors may contribute to high pagefile utilization.
Why does Windows use the pagefile even with plenty of RAM?
Windows reserves space for the pagefile regardless of available physical memory (RAM) to ensure system stability and to accommodate system crash dumps in case of failure.
For system-managed pagefiles, Windows dynamically manages the pagefile size based on system demand. However, it’s important to understand some of the rules Windows follows:
- The pagefile can grow up to three times the amount of physical RAM or 4GB, whichever is larger.
- It is also limited to one-eighth of the total volume disk drive (C: in your case).
For example, if you have 32GB of RAM but only 100GB on your C: drive, the pagefile size will be capped at 12.5GB (one-eighth of 100GB) for a system-managed pagefile.
How to check pagefile usage
You can check the pagefile’s current usage by running this command in Command Prompt or PowerShell:
wmic pagefile list /format:listThis shows both the allocated size and current usage of the page file. You can calculate pagefile utilization using the formula:
System-managed pagefile and dynamic allocation
As noted earlier, AllocatedBaseSize can change dynamically based on system demand for system-managed pagefiles. One factor to be aware of is the system commit charge %, which you can view in Resource Monitor. The commit charge represents the total amount of virtual memory (physical RAM + pagefile) being used by processes. When the commit charge approaches the system's commit limit (usually around 90%), Windows will expand the pagefile to provide more virtual memory. If the commit charge exceeds 90% without the pagefile size growing, it may be worth investigating further to ensure it's not hitting one of the earlier mentioned system-managed constraints.
What happens when virtual memory is saturated?
If your system’s virtual memory is being heavily used, you may start seeing warnings about low or out-of-memory conditions. This can lead to application or service failures, including crashes of components like your postgres database. You can check for errors in Event Viewer logs. When virtual memory is exhausted, Windows will log events, typically with Event ID 2004, indicating low virtual memory or out of virtual memory conditions. These events are typically logged in the System logs and may look something like this:
Windows successfully diagnosed a low virtual memory condition. The following programs consumed the most virtual memory: postgres.exe (12743) consumed 24643243160 bytes
Referenced documentation:
Thank you that is an excellent collection of information and explanation.
This machine has ~111GB of C:\ disk space allocated, currently at ~73gb free. So it seems the 1/8th cap (~13 to 14gb) you described isn't being hit. (EDIT: however, virtual server clusters can be weird, where although it has an 'allocation' of 111GB, it might not actually have that 'dedicated' to it on the server node unless the machine actually uses it all. Not sure if that could have any relevance here).
EDIT #2: my postgres tablespace for my databases are on a secondary .D: drive (same ssd pool of the server node as the C: system drive). I'm not sure if that introduces any weirdness into the discussion. Postgres itself is installed on the C: drive.
Here are the results of the wmic pagefile list /format:list command
I checked Event Viewer and it seems there are no System log entries relating to low virtual memory condition. I tried manual review, and also specifically filtering for Event ID 2004 in the entire history, but I didn't come up with anything. I also filtered for all warning/error/critical level events and thankfully found nothing of note that has any relevance so far that I can tell. So that seems good that windows isn't screaming behind the scenes!
Your note on the dynamic allocation base size is interesting though. It seems in theory, since my utilization remains above 90%, and that I haven't hit a cap of either 1/8 the hard disk volume, or 3x the physical RAM, that it should be automatically expanding the base size in my circumstance? But since there are no System errors being logged, it's not actually hitting a cap in a way that causes issues?
One big gap in my understanding still is if postgres is holding on to this allocation without using it, or if the OS is independently holding on to it for some other reason/cause. I am not even at a level where I can ask the right questions about postgres' behavior this since this level of software/database architecture goes far beyond my level of understanding.
@CalvinHarmin wrote:Your note on the dynamic allocation base size is interesting though. It seems in theory, since my utilization remains above 90%, and that I haven't hit a cap of either 1/8 the hard disk volume, or 3x the physical RAM, that it should be automatically expanding the base size in my circumstance? But since there are no System errors being logged, it's not actually hitting a cap in a way that causes issues?
When I mentioned >90%, I was referring to commit charge %, not the pagefile utilization %. In Resource Monitor, go to the Memory tab and check the Commit Charge graph (see reference image below). This shows the total memory (physical RAM + pagefile) that has been reserved by Windows processes. If your commit charge % exceeds 90% and you're seeing high pagefile utilization without the allocated size growing, then you could probably resolve this by either increasing the capacity of your C:, or moving the system-managed pagefile to another volume with greater capacity.
@CalvinHarmin wrote:One big gap in my understanding still is if postgres is holding on to this allocation without using it, or if the OS is independently holding on to it for some other reason/cause. I am not even at a level where I can ask the right questions about postgres' behavior this since this level of software/database architecture goes far beyond my level of understanding.
Postgres doesn't directly manage the pagefile - that's entirely managed by Windows. However, settings like shared_buffers in postgres can indirectly affect the system's memory behavior. If you're not seeing any system errors or performance hits, the system is likely managing the pagefile well.
Ah hah, thank you for clarifying. Here is the commit charge level from resource monitor.
I think you've helped me exhaust the necessary steps to confirm things are fine. I'll consider this issue resolved and make sure ArcGIS Monitor doesn't scream from that pagefile metric on this machine. I appreciate your detailed answers, thanks again!
