MySQL Clusterにおけるチューニングの基礎

本連載では、実際に「MySQL Cluster」を利用するためのチュートリアルとなるように、その特徴と基本的なアーキテクチャからインストール方法、基本的な操作などをコマンド付きで解説していきます。第9回の今回は、MySQL Clusterにおけるチューニングの基礎について解説します。

スループットを向上させる

MySQL Clusterは、大量の細かいトランザクションを並列で捌くことに最適化されています。反対に、一度のトランザクションで大量のレコードを更新することは苦手です。そのため、まずは全体的なチューニングの方針として、「並列処理を最適化してスループット（単位時間あたりの処理量）を向上させる」という方針でのチューニング手法について解説します。

データノードで同時実行するスレッドの数をチューニングする

データノードのプロセスndbmtdは、マルチスレッドに対応しています。最近のCPUはマルチコア化が進んでいますので、データノードが稼働しているサーバーのCPU数に合わせて同時実行するスレッドの数をチューニングすることで、スループットの向上が期待できます。具体的には、MaxNoOfExecutionThreadsパラメータをデータノードが稼働しているサーバーの物理CPUコア数に合わせて設定します（デフォルト値は2、最大値は76）※。

※MaxNoOfExecutionThreadsを16以上に設定する場合は、NoOfFragmentLogPartsをデフォルト値より増加させる必要があります（デフォルト値は4、最大値は32）。NoOfFragmentLogPartsは4の倍数で設定しますが、LDM（Local Data Manager）スレッドの数より大きな値を設定しておく必要があるためです。MaxNoOfExecutionThreadsを16以上に設定する場合は、以下のマニュアルからMaxNoOfExecutionThreadsを増加させた時のLDMスレッド数を確認し、その数に応じてNoOfFragmentLogPartsを調整してください。

18.3.2.6 MySQL Cluster データノードの定義 : MaxNoOfExecutionThreads
https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-maxnoofexecutionthreads

また、MaxNoOfExecutionThreadsを設定するよりも厳密にチューニングしたい場合は、ThreadConfigパラメータを設定できます。MaxNoOfExecutionThreadsは同時実行するスレッドの数を設定するのみで、どのCPUコアがどのスレッドを実行するかは明確に制御できません。しかし、ThreadConfigを使用すると特定のCPUコアに特定のスレッドを明示的に割り当てることができ、より厳密にチューニングできます。ThreadConfigの詳細は以下のマニュアルで解説されていますので、ThreadConfigを使用する場合は参考にしてください。

18.3.2.6 MySQL Cluster データノードの定義 : ThreadConfig
https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-threadconfig

参考情報として、MaxNoOfExecutionThreadsやThreadConfigのチューニングについて、日本ヒューレット・パッカード株式会社様の検証結果が公開されています。検証結果からは、SQLノードが2台の状態でMaxNoOfExecutionThreadsやThreadConfigを調整することでデータノードのCPUがより多く使用され、スループットがMaxNoOfExecutionThreadsを用いてチューニングした場合で約1.7倍、ThreadConfigを用いてチューニングした場合で約2倍に向上していることが確認できます（該当内容の検証結果は11~19ページ、25ページに掲載）。

MySQL Clusterでもフラッシュドライブを活用してみる
http://h50146.www5.hp.com/services/ci/opensource/pdfs/HP_OpenServices.pdf

SQLノードからデータノードへの接続数を増加する

並列で実行する処理数が多い場合、SQLノードからデータノードへの接続数を増加させることで、スループットを向上できる場合があります。SQLノードからデータノードへの接続数を増加させたい場合は、SQLノードのmy.cnfにndb-cluster-connection-poolを設定し、2以上の値を設定します。第3回でも解説しましたが、SQLノードからデータノードへの接続毎にNodeIdが割り振られるため、ndb-cluster-connection-poolを2以上に設定する場合はconfig.ini内の［mysqld］セクションを対応する数だけ増やす必要があることに注意してください。

ndb-cluster-connection-poolについても、前述の日本ヒューレット・パッカード株式会社様の検証結果では、チューニングによる効果が測定されています。検証結果からは、ndb-cluster-connection-poolをチューニングすることでSQLノードが1台の状態でスループットが約2倍に、SQLノードが2台の状態でスループットが約4倍に向上していることが確認できます（該当内容の検証結果は20~25ページに掲載）。

トランザクションを細かく分割する

バッチ処理などで大量レコードを更新する必要がある場合は、処理を細かい単位に分割して少しずつ更新する方が結果的に高速になります。また、分割したトランザクションを並列で処理できる場合は、並列実行することでスループットの向上が期待できます。「何行毎に更新するのが最適か？」という点は、環境やテーブル定義にも依存するため一概に決められるものではありませんが、1000行程度を1つの目安としてください。また、一度に大量レコードを更新すると、MaxNoOfConcurrentOperationsやRedoBufferの上限値に引っかかって処理がエラーになる場合がありますが、トランザクションを細かく分割することでこれらのエラー回避にもつながります。

SQLをチューニングする

