MySQL 5.6での機能強化点（その1） - パフォーマンスと使い勝手を大きく向上

連載 :

2013年11月19日(火)

MySQL 5.6：今までで最高のリリース

2013年2月にMySQL 5.6がリリースされました。このバージョンは、パフォーマンスや使い勝手も大幅に向上し、「今までで最高のリリース」と言われています。また、製品品質の面でも、従来のバージョンよりも高い評価を頂けています。その背景にあるのが、DMR（Development Milestone Release）という開発モデルです。

MySQLは現在、正式リリース前に年に2～4回程度新たな機能を追加した開発途上版をリリースし、ユーザーからのフィードバックを受けて品質を高めた上で、正式版であるGA（Generally Availability）をリリースする、という開発モデルを採用しています。例えば、MySQL 5.6の場合は、以下の図のようにDMRをリリースし、機能追加/品質向上を続けてきました（最後のRCは、Release Candidateを意味します。RCではGAに含まれる機能セットはほぼ確定した状態で、その後バグが修正されてGAがリリースされる、という流れになっています）。

図1：MySQL 5.6のリリース履歴（クリックで拡大）

そしてこのMySQL 5.6は、特に以下の点について機能強化しています。

InnoDB
オプティマイザ
パフォーマンススキーマ
レプリケーション
NoSQLオプション(Memcached API)

今回はこの中でも、InnoDBとオプティマイザの改善について解説します。

InnoDB：最も使用されているストレージエンジン

MySQLにはプラガブルストレージエンジンという機能があり、データを格納するストレージ部分を、用途に応じて柔軟に変更することができます。ストレージエンジンが変われば、データの格納方式やインデックスの実装方法が変わるだけでなく、同時実行制御の方式やトランザクションの有無も変わってきます。

MySQLはデフォルトの状態でも複数のストレージエンジンが使用できる状態になっていますが、その中でも、現在最も使用されているストレージエンジンはInnoDBというストレージエンジンです。
InnoDBは、トランザクションに対応したストレージエンジンで、MySQL 5.5以降のデフォルトストレージエンジンです。MySQL 5.5の時点でも、InnoDBの性能は大幅に向上していたのですが、MySQL 5.6では更に性能が向上しています。また、運用する上で便利な機能も多く追加され、使い勝手も大きく向上しています。

InnoDBの性能を更に向上

MySQL 5.6では、カーネルmutexの分割やバッファプールのフラッシュの効率改善など、内部の実装を改良することで、InnoDBの性能が更に向上しています。また、参照専用トランザクションや可変ページサイズなど、パフォーマンス改善につながる各種の新機能が実装されていますので、どのような機能か紹介していきます。

参照専用トランザクション

参照中心のアプリケーションの場合、参照専用トランザクションを使用することで、オーバーヘッドの削減が可能になりました。MySQL 5.6では、START TRANSACTION READ ONLYというシンタックスが追加されています。参照しか行わないトランザクションの初めにこのコマンドを実行することで、そのトランザクションは参照専用トランザクションと認識されます。そうすることで、更新処理に伴う内部的な準備が不用になる分、オーバーヘッドの削減ができ、パフォーマンス向上が期待できます。

ログファイルサイズの上限が増加

これまで、InnoDBのログファイルの上限は合計4GBまででしたが、メモリを大量に搭載しているサーバーで更新処理が多発する場合など、サイズが不十分になるケースもありました。そこで、MySQL 5.6ではログファイルの最大サイズを拡張し、最大512GBまでのログファイルを作成できるようになっています。

また、こちらはMySQL 5.5時点での改善点ですが、InnoDBのクラッシュリカバリ処理が高速化されています。これにより、サイズの大きいログファイルを問題なく活用できます。

可変ページサイズ

