Amazon Aurora — スロークエリログ / 監査ログによる取得とセクション定義

# スロークエリログを有効化（1秒以上のクエリをログ）
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-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" \
    "ParameterName=log_queries_not_using_indexes,ParameterValue=0,ApplyMethod=immediate"

# CloudWatch Logs にスロークエリログをエクスポート
aws rds modify-db-cluster \
  --db-cluster-identifier my-aurora-cluster \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["slowquery","error","general"]}' \
  --apply-immediately

# Time: 2026-05-29T10:15:30.123456Z
# User@Host: appuser[appuser] @ 10.0.1.50 []  Id: 12345
# Query_time: 5.234567  Lock_time: 0.000123  Rows_sent: 1  Rows_examined: 1234567
SET timestamp=1748513730;
SELECT COUNT(*) FROM orders WHERE status = 'pending' AND created_at < '2026-01-01';

# 1秒以上のクエリをスロークエリとしてログに記録
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name my-aurora-pg-params \
  --parameters \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=none,ApplyMethod=immediate" \
    "ParameterName=log_connections,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=log_disconnections,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=log_line_prefix,ParameterValue='%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ',ApplyMethod=immediate"

# CloudWatch Logs にログをエクスポート
aws rds modify-db-cluster \
  --db-cluster-identifier my-aurora-pg-cluster \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql"]}' \
  --apply-immediately

2026-05-29 10:15:30.123 UTC [12345]: [3-1] user=appuser,db=mydb,app=webapp,client=10.0.1.50
  LOG:  duration: 5234.567 ms  statement: SELECT COUNT(*) FROM orders
        WHERE status = 'pending' AND created_at < '2026-01-01';

-- スロークエリの実行時間が長い上位を集計
fields @timestamp, @message
| filter @message like /Query_time/
| parse @message "Query_time: * " as query_time
| parse @message "Rows_examined: * " as rows_examined
| parse @message /SET timestamp=\d+;\n(?P.+)$/
| stats
    count() as executions,
    avg(toNumber(query_time)) as avg_query_sec,
    max(toNumber(query_time)) as max_query_sec,
    sum(toNumber(rows_examined)) as total_rows_examined
  by sql_text
| sort max_query_sec desc
| limit 20

-- duration が大きいクエリを集計
fields @timestamp, @message
| filter @message like /duration:/
| parse @message "duration: * ms" as duration_ms
| parse @message "statement: *" as sql_text
| stats
    count() as executions,
    avg(toNumber(duration_ms)) as avg_ms,
    max(toNumber(duration_ms)) as max_ms
  by sql_text
| sort max_ms desc
| limit 20

-- 時間帯別のスロークエリ発生数
fields @timestamp, @message
| filter @message like /duration:/
| parse @message "duration: * ms" as duration_ms
| filter toNumber(duration_ms) > 3000
| stats count() as slow_count by bin(5m)
| sort @timestamp asc

# Database Activity Streams を同期モードで有効化
aws rds start-activity-stream \
  --resource-arn arn:aws:rds:ap-northeast-1:123456789012:cluster:my-aurora-cluster \
  --mode sync \
  --kms-key-id arn:aws:kms:ap-northeast-1:123456789012:key/YOUR-KEY-ID \
  --apply-immediately