MySQLの機能でスロークエリ関連の解析を行う方法の紹介

はじめに

XTechグループ Advent Calendar 2021の16日目は、iXIT株式会社エンジニアの蝦名がお送りします。
最近ハマっているものは音楽系Vtuberです。VIRTUAFREAK良かった…。

qiita.com

本題

ツールなどを導入しなくてもSlowQueryを解析できる機能がMySQLには存在するので、今回はその一部を紹介します。
ちなみに私が開発しているサービスのMySQLバージョンは5.6です。

1. mysqldumpslow

一言で言うとスロークエリーログファイルを解析して内容のサマリーを出力してくれる機能です。
前提としてスロークエリーログを出力している必要があります。

使い方

コマンド
※合計実行時間が長い順に10件のSQLを出力する
mysqldumpslow -s at -t 10 /opt/fio1/slog/sp-prd-db1-slow.log

出力例(1件):

Count: 23  Time=0.12s (2s)  Lock=0.00s (0s)  Rows=1.0 (23), host
  SELECT COUNT(*) FROM `user` WHERE user.STATUS=N AND (user.ACCESS_DATE>='S' AND user.ACCESS_DATE<='S')

本家のマニュアル

dev.mysql.com

弊サービスでは0.1秒以下のSQLをスロークエリーログに出力しており、
1時間毎のサマリーがメールとslackに送られてきます。
基本的にはmysqldumpslowの出力にある上位のSQLからチューニングしていけばいいので、重宝すると思います。

2. performance_schema

一言で言うとパフォーマンスモニタリング用のストレージエンジンです。
MySQL5.6で強化され、デフォルトで使用されるようになりました。
MySQL5.7でもっと強化されるのですが、残念ながら弊サービスのMySQLのバージョンは5.6です。

performance_schemaを使えばSlowQueryでは可視化されない

トランザクションの実行時間
一回の実行時間は短いが、大量に実行して塵積になっているもの

などが取得できます。

使い方

MySQL5.5以上を使用していると、performance_schemaというデータベースが存在するかと思います。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| hoge               |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables;
+----------------------------------------------------+
| Tables_in_performance_schema                       |
+----------------------------------------------------+
| accounts                                           |
| cond_instances                                     |
| events_stages_current                              |
| events_stages_history                              |
| events_stages_history_long                         |
| events_stages_summary_by_account_by_event_name     |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| table_io_waits_summary_by_index_usage              |
| table_io_waits_summary_by_table                    |
| table_lock_waits_summary_by_table                  |
| threads                                            |
| users                                              |
+----------------------------------------------------+
52 rows in set (0.00 sec)

performance_schemaデータベース内にはテーブルが大量にあるのですが、
それぞれどんなテーブルなのかは以下のドキュメントを参照してください。

dev.mysql.com

performance_schemaは私もあまり触れていないので、良さげな記事をいくつか紹介します。

thinkit.co.jp

tech.stmn.co.jp

本家のマニュアル

dev.mysql.com