従来はInnoDBのページサイズは16KBに固定されていました。InnoDBはページ単位でI/Oするため、ページサイズはパフォーマンスに影響を与える要因の1つとなっています。例えば、大量の行を一度に更新するようなUPDATE処理では、ページサイズが大きい方が多くの行にまとめてアクセスすることができるためI/O効率が良くなりますが、1行だけを更新するUPDATE処理では、ページサイズが小さい方が余分なデータが少なくなるためアクセス効率が良くなります。また、I/Oの特性はハードウェアによっても異なります。

HDDはランダムアクセスが苦手ですが、SSDはランダムアクセスが得意です。16KBというページのサイズは、ランダムアクセスが苦手なHDDを考慮し、1回のアクセスである程度まとまったサイズのデータをやり取りできるように設定されていました。しかし、ランダムアクセスが得意なSSDの環境では、ページサイズが小さい方が全体のスループットが向上する可能性もあります。そこで、MySQL 5.6ではページサイズをパラメータで変更できるようにしました。

ページサイズを変える場合は、インスタンス作成時に以下のパラメータを設定しておきます。

-	innodb_page_size=4k

※設定可能な値は4k、8k、16k（デフォルト値は16k）詳細はこちらを参照下さい。

インスタンス作成後、後からページサイズを変更することや、テーブル単位でページサイズを変更することはできないので、注意して下さい。

SSD環境に向けた最適化

InnoDBには、バッファプール上で変更されたデータをディスクに書き出す場合に、近隣のページをまとめて1回のI/Oで書き出す仕組みがありますが、この仕組みはランダムアクセスが苦手なHDD向けに最適化された機能です。
SSDの環境では、必要なページのみをディスクに書き出した方が全体の処理効率が上がるため、近隣ページの書き出しを無効化できます。近隣ページの書き出しを無効化する場合は、以下のパラメータを設定します。

-	innodb_flush_neighbors=0

※設定可能な値は0、1、2（デフォルト値は1）詳細はこちらを参照下さい。

UNDO表領域の分離

UNDOログ（ロールバックセグメント）専用の独立したテーブルスペースを作成できるようになりました。ディスクが複数ありI/O分散できるケースなどでは、別ディスクにUNDO表領域を配置することで、パフォーマンス向上が期待できます。

InnoDBの運用をより便利に、柔軟に

MySQL 5.6では、InnoDBの運用がより便利になるような機能も複数実装されています。どのような機能が実装されたのか、紹介します。

オンラインDDL

従来は、インデックスの追加/削除を実行する時は、INSERT、UPDATE等の操作はブロックされていました。そのため、サービス提供中にパフォーマンスチューニングできず、メンテナンス時間を確保してインデックスを追加する、といった対応をしているケースもありました。MySQL 5.6では参照・更新処理をブロックせずにインデックスの追加/削除ができるようになっているため、メンテナンス時間を最小限にすることが可能です。

また、列の追加/削除処理もオンラインで実行できます。そのため、サービス稼働後にアプリの修正があり、後からスキーマ定義を変更する必要が出てきた場合でも、サービスを稼働させながらスキーマ定義の変更が可能です。

以下は、オンラインで実行できるDDL操作の一覧です。

図2：オンラインDDL（クリックで拡大）

MySQLサーバー間で、テーブル単位でのデータ移行が簡単に

「InnoDBでもMyISAMのように、テーブル単位でデータを別のサーバーに移行したい」これは以前から多く頂いていた要望ですが、MySQL 5.6では、InnoDBのデータもテーブル単位で移行できるようになりました。MyISAMの場合と手順は異なりますが、次の手順でInnoDBのデータをテーブル単位で移行できます。

移行元のMySQLサーバー

FLUSH TABLE <テーブル名> FOR EXPORT;
OS上で、.ibdファイルと.cfgファイルをコピー (1.を実行することにより、.cfgファイルが作成される)
UNLOCK TABLES;

移行先のMySQLサーバー

