第3回　MySQLチューニング（2）　設定項目と稼働状況の確認

稼働中のMySQLサーバのパラメータ確認－SHOW VARIABLESコマンド

SHOW VARIABLESコマンドでは現在のパラメータを確認できます。SHOW VARIABLESコマンドの出力内容はLIKEやWHEREで絞り込むこともできます。パラメータ名がわかっている場合には、「⁠SELECT @@パラメータ名;」で値の確認も可能です。

図1　SHOW VAIRABLESコマンドの実行例

mysql> SHOW VARIABLES LIKE 'char%';
mysql> SHOW VARIABLES WHERE Value = 'ON';
mysql> SELECT @@character_set_server;

ここでの注意すべきポイントが2点あります。

MySQLサーバ起動時の設定ではなく現在有効なパラメータが表示される
デフォルトでは現在の接続で有効なパラメータが表示され、他の接続とは値が異なる可能性がある

1. MySQLサーバ起動時のパラメータではなく現在有効なパラメータが表示される

SETコマンドによって接続毎に一時的にパラメータを変更できる項目があります。SHOW VARIABLESコマンドの結果にはSETコマンドによって変更された後のパラメータが出力されるため、MySQLサーバの起動時の設定とは異なる可能性があります。

2. デフォルトでは現在の接続で有効なパラメータが表示され、他の接続とは値が異なる可能性がある

SETコマンドはデフォルトではコマンドを実行しているクライアントのパラメータを一時的に変更し、他のクライアントとは別の値を利用することができます。SETコマンドで設定した値はクライアントを切断するまでの間のみ有効です。他のクライアントのパラメータはGLOBALオプションを付けたSHOW GLOBAL VARIABLESコマンドで確認できます。現在の接続に関するコマンドと明示する場合はLOCALオプションを付けます。

例）: SHOW LOCAL VARIABLES;

SETコマンドにもGLOBALオプションが利用可能です。この場合、設定した値は「SET GLOBALコマンド以降に接続したクライアント」のみに適用され、コマンドを実行しているクライアントや接続済みの他のクライアントには適用されないので注意してください。またSET GLOBALコマンドの実行にはSUPER権限が必要です。

MySQLのパラメータ設定

MySQLのパラメータ設定方法は、設定ファイルでの指定とMySQLサーバのコマンドラインオプションでの指定があります。MySQLの設定ファイルはテキストファイルのため、各種のエディタで編集可能です。

どこに置かれた設定ファイルを使用するかはMySQLサーバのバージョンやOSによって異なるので注意が必要です。複数の設定ファイルに同じ設定項目で異なる値が設定されていた場合は、後から読み込まれたファイル（下記の図では後ろに書かれたファイル）の内容が優先されます。セキュリティの観点から、全ユーザが書き込み可能になっている設定ファイルはMySQLサーバからは利用されません。

参考リンク: MySQL 5.6リファレンスマニュアル 4.2.6 Using Option Files

図2　Mac OS X 10.9上でのMySQL 5.6.22が利用する設定ファイル

$ ./mysqld --verbose --help|grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

このため、MySQLサーバ起動時のコマンドラインオプション「--defaults-file」で設定ファイルを明示的に指定することを推奨します。なお、その他のコマンドラインオプションは設定ファイルの内容よりもさらに優先されるため、テストなどの目的で一時的にパラメータを変更する際に利用できます。

稼働状況の確認方法（1）－SHOW STATUSコマンド

SHOW STATUSコマンドではMySQLサーバの内部カウンタなどの値を確認できます。コマンド実行回数などは、デフォルトではSHOW STATUSコマンドを実行したクライアントによる値、GLOBALオプションを付けることでMySQLサーバ全体の値を確認できます。

主なカテゴリは以下のとおりです。

表　接頭辞とカテゴリ

接頭辞	カテゴリ
Com_	MySQLサーバ内でのコマンド実行回数
Create_tmp_	テンポラリテーブル作成
Handler_	Handlerインターフェースに対するコマンド実行回数
Innodb_	InnoDBストレージエンジン
Key_	MyISAMのインデックスキャッシュ
Performance_schema_	パフォーマンススキーマ
Qcache_	クエリキャッシュ
Select_	問題となり得るSELECT文
Slave_	レプリケーションスレーブ
Sort_	ソート
Ssl_	SSL
Table_	テーブルレベルロックおよびテーブルキャッシュ
Thread_	MySQLサーバの内部スレッド
Uptime_	稼働時間

SHOW GLOBAL STATUSコマンドの出力は、MySQLサーバの起動時点から現在までの積算値が出力されます。SHOW STATUSコマンドの場合は、Uptimeなど一部の値を除いて接続時からの積算値となります。FLUSH STATUSコマンドを実行することで一部のカウンタをリセットができるので、その後に挙動を確認したいSQL文を実行してカウンタの変化が確認可能となります。

稼働状況の確認方法（2）－MySQL Enterprise Monitor

MySQL Enterprise MonitorはMySQL Enterprise Editionで提供されている監視ツールです。OSやMySQLサーバ内のリソース利用状況について複数のMySQLサーバを一括して監視可能です。通常の利用には監視対象の各サーバにエージェントプログラムを導入する必要がありますが、クラウド環境等でエージェントプログラムを動作させることができない場合には、エージェントレスモードにてMySQLサーバ内のリソース利用状況のみ監視することができます。

MySQL Enterprise Monitorでは全てのSQL文の稼働統計を取得するQuery Analyzer機能が含まれています。このQuery Analyzerでは各SQL文の実行回数、実行時間や送信されたレコード数、データサイズが集計されます。Query Analyzerを利用することで、チューニング対象とすべきSQL文が簡単に抽出でき、問題となり得るフルテーブルスキャンや誤ったインデックスの利用など画面上から確認可能です。また、JavaアプリケーションからSQL文が実行されている場合にはスタックトレースも表示されるため、問題となるSQL文を実行しているアプリケーションの箇所を簡単に見つけることができます。

MySQL Enterprise EditionではMySQL Enterprise Monitorの他にも、大規模およびミッションクリティカル環境での運用を支援する各種のツールやサービスが提供されています。

表　MySQL Enterprise Editionで提供されるツールやサービス

ツールやサービス名	説明
MySQL Enterprise Backup	高速なオンラインバックアップ、ポイントインタイムリカバリ
MySQL Enterprise Scalability	Thread Poolプラグインによる性能拡張性の向上
MySQL Enterprise Security	LDAPやWindows Active Directoryでの外部認証
MySQL Enterprise Audit	ユーザ処理の監査、Oracle DBと同じツールで管理可能
MySQL Enterprise Encryption	より強固な非対称暗号化
MySQL Enterprise High Availability	Oracle Clusterware, DRBDなど用途に応じた動作検証済み構成
Oracle Premier Support	24×7, インシデント無制限、コンサルティングサポート

次回は

次回はMySQL 5.5で実装され、MySQL 5.6で本格的に利用可能となったパフォーマンススキーマについてご紹介いたします。