MySQLクエリログの設定

SQL Diagnostic Managerは、MySQLサーバーからGeneral（一般）クエリログとSlow（スロー）クエリログ（の全部または一部）を取り出し、それらを解析します。ここでは、SQL Diagnostic Managerでログ解析ができるように接続の詳細情報をセットアップする方法を解説します。一般クエリログとスロークエリログは、その詳細情報を個別にセットアップします。代わりにスロークエリログを有効にするには（インデックスを使用せずにクエリをログ）、SUPER権限が必要です。ロギングの有効化の方法と構成方法については、MySQLドキュメントを参照してください。MySQLサーバーログは、サーバーマシンのファイルまたはMySQLデータベースそのもののテーブルに書き込むことができます。

MySQLサーバー（バージョン5.0以降）には、インデックスを使用しないクエリを（スローログに）ログするオプションがあります。このようなクエリは、関連テーブルに含まれるレコード数が数百から数千の場合、スローである必要はありません。しかし、これらは‘潜在的にスロー’であり、それらのクエリがテーブルをアクセスすると、際限なくサイズが大きくなるので、識別しておく必要があります。この機能は、ここからも有効、無効を切り替えることができます（SQL Diagnostic ManagerはMySQLに適切なステートメントSETを送信します）。

注意

注意：スロークエリログに記録されるのは、DMLクエリとDDLクエリだけです。

ファイルに書き込むログ

最初に、サーバーログをファイルとしてサーバーマシンに保存する状況を想定します。これはよくある状況であり、バージョン5.1 より前のMySQLサーバーでは、この方法しかありませんでした。初めてこのオプションでサーバーを構成するとき、‘Fetch query log details（クエリログ詳細情報を取得）’ボタンをクリックします。有効なログがどれであって、ロギングがどのように設定されているかをMySQLサーバーは認識しています（サーバー変数に保存）。そのパスを確認するためには、‘Test Path（パスをテスト）’をクリックします。SQL Diagnostic Managerは、接続し、そのファイルの存在を確認します（ファイルの内容は確認しません）。

SQL Diagnostic ManagerとMySQLが同じコンピューターで実行している場合、ログファイルは、ローカルファイルシステムからアクセスできます。SQL Diagnostic ManagerとMySQLが別のコンピューターで実行している場合は、SFTPを使用してアクセスします。この際、ログがあるマシンのファイルとパス構文を使用してください。

ネットワーク経由で共有ドライブから、またはネットワーク対応ファイルシステム（Linux上のNFSなど）からログファイルにアクセスできる場合、SQL Diagnostic Managerからはそれらのファイルにローカルファイルのようにアクセスできます。この場合、それ以外のSSH/SFTP設定は不要です。ファイル転送はオペレーティングシステムが透過的に処理します。

‘via SFTP（SFTPで）’オプションを選択すると、‘SSH server details settings（SSHサーバー詳細設定）’に定義されたSSHサーバーの詳細情報に従って、リモートシステムからファイルを読み取ります。このとき、SSHユーザーには、ログファイルのアクセス権限が必要なので注意してください！

MySQLサーバーのバージョンが5.1.6より後の場合、ログアナライザーでアクセスできるフィールドは、すべて編集できます。つまり、ユーザーが設定を変更し、‘Save’ボタンをクリックして保存する場合、ポップアップが表示され、そこでユーザーは対応するMySQLサーバーに新しい値を設定できます。

Monyog（SQL Diagnostic Manager）サービスは、デフォルトで、ローカルシステムアカウントで実行します。Mapped Network Drive（マップしたネットワークドライブ）にSlow（スロー）クエリログまたはGeneral（一般）クエリログがある場合、SQL Diagnostic Managerはそれにアクセスできません。それらにアクセスできるようにするためには、UNC表記を使用する必要があります。詳細については、FAQ 31を参照してください。

MySQLテーブルに書き込んだログ

