RDS for MySQLのスロークエリーログをAWS LambdaでElasticsearchに取り込む

はじめに

藤本です。

Elasticseachに取り込むネタが続いています。 前回のELBのアクセスログをAWS LambdaでElasticsearchに取り込むに続いて、今回はRDS for MySQLのスロークエリログをElasticsearchに取り込む実装をご紹介します。

概要

MySQL Serverはスロークエリーログにより指定した秒数を超えるクエリを記録することができます。スロークエリログはパフォーマンス劣化の解析、クエリの適切性、DBのマシンパワーの適切性のチェックに役立ちます。RDS for MySQLも例外ではありません。パラメータグループを設定することにより、スロークエリログを有効にすることができます。それに加えてRDS for MySQLの場合、AWSのAPIによりデータベースに接続せずともスロークエリログを取得することができます。

スロークエリログフォーマット

# Time: ＜ログ出力時間＞
# User@Host: ＜ユーザー名＞ @ ＜接続元IP＞ Id: ＜接続元識別子＞
# Query_time: ＜処理実行時間＞ Lock_time: ＜ロック発生時間＞ Rows_sent: ＜送信された件数＞ Rows_examined: ＜処理された件数＞
＜SQL文＞

スロークエリログサンプル

# Time: 151120 23:58:31
# User@Host: root[root] @ [10.232.10.32] Id: 10932
# Query_time: 36.467637 Lock_time: 0.000213 Rows_sent: 0 Rows_examined: 0
use db;
SET timestamp=1448063911;
LOAD DATA LOW_PRIORITY LOCAL INFILE '/tmp/test.csv'
INTO TABLE items

今回はAWS Lambdaのスケジュールイベントにより一時間に一回、RDS for MySQLからスロークエリログをダウンロードし、加工し、Amazon Elasticsearch Serviceに取り込みます。

設定は以下のような流れとなります。

• 事前準備
• RDS for MySQLのスロークエリログ出力設定
• Amazon Elasticsearch Serverce作成
• Amazon Elasticsearch Service設定
• Index Template作成
• AWS Lambda Funcion作成
• Lambda Function作成
• Event sources作成
• 動作確認
• ログ取り込み
• Kibanaによる可視化

環境

やってみた

事前準備

1. RDS for MySQLのスロークエリログ出力設定

DBパラメータグループの「slow_query_log」を「1」に設定してください。

2. Amazon Elasticsearch Serverce作成

Amazon Elasticsearch Serviceのドメイン作成は[新機能]Amazon Elasticsearch Serviceがリリースされました！を参照。

Amazon Elasticsearch Service設定

3. Index Template作成

RDS for MySQLのスロークエリログ用にIndex Templateを作成します。今回、MySQLのスロークエリログから取得したい情報を基にMappingのTemplateを作成します。

• template : スロークエリログが投入されるインデックスの命名規則を指定します。今回はslowquerylog-YYYYMMDDという名前でINDEXを作成します。
• mappings : タイプ名を指定します。今回はRDSのIDでタイプを作成します。
• timestamp : スロークエリログが出力された日時。date型。
• user : DBの接続したユーザー名。
• client : DBへ接続したクライアントのIPアドレス。
• client_id : DBへ接続したクライアントの識別子。
• query_time : クエリの実行時間
• lock_time : クエリによりロックが発生した時間
• rows_sent : クエリで送信された行数
• rows_examined : クエリの処理対象となった行数
• sql : SQL文

curl -XPUT 'http://search-******************.ap-northeast-1.es.amazonaws.com/_template/slowquerylog' -d '{
"template": "slowquerylog-*",
"mappings": {
"": {
"properties": {
"timestamp": { "type": "date" },
"user": { "type": "string", "index": "not_analyzed" },
"client": { "type": "ip" },
"client_id": { "type": "integer" },
"query_time": { "type": "float" },
"lock_time": { "type": "float" },
"rows_examined": { "type": "integer" },
"rows_sent": { "type": "integer" },
"sql": { "type": "string", "index": "not_analyzed" }
}
}
}
}'

AWS Lambda Funcion作成

4. Lambda Function作成

PythonのLambda Functionを作成します。今回は標準ライブラリのみで実装していますので、ソースコードをベタ貼りすれば大丈夫です。ソースコードはGistにアップロードしました。実行時間をデフォルトの3秒から伸ばしてください。今回は100件以下のログ数でしたので2秒で完了しています。RoleはRDSへのリード権限、Amazon Elasticsearch ServiceのESHttpPost権限を割り当てたIAM Roleを設定してください。Memoryはスロークエリログファイルのサイズが大きい場合は合わせて増やしましょう。

現在は4つのパラメータを設定可能です。コメントアウトで括られた変数に設定してください。

• ES_HOST
• 接続先となるElasticsearchのホスト名、IPアドレス
• INDEX_PREFIX
• ログを格納するINDEX名のプレフィックス
• INDEX名は「INDEX_PREFIX-YYYYMMDD」の形式
• RDS_ID
• スロークエリログを取得したいRDSのIDを指定
• RDSからのログ取得、Elasticsearchのタイプに利用
• TIMEZONE
• RDSのデフォルトTIMEZONEはUTC
• Elasticsearchでのログデータのタイムゾーンを変換したい場合、変換したいタイムゾーンを指定（ex.JSTの場合、Asia/Tokyo）
• 変換しない場合はブランク
• 各種ログとタイムゾーンを合わせることをオヌヌメ

5. Event sources作成

Lambda Functionに対してEvent sourcesを作成します。RDS for MySQLは一時間おきにログファイルをローテーションします。合わせて、Event sourcesの種類は一時間に一回のスケジューリング実行とします。過去のログファイルを眺めていると、0分ジャストではなく、次の時間の5分のログも確認出来たので、スケジューリング実行もずらして実行することが良さそうです。

ログ取得にMarkerやNumberOfLinesを利用すれば、5分おきの取り込みが可能となるのかもしれません。

動作確認

ログ取り込み

今回、弊社の某WEBのバックエンドで動作するMySQLのログを収集しました。

。。。3時間ほど放置しました。 Elasticsearchの状況を確認してみましょう。

データの内容は後で確認するとして、データ数が172件、スキーマも適用されています。

Kibanaによる可視化

Kibanaでグラフ化してみましょう。

時間単位のスロークエリログに残されたQuery_Timeを見てみましょう。

定期的に7分近くのクエリが発行されていることが分かります。 マウスポインタを点に合わせるとクエリにかかった時間、そのSQLが表示されます。（画像を載せたかったのですが、キャプチャする際に消えてしまう。。）

まとめ

いかがでしょうか？ ELBの時と処理フローは変わりません。 イベントによる発火はAWS Lambdaが、ETLのExtractは各種AWSサービスのAPIが、LoadはElasticsearchのAPIが用意されています。実装者はログファイルがどのようなフォーマットであるか理解し、どのようにして必要なデータを取り出し、JSON形式に変換することができるかというETL処理のTransformに注力すればいいだけです。 集中したいところに集中できる！まさにこれに尽きますね。