Monitoring

This topic describes the metrics, wait events, and diagnostic queries you use to monitor your analytics workload, and how to build dashboards from them. For specific symptoms, see Troubleshooting.

Aurora PostgreSQL reports its analytics activity through four channels. Each channel answers a different question, and most investigations use more than one.

Amazon CloudWatch metrics

The following Amazon CloudWatch metrics are published for Aurora Analytics.

Monitoring local storage performance

Aurora PostgreSQL uses local storage for the on-disk read cache and for query spill files, such as sorts and hash tables that exceed query_mem. Which metrics report that I/O depends on how local storage is attached to the instance class, so check your class first and then use the matching set of metrics below.

Instances with Amazon EBS storage only

Classes such as db.r8g, db.r7g, and db.r7i have no instance store, so the read cache and spill files share the Amazon EBS-backed volume with the rest of the database. Analytical I/O therefore competes with normal PostgreSQL I/O, and it is reported by the general storage metrics rather than by a set of metrics specific to Analytics.

Instances with local NVMe storage

Classes such as db.r8gd and db.r6gd include an NVMe instance store, and Aurora PostgreSQL places the read cache and spill files there instead of on the Amazon EBS volume. This keeps analytical I/O off the database volume and gives the cache much lower read latency. The NVMe device reports through its own set of ephemeral storage metrics, so ReadIOPS, ReadLatency, and DiskQueueDepth no longer reflect analytical I/O on these classes.

Standard Aurora metrics relevant to Analytics

Beyond the metrics specific to analytics, the following standard Aurora metrics are useful for monitoring an analytical workload.

SQL-based diagnostic functions

Aurora PostgreSQL provides SQL-callable functions that report counters Amazon CloudWatch cannot attribute: exact bytes, spill, and thread counts for one query or one backend. Start with the following table to choose a function, then see the SQL functions reference for its columns.

All byte columns report raw bytes, so wrap them in pg_size_pretty() for readable output. For the syntax, permissions, and worked examples of each function, see SQL functions reference.

Sample queries and output

To find queries reading from Amazon S3 instead of the cache, run the following query.

-- Optional: enable the detailed columns, such as CPU time
SET aurora_analytics.track_detailed_metrics = true;

-- Run your queries, then check statistics
SELECT SUBSTR(query, 1, 45) AS query,
       calls,
       pg_size_pretty(analytics_cache_hit_bytes) AS cache_hits,
       pg_size_pretty(analytics_remote_read_bytes) AS s3_reads,
       pg_size_pretty(analytics_total_disk_spill) AS spill,
       round(analytics_total_cpu_time::numeric, 2) AS cpu_ms
FROM aurora_analytics_stat_statements()
WHERE analytics_cache_hit_bytes > 0 OR analytics_remote_read_bytes > 0
ORDER BY analytics_remote_read_bytes DESC;

Interpret the results as follows.

To attribute memory, threads, and spill to a backend, run the following query.

SELECT pid,
       pg_size_pretty(current_memory_usage) AS mem_used,
       active_threads,
       pg_size_pretty(disk_spill_size) AS spill
FROM aurora_analytics_stat_resource_usage()
WHERE current_memory_usage > 0
ORDER BY current_memory_usage DESC;

Join the pid column to pg_stat_activity to see which query each backend is running. Use this when AuroraAnalyticsMemoryUsage is high and you need to know which session is responsible.

To view instance-wide Amazon S3 and cache activity, run the following query.

SELECT s3_head_request_count,
       s3_get_request_count,
       pg_size_pretty(s3_cache_hit_bytes) AS cache_hit,
       pg_size_pretty(s3_remote_read_bytes) AS s3_read
FROM aurora_analytics_instance_metrics();

To check cache size, run the following query.

SELECT pg_size_pretty(aurora_analytics_cache_size()) AS cache_size;

To find active analytical queries, run the following query.

