MySQLのスロークエリをslackに通知できるようにする

やりたいこと

こんな感じで、MySQLでボトルネックになりそうなslowクエリをslackに通知させて、
継続的に、クエリチューニングできるようにしたい。

なので、Mysqlで出力されたスロークエリログをfluentdで取得し、
そのクエリに対して、explainを実行させて、クエリの内容と一緒に、
explainの結果をslackに通知できるようにする。

1. 準備

slackの投稿先webhookURLを用意する

IncomingWebHookのキー発行は以下から取得する
https://xxx.slack.com/services/new

参考URL: http://qiita.com/vmmhypervisor/items/18c99624a84df8b31008

2. 環境構築

fluentd

• インストール

curl -L https://toolbelt.treasuredata.com/sh/install-redhat-td-agent2.sh | sh

• 起動確認

# /etc/init.d/td-agent start
td-agent td-agent:                                         [  OK  ]
# /etc/init.d/td-agent status
td-agent is running                                        [  OK  ]
# /etc/init.d/td-agent stop
Stopping td-agent: td-agent                                [  OK  ]

• 実装するのに、必要なモジュールをインストール

# td-agent-gem install fluent-plugin-nata2
# td-agent-gem install fluent-plugin-mysql_explain
# td-agent-gem install fluent-plugin-sql_fingerprint
# td-agent-gem install fluent-plugin-slack

• 各モジュールについて

fluent-plugin-mysql_explain

SQLに対して、EXPLAINを実行して、その結果をexplain属性に保持するfilter plugin

github：https://github.com/kikumoto/fluent-plugin-mysql_explain

引用：http://kikumoto.hatenablog.com/entry/2015/12/04/000152

fluent-plugin-sql_fingerprint

in_mysqlslowquery_exで取得されたJSONのsql属性に保持されているSQL文のパラメータ部分を抽象化するplugin

github：https://github.com/kikumoto/fluent-plugin-sql_fingerprint

引用：http://kikumoto.hatenablog.com/entry/2015/12/04/000152

fluent-plugin-nata2

github：https://github.com/studio3104/fluent-plugin-nata2

fluent-plugin-slack

slackにpostするために利用したplugin

github：https://github.com/sowawa/fluent-plugin-slack

percona-toolkit

• rpmファイルの取得

# wget percona.com/get/percona-toolkit.rpm

• 取得できたかを、確認

# ls | grep percona
percona-toolkit.rpm

• インストール

# yum install percona-toolkit.rpm

3. fluentdの設定

confファイルの編集

vim /etc/td-agent/td-agent.conf

<source>
    type mysqlslowquery_ex
    read_from_head
    path 【パスを指定】/slow.log
    pos_file /var/log/td-agent/mysql-slow.pos
    tag mysqld.slow_query.bp
    last_dbname_file /tmp/slowquery.log.lastdb
</source>

<filter mysqld.slow_query.**>
    type record_transformer
    <record>
        hostname ${hostname}
    </record>
</filter>

<filter mysqld.slow_query.**>
    type     mysql_explain
    host     127.0.0.1
    port     3306
    username 【DBへのアクセスユーザ名を指定】
    password 【DBへのアクセスパスワードを指定】
    database 【DB名を指定】
    sql_key  sql
    added_key explain
</filter>

<filter mysqld.slow_query.**>
    type sql_fingerprint
    fingerprint_tool_path /usr/bin/pt-fingerprint
</filter>

<match mysqld.slow_query.**>
    type copy
    <store>
        type slack
        webhook_url 【slackのwebhookURLを指定】
        channel 【slackのchannel名を指定】
        username 【slackの投稿ユーザ名を指定】
        icon_emoji :x:
        color danger
        message "*[User]* %s\r\n *[Host]* %s\r\n *[Query Time]* %s\r\n *[Lock Time]* %s\r\n *[Rows sent]* %s\r\n *[Rows Examined]* %s\r\n *[SQL]* %s \r\n *[Explain]* \r\n %s \r\n"
        message_keys user,host,query_time,lock_time,rows_sent,rows_examined,fingerprint,explain
        flush_interval 1m
    </store>
</match>

fluentdを再起動

service td-agent restart

4. 疎通確認

slowクエリを作って、slackに通知がいくことを確認する

• slowクエリを実行

SELECT COUNT(*), sleep(10) 
FROM blog_t

普段はあまり使わないが、sleepで10秒かかるクエリを作成して、slackで通知を確認

• 下記画面のように、通知が届くことを確認する

うまくいかなかった場合は、tdログを確認してみる

・td-agent.confで設定したユーザのDBアクセス権限が付与されていない
・explainの実行がエラーになっている
・td-agent自体が停止してる、パースがうまくいってない

など、warnやerrorが出ていたら、うまくいっていない可能性あり