Skip to content

Instantly share code, notes, and snippets.

@varun-dhawan
Last active January 18, 2023 21:10
Show Gist options
  • Save varun-dhawan/ac56513f22912786075300ca7e2380a7 to your computer and use it in GitHub Desktop.
Save varun-dhawan/ac56513f22912786075300ca7e2380a7 to your computer and use it in GitHub Desktop.
title description ms.service ms.subservice ms.topic ms.author ms.date
Azure Database for PostgreSQL Flexible Server Enhanced metrics - Preview
List of new metrics
postgresql
flexible-server
conceptual
varun.dhawan
10/13/2022

Azure Postgres Flexible Server - Enhanced metrics (Preview) [Zn Sem]

Enhanced Metrics

Azure Database for PostgreSQL provides various metrics that give insight into the behavior of the resources supporting the PostgreSQL server. Each metric is emitted at a one-minute frequency, and has up to 93 days of history. You can configure alerts on the metrics. Other options include setting up automated actions, performing advanced analytics, and archiving history. For more information, see the Azure Monitor Metrics Overview.

[Note] In addition to these new metrics, we already have over 21 metrics already avaiavble in Flexible Server. For list of existing metrics, refer Monitor metrics on Azure Database for PostgreSQL - Flexible Server.

Enabling enhanced metrics

  • Most of these new metrics are disabled by default, barring a few exeptions (per the list below)
  • To enable these metrics, please turn ON the server parameter metrics.collector_database_activity.
    • This parameter is dynamic, hence will not require instance restart.

List of enhanced metrics

Activity
Display Name Metric ID Unit Description Dimension Default enabled
Sessions By State (Preview) sessions_by_state Count Overall state of the backends State No
Sessions By WaitEventType (Preview) sessions_by_wait_event_type Count Sessions by the type of event for which the backend is waiting Wait Event Type No
Oldest Backend (Preview) oldest_backend_time_sec Seconds The age in seconds of the oldest backend (irrespective of the state) N/a No
Oldest Query (Preview) longest_query_time_sec Seconds The age in seconds of the longest query that is currently running N/a No
Oldest Transaction (Preview) longest_transaction_time_sec Seconds The age in seconds of the longest transaction (including idle transactions) N/a No
Oldest xmin (Preivew) oldest_backend_xmin Count The actual value of the oldest xmin. If xmin is not increasing it indicates there are some long running transactions that can potentially hold dead tuples from being removed N/a No
Oldest xmin Age (Preivew) oldest_backend_xmin_age Count Age in units of the oldest xmin. It indicated how many transactions passed since oldest xmin N/a No
Database
Display Name Metric ID Unit Description Dimension Default enabled
Backends (Preview) numbackends Count Number of backends connected to this database Database Name No
Deadlocks (Preview) deadlocks Count Number of deadlocks detected in this database Database Name No
Disk Blocks Hit (Preview) blks_hit Count Number of times disk blocks were found already in the buffer cache, so that a read was not necessary Database Name No
Disk Blocks Read (Preview) blks_read Count Number of disk blocks read in this database Database Name No
Temporary Files (Preview) temp_files Count Number of temporary files created by queries in this database Database Name No
Temporary Files Size (Preview) temp_bytes Bytes Total amount of data written to temporary files by queries in this database Database Name No
Total Transactions (Preview) xact_total Count Number of total transactions executed in this database Database Name No
Transactions Committed (Preview) xact_commit Count Number of transactions in this database that have been committed Database Name No
Transactions Rolled back (Preview) xact_rollback Count Number of transactions in this database that have been rolled back Database Name No
Tuples Deleted (Preview) tup_deleted Count Number of rows deleted by queries in this database Database Name No
Tuples Fetched (Preview) tup_fetched Count Number of rows fetched by queries in this database Database Name No
Tuples Inserted (Preview) tup_inserted Count Number of rows inserted by queries in this database Database Name No
Tuples Returned (Preview) tup_returned Count Number of rows returned by queries in this database Database Name No
Tuples Updated (Preview) tup_updated Count Number of rows updated by queries in this database Database Name No
Logical Replication
Display Name Metric ID Unit Description Dimension Default enabled
Max Logical Replication Lag (Preview) logical_replication_delay_in_bytes Bytes Maximum lag across all logical replication slots N/a Yes
Replication
Display Name Metric ID Unit Description Dimension Default enabled
Max Physical Replication Lag (Preview) physical_replication_delay_in_bytes Bytes Maximum lag across all asynchronous physical replication slots N/a Yes
Read Replica Lag (Preview) physical_replication_delay_in_seconds Seconds Read Replica lag in seconds N/a Yes
Saturation
Display Name Metric ID Unit Description Dimension Default enabled
Disk Bandwidth Consumed Percentage disk_bandwidth_consumed_percentage Percent Percentage of data disk bandwidth consumed per minute N/a Yes
Disk IOPS Consumed Percentage disk_iops_consumed_percentage Percent Percentage of data disk I/Os consumed per minute N/a Yes
Traffic
Display Name Metric ID Unit Description Dimension Default enabled
Max Connections^ max_connections Count Max Connections N/a Yes

^ max_connections represents the configured value for max_connections server parameter, and it is collected every 30 minutes.

Applying Filters and Splitting on enhanced metrics

In the above list of metrics, some of the metrics have dimension such as database name, state etc. Filtering and Splitting are allowed for the metrics that have dimensions. These features show how various metric segments ("dimension values") affect the overall value of the metric. You can use them to identify possible outliers.

  • Filtering lets you choose which dimension values are included in the chart. For example, you might want to show idle connections when you chart the Sessions-by-State metric. You apply the filter on the idle on state dimension.
  • Splitting controls whether the chart displays separate lines for each value of a dimension or aggregates the values into a single line. For example, you can see one line for an Sessions-by-State metric across all sessions. Or you can see separate lines for each session grouped by their state. You apply splitting on the State dimension to see separate lines.

Here in this example below, we have done splitting by State dimension and filtered on a specific state types.

image

For more details on setting-up charts with dimensional metrics, see Metric chart examples.

Considerations when using the enhanced metrics

  • There is 50 database limit on metrics with database name dimension.
    • On Burstable SKU - this limit is 10 database name dimension
  • database name dimension limit is applied on OiD column (in other words Order-of-Creation of the database)
  • The database name is metics dimension is case insensitive. Therefore the metrics for same database names in varying case (ex. foo, FoO, FOO) will be merged, and may not show accurate data.

Feedbacks

For any questions and feedback, please reach out to Mailto:AskAzureDBforPostgreSQL@service.microsoft.com

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment