RDS for MySQL のスロークエリログを可視化して、パフォーマンスをモニタリングする

藤本真司

2018.01.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

ども、藤本です。

2年前に RDS for MySQL のスロークエリログを Elasticsearch に取り込んで、Kibana で可視化するブログをエントリしました。

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

ちょいちょい使ってはいたのですが、RDS のログの取り扱いの難しさで完全な取り込みができていない状況でした。先日、RDS for MySQL/MariaDB にてログを CloudWatch Logs へ出力できるようになりました。

RDSのMySQL/MariaDBでログをCloudWatch Logsへ出力可能になりました

これを使うことで以前の実装よりも簡単で、かつデータの完全性を高められそうだったのでやってみました。

概要

RDS for MySQL/MariaDB のスロークエリログは簡単に出力、閲覧できますが、監視、モニタリングすることが難しいです。明らかな障害発生時に調査する上では問題ないですが、Webアプリケーションのレスポンス性能低下や障害の予兆を事前に対応するにはあのログを閲覧するのは辛いです。そこで可視化することでレスポンス性能低下や障害の予兆をいち早く気づくことができるかもしれません。そこでログの可視化といえば、Kibana ですよね。Kibana でスロークエリログを可視化して、より安定したシステム運用を目指しましょう。

構成図

今回の構成は以下の通りとなります。

成果物

今回の構成を作ることでこんなダッシュボードを作ることができます。

あくまでサンプルです。システム要件によって必要なグラフ、ダッシュボードは変わります。このダッシュボードでは以下の 4つのグラフを表示しています。

スロークエリログの件数（上段左）一定時間内のスロークエリログ出力数。この数値が多くなっているとシステムへのアクセス増、DB の負荷増、などを疑うことができます。
SQLサマリ（上段右） SQL 毎の最も遅い処理時間（query_time）、実行件数、平均処理時間。特定の SQL が遅いのか、平均して遅いクエリなのか、単発で遅いクエリなのかを切り分けることができます。
時間単位の件数（中段）細かい時間単位内のスロークエリログ出力数。特定の時間に負荷増、などを疑うことができます。
時間単位の高レイテンシ SQL（下段）細かい時間単位で指定したしきい値を超えた SQL とその処理時間（上位◯件）。上の件数と照らし合わせることでたまたま重めの処理で件数が増えたのか、システム的に異常が発生しているのか切り分けることができます。

立ち上げただけの RDS なのであまり魅力を伝えられない笑

可視化してみる

上の構成図を作っていきましょう。以下の順番で設定します。

RDS for MySQL のログを CloudWatch Logs へエクスポート
Amazon Elasticsearch Service へのログパース設定
CloudWatch Logs の Elasticsearch への出力
Lambda の修正（ログパース機能を使うように）

RDS for MySQL のログを CloudWatch Logs へエクスポート

AWS CLI でスロークエリログのみ CloudWatch Logs へのエクスポートを有効化します。

$ aws rds modify-db-instance --db-instance-identifier ${DB_INSTANCE_NAME} --cloudwatch-logs-export-configuration EnableLogTypes=slowquery

Amazon Elasticsearch Service へのログパース設定

Amazon Elasticsearch Service の Ingest Node を使ってログメッセージから、処理実行時間や SQL といった意味のある数値、文字列を取り出します。curl コマンドで Ingest Pipeline を設定します。

$ curl -XPUT "http://${ES_DOMAIN}/_ingest/pipeline/slowquerylog" -H 'Content-Type: application/json' -d'{
"processors" : [
{
"gsub": {
"field": "@message",
"pattern": "\n",
"replacement": ""
}
},
{
"grok" : {
"field" : "@message",
"patterns" : ["# User@Host: %{WORD:user}\\[%{WORD}\\] @ %{WORD:host} \\[%{IP:ip}\\]%{SPACE}Id:%{SPACE}%{NUMBER:id}%{SPACE}# Query_time: %{NUMBER:query_time:float}%{SPACE}Lock_time: %{NUMBER:lock_time:float}%{SPACE}Rows_sent: %{NUMBER:rows_sent:int}%{SPACE}Rows_examined: %{NUMBER:rows_examined:int}%{SPACE}(use %{WORD:database};%{SPACE})?(SET timestamp=%{NUMBER:timestamp:int};%{SPACE})?%{GREEDYDATA:sql}"],
"ignore_failure" : true
}
},
{
"date": {
"field": "timestamp",
"formats": ["UNIX"],
"ignore_failure" : true
}
},
{
"remove": {
"field": ["timestamp", "@message"],
"ignore_failure" : true
}
}
]
}'

CloudWatch Logs の Elasticsearch への出力

CloudWatch Logs に作成されたスロークエリログのロググループを Amazon Elasticsearch Service へ連携します。マネジメントコンソールから簡単に設定できます。また同時にストリーム用の Lambda Function が自動生成されます。

CloudWatch Logs の画面へ遷移します。ロググループ「/aws/rds/instance/mysql/slowquery」を選択し、Actions から Stream to Amazon Elasticsearch Service をクリックします。

連携先の Amazon Elasticsearch Service を選択します。今回は同アカウントにある Amazon Elasticsearch Service ドメインを選択します。また自動生成される Lambda Function に設定する IAM Role を選択します。IAM には Amazon Elasticsearch Service にリクエストを投げられる権限を付与してください。

Log Format を選択します。スロークエリログのフォーマットは定義されていないため、Otherを選択し、パースは Elasticsearch 側で行うためパターンは空欄のままとします。

あとは確認画面はそのまま Next をして、ストリームを開始します。

Lambda の修正

自動生成された Lambda のソースコードを数行修正します。Lambda Function の設定画面からソースコードを修正します。修正内容は下記の修正前後の diff 結果をご参考に修正してください。

$ diff -u default.js custom.js
--- default.js 2018-01-26 19:01:12.000000000 +0900
+++ custom.js 2018-01-26 19:01:54.000000000 +0900
@@ -4,6 +4,8 @@
var crypto = require('crypto');

var endpoint = 'search-es6-xxxxxxxxxxxxxxxxx.ap-northeast-1.es.amazonaws.com';
+var pipeline = 'slowquerylog';
+var query_string = 'pipeline=' + pipeline;

exports.handler = function(input, context) {
// decode input from base64
@@ -212,7 +214,8 @@

var canonicalString = [
request.method,
- request.path, '',
+ request.path,
+ query_string,
canonicalHeaders, '',
signedHeaders,
hash(request.body, 'hex'),
@@ -233,6 +236,7 @@
'Signature=' + hmac(kSigning, stringToSign, 'hex')
].join(', ');

+ request.path = request.path + '?' + query_string;
return request;
}

設定は以上で完了です。簡単ですね。