この記事は Retty Advent Calendar 1日目です。
DBを運用していて、DBの負荷やI/Oの問題に遭遇した事はありませんか？
僕は最近DBのレプリケーション本番とそれ以外のDBで、レプリケーション遅延が大きく発生してしまう問題に悩まされています。
そこで、今回はその原因を特定するためにquery_logから実行時間と重複queryをKibanaで可視化して分析しようと思います。
一応、 RDSのMySQLを使用した場合の方法も書きたいと思います。
方法
- MySQLのquery_logとslow_queryをはくように設定
- ログを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
[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にしたい場合は以下を追記する。
network.host: _local_, _site_
Kibanaのインストール
sudo yum install -y kibana
sudo vi /etc/kibana/kibana.yml
Kibanaも同様にlocalのみのアクセス許可になっているため、publicにしたい場合は以下を追記する。
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
[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
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 ~]$
ビジュアライズ結果
以下の様になりました。
まとめ
本当はもっと正確にログを取得して分析したかったのですが、この投稿時間の関係で途中になってしまったので、
次に書く番が回ってくるまでにはもっときれいにしておいて、その辺も書いていきたいと思います。
追記
RDSからslow_queryをビジュアライズする方法と、この記事の続きを書きました