How to Investigate a Slow Query in Aurora MySQL

Applicability of This Document

Ready-to-Run Commands

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: 接続権限（グローバル変数の参照のみ）
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
    'slow_query_log',
    'long_query_time',
    'log_output',
    'log_queries_not_using_indexes',
    'min_examined_row_limit',
    'performance_schema'
);

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: 自分以外のセッションを見るには PROCESS 権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME                    AS elapsed_sec,
    STATE,
    LEFT(INFO, 200)         AS query_head
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;

-- 対象: Aurora MySQL 2.x / 3.x
-- 権限: PROCESS 権限
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    trx_id,
    trx_state,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS open_sec,
    trx_mysql_thread_id,
    trx_rows_locked,
    trx_rows_modified,
    LEFT(trx_query, 200)                      AS current_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started ASC;

-- 対象: Aurora MySQL 2.x / 3.x（performance_schema = ON が前提）
-- 権限: performance_schema への SELECT
-- 変更作業: なし（参照のみ）
-- Production 実行: 可能
SELECT
    SCHEMA_NAME,
    LEFT(DIGEST_TEXT, 200)                          AS digest_head,
    COUNT_STAR                                      AS exec_count,
    ROUND(SUM_TIMER_WAIT / 1000000000000, 3)        AS total_sec,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 6)        AS avg_sec,
    SUM_ROWS_EXAMINED                               AS rows_examined,
    SUM_ROWS_SENT                                   AS rows_sent,
    SUM_NO_INDEX_USED                               AS no_index_used,
    SUM_CREATED_TMP_DISK_TABLES                     AS tmp_disk_tables,
    FIRST_SEEN,
    LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

-- 対象: EXPLAIN は Aurora MySQL 2.x / 3.x、EXPLAIN ANALYZE は 3.x（MySQL 8.0互換）のみ
-- 権限: 対象テーブルへの SELECT
-- 変更作業: なし（EXPLAIN ANALYZE はクエリを実際に実行する点に注意）
-- Production 実行: EXPLAIN は可能／EXPLAIN ANALYZE は実行負荷を許容できる場合のみ

-- 1) 実行計画だけを見る（クエリは実行されない）
EXPLAIN
SELECT id, name, updated_at
FROM SampleDB.sample_table
WHERE status = 'active'
  AND updated_at >= '2026-08-01'
ORDER BY updated_at DESC
LIMIT 100;

-- 2) 実測値つきの実行計画（Aurora MySQL 3.x のみ。クエリを実際に実行する）
EXPLAIN ANALYZE
SELECT id, name, updated_at
FROM SampleDB.sample_table
WHERE status = 'active'
  AND updated_at >= '2026-08-01'
ORDER BY updated_at DESC
LIMIT 100;

# 対象: Aurora MySQL 2.x / 3.x（DBパラメータグループとDBクラスター設定）
# 権限: IAM rds:ModifyDBParameterGroup, rds:ModifyDBCluster
# 変更作業: あり（パラメータ変更 + ログ出力先の変更）
# Production 実行: 変更管理を通したうえで実施。ログ量とストレージ増加を見込むこと

# 1) スロークエリログを有効化する（いずれも動的パラメータとして扱われる想定。
#    実際の反映方法は describe-db-parameters の ApplyType で確認すること）
aws rds modify-db-parameter-group \
  --db-parameter-group-name example-aurora-mysql-params \
  --parameters '[
    {"ParameterName":"slow_query_log","ParameterValue":"1","ApplyMethod":"immediate"},
    {"ParameterName":"long_query_time","ParameterValue":"1","ApplyMethod":"immediate"},
    {"ParameterName":"log_output","ParameterValue":"FILE","ApplyMethod":"immediate"}
  ]'

# 2) クラスター単位でスロークエリログをCloudWatch Logsへ出力する
aws rds modify-db-cluster \
  --db-cluster-identifier example-aurora-cluster \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["slowquery","error"]}' \
  --apply-immediately

# 3) 反映後の値を確認する
aws rds describe-db-parameters \
  --db-parameter-group-name example-aurora-mysql-params \
  --query "Parameters[?ParameterName=='slow_query_log' || ParameterName=='long_query_time'].{Name:ParameterName,Value:ParameterValue,Apply:ApplyType,Status:ApplyMethod}" \
  --output table

# 対象: Aurora MySQL 2.x / 3.x（DBパラメータグループ）
# 権限: IAM rds:ModifyDBParameterGroup, rds:RebootDBInstance
# 変更作業: あり（静的パラメータ変更 + インスタンス再起動）
# Production 実行: 不可（再起動を伴うためメンテナンス時間帯に計画実行）

# 1) performance_schema を有効化する（再起動時に反映）
aws rds modify-db-parameter-group \
  --db-parameter-group-name example-aurora-mysql-params \
  --parameters '[
    {"ParameterName":"performance_schema","ParameterValue":"1","ApplyMethod":"pending-reboot"}
  ]'

