MySQL HeatWave の制限事項と RDS for MySQL → HeatWave on AWS の DMS レプリケーションを実際に試してみた

MySQL HeatWave の制限事項と RDS for MySQL → HeatWave on AWS の DMS レプリケーションを実際に試してみた HeatWavejp Meetup #04 2023/10/12 まつひさ（hmatsu47）

自己紹介 松久裕保（@hmatsu47） ● https://qiita.com/hmatsu47 ● 現在のステータス： ○ 名古屋で Web インフラのお守り係をしています ■ 2017 年秋に AWS 上へ引っ越し完了 ○ 現在は自社サービスのセキュリティ強化中 ○ そしてなぜかフロントエンド刷新に取り組み中 ○ ついでに MySQL HeatWave をのんびり検証中 2

• https://qiita.com/hmatsu47

本日お話しする内容 ● MySQL HeatWave の制限事項 ○ MySQL DB で実行可能な SQL 文の一部が実行不可 ■ 基本的には HeatWave にオフロードされず MySQL DB で実行 ○ MySQL DB → HeatWave 間のデータ不整合を引き起こすものも →実際に試してみた ● MySQL HeatWave on AWS でのレプリケーション制限 ○ 現時点ではインバウンド binlog レプリケーションに非対応 → DMS で代用可能か試してみた 3

MySQL HeatWave 制限事項について：おことわり ● 2023/10/11 現在の検証結果です ○ MySQL HeatWave on AWS version 8.1.0-u4-cloud で実行 ● Qiita に書いた記事を再検証したものです ○ https://qiita.com/hmatsu47/items/a9667762fb5ecdd66e75 からの 5 記事 ● 2023/8・9 検証時から結果が一部変わりました ○ Qiita 記事には後日追記予定です ○ 今後も結果が変わる可能性があります 4

• https://qiita.com/hmatsu47/items/a9667762fb5ecdd66e75

MySQL HeatWave 制限事項について：おことわり ● 検証結果には誤りがある可能性があります ○ 2023/10/11 に慌てて再検証したので誤認がある可能性も 5

HeatWave 制限事項① CASCADE UPDATE/DELETE ● ON UPDATE(DELETE) CASCADE 付きのテーブル ○ MySQL DB 側で UPDATE/DELETE ■ ○ 成功後、HeatWave 側で SELECT するとエラー https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html 6

• https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html

HeatWave 制限事項① CASCADE UPDATE/DELETE ● ON UPDATE(DELETE) CASCADE 付きのテーブル ○ MySQL DB 側で UPDATE/DELETE ■ ○ 成功後、HeatWave 側で SELECT するとエラーデータ不整合発覚 https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html 7

• https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html

HeatWave 制限事項① CASCADE UPDATE/DELETE ● テーブルを作成し初期データを登録 8

HeatWave 制限事項① CASCADE UPDATE/DELETE ● HeatWave にロード 9

HeatWave 制限事項① CASCADE UPDATE/DELETE ● HeatWave でデータを確認（area テーブル） 10

HeatWave 制限事項① CASCADE UPDATE/DELETE ● HeatWave でデータを確認（person テーブル） 11

HeatWave 制限事項① CASCADE UPDATE/DELETE ● area テーブル（親テーブル）から 1 行削除 12

HeatWave 制限事項① CASCADE UPDATE/DELETE ● HeatWave 側で親テーブルの行削除は反映されている 13

HeatWave 制限事項① CASCADE UPDATE/DELETE ● HeatWave 側で子テーブルの行削除が反映されていない 14

HeatWave 制限事項① CASCADE UPDATE/DELETE ● 参考：MySQL DB 側（削除されている） 15

HeatWave 制限事項② 長い TEXT 型 ● TEXT 型の列を持つテーブル ○ 65532 バイトを超える TEXT 列を持つ行を INSERT ■ ○ 成功後、HeatWave 側で SELECT するとエラー https://dev.mysql.com/doc/heatwave/en/mys-hw-troubleshooting.html 16

• https://dev.mysql.com/doc/heatwave/en/mys-hw-troubleshooting.html

HeatWave 制限事項② 長い TEXT 型 ● テーブル作成・初期データ登録＆ HeatWave にロード 17

HeatWave 制限事項② 長い TEXT 型 ● HeatWave 側で SELECT 18

HeatWave 制限事項② 長い TEXT 型 ● 65533 バイトの TEXT 列を INSERT 19

HeatWave 制限事項② 長い TEXT 型 ● HeatWave 側で SELECT →エラー ● 20

