Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL 8.0への移行を考える

hmatsu47
April 02, 2021

MySQL 8.0への移行を考える

第 31 回 中国地方 DB 勉強会 in オンライン 2021/04/02

hmatsu47

April 02, 2021
Tweet

More Decks by hmatsu47

Other Decks in Technology

Transcript

  1. MySQL 5.6・5.7 から         MySQL 8.0 への移行を考える 第 31 回 中国地方

    DB 勉強会 in オンライン 2021/04/02 まつひさ(hmatsu47)
  2. 自己紹介 松久裕保(@hmatsu47) https://qiita.com/hmatsu47 名古屋で Web インフラのお守り係をしています MySQL 8.0 の薄い本を作って配っていました ◦

    Qiita の記事: https://qiita.com/hmatsu47/items/ceb75caf46e3c761095d ◦ GitHub リポジトリの他、印刷版を BOOTH で配布していました ◦ 5 月発行予定の 8.0.24 対応版を最後に更新停止する予定です https://note.com/hmatsu47/n/n3ad586c31dce 2
  3. 今回のテーマ MySQL 5.6・5.7 から 8.0 に移行するときに、知っておくと良さ そうな点について取り上げます • [1] MySQL

    8.0 に移行すると嬉しいこと • [2] MySQL 8.0 に移行するときの注意点 • [3] クラウドのマネージドサービスとの比較 ◦ Amazon Aurora(Amazon Web Services) ◦ ApsaraDB for PolarDB(Alibaba Cloud) ◦ MySQL Database Service(Oracle Cloud Infrastructure)※HeatWave ※TiDB Cloud(PingCAP)も紹介したかったのですが知識不足のため省略 3
  4. おことわり 内容は個人的な見解です • 私自身は DBA ではありません • 趣味で MySQL 8.0

    を触っています NDB Cluster は対象外です • ちなみに InnoDB Cluster についてもほとんど触れません 移行手順そのものは対象外です • 移行を検討する際の参考情報を提示します 4
  5. [1] MySQL 8.0 に移行すると嬉しいこと 使える機能が増える • 他の RDBMS と遜色のないレベルの SQL(文)サポート

    ◦ 例:ウィンドウ関数、CTE、CHECK 制約、降順インデックス、関数インデックス • ドキュメントデータベース機能のサポート 管理・運用が楽になる • MySQL Shell による管理・運用機能の強化 ◦ 例:バックアップ&リストア、InnoDB Cluster・ReplicaSet の構築・設定 処理が効率的になる • 例:ハッシュジョイン、アンチジョイン 5
  6. ウィンドウ関数 RANK() を試してみる(鳥取県の市町村人口データを使って) • 市町村種別で区切って順位を付ける • OVER 句で区画(PARTITION)を指定 ◦ PARTITION

    BY で市町村種別を指定→市・町・村別の順位に ◦ ORDER BY で人口の降順を指定 7 SELECT RANK() OVER (PARTITION BY municipality_type ORDER BY population DESC) AS pop_rank, name, population, municipality_type FROM population ORDER BY municipality_type ASC;
  7. ウィンドウ関数 結果:市・町・村それぞれの順位が表示される(pop_rank) 8 +----------+----------+------------+-------------------+ | pop_rank | name | population

    | municipality_type | +----------+----------+------------+-------------------+ | 1 | 鳥取市 | 188551 | 1 | | 2 | 米子市 | 147638 | 1 | | 3 | 倉吉市 | 46833 | 1 | | 4 | 境港市 | 32842 | 1 | | 1 | 琴浦町 | 16461 | 2 | | 2 | 湯梨浜町 | 16078 | 2 | | 3 | 八頭町 | 15896 | 2 | | 4 | 大山町 | 15633 | 2 | | 5 | 北栄町 | 14214 | 2 | | 6 | 岩美町 | 10884 | 2 | | 7 | 伯耆町 | 10612 | 2 | | 8 | 南部町 | 10434 | 2 | | 9 | 智頭町 | 6508 | 2 | | 10 | 三朝町 | 6092 | 2 | | 11 | 日南町 | 4166 | 2 | | 12 | 若桜町 | 2927 | 2 | | 13 | 日野町 | 2881 | 2 | | 14 | 江府町 | 2690 | 2 | | 1 | 日吉津村 | 3515 | 3 | +----------+----------+------------+-------------------+ 19 rows in set (0.00 sec) 日吉津村(ひえづそん)は市・町の下に
  8. ウィンドウ関数 MySQL 5.7 以前で再現してみると… • ちょっと読みづらい 9 SELECT ( SELECT

    COUNT(*) + 1 FROM population AS pop WHERE pop.municipality_type = population.municipality_type AND pop.population > population.population ) AS pop_rank, name, population, municipality_type FROM population ORDER BY municipality_type ASC, population DESC;
  9. 再帰共通テーブル式(CTE) MySQL 8.0 で試してみる 11 mysql> SELECT * FROM log_relation1

    ORDER BY log_id; +--------+---------------+ | log_id | parent_log_id | +--------+---------------+ | 101 | NULL | | 102 | 101 | | 103 | 101 | | 104 | 103 | | 105 | 103 | | 106 | 103 | | 107 | NULL | | 108 | 107 | | 109 | 107 | +--------+---------------+ 9 rows in set (0.00 sec) MySQL 8.0 では必ずしも 主キー順にならなくなった
  10. 再帰共通テーブル式(CTE) MySQL 8.0 で試してみる(log_id=101 の子ノードを全取得) 12 mysql> WITH RECURSIVE temp(log_id)

    AS -> (SELECT log_id FROM log_relation1 WHERE log_id = 101 -> UNION ALL -> SELECT a.log_id FROM log_relation1 a, temp b -> WHERE a.parent_log_id = b.log_id) -> SELECT log_id FROM temp; +--------+ | log_id | +--------+ | 101 | | 102 | | 103 | | 104 | | 105 | | 106 | +--------+ 6 rows in set (0.00 sec)
  11. CHECK 制約 MySQL 5.7 以前は「記述だけはできた」CHECK 制約が有効に • MySQL 5.7 では(必要な場合)生成列が

    CHECK 制約代わりに 14 mysql> CREATE TABLE t1 -> ( -> id INT PRIMARY KEY AUTO_INCREMENT, -> c1 INT CHECK (0 < c1 AND c1 <= 10) -> ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t1 SET c1 = 10; Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO t1 SET c1 = 0; ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated. # 範囲外なのでエラー
  12. ドキュメントデータベース機能のサポート X DevAPI を用いたデータ操作(Java の例) 15 // サーバに接続 String sessionString

    = "mysqlx://localhost:33060/testdb?user=testuser&password=P@55w0rd" Session session = new SessionFactory().getSession(sessionString); // DB に接続 Schema db = session.getSchema("testdb"); // コレクション 'testcollection' を作成 Collection collection = db.createCollection("testcollection", true); // コレクションにドキュメントを追加 collection.add("{\"id\":1, \"name\":\"MySQL\", \"version\":\"5.7\"}").execute(); collection.add("{\"id\":2, \"name\":\"MySQL\", \"version\":\"8.0\"}").execute(); collection.add("{\"id\":3, \"name\":\"MariaDB\", \"version\":\"10.5\"}").execute(); // コレクションの「 id」列にインデックスを追加 collection.createIndex("id_index", "{\"fields\": [{\"field\": \"$.id\", \"type\": \"INT\"}]}"); // コレクションから「 version LIKE '%8.0%'」を検索 DocResult docs1 = collection.find("version like :ver").bind("ver", "%8.0%").execute(); // コレクションから「 id=2」を検索 DocResult docs2 = collection.find("id = :id").bind("id", 2).execute();
  13. ドキュメントデータベース機能のサポート JSON 機能の強化 • JSON 関数の強化 ◦ JSON_TABLE() などを除いて MySQL

    5.7 にもバックポート • JSON 列の部分アップデートをサポート ◦ レプリケーション時の部分アップデートもサポート • Multi-Valued Indexes のサポート ◦ JSON 列の中にキー値に対応する値として(配列のように)複数値を持つものが 含まれている場合にも有効に機能するインデックス 16
  14. その他 GIS 機能で SRID をサポート • 平面以外の地理座標系をサポート ◦ MySQL 5.7

    では ST_Distance_Sphere() で地球を「半径 6,370,986m の真球」と 見立てて疑似的に距離(m)を計算する以外は平面しか扱えなかった 関数・式インデックス、降順インデックスをサポート 不可視インデックス、不可視列をサポート NOWAIT / SKIP LOCKED をサポート …など 17
  15. 管理・運用が楽になる MySQL Shell による管理・運用機能の強化 • バックアップ&リストア ◦ インスタンス単位、スキーマ単位の並列バックアップ&リストア ◦ MySQL

    5.6・5.7 からの移行にも使用可能 ▪ MySQL 5.6 はバックアップのみ可能でリストアは不可(移行専用) • InnoDB Cluster・ReplicaSet の構築・設定 ◦ データのノード間クローン、クラスタのノード追加・削除など • アップグレードチェッカー ◦ 旧バージョンからのアップグレード時に互換性を確認 18
  16. バックアップ&リストア インスタンスバックアップの例 19 MySQL JS > \connect root@localhost Creating a

    session to 'root@localhost' (中略) MySQL localhost:33060+ ssl JS > util.dumpInstance("C:/Users/testuser/testdump") (中略) 1 thds dumping - 111% (10.13M rows / ~9.10M rows), 88.16K rows/s, 45.20 MB/s uncompressed, 35.37 KB/s compressed Duration: 00:01:25s Schemas dumped: 14 Tables dumped: 35 Uncompressed data size: 4.67 GB Compressed data size: 237.06 MB Compression ratio: 19.7 Rows written: 10129751 Bytes written: 237.06 MB Average uncompressed throughput: 54.66 MB/s Average compressed throughput: 2.77 MB/s
  17. InnoDB ReplicaSet の設定 1. Replication Source インスタンスを設定 20 MySQL JS

    > \connect root@rs-1:3306 Creating a session to 'root@rs-1:3306' Please provide the password for 'root@rs-1:3306': ************ ※パスワードを入力 (中略) MySQL rs-1:33060+ ssl JS > dba.configureReplicaSetInstance('root@rs-1:3306', {clusterAdmin: "'rsadmin'@'rs-1%'"}); Please provide the password for 'root@rs-1:3306': ************ ※パスワードを入力 (中略) Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Password for new account: ************ ※ReplicaSet管理アカウント(新規追加)用パスワードを入力 (中略)
  18. InnoDB ReplicaSet の設定 2. ReplicaSet を作成 21 MySQL rs-1:33060+ ssl

    JS > var rs = dba.createReplicaSet("example") ※ReplicaSet「example」を作成 A new replicaset with instance 'rs-1:3306' will be created. * Checking MySQL instance at rs-1:3306 This instance reports its own address as rs-1:3306 rs-1:3306: Instance configuration is suitable. * Updating metadata... ReplicaSet object successfully created for rs-1:3306. Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
  19. InnoDB ReplicaSet の設定 3. Replica インスタンスを設定 22 MySQL rs-1:33060+ ssl

    JS > \connect root@rs-2:3306 Creating a session to 'root@rs-2:3306' Please provide the password for 'root@rs-2:3306': ************ ※パスワードを入力 (中略) MySQL rs-2:33060+ ssl JS > dba.configureReplicaSetInstance('root@rs-2:3306', {clusterAdmin: "'rsadmin'@'rs-2%'"}); Please provide the password for 'root@rs-2:3306': ************ ※パスワードを入力 (中略) Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Password for new account: ************ ※ReplicaSet管理アカウント用パスワードを入力 (中略)
  20. InnoDB ReplicaSet の設定 4. Replica インスタンスを ReplicaSet に追加・データクローン 23 MySQL

    rs-2:33060+ ssl JS > \connect root@rs-1:3306 Creating a session to 'root@rs-1:3306' Please provide the password for 'root@rs-1:3306': ************ ※パスワードを入力 (中略) MySQL rs-1:33060+ ssl JS > rs.addInstance('rs-2:3306') (中略) Please select a recovery method [C]lone/[I]ncremental recovery/[A]bort (default Clone): C ※「C」を入力してデータをクローン (中略) ** Clone Transfer FILE COPY #################################################### 100% Completed PAGE COPY #################################################### 100% Completed REDO COPY #################################################### 100% Completed (中略) The instance 'rs-2:3306' was added to the replicaset and is replicating from rs-1:3306
  21. 処理が効率的になる ハッシュジョイン • インデックスのないテーブルを等結合する処理(など)の効率化 ◦ Block Nested Loop Join を置き換え

    アンチジョイン • NOT IN (SELECT ... FROM ...)、NOT EXISTS (SELECT ... FROM ...) など その他 • Multi-Table Trick 不要化、Skip Scan Range Access Method など • ロック機構の変更(CATS) 24
  22. ハッシュジョイン(実行例はいずれも公式マニュアルより) インデックスのないテーブルを等結合する処理(など)の効率化 • 等結合であれば LEFT (RIGHT) OUTER JOIN やセミジョインでも使える ◦

    Block Nested Loop Join を置き換えるもの • 等結合でなくても INNER JOIN なら使える 25 mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Left hash join (t2.c1 = t1.c1) (cost=3.99 rows=36) -> Table scan on t1 (cost=0.85 rows=6) -> Hash -> Table scan on t2 (cost=0.14 rows=6) mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G *************************** 1. row *************************** EXPLAIN: -> Filter: (t1.c1 < t2.c1) (cost=4.70 rows=12) -> Inner hash join (no condition) (cost=4.70 rows=12) -> Table scan on t2 (cost=0.08 rows=6) -> Hash -> Table scan on t1 (cost=0.85 rows=6)
  23. ハッシュジョイン 使用上の注意 • join_buffer_size を大きめに取る必要あり • インデックスがあるテーブルであえて使う場合は、オプティマイザヒント 「NO_JOIN_INDEX」を指定する 26 mysql>

    EXPLAIN FORMAT=tree SELECT /*+ NO_JOIN_INDEX(t1) NO_JOIN_INDEX(t2) */ * FROM t1 JOIN t2 ON t1.col1 < t2.col1\G *************************** 1. row *************************** EXPLAIN: -> Filter: (t1.col1 < t2.col1) (cost=859311907.40 rows=2864055110) -> Inner hash join (no condition) (cost=859311907.40 rows=2864055110) -> Index scan on t2 using col1 (cost=0.04 rows=93215) -> Hash -> Index scan on t1 using col1 (cost=9274.75 rows=92185) 1 row in set (0.00 sec)
  24. アンチジョイン(実行例は公式マニュアルより) サブクエリ側テーブルとマッチしない行を抽出 • NOT EXISTS (SELECT ... FROM ...) の例

    • 以前よく使われていた LEFT JOIN で null と結合する手法では 27 mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 -> WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.col1 = t2.col1)\G *************************** 1. row *************************** EXPLAIN: -> Nested loop antijoin -> Table scan on t2 (cost=0.85 rows=6) -> Single-row index lookup on <subquery2> using <auto_distinct_key> (c1=t2.c1) -> Materialize with deduplication -> Filter: (t1.c1 is not null) (cost=0.85 rows=6) -> Table scan on t1 (cost=0.85 rows=6) SELECT t2.* FROM t2 LEFT JOIN t1 ON t1.col1 = t2.col1 WHERE t1.col1 IS null;
  25. その他 Multi-Table Trick 不要化 • ↓のような書き換えをしなくても効率よく UPDATE / DELETE できるように

    Skip Scan Range Access Method • 複合インデックスの 1 列目を WHERE 句で指定していなくてもインデックス が使えるように(細かい制約条件あり) CATS(The Contention-Aware Transaction Scheduling) • 効率的なロックスケジューラ(FIFO から置き換え) 28 UPDATE t1 SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2); ↓ UPDATE t1, (SELECT 1) dummy SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2);
  26. [2] MySQL 8.0 に移行するときの注意点 マイナーバージョンアップで機能追加・変更・非推奨化・削除 • 例:予約語、実行計画とオプティマイザスイッチ デフォルトの変更により動作が変わる • 例:認証プラグイン、暗黙のソート順

    バージョンアップに関するポリシーの変更による影響がある • 例:3 バージョン以上をまたぐレプリケーションの非サポート 必ずしも性能が上がるわけではない • 大きめのサーバに合わせた設計に変化してきたため 29
  27. デフォルト認証プラグインの変更 caching_sha2_password がデフォルトに • 従来は mysql_native_password アプリケーションからの接続に注意 • 環境によっては mysql_native_password

    しか対応していないケースも MySQL 5.7 からインプレースアップグレードした場合、以前から 設定されていたユーザは mysql_native_password を使用する 32
  28. 暗黙のソート順の変化(廃止) テーブルスキャンでも、主キー順にソートされない場合がある • ソートが必要なら「ORDER BY」でソート順を指定する 33 mysql> SELECT * FROM

    log_relation1 ORDER BY log_id; +--------+---------------+ | log_id | parent_log_id | +--------+---------------+ | 101 | NULL | | 102 | 101 | | 103 | 101 | | 104 | 103 | | 105 | 103 | | 106 | 103 | | 107 | NULL | | 108 | 107 | | 109 | 107 | +--------+---------------+ 9 rows in set (0.00 sec) 再帰 CTE の説明で使ったテーブル(再掲)
  29. バージョンアップに関するポリシーの変更 3 バージョン以上をまたぐレプリケーションが非サポートに • 5.6 → 5.7 → 8.0 のレプリケーションによるバージョンアップが非推奨に

    ◦ 以前は 2 段階バージョンアップの常套手段だった • 8.0.21 → 8.0.22 → 8.0.23 のようなマイナーバージョンでも非推奨 マイナーバージョンダウンが非サポートに • データのチェックが行われ、8.0.23 → 8.0.22 のようなバージョンダウンは エラーになる 34
  30. 必ずしも性能が上がるわけではない 大きめのサーバに合わせた設計に変化してきたため • 小さめのサーバで使うときは Dedicated log writer を OFF に

    ◦ いろんな事情を知るには中の人のブログが参考になる https://buildup-db.blogspot.com/ • MySQL 8.0 では、ついにクエリキャッシュが廃止された ◦ 並列処理の妨げになりやすい 35
  31. [3] クラウドのマネージドサービスとの比較 Amazon Aurora(Amazon Web Services) • MySQL 5.6・5.7 互換

    ApsaraDB for PolarDB(Alibaba Cloud) • MySQL 5.6・8.0 互換 MySQL Database Service(Oracle Cloud Infrastructure) • MySQL 8.0 Enterprise Edition がベース • HeatWave を組み合わせて利用可能 36
  32. Amazon Aurora(Amazon Web Services) MySQL 5.6 互換・5.7 互換がリリース済み • バージョン

    1 系:MySQL 5.6 互換は MySQL 5.6.10a ベース • バージョン 2 系:MySQL 5.7 互換は MySQL 5.7.12 ベース MySQL 8.0 互換はまもなく登場? https://blog.serverworks.co.jp/reinvent2020-whats-new-in-amazon-aurora#10-Amazon-Auror a-Versions 37
  33. Amazon Aurora の特徴 ストレージ層がコンピューティング層(SQL ノード)と分離 • データは同一リージョン内の 3AZ に計 6

    つのコピーを保管 • 複数のインスタンス(Writer・Reader)でストレージを共用 • ストレージ層の処理能力を活かした独自機能がある ◦ パラレルクエリ、先読み機能など ◦ 独自実装のハッシュジョインもサポート Global Database などリージョンをまたぐサービスも • Multi-Master もあるが、性能面や機能面の制約により使いどころが難しい 監査機能を有効にしたときのオーバーヘッドが本家より小さい 38
  34. 39 Aurora Global Database(データ読み取り) apne1-az1 Writer apne1-az2 Reader apne1-az4 Reader

    ap-northeast-1 (東京) apne3-az1 Reader apne3-az2 Reader apne3-az3 Reader ap-northeast-3 (大阪)
  35. 40 Aurora Global Database(データ書き込み①) apne1-az1 Writer apne1-az2 Reader apne1-az4 Reader

    ap-northeast-1 (東京) AZ またぎのアクセスになる ↓       ↓ apne3-az1 Reader apne3-az2 Reader apne3-az3 Reader ap-northeast-3 (大阪) リージョンまたぎのアクセスになる  ↓    ↓       ↓
  36. 41 Aurora Global Database(データ書き込み②) apne1-az1 Writer apne1-az2 Reader apne1-az4 Reader

    ap-northeast-1 (東京) AZ またぎのアクセスになる ↓       ↓ apne3-az1 Reader apne3-az2 Reader apne3-az3 Reader ap-northeast-3 (大阪) 各 Reader で書き込み転送を使用  ↓    ↓       ↓
  37. Amazon Aurora の(個人的な)印象 速度 • ✖ 速い • 〇 同時接続が多くなっても遅くなりにくい

    ◦ 並列処理性能重視 ◦ データの信頼性+保全性重視 (RDS と比較して)管理・運用は楽だが I/O 料金が高い マイナーバージョンアップに追従しない(できない)不安はある • 未修正の既知のバグが残っていることも • MySQL 8.0 のマイナーバージョンアップでの機能追加には追従しない? 42
  38. 出典:  https://www.alibabacloud.com/help/ja/doc-detail/173266.htm • 前段に Proxy(R/W & Read LB) • DB

    Server とストレージ層を分離 • Log structured storage(おそらく) • Shared distributed storage • 25Gbps x 2ch RDMA • 32Gb/s NVMe SSD ApsaraDB for PolarDB(Alibaba Cloud) 43
  39. Amazon Aurora との比較 MySQL 8.0 互換がリリース済み • MySQL 8.0.13 ベース

    データは単一ゾーン 3 コピーが最小単位 • ネットワーク遅延が少ない分ストレージが高速 ◦ データ信頼性の観点からは劣る ◦ 2 ゾーンで組む構成もある 大容量向けのストレージエンジン(X-Engine)もある Global Database Network もある • Aurora の Global Database相当 44
  40. 本家 MySQL 8.0 との比較(PolarDB MySQL 8.0 互換) 独自実装のパラレルクエリが強力 • Aurora

    と比べて(帯域が同じなら)ストレージが高速な分、速い Aurora と同様、ストレージ共有型なのでレプリカラグが小さい • Aurora 以上(?)に Oracle RAC っぽい ◦ 個人の見解です バッファプールを機械学習で効率的に使う謎機能(?)がある マイナーバージョンアップに追従する考えはあるらしい • 実現はちょっと難しいかも 45
  41. MySQL Database Service(Oracle Cloud Infrastructure) クラウドサービスでは唯一の MySQL Enterprise Edition ベース

    • 他社のサービスは Community Edition ベース • Enterprise レベルのサポート付き 後発なので他社クラウドより低価格 • I/O 料金・ネットワーク通信料金などが無料 or 低額 分析・集計クエリを処理する HeatWave が追加された • インメモリ・列指向のデータベース • MySQL が苦手とする分析・集計クエリを自動で HeatWave に振り分ける 46
  42. まとめ 使える機能が増える • SQL(文)は、ようやく他の RDBMS 並みに(?) 管理・運用が楽になる • MySQL Shell

    が便利 処理が効率的になる • ハッシュジョインなど 機能追加・変更・非推奨化・削除が頻繁に発生する • マイナーバージョンアップでも機能追加・変更・非推奨化・削除がある 48
  43. まとめ デフォルトの変更により動作が変わる • 認証プラグインなど バージョンアップに関するポリシーの変更による影響がある • 3 バージョン以上をまたぐレプリケーションの非サポートなど 必ずしも性能が上がるわけではない •

    小さめのサーバで使うときは設定の調整が必要 クラウドのマネージドサービスとの比較検討がお勧め • クラウドのマネージドサービスは各社それぞれに違いがある 49