5.16. Metrics #
Key performance indicators of the system can be monitored using graphs based on metrics collected by pgpro-otel-collector. For the graphs to work properly, you must first install and configure logging and monitoring tools.
You can also access SQL metrics that are provided based on planning statistics and SQL statement execution statistics collected by the pgpro_stats extension.
Important
When creating SQL metrics use queries with aggregate functions, such as COUNT, SUM, and AVG. The instance can fail when using metrics based on queries returning more than one row or separate values, for example, SELECT 1.
Viewing Main Metrics
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to Metrics.
The following graphs will be displayed:
WAL Archiver: The number of archived WAL segments.
Vacuum workers: The number of vacuum operations.
Background Writes: Buffers: The data volume written on the background from shared cache to disk.
Background Writes: Maxwritten/Fsync:
maxwritten: The number of times the background writer had to stop writing because the limit was reached.
fsync: The number of forced fsync calls.
Background Writes: Checkpoints: The number of checkpoints.
Background Writes: Checkpoints Write/Sync: The time spent writing and synchronizing blocks during execution of checkpoints.
Instance: Connections: Connections established with the DBMS instance and their state.
Instance: Blocks rate: The number of cache hits and misses that resulted in the need to read data from disk.
Instance: Transactions rate: Transactional activity in a DBMS instance.
Instance: Events: Deadlocks, replication conflicts, and checksum validation errors in the DBMS instance.
Instance: Tuples: The number of rows read, inserted, updated, and deleted.
Instance: Cache hit ratio: The proportion of cache hits relative to all shared cache accesses.
Instance: Temp bytes written: The amount of data written to temporary files.
Instance: Temp files: The number of temporary files written by the DBMS instance.
Instance: Locks: Lock dynamics.
WAL: Written bytes: Bytes written to WAL.
System: Load Average: Average server load.
System: Memory Usage: Usage of server memory.
System: Swap Usage: Usage of swap space in the system.
System: Processes: Usage of processes in the system.
SQL Metrics #
This section explains how to manage SQL metrics. It contains the following instructions:
Creating an SQL Metric
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to Metrics → SQL metrics.
In the top-right corner of the page, click Add SQL metric.
Enter parameters of the new SQL metric (parameters marked with an asterisk are required):
Name.
Database: The database where the query will be executed.
User: The system role on behalf of which the query will be executed.
Collection interval: The frequency of data collection.
Query: The SQL query based on which the metric is computed.
Important
Use queries with aggregate functions, such as
COUNT,SUM, andAVG. The instance can fail when using metrics based on queries returning more than one row or separate values, for example,SELECT 1.Instance restart is required: Specifies whether to restart the instance.
Click Add.
Viewing SQL Metrics
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to Metrics → SQL metrics.
The table of SQL metrics with the following columns will be displayed:
ID.
Name.
Database: The database where the query is executed.
User: The system role on behalf of which the query will be executed.
Interval: The frequency of data collection.
Last value: The result of the last query in the
JSONformat.Updated at: The time the data was last updated.
Query: The SQL query based on which the metric is computed.
Actions.
For more information about available actions, refer to other instructions in this section.
To view the history of collected data, click the metric name.
Editing an SQL Metric
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to Metrics → SQL metrics.
Click
next to the SQL metric. Edit SQL metric parameters.
Click Save.
Deleting an SQL Metric
Important
Deleted SQL metrics cannot be restored.
To delete an SQL metric:
In the navigation panel, go to Infrastructure → Instances.
Click the name of the instance.
In the navigation panel, go to Metrics → SQL metrics.
Click
next to the SQL metric. Click Delete.