OCI Postgres database monitoring
Oracle Cloud Infrastructure (OCI) Database with PostgreSQL is a managed Postgres database service that provides database systems and nodes for running Postgres workloads. Site24x7 integrates with OCI Postgres DB to provide visibility into the database system and node health, performance, resource utilization, sessions, queries, storage, and replication.
Overview
Site24x7's OCI Postgres DB integration provides monitoring at both the database system and node levels. The integration helps you track the health and performance of your Postgres DB environment and identify resource or database activity that may require attention.
When you integrate OCI Postgres DB with Site24x7, the following monitors are available:
- Postgres DB Systems: Monitors database system-level performance and health. It includes metrics for CPU, memory, storage, connections, IOPS, latency, throughput, sessions, blocked queries, long-running queries, replication, and WAL storage.
- Postgres Node: Monitors individual PostgreSQL nodes and provides node-level visibility into CPU, memory, storage, IOPS, latency, throughput, sessions, blocked queries, long-running queries, replication, and WAL storage.
Use case
Postgres DB workloads can experience performance issues when CPU or memory utilization increases, database connections grow unexpectedly, queries remain blocked or run for long periods, or replication falls behind. With Site24x7's OCI Postgres DB integration, you can monitor these conditions across database systems and nodes, set thresholds, and receive alerts when monitored values require attention.
Benefits of OCI Postgres DB integration
Site24x7's OCI Postgres DB integration provides the following benefits:
- Performance visibility: Monitor CPU, memory, storage, IOPS, latency, and throughput across Postgres database systems and nodes.
- Database activity monitoring: Track connections, sessions, blocked queries, long-running queries, and deadlocks.
- Replication visibility: Monitor replication lag, inactive replication slots, and WAL storage usage.
- Proactive alerting: Configure thresholds for supported metrics and receive alerts when values breach defined limits.
- Centralized monitoring: View database system and node performance from the Site24x7 console.
- Automate remediation with IT automation: Use IT automation support for OCI Postgres DB monitors to automate predefined actions in response to monitoring alerts, minimize manual intervention, and speed up issue resolution.
Setup and configuration
To get started with OCI Postgres DB monitoring, complete the following setup steps:
- Site24x7 uses cross tenancy access to monitor OCI resources using the Site24x7 tenancy user. Create the required OCI policy to allow Site24x7 to view your resources.
- Log in to your Site24x7 account and navigate to Cloud > OCI > Integrate OCI Monitor.
- On the Integrate OCI Monitor page, select Postgres DB Systems from the Services to be discovered list.
Permissions
Ensure that Site24x7 receives the following permissions to monitor OCI Postgres database systems:
- listDbSystems-POSTGRES_DB_SYSTEM_INSPECT
- getDbSystem-POSTGRES_DB_SYSTEM_READ
Polling frequency
Site24x7 queries OCI service level APIs according to the configured polling frequency, which can range from once a minute to once a day, to collect performance data and metadata from OCI PostgreSQL monitors.
Supported metrics
The supported metrics for OCI Postgres DB monitors are given below.
Postgres DB Systems
| Metric name | Description | Statistics | Unit |
|---|---|---|---|
| Buffer Cache Hit Ratio | Percentage of data requests served from the buffer cache on the DB Systems. | Mean | Percentage |
| Buffer Cache Hit Ratio (Replica) | Percentage of data requests served from the buffer cache on all the replica instances. | Mean | Percentage |
| DB Connections | Number of client connections currently open to the DB Systems. | Mean | Count |
| DB Connections (Replica) | Number of client connections currently open to the replica databases. | Mean | Count |
| CPU Utilization | Percentage of allocated CPU consumed by the DB Systems. | Mean | Percentage |
| CPU Utilization (Replica) | Percentage of allocated CPU consumed by all the replica instances. | Mean | Percentage |
| Deadlocks | Total number of deadlocks detected on the DB Systems. | Sum | Count |
| Deadlocks (Replica) | Total number of deadlocks detected on the replica databases. | Sum | Count |
| Write IOPS | Number of write I/O operations performed per second on the DB Systems. | Mean | Count |
| Write IOPS (Replica) | Number of write I/O operations performed per second on all the replica instances. | Mean | Count |
| Read IOPS | Number of read I/O operations performed per second on the DB Systems. | Mean | Count |
| Read IOPS (Replica) | Number of read I/O operations performed per second on all the replica instances. | Mean | Count |
| Write Latency | Average time taken to complete a write operation on the DB Systems. | Mean | ms |
| Write Latency (Replica) | Average time taken to complete a write operation on all the replica instances. | Mean | ms |
| Read Latency | Average time taken to complete a read operation on the DB Systems. | Mean | ms |
| Read Latency (Replica) | Average time taken to complete a read operation on all the replica instances. | Mean | ms |
| Write Kilobytes | Volume of data written to storage by the DB Systems. | Mean | KB |
| Write Kilobytes (Replica) | Volume of data written to storage by all the replica instances. | Mean | KB |
| Read Kilobytes | Volume of data read from storage by the DB Systems. | Mean | KB |
| Read Kilobytes (Replica) | Volume of data read from storage by all the replica instances. | Mean | KB |
| Memory Utilization | Percentage of allocated memory in use on the DB Systems. | Mean | Percentage |
| Memory Utilization (Replica) | Percentage of allocated memory in use on all the replica instances. | Mean | Percentage |
| Used Storage | Amount of storage currently consumed by the DB Systems. | Mean | GB |
| Used Storage (Replica) | Amount of storage currently consumed by the replica database. | Mean | GB |
| Inactive Replication Slots | Number of replication slots on the DB Systems that are currently inactive. | Mean | Count |
| Inactive Replication Slots (Replica) | Number of replication slots on the replica that are currently inactive. | Mean | Count |
| Active Sessions | Number of sessions actively executing queries on the DB Systems. | Mean | Count |
| Active Sessions (Replica) | Number of sessions actively executing queries on the replica database. | Mean | Count |
| Idle Sessions | Number of connected sessions sitting idle on the DB Systems. | Mean | Count |
| Idle Sessions (Replica) | Number of connected sessions sitting idle on the replica database. | Mean | Count |
| Idle In Transaction | Number of sessions on the DB Systems that are idle while holding an open transaction. | Mean | Count |
| Idle In Transaction (Replica) | Number of sessions on the replica that are idle while holding an open transaction. | Mean | Count |
| Number of Blocked Queries | Number of queries on the DB Systems that are waiting on a lock held by another session. | Mean | Count |
| Number of Blocked Queries (Replica) | Number of queries on the replica that are waiting on a lock held by another session. | Mean | Count |
| Number of Long-running Queries (Over 5 Minutes) | Number of queries on the DB Systems that have been running for more than five minutes. | Mean | Count |
| Number of Long-running Queries (Over 5 Minutes) (Replica) | Number of queries on the replica that have been running for more than five minutes. | Mean | Count |
| Average Read Latency | Average latency across all read operations on the DB Systems. | Mean | ms |
| Average Read Latency (Replica) | Average latency across all read operations on the replica instance. | Mean | ms |
| Replication Lag | Replay LSN lag between the primary and replica, indicating how much WAL data is pending to be replayed on the replica. | Mean | MB |
| Replication Lag (Replica) | Volume of write-ahead log data by which the replica trails the primary. | Mean | MB |
| WAL Storage | Amount of storage consumed by write-ahead log files on the DB Systems. | Mean | GB |
| WAL Storage (Replica) | Amount of storage consumed by write-ahead log files on all the replica instance. | Mean | GB |
| Instance Count | Total number of database instances provisioned in the system. | Average | Count |
| OCPU Count | Number of Oracle CPUs (OCPUs) allocated to the database instance. | Average | Count |
| Instance Memory Size | Amount of memory allocated to the database instance. | Average | GB |
Postgres Node
| Metric name | Description | Statistics | Unit |
|---|---|---|---|
| Node Buffer Cache Hit Ratio | Percentage of data requests served from the buffer cache on the node. | Mean | Percentage |
| Node DB Connections | Number of client connections currently open to the node. | Mean | Count |
| Node CPU Utilization | Percentage of allocated CPU consumed by the node. | Mean | Percentage |
| Node Deadlocks | Total number of deadlocks detected on the node. | Sum | Count |
| Node Write IOPS | Number of write I/O operations performed per second on the node. | Mean | Count |
| Node Read IOPS | Number of read I/O operations performed per second on the node. | Mean | Count |
| Node Write Latency | Average time taken to complete a write operation on the node. | Mean | ms |
| Node Read Latency | Average time taken to complete a read operation on the node. | Mean | ms |
| Node Write Kilobytes | Volume of data written to storage by the node. | Mean | KB |
| Node Read Kilobytes | Volume of data read from storage by the node. | Mean | KB |
| Node Memory Utilization | Percentage of allocated memory in use on the node. | Mean | Percentage |
| Node Used Storage | Amount of storage currently consumed by the node. | Mean | GB |
| Node Inactive Replication Slots | Number of replication slots on the node that are currently inactive. | Mean | Count |
| Node Active Sessions | Number of sessions actively executing queries on the node. | Mean | Count |
| Node Idle Sessions | Number of connected sessions sitting idle on the node. | Mean | Count |
| Node Idle In Transaction | Number of sessions on the node that are idle while holding an open transaction. | Mean | Count |
| Node Number of Blocked Queries | Number of queries on the node that are waiting on a lock held by another session. | Mean | Count |
| Node Number of Long-running Queries (Over 5 Minutes) | Number of queries on the node that have been running for more than five minutes. | Mean | Count |
| Node Average Read Latency | Average latency across all read operations on the node. | Mean | ms |
| Node Replication Lag | Volume of write-ahead log data by which the node trails the primary. | Mean | MB |
| Node WAL Storage | Amount of storage consumed by write-ahead log files on the node. | Mean | GB |
Threshold configuration
To configure thresholds for OCI PostgreSQL monitors:
- Log in to your Site24x7 account and navigate to Admin > Configuration Profiles > Threshold and Availability .
- Click Add Threshold Profile .
- Select the applicable monitor type from the Monitor Type drop-down menu. The applicable monitor types are Postgres DB Systems and Postgres Node .
- Provide an appropriate name in the Display Name field.
- The supported metrics are displayed in the Threshold Configuration section. You can set threshold values for the supported metrics.
- Click Save .
- Notify When DB System Is in Failed Status: Enable this option to receive an alert when the DB System enters the Failed state.
- Notify When DB System Is in Inactive Status: Enable this option to receive an alert when the DB System enters the Inactive state.
- Notify When DB System Is in Needs Attention Status: Enable this option to receive an alert when the DB System enters the Needs Attention state.
IT automation
IT automation is available for OCI Postgres DB Systems and OCI Postgres Node monitors. The integration provides IT automation actions for managing these resources through Site24x7.
Capacity planning
Capacity planning is available for Postgres DB Systems and helps you understand how database resources are being utilized over time. Use PostgreSQL performance and resource metrics to identify sustained increases in CPU and memory utilization, storage consumption, database connections, IOPS, and read or write throughput. Reviewing these trends can help you identify workloads that are approaching their current resource limits and plan capacity changes before performance is affected.
Capacity planning can also help you compare resource usage across monitored database systems and identify systems that consistently operate at higher utilization. This gives you better visibility into resource requirements and future growth based on historical monitoring data.
Monitor Groups
Monitor groups can also be used to view the overall status of related OCI PostgreSQL monitors and streamline day-to-day monitoring. You can add or remove monitors from groups as your PostgreSQL environment changes.
For example, you can create separate monitor groups for production and development PostgreSQL environments, or group the Postgres DB Systems and their associated Postgres Node monitors for a particular application. This makes it easier to identify issues affecting a specific environment or application without reviewing individual monitors separately.
You can organize your OCI PostgreSQL monitors into monitor groups based on your application, environment, business unit, region, or other operational requirements. Grouping related monitors provides a consolidated view of their availability and performance from a single location.
Licensing
- Each OCI Postgres DB Systems monitor utilizes one basic monitor license.
- Each OCI Postgres Node monitor utilizes one basic monitor license.
Viewing OCI Postgres DB Systems
To monitor your OCI PostgreSQL environment, log in to the Site24x7 console and navigate to Cloud > OCI > Postgres DB Systems.
Monitor data
The monitor data for each OCI PostgreSQL monitor is given below.
Postgres DB Systems
The monitor data for the Postgres DB Systems monitor is given below.
Summary
The Summary tab provides an overview of the monitor status, events timeline, and PostgreSQL performance metrics in the form of charts.
Postgres DB Nodes
The Postgres DB Nodes tab displays the PostgreSQL nodes associated with the database system, along with their status and key node metrics such as connections, CPU utilization, memory utilization, and used storage.
Configuration
The Configuration tab displays key database system configuration details, including storage system type, lifecycle details, configuration ID, storage availability information, IOPS, subnet, primary and reader endpoints, endpoint addresses, and ports.
Outages
The Outages tab provides details on an outage's start time, end time, duration, and comments, if any.
Notes
The Notes tab provides details and notes associated with the monitor.
Log Report
The Log Report tab provides a consolidated report of the Postgres DB Systems monitor's log status, which can be downloaded as a CSV file.
Alert Logs
The Alert Logs tab displays a chronological list of all triggered alerts related to the Postgres DB Systems monitor.
Audit Logs
The Audit Logs tab provides audit information for actions and changes associated with the monitor.
Postgres Node
The monitor data for the Postgres Node monitor is given below.
Summary
The Summary tab provides an overview of the node status, events timeline, and PostgreSQL node performance metrics in the form of charts.
Configuration
The Configuration tab displays key node configuration and metadata details such as region, name, state, life cycle details, compartment ID, creation time, last updated time, description, database version, shape, availability domain, endpoint details, and other available attributes.
Outages
The Outages tab provides details on an outage's start time, end time, duration, and comments, if any.
Notes
The Notes tab provides details and notes associated with the monitor.
Log Report
The Log Report tab provides a consolidated report of the monitor's log status, which can be downloaded as a CSV file.
Alert Logs
The Alert Logs tab displays a chronological list of all triggered alerts related to the monitor.
Audit Logs
The Audit Logs tab provides audit information for actions and changes associated with the monitor.