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

HeatWavejp Meetup#04 / MySQL HeatWave と RDS/Aurora/Redshift で比較と検証してみた！ 2023/10/12

Transcript

1. MySQL HeatWave の制限事項と RDS for MySQL → HeatWave on AWS の DMS レプリケーションを実際に試してみた HeatWavejp Meetup #04 2023/10/12 まつひさ（hmatsu47）
2. 自己紹介 松久裕保（@hmatsu47） • https://qiita.com/hmatsu47 • 現在のステータス： ◦ 名古屋で Web インフラのお守り係をしています ▪ 2017 年秋に AWS 上へ引っ越し完了 ◦ 現在は自社サービスのセキュリティ強化中 ◦ そしてなぜかフロントエンド刷新に取り組み中 ◦ ついでに MySQL HeatWave をのんびり検証中 2
3. 本日お話しする内容 • MySQL HeatWave の制限事項 ◦ MySQL DB で実行可能な SQL 文の一部が実行不可 ▪ 基本的には HeatWave にオフロードされず MySQL DB で実行 ◦ MySQL DB → HeatWave 間のデータ不整合を引き起こすものも →実際に試してみた • MySQL HeatWave on AWS でのレプリケーション制限 ◦ 現時点ではインバウンド binlog レプリケーションに非対応 → DMS で代用可能か試してみた 3
4. 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
5. MySQL HeatWave 制限事項について：おことわり • 検証結果には誤りがある可能性があります ◦ 2023/10/11 に慌てて再検証したので誤認がある可能性も 5
6. 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
7. 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
8. HeatWave 制限事項① CASCADE UPDATE/DELETE • テーブルを作成し初期データを登録 8
9. HeatWave 制限事項① CASCADE UPDATE/DELETE • HeatWave にロード 9
10. HeatWave 制限事項① CASCADE UPDATE/DELETE • HeatWave でデータを確認（area テーブル） 10
11. HeatWave 制限事項① CASCADE UPDATE/DELETE • HeatWave でデータを確認（person テーブル） 11
12. HeatWave 制限事項① CASCADE UPDATE/DELETE • area テーブル（親テーブル）から 1 行削除 12
13. HeatWave 制限事項① CASCADE UPDATE/DELETE • HeatWave 側で親テーブルの行削除は反映されている 13
14. HeatWave 制限事項① CASCADE UPDATE/DELETE • HeatWave 側で子テーブルの行削除が反映されていない 14
15. HeatWave 制限事項① CASCADE UPDATE/DELETE • 参考：MySQL DB 側（削除されている） 15
16. HeatWave 制限事項② 長い TEXT 型 • TEXT 型の列を持つテーブル ◦ 65532 バイトを超える TEXT 列を持つ行を INSERT ▪ 成功後、HeatWave 側で SELECT するとエラー ◦ https://dev.mysql.com/doc/heatwave/en/mys-hw-troubleshooting.html 16
17. HeatWave 制限事項② 長い TEXT 型 • テーブル作成・初期データ登録＆ HeatWave にロード 17
18. HeatWave 制限事項② 長い TEXT 型 • HeatWave 側で SELECT 18
19. HeatWave 制限事項② 長い TEXT 型 • 65533 バイトの TEXT 列を INSERT 19
20. HeatWave 制限事項② 長い TEXT 型 • HeatWave 側で SELECT →エラー • 20
21. 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
22. 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 22 但し「Asia/Tokyo」形式の time_zone では明示的にエラーが発生するように
23. HeatWave 制限事項③ SYSTEM 以外の time_zone • タイムゾーン設定 23
24. HeatWave 制限事項③ SYSTEM 以外の time_zone • テーブル作成・初期データ登録＆ HeatWave にロード 24
25. HeatWave 制限事項③ SYSTEM 以外の time_zone • HeatWave 側で SELECT 25
26. HeatWave 制限事項③ SYSTEM 以外の time_zone • TIMESTAMP 列の値を UPDATE 26
27. HeatWave 制限事項③ SYSTEM 以外の time_zone • HeatWave 側で SELECT →値は正常 • 27
28. HeatWave 制限事項④ 整数部が 18 桁を超える数値 • 例：整数部 19 桁の DECIMAL 型の列を持つテーブル ◦ 整数部 19 桁の値を入れた行を INSERT ▪ 成功後、HeatWave 側で SELECT するとエラー ◦ https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html 28
29. HeatWave 制限事項④ 整数部が 18 桁を超える数値 • 例：整数部 19 桁の DECIMAL 型の列を持つテーブル ◦ 整数部 19 桁の値を入れた行を INSERT ▪ 成功後、HeatWave 側で SELECT するとエラーしてもエラーが出なくなった ◦ https://dev.mysql.com/doc/heatwave/en/mys-hw-limitations-data-types.html 29
30. HeatWave 制限事項④ 整数部が 18 桁を超える数値 • 整数部 19 桁の値を HeatWave で SELECT →正常 30
31. MySQL HeatWave 制限事項について：まとめ • データ変更（追加・更新・削除）の伝搬に関する制限 ◦ MySQL DB → HeatWave の間でデータ不整合が発生するケース ◦ 追加・更新・削除のタイミングではエラーが発生せず ▪ 気づかないうちに不整合が発生 ◦ その後 SELECT したときに不整合が発覚／エラーが発生 ▪ トランザクション処理中なら ROLLBACK 可能 ▪ そうでなければセカンダリのアンロード→再ロードが必要に →公式ドキュメント記載の制限のうち一部はすでに解消されていた 31
32. HeatWave on AWS のレプリケーション制限 • インバウンド binlog レプリケーション非対応 ◦ https://dev.mysql.com/doc/heatwave-aws/en/mysql-server-limitations.html 32
33. HeatWave on AWS のレプリケーション制限 • DMS で代用可能か試してみた ◦ 詳細はこちらの Qiita 記事を参照 ▪ https://qiita.com/hmatsu47/items/34f4ab2047b95f767f18 33
34. HeatWave on AWS のレプリケーション制限 • 注：こちらの実験結果も無保証です ◦ 今後実行結果や利用可否などが変わる可能性があります ▪ PrivateLINK 対応時など 34
35. 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
36. RDS→HeatWave on AWS の DMS レプリケーション • 大まかな流れ（2/4） ◦ ターゲット DB（MySQL HeatWave on AWS）を作成 ◦ DMS レプリケーションを設定 (1) ▪ レプリケーションサブネットグループ作成 ▪ レプリケーションインスタンス作成（パブリックアクセス可能に） ▪ レプリケーションインスタンスのパブリック IP アドレスを確認 ◦ HeatWave on AWS の Networking 設定を変更 ▪ レプリケーションインスタンスのパブリック IP アドレスから接続可能に 36
37. RDS→HeatWave on AWS の DMS レプリケーション • 大まかな流れ（3/4） ◦ DMS レプリケーションを設定 (2) ▪ ソース／ターゲットエンドポイント作成 ▪ データ移行タスク作成（テーブルマッピングおよび変換ルールを適宜調整） • DMS レプリケーションを確認 ◦ 初期データの移行（ロード）完了を待つ ◦ テーブル統計確認 ◦ ソース／ターゲット DB のデータ状態確認 37
38. RDS→HeatWave on AWS の DMS レプリケーション • 大まかな流れ（4/4） ◦ HeatWave へのオフロードを有効化 ◦ データを INSERT ▪ CDC レプリケーション確認 38
39. 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
40. 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
41. データ INSERT 前のテーブル統計 41
42. データ INSERT mysql> INSERT INTO region SET R_REGIONKEY = 999, R_NAME = 'SPACE', R_COMMENT = 'outside the earth'; 42
43. 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
44. データ INSERT 後のテーブル統計 44
45. 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
46. 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
47. DMS レプリケーション：まとめと補足 • binlog レプリケーションの代わりとして（一応）使える • ただし注意点がいくつか ◦ DMS レプリケーションインスタンスの利用料金が掛かる ▪ https://aws.amazon.com/jp/dms/pricing/ ◦ テーブルに含まれるデータ型によってはデータ不整合が発生する ▪ https://zenn.dev/hmatsu47/articles/mysql-dms-cdc-timestamp-mismatch ◦ レプリケーションインスタンスにパブリック IP アドレスが必要 ▪ セキュリティグループの設定に注意 47