第4回　MySQLチューニング（3）　パフォーマンススキーマ

パフォーマンススキーマとは

パフォーマンススキーマはMySQL 5.5から実装された性能統計情報に関するメタデータを格納するスキーマです。MySQL 5.5ではメタデータ取得のオーバーヘッドが大きく、本番運用時に利用することは推奨していませんでしたが、MySQL 5.6ではオーバーヘッドが大幅に改善されデフォルトで有効となっています。

パフォーマンススキーマは性能統計情報を記録するストレージエンジンの一種として実装されており、performance_schemaスキーマのテーブルに格納された処理のレイテンシ（ピコ秒単位）やデータのバイト数、ソースでの位置、オブジェクトのデータなどに対してSQLでアクセス可能です。MySQLサーバのソースコード中にある“⁠instrumentation point⁠”（または“⁠instrument⁠”と表現される）にて「イベント」毎の処理時間や処理データ量などを計測および表示します。

MySQL 5.5では性能統計情報取得のフレームワークとして設計され、まずは低レベルな稼働統計情報の収集に重点が置かれていました。取得できる情報はファイルIOや参照更新ロック、mutex関連などの200項目強でした。性能へのオーバーヘッドに課題があり、デフォルトではオフになっています。

MySQL 5.6ではよりデータベース管理者やアプリ開発者に役立つ情報に重点がおかれ、SQL文単位での情報、テーブルやインデックス単位でのIO、ネットワークIOなどが追加され、約550項目となりました。MySQL 5.7ではメモリ割り当て状況やストアドプログラムの性能統計情報など、さらに400項目以上が追加されています。

パフォーマンススキーマの階層構造

パフォーマンススキーマのテーブル名や格納されている値の“⁠Instruments⁠”名は階層構造の各レベルに対応しています。

Session：接続
Transaction：トランザクション（MySQL 5.7から）
Statement：SQL文
Stage：SQL実行プロセスのステップ
Wait：処理実行待ち

パフォーマンススキーマの設定

パフォーマンススキーマには、3種類の設定方法があります。

測定データの蓄積に割り当てるメモリ量はオプションファイル（my.cnf/my.ini）にてパラメータを設定します。この設定値は動的な変更はできません。
測定項目（Instruments）を各setupテーブルに対してUPDATE文を発行して、オン／オフを動的に変更可能です。
測定項目（Instruments）をオプションファイルにてパラメータを設定して、オン／オフの制御が可能です。この方法はMySQL 5.6から利用可能となりました。

クラス数とインスタンス数の設定

上記の1.では、クラスは“⁠Instruments⁠”の実装の測定する件数を、インスタンスは実際のファイルなどサーバ稼働時の実体を測定する件数を、カテゴリ別に指定します。例えばバイナリログのファイルIO待ちに関するクラスの名称はwait/io/file/sql/binlogとなり、実際のファイルであるインスタンスはsakila-binlog.000001などになります。

図1　測定対象のクラス数とインスタンス数の設定値の例

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
    ->   FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
    ->  WHERE VARIABLE_NAME LIKE 'PERF%CLASSES'
    ->     OR VARIABLE_NAME LIKE 'PERF%INSTANCES'
    ->  ORDER BY VARIABLE_NAME;
