How to Investigate a Slow Query in Aurora MySQL

この文書の適用条件

そのまま実行できるコマンド

-- 対象: 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

結果の読み方

こういう状況で使います

• 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

考えられる原因（可能性の高い順）

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.

確認手順

1. 1

   Identify which instance and time window

   参照のみ

   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

   参照のみ

   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

   参照のみ

   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

   参照のみ

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

5. 5

   Identify top offenders via digest aggregation

   参照のみ

   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

   低

   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.

対応方法

バージョン・環境による違い

これで解決しない場合に確認すること

• 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.

この文書の根拠と限界

よくある質問

この文書がカバーする質問

• 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

リスク表示の意味

• 参照のみデータと設定を変更しません。
• 低影響は限定的ですが、権限と負荷の確認が必要です。
• 中性能・ロック・コストに影響する可能性があります。
• 高障害・データ損失・復旧作業が発生する可能性があります。
• 専門家レビュー必須本番適用前に別途レビューが必須です。

GIIPの対応範囲

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.

関連サービス

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