OCIのLogging Analyticsでスロークエリログを分析する

2022年7月8日

この記事は最終更新から2年以上経過しています。内容が古くなっている可能性があります。

はじめに

OCIの Logging Analytics は、MySQLの各種ログの取り込みに対応しています。

Oracle Cloud Infrastructureドキュメント / Oracle定義ソースでは、以下のソース定義があることが確認できます。

MySQLデータベース監査XMLログ
MySQLエラー・ログ
データベースに格納されたMySQL一般ログ・ソース
MySQL一般クエリー・ログ
MySQLスロー・クエリー・ログ

また、こちらのリファレンスからはパーサーを確認できませんが、以下のようにCLIを実行すると、パーサーが用意されていることが分かります。

# oci log-analytics parser list-parsers --namespace-name {ネームスペース} --query 'data.items[].["display-name","name"]' --output table
+-----------------------------------------------------------------------+------------------------------------------------------------+
| Column1                                                               | Column2                                                    |
+-----------------------------------------------------------------------+------------------------------------------------------------+
・・・略)
| MySQL Audit XML Log Format                                            | db_mysqlxmllog_logtype                                     |
| MySQL Error Log Body Format                                           | db_mysql_errorlog_body_logtype                             |
| MySQL Error Log Header Format                                         | db_mysql_errorlog_header_logtype                           |
| MySQL General Query Log Format                                        | db_mysql_generalquerylog_logtype                           |
| MySQL Slow Query Log Format                                           | db_mysql_slowquerylog_logtype                              |
・・・略)

# oci log-analytics parser list-parsers --namespace-name {ネームスペース} --query 'data.items[].["display-name","name"]' --output table

+-----------------------------------------------------------------------+------------------------------------------------------------+

| Column1 | Column2 |

+-----------------------------------------------------------------------+------------------------------------------------------------+

・・・略)

| MySQL Audit XML Log Format | db_mysqlxmllog_logtype |

| MySQL Error Log Body Format | db_mysql_errorlog_body_logtype |

| MySQL Error Log Header Format | db_mysql_errorlog_header_logtype |

| MySQL General Query Log Format | db_mysql_generalquerylog_logtype |

| MySQL Slow Query Log Format | db_mysql_slowquerylog_logtype |

・・・略)

そこで、今回は、Logging Analytics にスロークエリログを取り込んで確認してみたいと思います。

前提

Logging Analytics を使用されたことがない方は、以下のリファレンスから必要なポリシー等をご確認下さい。

Oracle Cloud Infrastructureドキュメント / ログ・アナリティクスとそのリソースへのアクセスの有効化

MySQLのスロークエリログがテーブル出力されている場合は、標準で用意されているパーサーが使用できませんのでファイル出力されたものを対象としてします。

ロググループの作成

まずはロググループを作成します。
コンソール画面のグローバルメニューから「監視および管理」を押下し、「ログ・アナリティクス」下の「管理」を押下します。

表示された管理画面の「ログ・グループの作成」リンクを押下します。

ロググループの名前と説明を設定し、「作成」ボタンを押下します。

以上で、ロググループの作成は完了となります。

ファイルのアップロード

次に、MySQLのスロークエリログをアップロードします。

（今回は、取得済みのスロークエリログをアップロードしていますが、ロギングサービスのカスタム・ログとサービス・コネクタ・ハブを使用することで、Computeインスタンス等にインストールしたMySQLのスロークエリログを直接分析することも可能です。）

管理画面の「アップロード・ファイル」リンクを押下します。

ファイルの選択画面が表示されますので、以下のように設定しました。

項目	設定値
アップロード名	SlowQueryLog
ログ・グループ・コンパートメント	blog
ログ・グループ	前手順で作成したロググループ
ファイルの選択	MySQL8.0.27で出力されたスロークエリログを指定 zip, tar, tar.gz, tgzに対応しているようです

以上を設定したら、「次」ボタンを押下します。

「プロパティの設定」ボタンを押下します。

「ソース」で「MySQL Slow Query Logs」を選択し、「変更の保存」ボタンを押下します。

プロパティの設定画面で、「次」ボタンを押下します。

内容を確認して、「アップロード」ボタンを押下します。

「送信ステータス」が「成功」になれば、「閉じる」ボタンを押下します。

ファイルのアップロードは以上となります。

スロークエリログの確認①

続いて、スロークエリログの確認をします。
アップロードファイルの詳細画面にある「ログ・エクスプローラで表示」ボタンを押下します。

しばらくすると、以下のように時間帯ごとに何個のスロークエリが出力されたかが、グラフで確認できます。

グラフ内をドラッグすると、ドラッグした時間帯に絞り込むことが可能となり、最小で1分単位での確認が可能でした。
また、選択した時間帯に出力されたスロークエリの情報が、グラフの下に一覧表示されるため、実行されているクエリについても確認することができます。

非常に簡単に時間帯ごとに出力されたスロークエリの数を確認することができましたが、スロークエリに含まれる以下のような情報でもフィルタリングできるものと思いきや、フィルタリングすることができませんでした。

# Query_time: 13.265365  Lock_time: 0.000872 Rows_sent: 0  Rows_examined: 4638074

1	# Query_time: 13.265365 Lock_time: 0.000872 Rows_sent: 0 Rows_examined: 4638074

フィルタリングできない原因について調査したところ、原因は、標準で用意されている「MySQL Slow Query Log Format」に設定されている「パーサー正規表現」が、今回取り込んだ MySQL8.0.27 のスロークエリログのフォーマットに沿っていないことが原因のようです。

ということで、今回取り込んだスロークエリログでも、ちゃんと解析できるように新しくパーサーを用意してみます。