+------------------------------------------+----------------+
| VARIABLE_NAME                            | VARIABLE_VALUE |
+------------------------------------------+----------------+
| PERFORMANCE_SCHEMA_MAX_COND_CLASSES      | 80             |
| PERFORMANCE_SCHEMA_MAX_COND_INSTANCES    | 3504           |
| PERFORMANCE_SCHEMA_MAX_FILE_CLASSES      | 50             |
| PERFORMANCE_SCHEMA_MAX_FILE_INSTANCES    | 7693           |
| PERFORMANCE_SCHEMA_MAX_MUTEX_CLASSES     | 200            |
| PERFORMANCE_SCHEMA_MAX_MUTEX_INSTANCES   | 15906          |
| PERFORMANCE_SCHEMA_MAX_RWLOCK_CLASSES    | 40             |
| PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES  | 9102           |
| PERFORMANCE_SCHEMA_MAX_SOCKET_CLASSES    | 10             |
| PERFORMANCE_SCHEMA_MAX_SOCKET_INSTANCES  | 322            |
| PERFORMANCE_SCHEMA_MAX_STAGE_CLASSES     | 150            |
| PERFORMANCE_SCHEMA_MAX_STATEMENT_CLASSES | 168            |
| PERFORMANCE_SCHEMA_MAX_TABLE_INSTANCES   | 12500          |
| PERFORMANCE_SCHEMA_MAX_THREAD_CLASSES    | 50             |
| PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES  | 402            |
+------------------------------------------+----------------+
15 rows in set (0.01 sec)

実際に測定する件数が設定値を超えた場合には、ステータス変数のPERFORMANCE_SCHEMA_*_LOSTが増加します。例えばファイルに関するインスタンス数が設定値を超えた場合に増加するステータス変数はPERFORMANCE_SCHEMA_FILE_INSTANCES_LOSTです。クラスの設定値の変更が必要になるケースは少ないですが、インスタンスの設定値はテーブルの数が多い場合やパーティショニングなどでファイルが多い場合など変更を検討するケースもあります。

履歴件数の設定

どれだけの履歴データを蓄積するかもオプションファイルのパラメータで、タイプ別に設定します。パラメータ名が*_HISTORY_SIZEはスレッド毎の値、*_HISTORY_LONG_SIZEはサーバ全体の値です。

図2　履歴件数の設定値の例

mysql> SELECT VARIABLE_NAME, VARIABLE_VALUE
     ->   FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
     ->  WHERE VARIABLE_NAME LIKE 'PERF%'
     ->    AND VARIABLE_NAME NOT LIKE '%CLASSES'
     ->    AND VARIABLE_NAME NOT LIKE '%INSTANCES'
     ->  ORDER BY VARIABLE_NAME;
 +--------------------------------------------------------+----------------+
 | VARIABLE_NAME                                          | VARIABLE_VALUE |
 +--------------------------------------------------------+----------------+
 | PERFORMANCE_SCHEMA                                     | ON             |
 | PERFORMANCE_SCHEMA_ACCOUNTS_SIZE                       | 100            |
 | PERFORMANCE_SCHEMA_DIGESTS_SIZE                        | 10000          |
 | PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_LONG_SIZE     | 10000          |
 | PERFORMANCE_SCHEMA_EVENTS_STAGES_HISTORY_SIZE          | 10             |
 | PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_LONG_SIZE | 10000          |
 | PERFORMANCE_SCHEMA_EVENTS_STATEMENTS_HISTORY_SIZE      | 10             |
 | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_LONG_SIZE      | 10000          |
 | PERFORMANCE_SCHEMA_EVENTS_WAITS_HISTORY_SIZE           | 10             |
 | PERFORMANCE_SCHEMA_HOSTS_SIZE                          | 100            |
 | PERFORMANCE_SCHEMA_MAX_FILE_HANDLES                    | 32768          |
 | PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES                   | 4000           |
 | PERFORMANCE_SCHEMA_SESSION_CONNECT_ATTRS_SIZE          | 512            |
 | PERFORMANCE_SCHEMA_SETUP_ACTORS_SIZE                   | 100            |
 | PERFORMANCE_SCHEMA_SETUP_OBJECTS_SIZE                  | 100            |
 | PERFORMANCE_SCHEMA_USERS_SIZE                          | 100            |
 +--------------------------------------------------------+----------------+
 16 rows in set (0.00 sec)

setup_テーブルでの設定

パフォーマンススキーマでどのような情報を取得するかはperformance_schema内の各setup_テーブルで定義します。これらのテーブルは稼働中にUPDATE文で値の変更が可能です。

表1　パフォーマンススキーマの設定関連のテーブル

