What's New in MySQL 5.7 InnoDB

50 %
50 %
Information about What's New in MySQL 5.7 InnoDB

Published on July 14, 2016

Author: nippondanji

Source: slideshare.net

1. What's New inWhat's New in MySQL 5.7 InnoDBMySQL 5.7 InnoDB 奥野 幹也 Twitter: @nippondanji mikiya (dot) okuno (at) gmail (dot) com @DB Tech Showcase Tokyo 2016

2. 免責事項 本プレゼンテーションにおいて示されている見解は、私 自身の見解であって、オラクル・コーポレーションの見 解を必ずしも反映したものではありません。ご了承くだ さい。

3. 自己紹介 ● MySQL サポートエンジニア – 日々のしごと ● トラブルシューティング全般 ● Q&A 回答 ● パフォーマンスチューニング など ● ライフワーク – 自由なソフトウェアの普及 ● オープンソースではない ● GPL 万歳!! – 最近はまってる趣味はリカンベント(自転車) ● ブログ – 漢のコンピュータ道 – http://nippondanji.blogspot.com/

4. MySQL 5.7 の 新機能概要

5. MySQL 5.7 の数多くの新機能 ● 実に 150 以上もの新機能が追加された!! – MySQL 5.7 の新機能完全リスト https://yakst.com/ja/posts/3037 – yoku0825++ ● レプリケーション関連 ● InnoDB 関連 ● オプティマイザー関連 ● セキュリティ関連 ● パフォーマンススキーマ関連 ● GIS 関連 ● JSON 関連 etc etc... 本日のお題

6. オプティマイザの新機能 ● EXPLAIN for CONNECTION ● JSON EXPLAIN ● コストモデル – JOIN の順序選択 – 統計情報の正確性 – コストの係数のユーザーによる設定 ● GROUP BY ● FROM 句のサブクエリ ● IN サブクエリ ● UNION ALL ● ソート ● テンポラリテーブル

7. レプリケーションの新機能 ● マルチスレッドスレーブの改良 ● マルチソースレプリケーション ● 準同期レプリケーションの改良 – ロスレスレプリケーション – パフォーマンスの改善 – ACK を返すスレーブ数の指定 ● GTID の改良 – スレーブでの管理が効率化 – 再起動時の GTID 計算方法が効率化 ● オンライン操作の拡充 – GTID 有効化 – CHAMGE MASTER の一部 – レプリケーションフィルター

8. セキュリティ関係の新機能 ● パスワード期限の設定 ● ユーザーのロック、アンロック ● SET PASSWORD コマンドの仕様変更 ● CREATE USER … IF NOT EXISTS ● ログイン不可能なユーザーアカウント ● SSL のセットアップが容易に – キーファイルの自動生成 – mysql_ssl_rsa_setup ● 透過的テーブルスペース暗号化 ● デフォルトの状態がセキュアに – test データベースの廃止 – 匿名ユーザーの廃止 – localhost 以外の root の廃止

9. InnoDB の新機能 ● 一般テーブルスペース ● テンポラリテーブルの InnoDB 化 ● バッファプールのオンラインリサイズ ● UNDO ログの自動トランケート ● 32/64KB ページのサポート ● REDO ログフォーマットの改善 ● バッファプールをダンプする割合の指定 ● ページ統合におけるページ充填率の指定 ● コピーしない ALTER TABLE 操作の増加

10. InnoDB の新機能 つづき ● 全文検索用プラガブルパーサーのサポート – ngram あるいは MeCab による日本語の全文検索 ● 全文検索の最適化 ● 空間インデックスのサポート ● 透過的テーブル圧縮 ● 透過的テーブルスペース暗号化 ● ダブルライトが不要なとき自動的に無効化 ● NUMA サポートの追加 ● InnoDB モニターの有効化方法変更 ● 情報スキーマの改良 ● デフォルト行フォーマットの指定