通常のMySQL Serverと同様に、インデックスを活用するなどしてSQLをチューニングすることはMySQL Clusterにおいても有効です。第7回で解説したように、MySQL Clusterは通常のMySQL Serverと比べてJOIN処理が苦手であるため、ここではMySQL ClusterにおけるJOIN処理の高速化に関するトピックを解説します。

AQLによりJOINを高速化する

MySQL Cluster 7.2以降では、JOIN処理を高速化するAQL（Adaptive Query Localization）というアルゴリズムが実装されています。これにより、従来は苦手だったJOIN処理のパフォーマンスが大幅に改善されています。AQLはデフォルトで有効になっているため、使用するために特別な準備は必要ありません。しかし、どのようなJOINにも対応しているわけではなく、次のような制限があります。

［AQLの制限事項］

• JOIN対象の列の型は、両方のテーブルでまったく同じデータ型でないといけない
  (例：INTとBIGINTは両方整数型だが、まったく同じデータ型ではないためAQLが使用できない。INTとSMALLINT等の組み合わせについても同様)
• BLOBまたはTEXT列を参照するSQLはAQLが使用できない
• FOR UPDATE文はAQLが使用できない
• AQLが使えるJOINのアクセスタイプはref、eq_ref、constのみ
• パーティションテーブルへのアクセスはAQLが使用できない

MySQL ClusterでJOIN処理を行う場合は、上記の制限に引っかからないようにJOINを実現できないか検討し、上記の制限に引っかからない場合はSQLの実行計画をEXPLAINコマンドで確認してAQLが使用できるかどうか確認しましょう。AQLが使用できる場合は、EXPLAINコマンドのExtraフィールドに"pushed join"というキーワードが出力されます。

BKAとMRRによりJOINを高速化する

MySQL Server 5.6以降では、BKA（Batched Key Access）とMRR（Multi Range Read）というJOIN処理を高速化できるアルゴリズムが使用できます。そしてMySQL Cluster 7.3、7.4ではSQLノードにMySQL Server 5.6を使用しており、これらのアルゴリズムはNDBストレージエンジンにおいても有効であるため、BKAとMRRを使用することでJOIN処理が高速化できる可能性があります。

BKAはデフォルトで無効化されています。また、MRRはデフォルトで有効化されていますが、オプティマイザがMRRをほぼ選択しない状態に設定されています。そのため、BKAとMRRを使用する場合は、JOIN処理を実行する前にリスト1のコマンドを実行するなどして、SQLノードのoptimizer_switchパラメータの設定を変更する必要があります。

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

SQLの実行計画をEXPLAINコマンドで確認した時に、typeフィールドがrefまたはeq_refであり、ExtraフィールドにUsing join buffer（Batched Key Access）が含まれる場合はBKAが使用できます。また、ExtraフィールドにUsing MRRが含まれる場合はMRRが使用できます。BKAとMRRの詳細については、以下のマニュアルを参照してください。

8.2.1.14.3 Batched Key Access 結合
https://dev.mysql.com/doc/refman/5.6/ja/bnl-bka-optimization.html#bka-optimization

8.2.1.13 Multi-Range Read の最適化
https://dev.mysql.com/doc/refman/5.6/ja/mrr-optimization.html

その他のチューニングTIPS

ODirectを有効にする

通常のMySQL Serverのチューニング手法としてもよく知られた手法ですが、Linux環境におけるMySQL Clusterのデータノードにおいても、多くの場合有効です。O_DIRECT（Direct I/O）を使用することでページキャッシュを経由せずにデバイスへの書き込みを行い、CPU使用率の低下が期待できます。マニュアルでも、「Linux上でMySQL Clusterを使用するときに2.6以降のカーネルを使用する場合は、ODirectを有効にしてください。」と明記されています（デフォルト値はfalseであるため、明示的に有効にする必要あり）。

コンサルティングサポートを活用して、チューニングに関する問合せをする

MySQL Clusterには、GPLライセンスで提供されているオープンソース版以外に、「MySQL Cluster CGE（Cluster Carrier Grade Edition）」という商用版があります。MySQL Cluster CGEでは、商用版のみで使用できるMySQL Cluster Manager（MySQL Cluster環境を効率的に管理できる管理ツール）などの便利な追加機能が使えるという利点に加え、サポートサービスも利用できます。そして、このサポートサービスにはコンサルティングサポートというメニューも含まれており、SQLチューニングやサーバー全体のチューニングもサポート範囲内となっています。そのため、商用版を契約すればMySQL Clusterのプロフェッショナルからチューニングに関する問合せに回答してもらうこともできます。

おわりに

今回は、MySQL Clusterのチューニングの基礎について解説しました。MySQL Clusterを使用する上でのチュートリアルとなるようにとの思いもあり、MySQL Clusterのインストール方法からコマンド付きで解説してきましたが、本連載も今回で最後となります。表1に今までの連載の解説内容を一覧にまとめたので参考にしてください。本連載をきっかけとして、MySQL Clusterを触られる方が1人でも増えれば、とても嬉しく思います。

表1：本連載各回の解説内容