Figure out how to collect any metric using the Azure Monitor integration

[zmoog]

I want to collect the following metrics:

Active Connections
Backup Storage Used
Number of disk blocks read in this database
Connections from clients which are associated with a PostgreSQL/MySQL connection
Total number of credits consumed by the database server
Total number of credits available to burst
CPU percent
Memory percent
Number of deadlocks detected in this database
Percentage of disk bandwidth consumed per minute
IO Operations per second
Indicates if the database is up or not

from the following namespaces:

Azure Database for MySQL flexible servers
Azure Database for PostgreSQL flexible servers

Using the generic Azure Monitor integration.

Overview

Here are the steps

• Create test resources (optional, if you already have servers)
• Explore the PostgreSQL Flexible server metrics on Azure Portal
• Select the metrics to collect
• Write the configuration
• Test run

[zmoog]

Create test resources

I am creating a new PostgreSQL Flexible server to collect metrics from:

I selected the "Development" workload type, I think it will be okay for this purpose.

[zmoog]

PostgreSQL Flexible server metrics in Azure Portal

Overview

With a new PostgreSQL Flexible server up and running, we can start taking a look at the metrics on the Azure Portal.

For example, here are the value for the Active Connections mertric:

Enabling enhanced metrics

From the enhanced metrics docs:

You can use enhanced metrics for Azure Database for PostgreSQL flexible server to get fine-grained monitoring and alerting on databases. You can configure alerts on the metrics. Some enhanced metrics include a Dimension parameter that you can use to split and filter metrics data by using a dimension like database name or state.

Enabling the enhanced metrics seems easy:

Most of these new metrics are disabled by default. There are a few exceptions though, which are enabled by default. Rightmost column in the following tables indicates whether each metric is enabled by default or not. To enable those metrics which are not enabled by default, set the server parameter metrics.collector_database_activity to ON. This parameter is dynamic and doesn't require an instance restart.

On the Azure Portal, visit Settings > Server parameters and search for parameter names containing "metrics":

I am setting all the metrics.* parameters to "ON".

Documentation

Here are a few resources about metrics for the PostgreSQL Flexible server service:

• Monitor metrics on Azure Database for PostgreSQL - Flexible Server
• Supported metrics for Microsoft.DBforPostgreSQL/flexibleServers

[zmoog]

Select the metrics to collect

We now have a PostgreSQL Flexible server instance, so the next step is open the metric reference for the PostgreSQL Flexible server service an look for interesting metrics.

Visit the Supported metrics for Microsoft.DBforPostgreSQL/flexibleServers list.

We can start with the Active Connections metric:

[zmoog]

Write the configuration

Start from the sample config

Here's the sample config from the generic Azure Monitor integration:

- resource_query: "resourceType eq 'Microsoft.DocumentDb/databaseAccounts'"
  metrics:
  - name: ["DataUsage", "DocumentCount", "DocumentQuota"]
    namespace: "Microsoft.DocumentDb/databaseAccounts"

We can use it as a starting point.

Replace query and metric names

Using the information at Supported metrics for Microsoft.DBforPostgreSQL/flexibleServers, we can rewrite the configuration for the PostgreSQL Flexible server metrics.

1. Update the resource_query selecting the resources with type Microsoft.DBforPostgreSQL/flexibleServers
2. Use the "Name in REST API" in the list of metric names
3. Use again Microsoft.DBforPostgreSQL/flexibleServers as a namespace

- resource_query: "resourceType eq 'Microsoft.DBforPostgreSQL/flexibleServers'"
  metrics:
  - name: 
    - "active_connections"
    namespace: "Microsoft.DBforPostgreSQL/flexibleServers"

Before adding more metrics and options, let's give this configuration a try.

[zmoog]

Test run

• Create an agent policy
• Add the generic Azure Monitor integration
• Assign the policy to an agent
• Explore the documents

Create an agent policy

Let's create a brand new agent policy for this test:

Add the generic Azure Monitor integration

Assign the policy to an agent

Pick an existing agent or install a new one, and assign the "Azure Metrics (PostgreSQL Flexible server)" policy to it.

Explore the documents

After a couple of minutes, we can see new documents with the "Active Connections" metrics show up:

[zmoog]

Add extra metrics

Using the same list of Supported metrics for Microsoft.DBforPostgreSQL/flexibleServers, we can add more metrics to the configuration:

- resource_query: "resourceType eq 'Microsoft.DBforPostgreSQL/flexibleServers'"
  metrics:
  - name: 
    - "active_connections"
    - "memory_percent"
    - "storage_free"
    namespace: "Microsoft.DBforPostgreSQL/flexibleServers"

Here are the metrics in Discover:

[zmoog]

Add extra metrics with dimensions

Up until now, we added new metrics without specifying any dimension. Let's try adding another metric definition that includes a dimension.

- resource_query: "resourceType eq 'Microsoft.DBforPostgreSQL/flexibleServers'"
  metrics:
  - name: 
    - "active_connections"
    - "memory_percent"
    - "storage_free"
    namespace: "Microsoft.DBforPostgreSQL/flexibleServers"
  - name: 
    - "numbackends"
    dimensions:
    - name: "DatabaseName"
      value: "*"
    namespace: "Microsoft.DBforPostgreSQL/flexibleServers"

The new numbackends metric definition brings two new fields:

1. The azure.metrics.numbackends.max metric field.
2. The azure.dimensions.database_name dimension field.