テーブル名	役割
setup_actors	情報取得対象とするユーザアカウントの設定
setup_consumers	どれだけの情報を取得するかの設定（現在のみ、履歴を含むなど）
setup_instruments	MySQLサーバのソースコード内に設けられた処理時間や待機時間を収集するための“⁠instrument⁠”の設定
setup_objects	情報取得対象とするオブジェクトの設定（MySQL 5.6ではテーブルのみ）
setup_timers	各“instrument⁠”カテゴリのデータ取得で使用するタイマーの設定。タイマーはperformance_timersテーブルのTIMER_NAME列の値

オプションファイルでの設定

MySQL 5.6からは上記のsetup_consumersテーブルとsetup_instrumentsテーブルの内容をオプションファイルにて設定可能です。consumerの有効無効を設定は以下の書式です。

performance_schema_consumer_consumer名=value: 例）performance_schema_consumer_events_stages_current=on

instrumentについては、onとoffだけではなく、回数のみを計測するcountedを設定可能です。

performance_schema_instrument='instrument名=値': 値 = [on | true | 1] / [off | false | 0] / [counted] （⁠回数計測のみ）

なお、Instrument名にはワイルドカードが利用可能です。

例）: wait/synch/mutex/%

パフォーマンススキーマのデータ格納テーブルの名称

名称がevents_で始まるテーブルは個別の“⁠instrument⁠”で発生したイベント、イベントの開始時間や終了時間、ネストされたイベントのIDなどが格納されます。SQL文単位でのイベントは名称に_statements_が含まれるテーブルに、「⁠テーブルを開く」「⁠データを送信する」「⁠一時表にデータをコピーする」などより詳細なステージ毎のイベントは_stages_が含まれるテーブルに、mutexやロック、IOによる待ちのイベントは_wait_が含まれるテーブルに格納されます。

テーブル名の末尾が_currentは現在のイベント、_historyはクライアント毎に最大でシステムパラメータperformance_schema_events_*_history_sizeで定義された件数までセッション単位での過去のイベント、_history_longは同様にperformance_schema_events_*_history_long_sizeで定義された件数までサーバ全体の過去のイベントが格納されます。またテーブル名_summary_が含まれるものは集計されたデータが表示されます。

SYSスキーマ

パフォーマンススキーマは非常に細かな粒度で情報が取得できるものの、データベース管理者やアプリケーション開発者にとっては細かすぎる場合もあります。そこでMySQLではパフォーマンススキーマやインフォメーションスキーマのデータに対して、よりシンプルなビューの集合であるSYSスキーマを用意し、性能問題の検出や診断などのタスクをより支援できるように改善しています。

SYSスキーマはps_helperと呼ばれていたスクリプト集へのフィードバックを元に開発さており、MySQL 5.5、5.6、5.7の各バージョンに対応しています。SYSスキーマには約80のビューが含まれており、コストの高いSQL文、IO量の大きいファイルや処理、ロックなどの状況を簡単に確認できます。SYSスキーマはGitHubで公開されているほか、後述のMySQL Workbenchから簡単にインストールすることもできます。

MarkLeith/mysql-sys：https://github.com/MarkLeith/mysql-sys/

テーブル名がx$で始まるものは単位無しのデータで、そうでないものは人間が判断しやすいようにデータに単位が付けられています。

稼働状況の確認方法（3）－MySQL Workbenchのパフォーマンスダッシュボードとレポート

MySQL WorkbenchはMySQLの運用や開発を支援するオープンソースのGUIツールです。MySQL Workbench 6.1にはMySQLの稼働状況を表示するパフォーマンスダッシュボードと、SYSスキーマの内容をカテゴリ別に表示するパフォーマンスレポートが追加されました。

パフォーマンスレポートでは、IOの状況、問題となり得るSQL文、スキーマ毎の稼働状況などカテゴリ別にSYSスキーマの情報が表示されます。

パフォーマンススキーマで取得する情報量をGUIから制御することも可能です。

次回は

次回はMySQLでのSQL文のチューニングの基本をご紹介いたします。