MySQLをチューニング，そしてスケールアップ／スケールアウトへ

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

MySQL, チューニング, パフォーマンススキーマ, MySQL Workbench, ダッシュボード, パフォーマンスレポート, SQL

この記事を読むのに必要な時間：およそ 4.5 分

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

パフォーマンススキーマは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種類の設定方法があります。

  1. 測定データの蓄積に割り当てるメモリ量はオプションファイル（my.cnf/my.ini）にてパラメータを設定します。この設定値は動的な変更はできません。
  2. 測定項目（Instruments）を各setupテーブルに対してUPDATE文を発行して，オン／オフを動的に変更可能です。
  3. 測定項目（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)

MySQL, チューニング, パフォーマンススキーマ, MySQL Workbench, ダッシュボード, パフォーマンスレポート, SQL

著者プロフィール

梶山隆輔

MySQL Sales Consulting Senior Manager。

日本オラクル（株）において，MySQLのお客様環境への導入支援や製品の技術解説を担当するセールスコンサルタントチームのアジア太平洋地域リーダー。多国籍なMySQL部門にて，オーストラリア，インド，台湾などに在籍するチームメンバーを束ね，アジア太平洋地域の25以上の国や地域でのMySQL普及やビジネスの拡大をミッションとする。