This blog is the part-3 of the blog series about Monitoring PostgreSQL on SAP BTP (observability).
In this blog post, we’ll explore how to monitor PostgreSQL on SAP BTP, via Monitoring Admin user access
There are 3 ways that you can use to implement monitoring for your instance as an external user:
Instance monitoring via extension APIInstance monitoring via SAP Alert Notification Service eventsInstance monitoring using Monitoring Admin user accessBest Practice:
The combination of all the above approaches will enable to establish a solid foundation for monitoring and observability of PostgreSQL instances on SAP BTP.
3. Instance monitoring using Monitoring Admin user access
Monitoring your instance of PostgreSQL on SAP BTP is crucial for maintaining optimal performance and ensuring system reliability. Some monitoring actions are only possible with direct database access, that allows for in-depth monitoring and management.
To minimize security risks, we advise using the recommended above options and limiting direct access to the instance database to cases where it is essential for critical monitoring tasks
To perform advanced monitoring tasks, you need elevated privileges. SAP BTP enables you to request a temporary ‘instance_monitoring_admin’ user with admin rights, valid for up to 29 days. This user grants the necessary permissions to execute monitoring and administrative tasks.
This section outlines the process of requesting an Admin Monitoring user and highlights some key SQL commands for effective monitoring.
For requesting the admin monitoring user, please follow the steps explained in the below blog post:
PostgreSQL – Request an ‘admin’ user access valid for ‘x’ days
You may also refer to the official product documentation here.
After requesting you can start using the ‘instance-monitoring-admin‘ user during the validity time.
With the ‘instance_monitoring_admin’ credentials, you can connect to your PostgreSQL on SAP BTP instance using any SQL client, such as pgAdmin. Once connected, you can execute various monitoring tasks.
Below some useful monitoring tasks that you may execute:
Check Active Sessions: Retrieves information about current database connections and activities
SELECT * FROM pg_stat_activity;
Monitor Long-Running Queries: Identifies queries running longer than 5 minutes, which may indicate performance issues.
SELECT pid, now() – pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = ‘active’ AND now() – pg_stat_activity.query_start > interval ‘5 minutes’;
Analyze Index Usage: Evaluates the effectiveness of indexes in query performance.
SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE indisunique IS FALSE;
Check Table Bloat: Identifies tables consuming excessive space, indicating potential bloat.
SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS relation_size
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) > 100000000;
View Locks: Displays locks that are waiting to be granted, useful for diagnosing blocking issues.
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;
Monitoring key performance metrics such as CPU usage, memory consumption, disk utilization, and active connections is essential for maintaining the health and efficiency of your PostgreSQL database on SAP BTP. Using SQL is not the best option, or even not fully possible, for these metrics, we recommend you to check instance monitoring via extension API. Nevertheless by connecting to your database instance you can perform some of these monitoring tasks effectively to help get more insights about performance.
CPU Usage: While PostgreSQL does not directly expose CPU usage statistics through SQL queries, you can infer high CPU utilization by identifying long-running or resource-intensive queries.
Memory Consumption: To monitor memory usage, focus on identifying queries that consume significant resources, complementary you may check the buffer usage.
Run the following SQL command to assess shared buffer utilization:
SELECT relname, heap_blks_hit, heap_blks_read,
heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio DESC;
A low hit ratio may suggest that queries are not efficiently utilizing memory, leading to increased disk I/O.
Disk Utilization: Monitoring disk space helps prevent issues related to insufficient storage. Checking table sizes can give some hints about the utilization of the storage space.
Use the following SQL command to list the sizes of all tables:
SELECT relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
This query provides an overview of the disk space used by each table, allowing you to identify tables that consume excessive storage.
You may also find the below blog post relevant to the above topic:
PostgreSQL – How to estimate the remaining storage free space?
Active Connections: Monitoring the number of active connections is crucial to ensure the database operates within its capacity.
Execute the following SQL command to count current connections:
SELECT count(*) AS total_connections
FROM pg_stat_activity;
To view connections by state (active, idle, etc.):
SELECT state, count(*) AS count
FROM pg_stat_activity
GROUP BY state;
Utilizing pgAdmin Dashboards
pgAdmin provides a user-friendly interface to interact with your PostgreSQL database, allowing you to execute SQL queries and view system statistics. By utilizing pgAdmin, you can monitor various performance metrics through SQL commands and built-in dashboards.
Please be aware that pgAdmin is not a SAP tool, for support please access here.
pgAdmin offers some graphical dashboards that provide real-time insights into database performance:
Server Activity: Displays information about active sessions, locks, and prepared transactions.Dashboard Tab: Provides an overview of server health, including graphs for transactions per second, tuples in/out, and block I/O.
These visual tools complement SQL queries, offering a comprehensive view of your database’s performance.
Thanks for reading! Be sure to check out what our product team has shared about PostgreSQL and Redis:
PostgreSQL on SAP BTP – Are you looking for official blog posts? Blog INDEXRedis on SAP BTP – INDEX of official blog posts
Regards,
Antonio
This blog is the part-3 of the blog series about Monitoring PostgreSQL on SAP BTP (observability).In this blog post, we’ll explore how to monitor PostgreSQL on SAP BTP, via Monitoring Admin user accessThere are 3 ways that you can use to implement monitoring for your instance as an external user:Instance monitoring via extension APIInstance monitoring via SAP Alert Notification Service eventsInstance monitoring using Monitoring Admin user accessBest Practice: The combination of all the above approaches will enable to establish a solid foundation for monitoring and observability of PostgreSQL instances on SAP BTP.3. Instance monitoring using Monitoring Admin user accessMonitoring your instance of PostgreSQL on SAP BTP is crucial for maintaining optimal performance and ensuring system reliability. Some monitoring actions are only possible with direct database access, that allows for in-depth monitoring and management.To minimize security risks, we advise using the recommended above options and limiting direct access to the instance database to cases where it is essential for critical monitoring tasks To perform advanced monitoring tasks, you need elevated privileges. SAP BTP enables you to request a temporary ‘instance_monitoring_admin’ user with admin rights, valid for up to 29 days. This user grants the necessary permissions to execute monitoring and administrative tasks.This section outlines the process of requesting an Admin Monitoring user and highlights some key SQL commands for effective monitoring.For requesting the admin monitoring user, please follow the steps explained in the below blog post:PostgreSQL – Request an ‘admin’ user access valid for ‘x’ daysYou may also refer to the official product documentation here.After requesting you can start using the ‘instance-monitoring-admin’ user during the validity time.With the ‘instance_monitoring_admin’ credentials, you can connect to your PostgreSQL on SAP BTP instance using any SQL client, such as pgAdmin. Once connected, you can execute various monitoring tasks.Below some useful monitoring tasks that you may execute:Check Active Sessions: Retrieves information about current database connections and activitiesSELECT * FROM pg_stat_activity;Monitor Long-Running Queries: Identifies queries running longer than 5 minutes, which may indicate performance issues.SELECT pid, now() – pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = ‘active’ AND now() – pg_stat_activity.query_start > interval ‘5 minutes’;Analyze Index Usage: Evaluates the effectiveness of indexes in query performance.SELECT relname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE indisunique IS FALSE;Check Table Bloat: Identifies tables consuming excessive space, indicating potential bloat.SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS relation_size
FROM pg_stat_user_tables
WHERE pg_total_relation_size(relid) > 100000000;View Locks: Displays locks that are waiting to be granted, useful for diagnosing blocking issues.SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;Monitoring key performance metrics such as CPU usage, memory consumption, disk utilization, and active connections is essential for maintaining the health and efficiency of your PostgreSQL database on SAP BTP. Using SQL is not the best option, or even not fully possible, for these metrics, we recommend you to check instance monitoring via extension API. Nevertheless by connecting to your database instance you can perform some of these monitoring tasks effectively to help get more insights about performance. CPU Usage: While PostgreSQL does not directly expose CPU usage statistics through SQL queries, you can infer high CPU utilization by identifying long-running or resource-intensive queries.Memory Consumption: To monitor memory usage, focus on identifying queries that consume significant resources, complementary you may check the buffer usage. Run the following SQL command to assess shared buffer utilization:SELECT relname, heap_blks_hit, heap_blks_read,
heap_blks_hit * 100.0 / (heap_blks_hit + heap_blks_read) AS hit_ratio
FROM pg_statio_user_tables
ORDER BY hit_ratio DESC;A low hit ratio may suggest that queries are not efficiently utilizing memory, leading to increased disk I/O.Disk Utilization: Monitoring disk space helps prevent issues related to insufficient storage. Checking table sizes can give some hints about the utilization of the storage space.Use the following SQL command to list the sizes of all tables:SELECT relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;This query provides an overview of the disk space used by each table, allowing you to identify tables that consume excessive storage.You may also find the below blog post relevant to the above topic:PostgreSQL – How to estimate the remaining storage free space?Active Connections: Monitoring the number of active connections is crucial to ensure the database operates within its capacity.Execute the following SQL command to count current connections:SELECT count(*) AS total_connections
FROM pg_stat_activity;To view connections by state (active, idle, etc.):SELECT state, count(*) AS count
FROM pg_stat_activity
GROUP BY state;Utilizing pgAdmin DashboardspgAdmin provides a user-friendly interface to interact with your PostgreSQL database, allowing you to execute SQL queries and view system statistics. By utilizing pgAdmin, you can monitor various performance metrics through SQL commands and built-in dashboards.Please be aware that pgAdmin is not a SAP tool, for support please access here.pgAdmin offers some graphical dashboards that provide real-time insights into database performance:Server Activity: Displays information about active sessions, locks, and prepared transactions.Dashboard Tab: Provides an overview of server health, including graphs for transactions per second, tuples in/out, and block I/O.These visual tools complement SQL queries, offering a comprehensive view of your database’s performance.Thanks for reading! Be sure to check out what our product team has shared about PostgreSQL and Redis:PostgreSQL on SAP BTP – Are you looking for official blog posts? Blog INDEXRedis on SAP BTP – INDEX of official blog postsRegards, Antonio Read More Technology Blogs by SAP articles
#SAP
#SAPTechnologyblog