11. InnoDB の新機能 性能改善系 ● テンポラリテーブルのための最適化 ● RO トランザクションの性能改善 ● RW トランザクションの性能改善 – index->lock の競合改善( SX ロック) ● リードビュー作成の性能改善 ● trx_t のキャッシュ効率改善 ● AHI のミューテックス競合改善 ● ページクリーナーのマルチスレッド化 ● フラッシュアルゴリズムの改善 ● クラッシュリカバリの性能改善 ● ログファイル書き込みの性能改善 ● インデックス作成の高速化 etc

12. 一般 テーブルスペース

13. 一般テーブルスペースとは ● 従来からある InnoDB のテーブルスペース – ibdata1 – .ibd ファイル ● innodb_file_per_table=1 – 個別の UNDO ログ ● 一般テーブルスペースとは第三のテーブルスペース – 明示的に作成する – 複数のテーブルをまとめて格納する – 任意のディレクトリ上にデータファイルを作成することが可 能

14. 作成例 mysql> CREATE TABLESPACE ts1 ADD DATAFILE '/path/to/ssdvol/tablespace.ibd' ENGINE INNODB; mysql> CREATE TABLE tbl_name1 (... 中略 ...) TABLESPACE ts1; mysql> CREATE TABLE tbl_name2 (... 中略 ...) TABLESPACE ts1;

15. 一般テーブルスペースの主な用途 ● 特定のテーブルだけ別のストレージを割り当てたい – 頻繁にアクセスされるテーブルに高速なストレージ – 殆どアクセスされないテーブルに低速で大容量のストレー ジ ● 複数のテーブルで、同じ圧縮設定を使いたい ● 多数のパーティションをまとめて格納したい ● 大きなバッファプール使用時の効率化 – DROP TABLE によるバッファプールのスキャンを避けたい

16. 圧縮設定 FILE_BLOCK_SIZE innodb_page_size FILE_BLOCK_SIZE KEY_BLOCK_SIZE 圧縮 / 非圧縮 64K 65536 N/A 非圧縮 32K 32768 N/A 非圧縮 16K 16384 N/A 非圧縮 8192 8 圧縮 4096 4 圧縮 2048 2 圧縮 1024 1 圧縮 8K 8192 N/A 非圧縮 4096 4 圧縮 2048 2 圧縮 1024 1 圧縮 4K 4096 N/A 非圧縮 2048 2 圧縮 1024 1 圧縮

17. テンポラリテーブルの InnoDB 化

18. 明示的なテンポラリテーブル ● CREATE TEMPORARY TABLE で作成 ● ストレージエンジンの指定が可能 ● 作成したセッションだけからアクセス可能 ● アプリケーションが中間結果を格納し、再利用したい場合に 便利

19. 暗黙的なテンポラリテーブル ● クエリの実行途中で自動的に作成される – 実行過程の中間結果を格納 – UNION, GROUP BY, JOIN + ORDER BY, DISTINCT + ORDER BY, FROM 句のサブクエリ , 一部のビュー ● 作成当初はメモリベース – MEMORY ストレージエンジン ● 大きくなるとディスクベースへ自動的に変換 – MySQL 5.6 までは MyISAM – MySQL 5.7 では InnoDB または MyISAM

20. MySQL 5.7 ではテンポラリテーブルの オーバーヘッドを徹底的に解消 ● CREATE/DROP のオーバーヘッドが激減した ● REDO ログを生成しない ● 行データにロールバックポインターを持たない ● 更新は DELETE と INSERT によって代替されている ● ダブルライトをしない ● 変更バッファを使用しない ● 専用のテーブルスペース( ibtmp1 )内に格納され、再起動時に再 作成される ● ページへのアクセスにおいてラッチをしない ● ページチェックサムをしない ● テーブルのメタデータがディスク上に格納されない ● アダプティブハッシュインデックスを使わない ● ROWID はテンポラリテーブルごとに固有のものが使われる

21. UNDO ログの 自動トランケート

22. innodb_file_per_table なのに ibdata1 が肥大化 ● それは、 UNDO ログのせいかもしれません・・・ – = ロールバックセグメント ● 更新前のデータをコピーしておく場所 – ロールバックするときに用いられる – MVCC によって参照される ● パージで開放される

