How can we investigate Aurora writer bottlenecks from a reader?

November 16, 2026 4 answers
Asked by 2a!cobaltspoon_763

We use Aurora MySQL and Aurora PostgreSQL, and ad-hoc queries are restricted to the reader endpoint. That makes it hard to investigate live waits, locks, and query plans on the writer.

Can a reader expose the writer's activity, and what is a safe way to get more detail when it cannot? Also, how useful is an execution plan captured on a reader?

Answers

_c71+gaugepiston.8

The activity views are generally instance-local. Connecting to a reader and querying pg_stat_activity or MySQL performance_schema shows activity on that reader, not a global view of the writer. Replica status information is not the same thing as the writer's process list or lock activity.

For a first pass without a SQL connection to the writer, look at the writer instance in the RDS performance dashboards, including Database Insights or Performance Insights, and CloudWatch metrics. Those can help narrow down load, waits, and SQL before deciding whether a closer look is needed.

2a!cobaltspoon_763:

So the dashboard can point us toward the writer's problem, but a reader session won't give us the writer's live lock view?

Vexnari.4u:

Right. For live, instance-local details, connect to the affected instance. Check the writer specifically rather than assuming the reader endpoint landed somewhere useful.

Reply to this answer
k.578+plumwicket

For deeper triage, consider a separate monitoring role and a controlled path to the writer endpoint. In PostgreSQL, pg_monitor is a starting point for visibility. In MySQL, PROCESS and narrowly scoped SELECT access to performance_schema and sys are relevant starting points. Check the exact privileges and visibility needed for your engine version.

Read-only permissions reduce the risk of changes, but they do not make expensive SELECT queries harmless. Restrict who can use the connection, use timeouts where available, and keep routine reporting on readers. A controlled access process is useful when the writer needs a closer look.

Reply to this answer
4h+byteferrule_629

Plain EXPLAIN on a reader can be a useful starting point, especially if the schema, statistics, and planner settings match. It is not a guarantee of the writer's exact plan. Check instance settings and other planning conditions before treating the plans as interchangeable.

EXPLAIN ANALYZE is different because it executes the statement. Reader timings reflect the reader's cache and workload, not necessarily the writer's. For an accurate picture of execution on the writer, you need writer-side evidence, but running the statement there can add load or make changes. Be particularly careful with write statements.

Reply to this answer
_v69!copperharbor.4

Also, reader-only does not mean unlimited analytics are free. Long-running reader queries can have effects beyond that reader, so watch query duration and the impact on the cluster. For heavy reporting, consider whether a separate replica cluster is a better fit.

6y~plaidnotch_417:

Check the behavior for your engine and version before assuming a particular transaction or history effect applies to both MySQL and PostgreSQL.

Reply to this answer

Add your answer

Answers are reviewed before publication.

Related Discussions