このオプションは、バージョン5.1以降のMySQLがサポートしています。また、SQL Diagnostic Managerは、いつこのオプションを使用できるかもサポートしています。ここで、‘Fetch Log Details From MySQL（MySQL からログ詳細情報を取得）’ボタンをクリックします。このオプションを使用するとき、構成するファイルパスや、検討するSSHの詳細情報はありません。SQL Diagnostic Managerでは、単純なSELECTステートメントを送信してサーバーログを取り出すことができます。そのテーブルに対するSELECT権限が必要なのは、SQL Diagnostic ManagerでMySQLに接続するときに使用するMySQLユーザーのみです。

‘Query Analyzer（クエリアナライザー）’タブで、必要なMySQLサーバーと、解析したいログのタイプ（‘疑似ログ’を含む）を選択します。次に、‘Analyze（解析）’をクリックすると、解析が開始します。しばらくすると、スロークエリログ用のような解析結果が表示されます。

一般クエリログの場合、

スニッファーの場合、

SQL Diagnostic Managerの‘query sniffer（クエリスニッファー）’は、‘疑似サーバーログ’を記録して、それをSQL Diagnostic Manager組み込みデータベースに保存する機能です。‘query sniffer’を有効にすると、SQL Diagnostic Managerは、以下の3種類の方法で‘疑似サーバーログ’を、ユーザーが指定した間隔で追加できます。

• パフォーマンススキーマテーブル（events_statements_summary_by_digest、events_statements_history_long）を使用し、定期的にスナップショットを収集する方法
• クエリSHOW FULL PROCESSLISTをMySQLサーバーに送信する方法
• 1つ以上のクライアントが MySQLサーバーとの接続に使用する、実行中のMySQL-Proxyプログラムのインスタンスに接続する方法

MySQL 5.6.14以降では、クエリ解析にはPerformance schema（パフォーマンススキーマ）、Proxy（プロキシ）、Processlist（プロセスリスト）を使用できます。MySQLで5.6.14より前のバージョンを使用している場合、SQL Diagnostic Managerで使用できるのは、ProxyかProcesslistのみです。

MySQLのPerformance Schemaには、サーバーで実行されるクエリと他の情報が含まれます。

• number of rows sent and examined（送信して調査する行数）
• number of temporary tables created on disk（ディスクに作成されるテンポラリテーブル）
• number of temporary tables created on memory（メモリに作成されるテンポラリテーブル数）
• number of joins performed and the type of join（実行されるジョイン数とジョインのタイプ）
• whether sorting happened and the type of sort（ソートが発生したかどうか、ソートのタイプ）
• whether index used（インデックスを使用したかどうか）
• whether good index used（適切なインデックスを使用したかどうか）

SQL Diagnostic Managerは、setup_consumersテーブルのstatements_digestに従って、パフォーマンススキーマステートメントダイジェストテーブル（events_statements_summary_by_digest）で、上記の情報を収集します。デフォルトで、この機能は有効です。無効の場合、以下のステートメントを実行して有効にできます。

UPDATE performance_schema.setup_consumers
SET enabled = 'YES'
WHERE name = 'statements_digest';

サンプルクエリは、events_statements_history_longテーブルにあり、有効にする必要があります。setup_consumersテーブルのevents_statements_history_longに従って実行されます。デフォルトで、これは有効になっていないので、以下のステートメントを実行して有効にしてください。

UPDATE performance_schema.setup_consumers 
SET enabled = 'YES' 
WHERE name = 'events_statements_history_long';

performance_schema.events_statements_summary_by_digestテーブルのサイズは、performance_schema_digests_sizeグローバル変数によって異なります。デフォルトで、このサイズは5000行に設定されます。この上限に達すると、クエリは失われます。SQL Diagnostic Managerには、パフォーマンススキーマダイジェストテーブルがperformance_schema_digests_sizeの80%に達すると切り捨てるオプションがあります。

Performance schemaベースのスニッファーには、Queries with errorsやQueries with warnings、Queries with missing indexes、Queries with poor indexesのような様々なフィルターがあります。

