MySQLのlogを可視化する

この記事は **Retty** Advent Calendar 1日目です。

DBを運用していて、DBの負荷やI/Oの問題に遭遇した事はありませんか？
僕は最近DBのレプリケーション本番とそれ以外のDBで、レプリケーション遅延が大きく発生してしまう問題に悩まされています。

そこで、今回はその原因を特定するためにquery_logから実行時間と重複queryをKibanaで可視化して分析しようと思います。
一応、 **RDSのMySQL**を使用した場合の方法も書きたいと思います。

　
# 方法
1. MySQLのquery_logとslow_queryをはくように設定
1. ログをfilebeat + ElasticSearch + Kibanaでビジュアライズ。

　
# MySQLの設定
## query_logの設定
MySQLにログインし、以下のコマンドを実行する。

```
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/query.log';
```

また、再起動した場合も継続するため以下の設定を **my.cnf** に追記しておく。

```
general_log = 1
general_log_file = /var/log/mysql/query.log
```

## slow_queryの設定
MySQLにログインし、以下のコマンドを実行する。

```
SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/query.log';
```

また、再起動した場合も継続するため以下の設定を **my.cnf** に追記しておく。

```
slow_query_log = 1
slow_query_log_file =　/var/log/mysql/slow_query.log
long_query_time = 3
```

　
# RDS-MySQLの場合
## query_logの設定
RDSのコンソールでパラメーターグループを選択する。
対象となるパラメーターグループを選択して、以下の様にパラメーターを変更する。
**general_log_file**も変更できるが、変更してしまうと再起動がかかるのでおすすめはしません。

```
general_log = 1
```

## slow_queryの設定
RDSのコンソールでパラメーターグループを選択する。
対象となるパラメーターグループを選択して、以下の様にパラメーターを変更する。
**slow_query_log_file**もquery_log同様で、変更はできるが、再起動を伴います。

```
slow_query_log = 1
long_query_time = 3
```

　
# ログのビジュアライズ
それぞれのツールのインストールは以下を参考というか、ままに行いました。
http://acro-engineer.hatenablog.com/entry/2016/10/27/165342

この際に、テスト環境をlocalにたてようとしましたが、思っていたよりもスペックが必要でメモリやCPUリソースが足りずに時間がかかってしまったので、最初から余裕のあるマシンを使うことをおすすめします。

## OpenJDKのインストール
Elasticsearchのバージョンの関係で Java8以上が必要。

```
sudo yum update -y
sudo yum remove -y java-1.7.0-openjdk
sudo yum install -y java-1.8.0-openjdk-devel
sudo yum install -y java-1.8.0-openjdk-debuginfo --enablerepo=*debug*
```

## リポジトリの追加

```
sudo rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch
sudo vim /etc/yum.repos.d/elastic.repo
```

```shll:elastic.repo
[elasticsearch-5.x]
name=Elasticsearch repository for 5.x packages
baseurl=https://artifacts.elastic.co/packages/5.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md

```

## Elasticsearchのインストール

```
sudo yum install -y elasticsearch
sudo vi /etc/elasticsearch/elasticsearch.yml
```

デフォルトはlocalのみのアクセス許可になっているため、publicにしたい場合は以下を追記する。

```shell:add
network.host: _local_, _site_
```

## Kibanaのインストール

```
sudo yum install -y kibana
sudo vi /etc/kibana/kibana.yml
```

Kibanaも同様にlocalのみのアクセス許可になっているため、publicにしたい場合は以下を追記する。

```shell:add
server.host: "0.0.0.0"
```

## ElasticsearchとKibanaの起動

```
sudo service elasticsearch start
sudo service kibana start
```

## filebeatのインストール
以下を実行し、リポジトリの追加を行う。

```
sudo vim /etc/yum.repos.d/beats.repo
```

```shell:beats.repo
[beats]
name=Elastic Beats Repository
baseurl=https://packages.elastic.co/beats/yum/el/$basearch
enabled=1
gpgkey=https://packages.elastic.co/GPG-KEY-elasticsearch
gpgcheck=1
```

以下を実行してインストール

```
sudo rpm --import https://packages.elastic.co/GPG-KEY-elasticsearch
sudo yum install filebeat
```
## logの設定

```
/etc/filebeat/filebeat.yml
```

```shell:add
paths:
        #- /var/log/*.log
        - /var/log/mysql/*query.log
```

以下を実行して起動

```
sudo service filebeat start
```

# 通信の確認
## elasticsearch
以下を実行し、filebeat-*があるかを確認

```
[ec2-user@ip-172-19-76-160 ~]$ curl 'localhost:9200/_cat/indices?v'
health status index                    uuid                   pri rep docs.count docs.deleted store.size pri.store.size
yellow open   .kibana                  bGUIEfzeSNWFB9yZ1Ra-zg   1   1        151           88      376kb          376kb
yellow open   filebeat-2016.11.30      1uNJrdFuReW2RAdUpkvN_w   5   1     133284            0     12.3mb         12.3mb
yellow open   metricbeat-2016.11.30    dFmHhQBJTaK7fIDexqvjRw   5   1     142470            0     59.9mb         59.9mb
yellow open   kibana_access-2016.11.30 AzOrJDMrSWqIMHeM4DrekA   5   1       1139            0      1.3mb          1.3mb
yellow open   packetbeat-2016.11.30    Z8vmYh92R6-OwhP93MWbcg   5   1      19978            0      6.6mb          6.6mb
[ec2-user@ip-172-19-76-160 ~]$
```


# ビジュアライズ結果
以下の様になりました。
![スクリーンショット 2016-11-30 23.38.54.png](https://qiita-image-store.s3.amazonaws.com/0/137186/592cac6a-775d-c818-5056-a80ffa89b7ea.png)


# まとめ
本当はもっと正確にログを取得して分析したかったのですが、この投稿時間の関係で途中になってしまったので、
次に書く番が回ってくるまでにはもっときれいにしておいて、その辺も書いていきたいと思います。

　
# 追記
RDSからslow_queryをビジュアライズする方法と、この記事の続きを書きました
### [MySQLのlogを可視化する part2](http://qiita.com/takumi-suzuki/items/3ebbde82f29b018e75dd)