移行元と同じテーブル定義でテーブルを作成
ALTER TABLE <テーブル名> DISCARD TABLESPACE;
OS上で、移行元で取得した.ibdファイルと.cfgファイルをコピー
ALTER TABLE <テーブル名> IMPORT TABLESPACE;

もちろん、上記手順は同一サーバー内でテーブル単位でのバックアップ目的でも利用できます。

バッファプールのダンプ＆リストア

InnoDBには、一度アクセスしたデータをInnoDB Buffer Poolというメモリ上の領域にキャッシュしておくことで、2回目以降のアクセスを高速化する仕組みがあります。この場合、メモリ上のデータはMySQLサーバーを再起動すると消えてしまうため、再起動直後はパフォーマンスが劣化してしまう、という現象が起きます。
このような現象を防ぐために、MySQLサーバー再起動直後にバッファプール上にデータをキャッシュするための適当なクエリを実行してからアプリケーションを動かしているケースもありますが、MySQL 5.6ではバッファプールの内容をダンプ＆リストアできるため、この課題を解決できます。

再起動時に自動的にバッファプールのダンプとリストアを行う場合は、以下のパラメータを設定します。

-	innodb_buffer_pool_dump_at_shutdown=ON

※設定可能な値はON、OFF（デフォルト値はOFF）詳細はこちらを参照下さい。

-　innodb_buffer_pool_load_at_startup=ON

※設定可能な値はON、OFF（デフォルト値はOFF）詳細はこちらを参照下さい。

また、任意のタイミングでバッファプールのダンプ、リストアを実施することもできます。夜間にバッチ処理が流れて、バッファプール上のデータが入れ替わってしまうような場合など、バッチ処理実行前にバッファプールをダンプし、バッチ処理完了後にリストアする、といった使い方が可能です。任意のタイミングでダンプ、リストアを実施する場合は、以下のコマンドを実行します。

バッファプールのダンプ

  SET GLOBAL innodb_buffer_pool_dump_now=ON;

※設定可能な値はON、OFF（デフォルト値はOFF）詳細はこちらを参照下さい。

バッファプールのリストア

  SET GLOBAL innodb_buffer_pool_load_now=ON;

※設定可能な値はON、OFF（デフォルト値はOFF）詳細はこちらを参照下さい。

また、ダンプされるデータは実データそのものではなく、テーブルスペースとページの情報のみのため、非常に小さいサイズになります。そのため、この機能を使うためにバッファプールと同程度のディスク空き容量を確保しないといけない、といったこともありません。

デッドロック検出の高速化、デッドロックの情報をエラーログに出力

デッドロックを検出する際のアルゴリズムが変更され、より高速にデッドロックを検出できるようになりました。

また、デッドロック発生時に情報をエラーログに出力することが可能になりました。従来であれば、SHOW ENGINE INNODB STATUSコマンドで直近に発生したデッドロックの情報のみが確認できる状態でしたが、エラーログに記録しておくことにより、デッドロック発生後の調査が楽になります。

デッドロック発生時の情報をエラーログに出力する場合は、以下のパラメータを設定します。

-	innodb_print_all_deadlocks=ON;

※設定可能な値はON、OFF（デフォルト値はOFF）詳細はこちらを参照下さい。

オプティマイザ：性能の向上

オプティマイザの改良

続いては、オプティマイザの改善点についてご紹介します。オプティマイザにより、SQLの実行計画が生成されます。MySQL 5.6ではオプティマイザを改良しているため、今までと同じクエリであっても5.6の環境で実行するだけで、パフォーマンスが良くなるケースもあります。どのように改善されているのか、改善内容をご紹介します。

・サブクエリの高速化

サブクエリは、MySQLにとって苦手な処理でした。しかし、MySQL 5.6では、サブクエリが高速化されています。そのため、チューニングのためにサブクエリをJOINに書き換える、といった対応が不要になります。

・Block Nested Loop Join（BNLJ）がOUTER JOINに対応