WITH analytics_tables AS (
    SELECT c.relname
    FROM pg_foreign_table ft
    JOIN pg_class c ON c.oid = ft.ftrelid
    JOIN pg_foreign_server fs ON fs.oid = ft.ftserver
    WHERE fs.srvname = 'aurora_analytics_server'
)
SELECT a.pid, a.usename, a.datname,
       NOW() - a.query_start AS runtime, a.state,
       LEFT(a.query, 100) AS query_preview
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND EXISTS (SELECT 1 FROM analytics_tables t WHERE a.query ILIKE '%' || t.relname || '%')
ORDER BY a.query_start;

Interpreting the active thread count

The AuroraAnalyticsActiveThreads Amazon CloudWatch metric and the active_threads column of aurora_analytics_stat_resource_usage() report the number of analytics worker threads that are actively executing at the moment of measurement. This count is often much higher than your number of concurrent sessions, which is expected. The active thread count includes two kinds of worker threads.

Only threads that are actively doing work are counted. A thread that is idle and waiting for work is not included, so the count rises and falls continuously as a query runs.

Because both kinds of threads are counted together, the total can be much higher than the number of concurrent queries. A handful of concurrent queries reading from Amazon S3 on cache misses can produce several hundred active threads. This is normal. A persistently high count driven by Amazon S3 I/O threads indicates a low cache hit ratio, so consider warming or scaling the cache.

These worker threads are internal to the analytics engine and do not appear in pg_stat_activity, which shows only your PostgreSQL sessions. This is why a small number of sessions can drive high CPU usage: a single session can run dozens or hundreds of active threads.

The active thread count answers "why is CPU high with only a few sessions?", while the Extension:AuroraAnalyticsExecute wait event tells you how many sessions are waiting on the analytics engine. For more information, see Interpreting the AuroraAnalyticsExecute wait event.

Monitoring xmin horizon on reader instances

Long-running analytical queries on reader instances hold an xmin snapshot open, which can prevent autovacuum on the writer from reclaiming dead tuples. Running analytical workloads on a dedicated reader isolates this impact; for more information, see Using a dedicated reader instance for analytics.

Set statement_timeout to bound how long a single query can run. Like other PostgreSQL settings, you can set it at the session, role, database, or cluster parameter group level; use the lowest level that fits. Set it per session for a single connection.

SET statement_timeout = 'XX';

Or set it as a default for the analytics role.

ALTER ROLE analytics_user SET statement_timeout = 'XX';

Database Insights

Where Amazon CloudWatch tells you that the instance is under pressure, Database Insights tells you which sessions and which SQL are responsible. Foreign table queries appear there like any other PostgreSQL query, so you use the same average active sessions (AAS) chart, the same Top SQL list, and the same wait event breakdown.

Aurora PostgreSQL adds one wait event for foreign table queries.

To identify top analytical queries, do the following.

Interpreting the AuroraAnalyticsExecute wait event

When a query references a foreign table, the PostgreSQL backend hands execution to the embedded analytics engine and blocks until the engine returns results. During this time, the backend reports the Extension:AuroraAnalyticsExecute wait event. In Database Insights, this appears in the average active sessions (AAS) chart as Extension:AuroraAnalyticsExecute. Keep the following in mind.

Tracking query duration trends

Use Database Insights to track whether average query time is stable or degrading over time. A sudden increase might indicate the following.

Logging

Aurora PostgreSQL can write analytics engine messages to the PostgreSQL error log. Logging is a diagnostic tool rather than a monitoring channel: it produces detail about individual operations, not time series you can trend or alarm on. Two parameters control it. Set them in a DB cluster parameter group to cover the whole workload, or with SET in a single session to scope the output to one investigation. The session-scoped SET requires the rds_superuser role.

Messages go to the PostgreSQL error log. Retrieve it in one of three ways.

Key metrics reference

The following table provides a quick reference for analytics health. Two rows depend on your instance class, so check which storage the class uses before you build alarms from this table. On NVMe classes, FreeLocalStorage does not track the volume that holds the read cache and spill files, so an alarm on it will stay quiet while the instance store fills.