AnalyticDB:Slow query diagnostics

By time range

Query all slow statements executed in the past 30 minutes:

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min';

Query all slow statements executed on a specific date (for example, February 26, 2024):

SELECT
    query_start       AS "Start time",
    query_end         AS "End time",
    query_duration_ms AS "Duration (ms)",
    query_id          AS "Query ID",
    query             AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-27 00:00:00';

By resource consumption (instance level)

query_duration_ms is the sum of four sub-fields:

When a query is slow, check which sub-field dominates to identify the bottleneck. For example, a high queue_wait_time_ms indicates resource queue contention rather than an execution problem.

Top 20 by CPU time in the past 30 minutes:

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;

Top 20 by memory usage in the past 30 minutes:

SELECT
    pg_size_pretty(mem_bytes) AS "Memory consumed",
    query_start               AS "Start time",
    query_end                 AS "End time",
    query_duration_ms         AS "Duration (ms)",
    query_id                  AS "Query ID",
    query                     AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY mem_bytes DESC
LIMIT 20;

mem_bytes records the cumulative peak memory usage across nodes. This value approximates actual memory usage and is not exact because memory fluctuates during query execution.

Top 10 by CPU time in a specific time window (for example, 00:00–12:00 on February 26, 2024):

SELECT
    (cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                         AS "Start time",
    query_end                           AS "End time",
    query_duration_ms                   AS "Duration (ms)",
    query_id                            AS "Query ID",
    query                               AS "SQL statement"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
  AND query_end   <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;

By resource consumption (node level)

Use qmonitor.host_slow_queries to diagnose slow queries on a specific node.

Top 20 by CPU time on a node in the past 30 minutes:

SELECT
    (host_cpu_time_ms / 1000)::text || ' s' AS "CPU time",
    query_start                              AS "Start time",
    query_end                                AS "End time",
    query_duration_ms                        AS "Duration (ms)",
    query_id                                 AS "Query ID",
    query                                    AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

Top 20 by memory usage on a node in the past 30 minutes:

SELECT
    pg_size_pretty(host_mem_bytes) AS "Memory consumed",
    query_start                    AS "Start time",
    query_end                      AS "End time",
    query_duration_ms              AS "Duration (ms)",
    query_id                       AS "Query ID",
    query                          AS "SQL statement"
FROM qmonitor.host_slow_queries
WHERE hostname    = '<node_hostname>'
  AND query_start >= now() - interval '30 min'
ORDER BY host_mem_bytes DESC
LIMIT 20;

Replace <node_hostname> with the actual hostname of the node to diagnose.

By user

Count slow queries per user in the past 10 minutes to identify which accounts generate the most slow queries:

SELECT
    user_name AS "User",
    COUNT(1)  AS "Slow query count"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;

By query ID

Retrieve the full details of a specific slow query by its ID:

SELECT * FROM qmonitor.instance_slow_queries
WHERE query_id = '<query_id>';

slow_query_min_duration

Controls the minimum execution time for a query to be recorded as a slow query. The default is 1s. You can also modify this configuration item to collect slow queries that consume less than 1 second.

• If execution time is greater than or equal to this value, the SQL statement, execution time, and related metrics are recorded.

• Set to -1 to disable slow query collection entirely.

To change the threshold for a specific database (requires superuser):

-- Collect slow queries that take 5 seconds or longer.
ALTER DATABASE '<database_name>' SET slow_query_min_duration = '5s';

To change the threshold for the current session only (any user can run this):

SET slow_query_min_duration = '5s';

slow_query_plan_min_duration

Controls the minimum execution time for a slow query's execution plan to be collected. The default is 10s.

• If execution time is greater than or equal to this value, the execution plan is collected alongside the query metrics.

• For ad-hoc plan inspection, use EXPLAIN instead—you do not need to collect plans automatically.

• Set to -1 to disable execution plan collection entirely.

To change the threshold for a specific database (requires superuser):

ALTER DATABASE '<database_name>' SET slow_query_plan_min_duration = '10s';

To change the threshold for the current session only:

SET slow_query_plan_min_duration = '10s';

qmonitor.instance_slow_queries (instance level)

qmonitor.host_slow_queries (node level)