23. UNDO ログの格納場所 ● MySQL 5.5 までの格納場所は ibdata1 で変更不可 ● MySQL 5.6 – デフォルトでは ibdata1 – オプションで分離可能(ファイルは複数) – ただしサイズ縮小はできない ● MySQL 5.7 – デフォルトでは ibdata1 – オプションで分離可能(ファイルは複数) – UNDO ログファイルをひとつずつトランケート可能

24. UNDO ログトランケートの様子 UNDO ログ空き領域 UNDO ログ使用中の領域 innodb_max_ undo_log_size INACTIVE 初期サイズ = 10MB INACTIVE にして 空になるまで待つ。 その間は他の UNDO ログファイルで凌ぐ。 UNDO ログ ファイルを初期化 UNDO ログファイルの サイズがしきい値を超えた

25. バッファプールまわりの 改善

26. バッファプールの オンラインリサイズ ● バッファプールサイズの増減を、無停止で実行 – innodb_buffer_pool_chunk_size: デフォルト 128MB – innodb_buffer_pool_instances: デフォルト 1 or 8 ● バッファプールサイズが 1GB 以上の時 8 ● チャンクサイズとインスタンス数の積の倍数でサイズを指定 mysql> SET GLOBAL innodb_buffer_pool_size = 100 * 1024 * 1024 * 1024; mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_resize_status';

27. バッファプールをダンプする 割合の指定 ● MySQL 5.6 において、バッファプール上のページの番号をダ ンプしたり、該当のページをロードする機能が追加された ● バッファプール上のキャッシュは LRU で管理 – 古いキャッシュデータは再び参照されない可能性が高い – ダンプしても無駄になるかも – 比較的最近アクセスされたページだけで OK ● 起動時間短縮 ● innodb_buffer_pool_dump_pct: デフォルト 25

28. 透過的テーブル圧縮

29. 圧縮フォーマットって 前からあったよね? ● innodb_file_format = Barracuda ● CREATE TABLE tblname (…) ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE=4; バッファプールが 二重に消費される。 オーバーヘッド大。

30. 透過的って何が? ● ファイルシステムの SPARSE ファイルを利用 ● データを圧縮して未使用領域を SPARSE 化 ● CREATE TABLE tblname (…) ENGINE = InnoDB COMPRESSION = lz4;

31. 透過的テーブル圧縮の模式図

32. 注意点 ● 使えるかどうかはファイルシステム次第 – Windows: NTFS で利用可能。ただしフォーマット時にクラス ターサイズをデフォルトの 4KB から 512B に変更する必要 がある。 ● Compression Unit: 64KB → 8KB – Linux: NVMFS 、 XFS (カーネル 2.6.38 以降)、 ext4 (カーネ ル 3.0 以降)、 btrfs (カーネル 3.7 以降)などで利用可能 ● SPARSE ファイルはランダムアクセスになりがち – HDD で使うとえらい目に・・・ – 高速なストレージデバイスを使おう

33. 透過的テーブルスペース 暗号化

34. 透過的テーブルスペース 暗号化とは ● コミュニティ版、商用版の両方で利用可能 ● InnoDB の .ibd ファイルを暗号化 – 書き込み時に暗号化 – 読み込み時に復号化 ● 暗号化されるのはファイル上のデータのみ – メモリ上(バッファプール)のデータは暗号化されない – 第三者からのファイルアクセスからデータを守る ● キーリングプラグインと連携 – 鍵管理のためのフレームワーク – コミュニティ版にはファイルベースの keyring_file プラグイン が付属 – 商用版には Oracle Key Vault と連携する keyring_okv プラグ インが付属

35. 透過的テーブルスペース 暗号化の仕組み

36. 使い方 ● キーリングの設定 – INSTALL PLUGIN keyring_file SONAME 'keyring_file.so'; ● テーブルの作成 – CREATE TABLE table_name (... 略 ...) ENCRYPTION='Y'; ● マスターキーのローテーション – ALTER INSTANCE ROTATE INNODB MASTER KEY;

37. SX ロック