BNLJは、インデックスが使えないJOIN処理を高速化できるアルゴリズムです。内部表へのアクセスにインデックスが使えない場合に、駆動表のレコード毎に内部表にアクセスするのではなく、駆動表のレコードをJOINバッファにためてからまとめて内部表へアクセスすることで、内部表へのアクセスを効率化できます。従来はINNER JOINにのみ対応していましたが、OUTER JOINにも対応しました。

・Batched Key Access（BKA）とマルチ・レンジ・リード(MRR)

ディスクアクセスの多いJOIN処理を高速化できるアルゴリズムです。インデックスを使って内部表にアクセスする場合に、MRRによってディスクの並び順に従ってデータファイルを読み取ることができます。また、BKAによって駆動表のレコードをJOINバッファにためてからMRRを使ってまとめて内部表にアクセスすることで、JOIN処理が高速化できます。

※BKAはデフォルトでは無効に設定されています。BKAを有効にする場合は、optimizer_switchパラメータを変更して下さい。詳細は、こちらを参照下さい。

・Index Condition Pushdown(ICP)

WHERE句の条件など、インデックスによる絞り込みを全てストレージエンジン側で行うことで、MySQLサーバー内のオーバーヘッドを削減します。

・LIMIT句使用時の最適化

ソートの中間ファイル作成をできる限り回避し、メモリ上で処理することで、LIMIT句の性能を向上させています。

・FROM句からのビュー/サブクエリの実データ取得を遅延

ビュー/サブクエリのマテリアライズをできる限り避けたり、マテリアライズ化したテーブルにインデックスを追加したりすることで、FROM句にビュー/サブクエリが含まれるクエリを高速化しています。

・オプティマイザ統計情報の永続化

実行計画を安定させるために、InnoDBテーブルやインデックスの統計情報を永続化できます。ANALYZE文での更新も可能です。

オプティマイザ：使い勝手の向上

Explainの改良やオプティマイザ・トレースなど、オプティマイザ関連の使い勝手も向上しています。

ExplainをSELECT以外でも実行可能に

SQLの実行計画を確認するExplainですが、従来はSELECTに対してのみ実行できました。そのため、例えばUPDATEのチューニングをする場合に、WHERE句が同じSELECT文に書き換えてからExplainを取得する、といった対応をしているケースもありましたが、MySQL 5.6ではこのような対応が不要になりました。SELECT以外にも、DELETE、INSERT、REPLACE、UPDATEに対してExplainを取得することが可能です。

ビジュアルExplain

MySQL 5.6では、従来のテキストフォーマットのExplain以外に、JSONフォーマットでExplainを出力することが可能になりました。JSONフォーマットのExplainはテキストフォーマットのExplainよりも情報量が多くなっている、という利点もありますが、それ以上に便利なのがMySQL Workbenchと連携して使える、ビジュアルExplainです。
MySQL WorkbenchのSQL EditorからExplainを取得することで、以下のようにExplain結果をビジュアルに表示することが可能です。

図3：ビジュアルExplain（クリックで拡大）

MySQL Workbenchの詳細については、別途連載している「モデリングツールから管理・開発統合環境に進化したMySQL Workbench 6.0」をご覧下さい。

また、JSONフォーマットでのExplainを取得する場合は、以下の構文で取得できます。

  EXPLAIN FORMAT=JSON <Explain対象のSQL文>;

オプティマイザ・トレース

オプティマイザが実行計画を生成する際にどのような判断をしたのか、詳細をトレースできるようになりました。以下の手順でオプティマイザ・トレースを取得することが可能です。トレースはJSON形式で出力されます。

  SET SESSION optimizer_trace='enabled=on,one_line=off';
  SET SESSION optimizer_trace_max_mem_size=102400;   ※必要に応じて設定(トレースを保持する量をバイト単位で指定、デフォルト値は16384)
  <任意のクエリを実行>
  SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
  SET SESSION optimizer_trace='enabled=off,one_line=off';