# 2) 反映のためインスタンスを再起動する（接続断が発生する）
aws rds reboot-db-instance --db-instance-identifier example-aurora-instance

How to Read the Results

When to Use This

• The same query that was fine until yesterday suddenly became slow at some point
• Timeouts have increased on the application side, but CPU usage is not high
• Only a specific screen or batch job is slow; other processing is normal
• Tried to check Performance Insights, but details don't appear because `performance_schema` is disabled
• The same query remains in `SHOW PROCESSLIST` for a long time

Possible Causes (Most Likely First)

1. 01

   The execution plan changed and the index is no longer being used

   An increase in data volume or a statistics update can cause the optimizer to choose a different access method. If `rows_examined` is extremely large compared to `rows_sent`, this is likely the cause.

2. 02

   Execution is stalled due to lock waiting

   Even if the query itself is lightweight, it will wait if another transaction holds a row lock. Check whether `information_schema.INNODB_TRX` shows a transaction that has remained open for a long time.

3. 03

   Scan cost increased due to growing data volume

   Even with the same execution plan, processing time increases as the number of target rows grows. In this case, the index design or search conditions need to be reviewed; parameter tuning alone will not resolve it.

4. 04

   Sort operations or temporary tables are spilling to disk

   When the target of `ORDER BY` or `GROUP BY` is large, a temporary table is created on disk. This can be checked via `SUM_CREATED_TMP_DISK_TABLES` in the digest aggregation.

5. 05

   Replica lag or resource contention on the reader instance side

   If reads are directed to the reader endpoint, the load situation differs between the writer and reader. First isolate which instance is slow.

Diagnostic Steps

1. 1

   Identify which instance and time window

   Read-only

   Check CloudWatch's `CPUUtilization`, `DatabaseConnections`, `ReadLatency` / `WriteLatency`, and `Deadlocks` to narrow down the time window and instance where the event occurred.

2. 2

   Check the slow query log settings

   Read-only

   Run the `SHOW GLOBAL VARIABLES` command above to check whether logging is actually happening and whether `long_query_time` matches the real situation.

3. 3

   Check running sessions

   Read-only

   Use `information_schema.PROCESSLIST` to check whether the slow query is actually running or waiting. The `STATE` column is a clue.

4. 4

   Check long-running transactions and locks

   Read-only

   Refer to `information_schema.INNODB_TRX`. This can be run even when `performance_schema` is disabled.

5. 5

   Identify top offenders via digest aggregation

   Read-only

   If `performance_schema` is enabled, check `events_statements_summary_by_digest` sorted by cumulative execution time in descending order.

6. 6

   Check the execution plan of the target SQL

   Low

   Use `EXPLAIN` to check the access method, index used, and estimated row count. Only use `EXPLAIN ANALYZE` (Aurora MySQL 3.x) when you want to see the gap between estimate and actual, and only within a load the system can tolerate.

Solutions

Differences by Version and Environment

If This Doesn't Resolve It

• Check application-side timeout and connection pool settings

  If things look slow even though the DB-side execution time hasn't changed, connection waiting or pool exhaustion can be the cause.

• Isolate whether the slowness is on the writer or reader side

  Run the same SQL on the writer and reader separately to see if there's a difference. If there is, suspect an instance-specific load factor.

• Check CloudWatch's `ReadIOPS` / `WriteIOPS` and buffer pool hit ratio

  The approach differs depending on whether the wait is on the storage side or the CPU side.

• Cross-reference schema changes or deployment history with the timing

  Check whether an index was dropped or an application query was changed around the time things slowed down.

• Check whether the same symptom appears across multiple queries

  The direction of investigation differs depending on whether it's a single-query problem or an instance-wide problem.

Basis and Limitations of This Document

FAQ

Questions This Document Covers

• Want to enable and check the slow query log in Aurora MySQL
• How to proceed with performance analysis in an environment where performance_schema=OFF
• Want to check running queries and wait states in MySQL
• Want to judge from EXPLAIN results whether the index is not being used effectively

What the Risk Labels Mean

• Read-onlyDoes not change data or settings.
• LowImpact is limited, but permissions and load should be checked.
• MediumMay affect performance, locking, or cost.
• HighMay cause an outage, data loss, or require recovery work.
• Expert Review RequiredRequires a separate review before applying to production.

GIIP's Scope of Support

The procedure in this article can get you as far as identifying a single slow query. What's difficult in actual operations is that information from "the moment" it became slow often isn't preserved. GIIP continuously collects the slow query log and CloudWatch metrics, and also records the running sessions and transaction state at the moment a threshold is exceeded, so that an investigation can begin afterward without waiting to reproduce the issue. Day-to-day collection and initial triage are handled by an AI agent, and a human takes over at the stage where judgment is required, such as changing an execution plan or adding an index.

Related Services

• データベース性能チューニング
• 24時間監視の対象を確認する