38. index->lock の競合 ● ひとつのテーブルあるいはインデックスにアクセスが集中す ると、 index->lock の競合が多発し、ボトルネックになってし まっていた。 – 排他処理はどうしても必要。 – 同時に実行ができる処理を増やせば良い。 ● SX ロックの導入 – 既存の rwlock を拡張。 – S ロック、 X ロックの他に、新たに SX ロックが加えられた。 – SX ロックは S ロックと互換性あり、 SX ロックと X ロックとは 互換性なし。 – X ロックが必要だった処理を見直し、 SX ロックへ変更する ことで、同時実行可能な S ロックの処理が増えた。

39. SX ロックの互換性 S ロック SX ロック X ロック S ロック ○ ○ × SX ロック ○ × × X ロック × × ×

40. インデックス作成の 高速化

41. Fast Index Creation (MySQL 5.5) ● 従来のインデックス作成は、新しい定義の(インデックス追 加後の定義を持つ)テーブルを作成し、すべてのデータを 再コピーするというものだった。 – インデックス構築中は参照だけが可能であり、更新はブ ロックされるようになっていた。 ● MySQL 5.5 で追加された Fast Index Creation では、セカンダリ インデックスだけを構築するようになった。 – ただし、参照だけが可能という点は変わらない。

42. オンライン DDL (MySQL 5.6) ● DDL 実行中にも更新ができるようになった。 ● これにより、長時間かかるであろう ALTER TABLE も、比較的 気軽に出来るようになった。 ● ただし、 ALTER TABLE 開始時と終了時のみ、アクセスが排 他的になる。 – 長時間実行中の SELECT があると、 ALTER TABLE は待たさ れてしまう。

43. Sorted Index Build (New in MySQL 5.7) ● MySQL 5.6 までは、セカンダリインデックスへの挿入は、クラ スタインデックスから順に 1 行ずつ読み取り、セカンダリイン デックスへ挿入するというアルゴリズムだった。 – セカンダリインデックスへのアクセスがランダムアクセスに なるので遅い上にフラグメンテーションが発生する ● MySQL 5.7 ではセカンダリインデックス構築のアルゴリズムが 改善された。 – クラスタインデックスからデータを読み取り、テンポラリファ イルに格納 – セカンダリインデックスのキーの順にソート – セカンダリインデックスへ、ソートされた状態で格納

44. 全文検索

45. プラガブルパーサーのサポート ● MySQL 5.6 – InnoDB がフルテキストインデックスをサポート – MySQL そのものにはプラガブルパーサーが存在 – ただし Inno DB は独自のフルテキストパーサーを搭載 ● stopword を用いた実装=日本語は扱えない ● MySQL 5.7 – 標準のプラガブルパーサーをサポート – stopword を用いないパーサーを選択可能 ● 日本語の全文検索が可能!!

46. パーサーの追加 ● MeCab – 言わずと知れたオープンソースの形態素解析エンジン – 辞書を用いて日本語を単語に分解 – インデックスのサイズがコンパクト ● NGRAM – 文字列を N 文字ごとに区切ってインデックスを構成 – 日本語の意味を考慮しないので、意図しない検索結果 (ノイズ)が多い – インデックスのサイズは大きくなりがち

47. 使い方 ● パーサープラグインのインストール mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so'; ● テーブルの作成 mysql> CREATE TABLE fttest ( -> a SERIAL , -> b VARCHAR(100), -> FULLTEXT(b) WITH PARSER mecab -> ) CHARACTER SET utf8; ● 検索例 mysql> insert into fttest (b) values(' この先生 きのこるには '),(' ここではきものをぬげ '); mysql> select * from fttest where match (b) against (' きもの ');

48. 性能について ● 正直なところ、検索性能はそんなに速くない ● InnoDB はトランザクショナルなので堅牢性は高い ● 絶対的な検索性能が必要なら mroonga を使うべき ● カジュアルにちょっと全文検索が欲しいというときに便利

49. 空間インデックス

50. 最小外接矩形 (Minimum Bounding Rectangle) ● 図形に隣接する矩形の中で最小のもの