HeatWave 制限事項③ SYSTEM 以外の time_zone ● SYSTEM（+00:00）以外の time_zone 設定時 ○ TIMESTAMP 型の列を UPDATE ■ ○ MySQL DB と HeatWave の間でデータ不整合が発生 https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html 21

• https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html

HeatWave 制限事項③ SYSTEM 以外の time_zone ● SYSTEM（+00:00）以外の time_zone 設定時 ○ TIMESTAMP 型の列を UPDATE ■ MySQL DB と HeatWave の間でデータ不整合が発生起きなくなった 但し「Asia/Tokyo」形式の time_zone では明示的にエラーが発生するように ○ https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html 22

• https://dev.mysql.com/doc/heatwave/en/mys-hw-restrictions-change-propagation.html

HeatWave 制限事項③ SYSTEM 以外の time_zone ● タイムゾーン設定 23

HeatWave 制限事項③ SYSTEM 以外の time_zone ● テーブル作成・初期データ登録＆ HeatWave にロード 24

HeatWave 制限事項③ SYSTEM 以外の time_zone ● HeatWave 側で SELECT 25

HeatWave 制限事項③ SYSTEM 以外の time_zone ● TIMESTAMP 列の値を UPDATE 26

HeatWave 制限事項③ SYSTEM 以外の time_zone ● HeatWave 側で SELECT →値は正常 ● 27

HeatWave 制限事項④ 整数部が 18 桁を超える数値 ● 例：整数部 19 桁の DECIMAL 型の列を持つテーブル ○ 整数部 19 桁の値を入れた行を INSERT ■ ○ 成功後、HeatWave 側で SELECT するとエラー https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html 28

• https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html

HeatWave 制限事項④ 整数部が 18 桁を超える数値 ● 例：整数部 19 桁の DECIMAL 型の列を持つテーブル ○ 整数部 19 桁の値を入れた行を INSERT ■ ○ 成功後、HeatWave 側で SELECT するとエラーしてもエラーが出なくなった https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html 29

• https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html

HeatWave 制限事項④ 整数部が 18 桁を超える数値 ● 整数部 19 桁の値を HeatWave で SELECT →正常 30

MySQL HeatWave 制限事項について：まとめ ● データ変更（追加・更新・削除）の伝搬に関する制限 ○ MySQL DB → HeatWave の間でデータ不整合が発生するケース ○ 追加・更新・削除のタイミングではエラーが発生せず ■ 気づかないうちに不整合が発生 ○ その後 SELECT したときに不整合が発覚／エラーが発生 ■ トランザクション処理中なら ROLLBACK 可能 ■ そうでなければセカンダリのアンロード→再ロードが必要に →公式ドキュメント記載の制限のうち一部はすでに解消されていた 31

HeatWave on AWS のレプリケーション制限 ● インバウンド binlog レプリケーション非対応 ○ https://dev.mysql.com/doc/heatwave-aws/en/mysql-server-limitations.html 32

• https://dev.mysql.com/doc/heatwave-aws/en/mysql-server-limitations.html

HeatWave on AWS のレプリケーション制限 ● DMS で代用可能か試してみた ○ 詳細はこちらの Qiita 記事を参照 ■ https://qiita.com/hmatsu47/items/34f4ab2047b95f767f18 33

• https://qiita.com/hmatsu47/items/34f4ab2047b95f767f18

HeatWave on AWS のレプリケーション制限 ● 注：こちらの実験結果も無保証です ○ 今後実行結果や利用可否などが変わる可能性があります ■ PrivateLINK 対応時など 34

RDS→HeatWave on AWS の DMS レプリケーション ● 大まかな流れ（1/4） ○ ソース DB（RDS for MySQL 8.0）インスタンスを作成 ■ パラメータグループで binlog を ROW 形式に ○ ソース DB の binlog 出力・保存を有効化 ■ バックアップ保持期間を 1 日以上に ■ binlog retention hours を設定 ○ ソース DB にテストデータを投入 ■ OCI チュートリアルの TPC-H 用サンプルデータ 35

RDS→HeatWave on AWS の DMS レプリケーション ● 大まかな流れ（2/4） ○ ターゲット DB（MySQL HeatWave on AWS）を作成 ○ DMS レプリケーションを設定 (1) ■ レプリケーションサブネットグループ作成 ■ レプリケーションインスタンス作成（パブリックアクセス可能に） ■ レプリケーションインスタンスのパブリック IP アドレスを確認 ○ HeatWave on AWS の Networking 設定を変更 ■ レプリケーションインスタンスのパブリック IP アドレスから接続可能に 36

