PostgreSQL Monitoring - Does the server have enough Memory
By Itay Braun, CTO, Metis
Monitoring the Memory usage of the server
Monitoring server memory is a critical aspect of maintaining the performance and stability of any server, particularly those hosting databases. In this context, we focus on understanding how memory is allocated, particularly for the buffer cache, and whether the allocated memory meets the demands of the databases running on the server.
The Main Questions:
What is the Buffer Cache?
How much memory is configured for the buffer cache?
How much memory is used by each DB?
Does the server need more memory or what it has is good enough?
Project "OpenInsight"
We provide a Jupyter Notebook called pg_server_cache contains the answers to the questions above.
This notebook is part of a bigger project I wrote about to help developers detect and solve db-related problems quickly. You can see the SQL commands, edit them, and provide a deeper analysis of the problem.
The concept of OpenInsight is:
Built-in good questions. We know you are not a DBA and probably not familiar with the DB internals. The flows are written by top DB experts who know what to ask and when to find the answers.
Collect the relevant data and organize it for analysis. Everything, in one click.
Generate insights about your database. These insights, as per their definition, assess the gathered data and classify it. The status can be good, bad, just okay, or it might say there isn't enough data for a conclusion. If possible, the insight will also suggest a remediation plan.
The Notebook
Buffers used by each DB
Cache Hit Ratio
Tables Cache Hit Ratio
Continues Monitoring
Please notice the notebook monitors the current status of the databases as it uses the system tables as is. It is recommended to install a monitoring tool such as:
Prometheus - plenty of manual hard configuration
pgwatch - agent + Grafana. No public pricing
Metis - a developer-friendly monitoring tool, with built-in insights. Offers a generous free tier,