注意

performance_schemaは、1024文字より後のクエリを切り捨て、常にワイルドカードとリテラルに置き換えます（したがって、P_S にはサマリ／集計のみが保存されます）。そのため、このオプションでは置換リテラルがリストにないクエリというものはありません。最後に、すべてのユーザーに対してテーブルは 1つしか割り当てられないので、このオプションを使用するときは、他のツール（またはユーザー）が書き込み（削除や切り捨ても含む）がevents_statements_summary_by_digestテーブルやevents_statements_history_longテーブルに書き込みをしていないことを確認してください（これはテンポラリテーブル、実装ビュー、または、ユーザーの類似プライベートテーブルではありません）。これ自体は、P_Sのテーブルの設計上の制約であり、SQL Diagnostic Managerの問題ではありません。

MySQLで5.6.14より前のバージョンを使用している場合、SQL Diagnostic Managerで使用できるのは、Proxy（プロキシ）かProcesslist（プロセスリスト）のみです。Proxyインスタンスの構成はこれより少し複雑ですが、PROXYベースのスニッファーには、PROCESSLISTベースのものに比べて、以下のようなメリットがあります。

1. Proxy（プロキシ）が処理するすべてのクエリは、PROXYオプションの使用時にSQL Diagnostic Managerの‘スニッファー’が記録します。PROCESSLISTオプションを使用すると、2つのSHOW FULL PROCESSLISTクエリ間で高速クエリが完全に実行されることがあり、記録されません。
2. 特定のクライアント／アプリケーションのみからのクエリの解析を選択することができます。この場合、単純に、ある時点で注目するクライアントのみをProxy（プロキシ）で接続することができます。
3. PROXYオプションの使用時には、どこにPROXYを設定するかによって、スニッファーでマシンに発生した負荷の一部を、以下のマシン間で、展開シナリオに最適な形で分散させることができます（空きリソースが最も多くなるシナリオ）: MySQLマシン、SQL Diagnostic Managerマシン（同じマシンでなければ）、まったく別のマシン。MySQLが実行しているマシンで、Proxy（プロキシ）が実行していなければ、スニッファーによる新たな負荷は生じません。

さらに、さらに多くのSQL Diagnostic Managerインスタンスが同じPROXYを使用するときは、最初のSQL Diagnostic ManagerインスタンスでProxy Sniffing（プロキシスニッフィング）が有効になっている場合に、収集した同じデータを使用します。SQL Diagnostic Managerスニッファーを処理するため、MySQL Proxy（プロキシ）インスタンスは、 ‘MONyog.LUA’（LUA はスクリプティング／プログラミング言語）というLUAスクリプト名を引数として開始します。これは、SQL Diagnostic Managerで配布されます。このスクリプトは、WebyogのウェブサイトからダウンロードしたSQL Diagnostic Managerプログラムパッケージをインストールするか（WindowsとLinux RPM）、アンパックすると（Linux .tar.gz）、SQL Diagnostic Managerプログラムフォルダーに見つかります。ただし、MySQL Proxy（プロキシ）プログラムは、MySQLのウェブサイトからダウンロードしてください（これはライセンス上の理由から組み込むことができなかったため）。SQL Diagnostic Managerは、WindowsとMacでは、0.7xバージョン以外（これら特定のビルドが原因）の、0.61バージョンから0.81バージョン（最新バージョン）のProxy（プロキシ）で動作します。Proxy（プロキシ）の詳細については、ここをクリックしてください。

SQL Diagnostic Managerで使用するためにProxy（プロキシ）インスタンスを開始するには、以下のコマンドを使用します：

• 古いバージョンの場合:

  Proxy installation folder>mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 --proxy-address=192.168.y.y:4045 --proxy-lua-script=MONyog.lua
  

• v0.81 以降:

  Proxy installation folder>mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 --proxy-address=192.168.y.y:4045 --admin-username=root --admin-password=root --admin-lua-script=MONyog.lua --proxy-lua-script=MONyog.lua
  