RDS→HeatWave on AWS の DMS レプリケーション ● 大まかな流れ（3/4） ○ DMS レプリケーションを設定 (2) ■ ソース／ターゲットエンドポイント作成 ■ データ移行タスク作成（テーブルマッピングおよび変換ルールを適宜調整） ● DMS レプリケーションを確認 ○ 初期データの移行（ロード）完了を待つ ○ テーブル統計確認 ○ ソース／ターゲット DB のデータ状態確認 37

RDS→HeatWave on AWS の DMS レプリケーション ● 大まかな流れ（4/4） ○ HeatWave へのオフロードを有効化 ○ データを INSERT ■ CDC レプリケーション確認 38

INSERT 前のデータ mysql> USE tpch; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +----------------+ | Tables_in_tpch | +----------------+ | customer | | lineitem | | nation | | orders | | part | | partsupp | | region | | supplier | +----------------+ 8 rows in set (0.01 sec) 39

INSERT 前のデータ mysql> SELECT * FROM region; +-------------+-------------+-------------------------------------------------------------------------------| R_REGIONKEY | R_NAME | R_COMMENT +-------------+-------------+-------------------------------------------------------------------------------| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests | 1 | AMERICA | hs use ironic, even requests. s | 2 | ASIA | ges. thinly even pinto beans ca | 3 | EUROPE | ly final courts cajole furiously final excuse | 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully fin +-------------+-------------+-------------------------------------------------------------------------------5 rows in set (0.00 sec) 40

データ INSERT 前のテーブル統計 41

データ INSERT mysql> INSERT INTO region SET R_REGIONKEY = 999, R_NAME = 'SPACE', R_COMMENT = 'outside the earth'; 42

INSERT 後のデータ（HeatWave で確認） mysql> SELECT /*+ SET_VAR(use_secondary_engine=FORCED) */ * FROM region; +-------------+-------------+-------------------------------------------------------------------------------| R_REGIONKEY | R_NAME | R_COMMENT +-------------+-------------+-------------------------------------------------------------------------------| 0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests | 1 | AMERICA | hs use ironic, even requests. s | 2 | ASIA | ges. thinly even pinto beans ca | 3 | EUROPE | ly final courts cajole furiously final excuse | 4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully fin | 999 | SPACE | outside the earth +-------------+-------------+-------------------------------------------------------------------------------6 rows in set (0.09 sec) 43

データ INSERT 後のテーブル統計 44

INSERT 後の SELECT（HeatWave で確認） mysql> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> -> SELECT l_returnflag, l_linestatus, SUM(l_quantity) AS sum_qty, SUM(l_extendedprice) AS sum_base_price, SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price, SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, AVG(l_quantity) AS avg_qty, AVG(l_extendedprice) AS avg_price, AVG(l_discount) AS avg_disc, COUNT(*) AS count_order FROM lineitem WHERE l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY GROUP BY l_returnflag , l_linestatus ORDER BY l_returnflag , l_linestatus; 45

INSERT 後の SELECT（HeatWave で確認） +--------------+--------------+-------------+-----------------+-------------------+---------------------+---| l_returnflag | l_linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg +--------------+--------------+-------------+-----------------+-------------------+---------------------+---| A | F | 37734107.00 | 56586554400.73 | 53758257134.8700 | 55909065222.827692 | 25. | N | F | 991417.00 | 1487504710.38 | 1413082168.0541 | 1469649223.194375 | 25. | N | O | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25. | R | F | 37719753.00 | 56568041380.90 | 53741292684.6040 | 55889619119.831932 | 25. +--------------+--------------+-------------+-----------------+-------------------+---------------------+---4 rows in set (1.08 sec) 46

DMS レプリケーション：まとめと補足 ● binlog レプリケーションの代わりとして（一応）使える ● ただし注意点がいくつか ○ DMS レプリケーションインスタンスの利用料金が掛かる ■ https://aws.amazon.com/jp/dms/pricing/ ○ テーブルに含まれるデータ型によってはデータ不整合が発生する ■ https://zenn.dev/hmatsu47/articles/mysql-dms-cdc-timestamp-mismatch ○ レプリケーションインスタンスにパブリック IP アドレスが必要 ■ セキュリティグループの設定に注意 47

• https://aws.amazon.com/jp/dms/pricing/
• https://zenn.dev/hmatsu47/articles/mysql-dms-cdc-timestamp-mismatch