Search code examples
postgresqlazuremetricsazure-monitoring

How to view Oldest Query results in Azure Monitor Metrics for an Azure PostgreSQL Flex Server instance

I have an Azure PostgreSQL Flex Server instance deployed with the appropriate shared libraries set, specifically the server parameter, AUTO_EXPLAIN, is enabled among the others required, per the azure docs for identifying slow queries.

The server restarted in a few minutes and I set auto_explain.log_min_duration to 5000 (milliseconds).

I have since made queries against this instance that take 6+ seconds to finish. Yet, in the Azure Monitor Metrics blade I've made a query with the following:

  • scope --> the psql instance
  • Metric Namespace --> PostgreSQL flexible server
  • Metric --> Oldest Query
  • Aggregation type --> Max (only one available)

and there are zero results.

Other metrics work fine such as Database Is Alive.

Is the missing piece to enable Query Store?

Az Flex Server Query Performance Insight

Solution

  • Steps that I took to get this to work:

    1. Browsed to the Azure psql flexible server instance and the Diagnostic Settings blade to create the Diagnostic resource. This is a prerequisite to set up the Query Store for enhanced logs.

    2. On the sae psql flexible instance, I clicked on the Settings | Server parameters blade and filtering on "auto_explain" I confirmed that at minimum the following were set:

      1. auto_explain.log_analyze to ON
      2. auto_explain.log_buffers to ON
      3. auto_explain.log_timing to ON
      4. auto_explain.log_verbose to ON

    3. Browsed to the Monitoring | Troubleshooting guides blade. Confirmed there was a graph at the bottom. Selected the Queries tab and the interval of time (e.g. last hour). There were a list of (5) slowest queries each with a unique id.

    4. Opened an admin (read) connection to the database instance and ran the following sql query against the azure_sys database: SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id = <query_id>; and interpolate the query_id with the numeric id gathered from the previous step. This is the raw sql query that ranked as one of the slowest (if not the slowest) in the query time range.

    References:

    1. https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-configure-and-access-logs?...
    2. https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-identify-slow-queries