（‘MONyog.LUA’はPROXYバイナリーがあるフォルダーにコピーしておくものとします）。さらに、ポートを指定しないと、PROXYはポート4040で監視します。以上で、1つ以上のクライアント／アプリケーションからProxyに接続できます。Proxy（プロキシ）はMySQLにクエリを送信し、結果がクライアントに返ります。ただし、SQL Diagnostic ManagerスニッファーのLUAスクリプトで開始すると、SQL Diagnostic Managerがスニッファー‘疑似ログ’の追加に使用する情報もSQL Diagnostic Managerに送信します。

この‘疑似ログ’を記録すると（説明した2つの方法：PROCESSLISTベースまたはPROXYベースのいずれかで）、SQL Diagnostic Managerログ解析機能は‘リアルログ’と同様に、‘疑似ログ’で動作します。‘疑似ログ’に記録されたデータは、ユーザーが設定した‘data retention timeframe（データ保持時間フレーム）’オプションに従って自動的にパージされます。

さらに、フィルタリングオプションもいくつか用意しました。ちなみに、このフィルタリングは、SQL Diagnostic Managerデータベースのソート前に実行されます。そのため、スニッファーデータベースが制御できなくなるのを防ぐことができます！フィルタリングオプションは次のとおりです：

• User and host（ユーザーとホスト）：ユーザーとホストの両方またはいずれかの特定の組み合わせによってのみ実行されたクエリの保存を選択できます。
• Minimum time taken（最短所要時間）：SQL Diagnostic Managerに戻るPROCESSLISTごとに、指定した最短実行時間を超えて実行された場合のみクエリは組み込みデータベースに記録されます。さらに、記録済みのクエリと同じ構造で詳細情報（プロセスIDなど）を持つクエリに遭遇すると、組み込みデータベースはUPDATEDされ、そのステートメントは1度だけ記録されます。なお、この設定は、サンプル間隔よりも少し長くしてください（また、接続遅延時間も考慮してください）。設定時間が短いと、何の意味もありません！
• Queries starting with（クエリの開始ストリング）：ストリングとそのストリングで始まるクエリのみを入力してください。例：SELECT *、UPDATE Customer_Base。

さらに、PROCESSLIST Snifferには、ユーザーが指定した時間より長い時間実行しているクエリを通知するかキルして、実行時間が長いクエリを監視できるオプション‘Long Running Query Options（長時間実行しているクエリ）’があります。さらにクエリが無視されるユーザーも指定することができます（このようなユーザーによるクエリは、SQL Diagnostic Managerがキルすることはなく、ユーザーが指定したアラート／キル設定時間より長い時間実行していてもアラートは発生しません）。

monitor only locked queries（ロックされているクエリのみ監視）をクリックすると、ロックされているクエリのみを監視します。

もちろん、クエリスニッファーを完全な‘一般クエリログ’にすることはできません。非常に高速なステートメントは、生成されたPROCESSLISTとPROCESSLISTとの間で終わることもあれば終わらないこともあるため、記録されないことがあります。‘疑似ログ’の後続のデータ収集の時間間隔は、MySQLサーバーとの接続によって異なります。

同じクエリは1回だけリストに計上されます。このクエリが何回実行されたかは、‘count（カウント）’列でわかります。

General Query Log（一般クエリ ログ）固有の問題は数個あります：

1. マルチラインクエリでは、ステートメントの先頭行だけが記録されます。理由は、ログには、ステートメントDELIMITERが記録されないので、マルチラインステートメントがどこで終了するかを判定できないためです。SQL Diagnostic Managerが保存する行は1行だけなので、オプション‘Show full（全体表示）’でも複数行表示されることはありません。FAQ 23を参照してください。
2. 特定のクエリを実行したユーザーが誰かは、いつも判定できるわけではありません。判定できない場合、Query Analyzer（クエリアナライザー）の出力の‘user（ユーザー）’列には何も表示されません。これは、SQL Diagnostic Managerのバグではなく、一般クエリログ自体の制約です。

