APMIA PostgreSQL Extension custom metric for cache monitoring

Products

• DX SaaS

Issue/Introduction

apmia PostgreSQL Extension cache_hit_ratio custom metric

Implemention of APMIA Infrastructure Agent PostgreSQL Extension custom metrics for cache hit ratio or rate. This customer metric provides better monitoring of cache usage.

Resolution

Please take the following steps to implement the APMIA Infrastructure Agent PostgreSQL Extension custom metric for cache_hit_ratio.

custom metrics for cache_hit_ratio or cache:

For example, to calculate `cache_hit_ratio (query)`

SELECT
datname,
blks_hit,
blks_read,
ROUND((blks_hit::numeric / (blks_hit + blks_read)) * 100, 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE (blks_hit + blks_read) > 0;

Take the following steps:

1- stop APMIA agent

2- make a backup copy of <apmia-home>/extensions/PostgreSQL-dd040260xt330-<version>/config/schema.json

 

3- Add the following statements.

       {
            "query": "SELECT datname, ROUND((blks_hit::numeric / (blks_hit + blks_read)) * 100, 2) AS cache_hit_ratio FROM pg_stat_database WHERE (blks_hit + blks_read) > 0 AND datname IS NOT NULL",
            "metrics": [

 
                {
                    "metricPath": [
                        {
                            "name": "Databases|"
                        },
                        {
                            "name": "$.resultSet[*].datname"
                        },
                        {
                            "name": "|"
                        },
                        {
                            "name": "Shared Buffer Usage:Cache Hit Ratio"
                        }
                    ],
                    "metricType": "LongFluctuatingCounter",
                    "metricValue": "$.resultSet[*].cache_hit_ratio"
                }
 
            ]
        },

        
        
You can add the above block close to following query block in the schema.json file.

"query": "SELECT * FROM pg_stat_database where datname IS NOT NULL",

 

4- start APMIA agent

5- custom metric "Cache Hit Ratio" should be reporting under following path.
SuperDomain|<host/node>|Infrastructure|Agent|Postgres Databases|<host/IP>|postgres|Databases|postgres|Shared Buffer Usage:Cache Hit Ratio