パーサーの作成

Logging Analytics の管理画面から「パーサー」にある合計数のリンクを押下します。

表示された画面から右上の検索窓に「MySQL」を入力しフィルタリングします。
表示された「MySQL Slow Query Log Format」リンクをクリックします。

詳細画面の「複製」ボタンを押下します。

パーサーの編集画面では、以下のように設定しました。

項目	設定値
名前	Custom of MySQL Slow Query Log Format
説明	MySQL8.0.27で出力されたスロークエリログのパーサー
サンプルのログ・コンテンツ	スロークエリログの内容を数件設定
パーサー正規表現	下記参照

# Time:\s*{TIMEDATE}(?:[\n\s]*)?(?:\#\sUser@Host:\s([^\[]+)\[([^\]]+)\]\s@\s(\S+)\s\[(.*)\]\s+Id:\s+(\d+)[\n\s]*)(?:\#\sQuery_time:\s([\d.]+)\s+Lock_time:\s([\d.]+)\s+Rows_sent:\s(\d+)\s+Rows_examined:\s(\d+)[\n\s]*)(?:SET\stimestamp=([\d.]+);[\n\s]*)?(.*)

1	# Time:\s{TIMEDATE}(?:[\n\s])?(?:\#\sUser@Host:\s([^\[]+)\[([^\]]+)\]\s@\s(\S+)\s\[(.)\]\s+Id:\s+(\d+)[\n\s])(?:\#\sQuery_time:\s([\d.]+)\s+Lock_time:\s([\d.]+)\s+Rows_sent:\s(\d+)\s+Rows_examined:\s(\d+)[\n\s])(?:SET\stimestamp=([\d.]+);[\n\s])?(.*)

※「パーサー正規表現」は、取り込むスロークエリログのフォーマットに合わせて変更する必要があります。

項目	設定値
ログ・レコードのスパン	デフォルト表示のまま変更なし

項目	設定値
フィールド名	上から順に以下を設定・User Name ・Database Name ・Host Name（client）・Host IP Address（client）・Thread ID ・Query Duration ・Query Lock Duration ・Rows Sent ・Rows Examined ・POSIX Timestamp（seconds since epoch）・Command

以上を設定したら、「パーサーの作成」ボタンを押下します。

ソースの変更

Logging Analytics の管理画面から「ソース」にある合計数のリンクを押下します。

表示された画面から右上の検索窓に「MySQL」を入力しフィルタリングします。
表示された「MySQL Slow Query Logs」リンクをクリックします。

詳細画面で「編集」ボタンを押下します。

編集画面で「パーサー」を「デフォルト」から「カスタム」に変更し、先程作成したパーサーの「Custom of MySQL Slow Query Log Format」を選択します。
パーサー変更後に「変更の保存」ボタンを押下します。

以上で、ソースの変更は完了です。
既存のソースを残したい場合は、新規にソースを作成して、パーサーを設定する方法でもよいかと思います。

スロークエリログの確認②

パーサーを変更したので、もう一度同じスロークエリログファイルを確認してみます。
先程アップロードしたファイルを削除し、再度、同じ要領でスロークエリログファイルをアップロードしました。

ログ・エクスプローラで表示すると、先程と同じように時間帯ごとのスロークエリの出力数を確認できます。

それでは、先程、抽出できなかったクエリの実行時間となる Query Duration でフィルタリングしてみます。
抽出条件を以下のように設定し、5分（300秒）以上実行時間を要したクエリを抽出します。

'Query Duration' >= 300 | timestats count as logrecords by 'Log Source' | sort -logrecords

1	'Query Duration' >= 300 \| timestats count as logrecords by 'Log Source' \| sort -logrecords

時間帯ごとに5分以上実行時間を要したクエリが抽出されるようになりました。
該当したクエリの情報もグラフの下の一覧に表示されます。

今度は、接続元のサーバがバッチサーバである batch01 の情報だけを抽出します。
抽出条件を以下のように設定します。

'Host Name (Client)' = batch01 | timestats count as logrecords by 'Log Source' | sort -logrecords

1	'Host Name (Client)' = batch01 \| timestats count as logrecords by 'Log Source' \| sort -logrecords

3時から6時の間のみに出力されていることが分かります。
グラフの表示期間を狭めることで、最小1分単位での出力を確認することができます。

以下のように接続元のサーバごとに出力したりする事も可能です。

また、パーサーを変更したことにより、他にも以下のようなスロークエリに出力される情報でフィルタリングすることもできます。

Query Lock Duration（ロックを取得した時間）
Rows Sent（クライアントに送信された行数）
Rows Examined（MySQLが内部で走査した行数）

例えば、MySQLが内部で走査した行数が1億行以上のクエリを抽出したい場合は、以下のように設定します。

'Rows Examined' >= 100000000 | timestats count as logrecords by 'Log Source' | sort -logrecords

1	'Rows Examined' >= 100000000 \| timestats count as logrecords by 'Log Source' \| sort -logrecords

まとめ

パーサーの正規表現を編集することで、MySQLのバージョンや設定によってスロークエリログのフォーマットが変更された場合でも、柔軟に Logging Analytics に取り込むことができることが理解できたかと思います。
また、パーサーの正規表現さえ準備できれば、スロークエリログをアップロードするだけで分析できる点も、非常にお手軽に活用できるサービスであるように思います。
（現時点では、Logging Analytics のストレージは、10GB まで無料となっています。）

Oracle Japan > クラウド > クラウドの価格表 / Observability and Management Platform – Logging Analytics*

よかったらシェアしてね！

URLをコピーしました！