表示内容は列ヘッダーをクリックしてソートすることができます。ステートメントによるグループ作成／カウント、ソートでは大文字と小文字を区別します。

スローサーバーログ（一般クエリログやスニッファーデータではない）を解析するとき、クエリをクリックすると、以下のような詳細情報が表示されます：

Slow_logテーブルロギング、Processlist（プロセスリスト）ベースのスニッファー、パフォーマンススキーマベースのスニッファーのQuery analyzer（クエリアナライザー）では、Explain plan（プランを説明）を使用できます。

フィルター設定

ここには、オプション‘replace literals from the query（クエリのリテラルを置換）’があります。このオプションの目的は、‘ほとんど同じ’のクエリ同士の小さい違いを取り除くことです。現在、‘引用符で囲んだストリング’と数字は、すべてダミーストリング‘?’に置換されます。フィルタリング設定が保存されるのは、その特定のセッションの間だけで、永久保存ではありません。

例えば、

SELECT * FROM customer_master 
WHERE cust_id = 23 AND address = ’r;#23 fleet street’;

は、以下のようになります。

SELECT * FROM customer_master 
WHERE cust_id = ? AND address = ?;

読み取り制限‘All’を選択すると、解析する全ファイルがその設定の対象になりますが、オプションが‘Last’の場合、ログファイル全体から、前回の指定したチャンクを、KB、MB、または、バイト単位で読み取ります。さらに、解析する時間フレームと、SQL Diagnostic Managerに転送する‘ログチャンク’のサイズ（ファイルベースのログでは単位KB、MB、バイト、テーブルベースのログでは単位行数）も定義できます。リストで‘All’を選択すると、時間フレームは処理の対象外になりますが、指定したサイズ／チャンク内のすべてのクエリが表示されます。さらに、解析に影響があるのは、2つの設定のうちの‘小さい’方です。‘スニッファー疑似ログ’を解析する場合、SQL Diagnostic Managerデータベースに保存されている‘疑似ログ’全体が処理対象になるため、定義する‘チャンクサイズ’はありません。選択した‘ログ チャンク’には、選択期間のステートメントが必要です。選択期間のステートメントがないと、SQL Diagnostic Managerは、もちろん利用できる最初のログレコードからデータを表示します。

Include User and Host Information（ユーザー情報とホスト情報をインクｰ度）：このオプションを選択すると、特定のクエリの‘user and host（ユーザーとホスト）’が表示され、クエリアナライザーテーブルは、‘user@host’と‘query’をベースにグループ化されます。

注意

SQL Diagnostic Managerがマシンにインストール済みで、Sniffer（スニッファー）が有効な場合、‘user（ユーザー）’情報がQuery Analyzer（クエリアナライザー）テーブルに表示されます。これは、古いSQL Diagnostic Managerは、‘host（ホスト）’情報を‘sniffer.data’テーブルに保存しないからです。なお、このオプションをMySQL Proxy（プロキシ）はサポートしていないので注意してください。General（一般）クエリログでは、“connect（接続）”ストリングを指定チャンクに組み込まないと、‘user@host’情報は表示されず、空のスペースが表示されるだけです。

Export As CSV（CSV としてエクスポート）

コンマ“,”を小数点として使用するLOCALEにローカライズしたWindowsプログラムでは、フィールドセパレーターがセミコロン“;”として必要なため、フィールド区切り記号を定義するオプションを用意しました。このプログラムには、Microsoft Officeプログラム（ExcelとAccess）と、そのようなローカライズされたWindowsのMicrosoft text-ODBCドライバーが含まれます。Linuxでは、状況はさらに統一が取れていませんが、ローカライズされたOpenOffice Calc（スプレッドシート）では、フィールドセパレーターとしてセミコロン“;”が必要です。

注意：Monyogをご利用の方

この機能は、Monyog EnterpriseとUltimateで利用できます。