51. InnoDB に R ツリーインデックスが追加

52. 空間データの検索 ● MBRContains() 、 MBRWithin() 、 ST_Contains() 、 ST_Within() といった 関数に対して使用可能。 mysql> SELECT id,ST_AsText(g) FROM geomtest -> WHERE ST_Contains(ST_GeomFromText( -> 'POLYGON((0 0,150 0,150 150,0 150,0 0))'), g); mysql> SELECT id,ST_AsGeoJSON(g) FROM geomtest -> WHERE ST_Contains(ST_GeomFromGeoJson('{ "type": "Polygon", "coordinates": [ [ [0.0, 0.0], [150.0, 0.0], [150.0, 150.0], [0.0, 150.0], [0.0, 0.0] ] ] }'), g); MySQL 5.7 では GeoJSON も追加された。 空間インデックスとは関係ないけど GeoHash というものも追加された。

53. まとめ

54. MySQL 5.7 の InnoDB は 超絶進化!! ● 性能向上 – 並列実行性能の向上 – 処理の効率化およびチューニング – 限界更新性能の向上 ● 管理性向上 – 一般テーブルスペースによるテーブルの一括管理 – UNDO ログトランケートによるディスクスペースの節約 – ALTER TABLE の高速化 ● 利便性向上 – 透過的データ圧縮 – 透過的暗号化 – 全文検索 – GIS 検索 – MyISAM よ、さらば! もうこれは 使うしかない!!

55. 宣伝: 新書籍「詳解 MySQL 5.7 」 ● MySQL 5.7 の新機能を網羅 – 175 もの新機能を解説 – 新機能の理解に欠かせ ないアーキテクチャの話 も盛りだくさん – 本日解説しなかった新 機能も詳しく解説!! ● 8 月 23 日発売予定

56. Q&Aご静聴ありがとうございました。

Add a comment

Related pages

MySQL :: InnoDB: What's New in MySQL 5.7

Join this webinar to learn about the new InnoDB features, enhancements and performance improvements available in MySQL 5.7. We will cover recovery ...
Read more

MySQL :: MySQL 5.7 Reference Manual :: 1.4 What Is New in ...

... What Is New in MySQL 5.7 ... row format for InnoDB tables. A new ...
Read more

InnoDB: What's New in MySQL 5.7 - www-jp.mysql.com

Join this webinar to learn about the new InnoDB features, enhancements and performance improvements available in MySQL 5.7. We will cover recovery ...
Read more

MySQL :: What's New in MySQL 5.7

What's New in MySQL 5.7. Themen: Leistung & Skalierbarkeit, InnoDB, Replikation. MySQL 5.7 is the best ...
Read more

MySQL :: What's New in MySQL 5.6

... and consistency across MySQL restarts. InnoDB precomputes ... framework for InnoDB. The new I_S ... you can really see what's ...
Read more

MySQL 5.7 InnoDB - What's new - percona.com

MySQL 5.7 InnoDB - What's new Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Sunny Bains – sunny.bains@oracle.com Senior ...
Read more

What’s New in MySQL 5.7 Geir Høydalsvik, Sr. Director ...

What’s New in MySQL 5.7 ... •Many specific new optimizations MySQL 5.7: ... MySQL 5.7: InnoDB Improvements 15 .
Read more

MySQL :: What's New in MySQL 5.7 - www-jp.mysql.com

What's New in MySQL 5.7. ... MySQL 5.7 is the best release ever of the world's most popular open source database and provides a new, ...
Read more

What’s New in MySQL 5.7? (Generally Available) | MySQL ...

GIS: Completely new in MySQL 5.7 and including InnoDB spatial indexes, ... 10 thoughts on “ What’s New in MySQL 5.7? (Generally Available) ” WinDev says:
Read more

What’s New in MySQL 5.7? (So Far) | MySQL Server Blog

... “MySQL 5.7 : Over 1M QPS with InnoDB Memcached Plugin“, Sunny Bains’ article ... 5 thoughts on “ What’s New in MySQL 5.7? (So Far ...
Read more