SELECT
table_name,
CASE
WHEN raw_sql LIKE '%namespaced_controller:%' THEN
CONCAT(
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_sql, 'namespaced_controller:', -1), '*/', 1), ',', 1)),
'#',
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_sql, 'action:', -1), '*/', 1), ',', 1))
)
WHEN raw_sql LIKE '%sidekiq_worker:%' THEN
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_sql, 'sidekiq_worker:', -1), '*/', 1), ',', 1))
WHEN raw_sql LIKE '%rake_task:%' THEN
CONCAT('rake:', TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_sql, 'rake_task:', -1), '*/', 1), ',', 1)))
ELSE 'unknown'
END AS query_source,
tx_duration_seconds AS max_tx_duration_seconds
FROM (
SELECT
CASE
WHEN ml.OBJECT_NAME LIKE '#sql-%' THEN 'DDL_IN_PROGRESS'
ELSE ml.OBJECT_NAME
END AS table_name,
COALESCE(esc.SQL_TEXT, it.trx_query, '') AS raw_sql,
TIMESTAMPDIFF(SECOND, it.trx_started, NOW()) AS tx_duration_seconds,
ROW_NUMBER() OVER (
PARTITION BY CASE WHEN ml.OBJECT_NAME LIKE '#sql-%' THEN 'DDL_IN_PROGRESS' ELSE ml.OBJECT_NAME END
ORDER BY TIMESTAMPDIFF(SECOND, it.trx_started, NOW()) DESC
) AS rn
FROM performance_schema.metadata_locks ml
JOIN performance_schema.threads th
ON ml.OWNER_THREAD_ID = th.THREAD_ID
JOIN information_schema.innodb_trx it
ON th.PROCESSLIST_ID = it.trx_mysql_thread_id
LEFT JOIN performance_schema.events_statements_current esc
ON th.THREAD_ID = esc.THREAD_ID
WHERE ml.OBJECT_TYPE = 'TABLE'
AND ml.OBJECT_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
AND TIMESTAMPDIFF(SECOND, it.trx_started, NOW()) >= 5
) ranked
WHERE rn = 1
default_zero(avg:custom.mysql.mdl_holder.max_tx_duration_by_table{account:timee-jp-prod,replication_role:writer, !query_source:unknown, !query_source:rake:tmp:*} by {query_source})
根本原因は、スイッチオーバーでクラスターエンドポイントの参照先がBlueからGreenに切り替わることです。その結果、Blue環境とGreen環境ではバイナリログのファイルとポジションに互換性がないため、Datastreamを再開できません。
Managing AWS DMS Tasks with RDS or Aurora Blue/Green Deployments の「How Blue Green switchover affects AWS DMS tasks」セクションに、バイナリログのファイル名とポジションは Blue・Green 間で異なると記載されています。これは DMS のドキュメントですが、ファイル名とポジションが変わるのは DB 側の挙動であるため、Datastream でも同様に問題になります。
【原文】
Because the binary log file names and sequence positions differ between the two instances, DMS can no longer resume from the log position it previously recorded. This causes Full Load + CDC tasks and CDC only tasks to fail or enter an error state.
ここで、機能開発を担当する Stream-aligned Team のドメイン知識が必要になりました。
Platform Team 側では Datadog を見ながら負荷の原因を整理し、Stream-aligned Team 側では仕様や処理の中身を確認しました。Slack やハドル、Datadog Notebook で状況を共有しながら、「どの処理が負荷に寄与しているのか」「仕様を壊さず処理を変えられるのか」「もし改善しない場合に、停止などの措置は取り得るのか」を相談しました。
しかし、この方法にはリスクがあります。バッファプール以外にも、MySQLの内部処理やOS、各種バックグラウンドプロセスがメモリを使用しています。バッファプールの比率を引き上げすぎると、これらに必要なメモリが不足し、最悪の場合OOM（Out of Memory）でインスタンスがクラッシュする可能性があります。
/* comment 1 */ SELECT * FROM test_table WHERE id = 1;
/* comment 2 */ SELECT * FROM test_table WHERE id = 1;
/* comment 3 */ SELECT * FROM test_table WHERE id = 2;
各重複排除モードで評価 SQL セットを作成した結果、行数は以下のようになりました。
重複排除モード
結果行数
区別の仕方
比較イメージ
しない
3
すべてのクエリをそのまま対象にする
-
SQL ID
3
コメントも含めた SQL 文の完全一致による重複排除が行われる
/* comment 1 */ SELECT * FROM test_table WHERE id = 1;
PI Hash
1
コメントの有無にかかわらず、リテラル値が異なる SQL のみ区別される
SELECT * FROM test_table WHERE id = ?
PI Hash モードではクエリが完全にパターン化されるため、上記の入力クエリはすべて同一のものとして扱われます。一方、SQL ID での重複排除はコメントも含めたクエリ文の完全一致による比較が行われます。
まとめると以下のようになります。
SQL ID: コメントも含めた SQL 文の完全一致で重複排除する。
同じクエリでもコメントが異なると別物として扱われ、重複排除されない。
PI Hash: リテラル値の違いで区別しない（コメントの有無は問わない）。
例えば WHERE id = 1 と WHERE id = 2 のように リテラルだけが違う SQL は同一として扱われる。
検証方法まとめ
これらを踏まえてまとめると、以下のように検証を行うことにしました。
項目
SQL 互換性検証
パフォーマンス検証
目的
構文エラーの検出
アップグレード後の SQL のパフォーマンス劣化可能性の検出
方針
PI Hash によるクエリ構文のパターン化が行われたクエリで構文エラーの確認を行う
利用されている SQL クエリの実行比較を行い、アップグレード前後でパフォーマンスの変化を確認する
利用する重複排除モード
PI Hash
SQL ID
テスト件数
約 31 万件
約 6700 万件
対象期間
過去 1ヶ月の SQL クエリ
全定期ジョブをカバーする最小のテスト期間（3 区間・合計 7 日間）
互換性検査（1ヶ月）が約 31 万件、性能検査（7 日間）が約 6700 万件と、期間が短い性能検査のほうが件数が多くなっています。これは、PI Hash がリテラル値まで畳んでクエリをパターン化するため件数が激減するのに対し、SQL ID はリテラル値の違いを区別するため件数が大きく残るためです。
このうち、クライアント上で表示される結果順序が変わってしまうなど、実際のプロダクトに問題が発生するクエリについては、該当クエリに ORDER BY を追加する等の対応を入れました。保存されているデータ自体に差分は確認されなかったため、現時点ではこれらの対応により、アップグレードに伴う影響は許容範囲内であると判断しています。
