スロークエリの確認方法：MySQL編

スロークエリは、アプリケーション側のボトルネックとしてよく現れる、処理速度が遅いSQLです。意図せずフルスキャンをしているケースが多く、データ件数が増えるにつれて急激に性能問題を引き起こします。負荷テスト実施時には1秒以上かかるものは把握しておくとよいでしょう。

スロークエリへの対処は次の3ステップで行います。

  1. スロークエリを特定する
  2. スロークエリを解析する
  3. スロークエリを改修する

MySQL、PostgreSQL、Oracleなど複数のRDBMSがありますが、基本的な流れは共通となります。

この記事では、MySQL8を対象に3ステップについて解説します。

1．スロークエリを特定する

スロークエリの設定確認

「show variables」コマンドで確認を行います。

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------------------+
| Variable_name       | Value                                     |
+---------------------+-------------------------------------------+
| slow_query_log      | OFF                                       |
| slow_query_log_file | /var/lib/mysql/slow.log |
+---------------------+-------------------------------------------+
2 rows in set (0.01 sec)

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

スロークエリの設定

設定はコマンドまたは設定ファイルから行います。1秒以上のクエリを「/tmp/slow.log」へ出力される設定は次の通りです。

コマンドの場合

「set global」コマンドで設定の変更が可能です。変更後は「show variables」コマンドで確認しましょう。「long_query_time」が変更されていない場合は、一度exitしたのちに再度確認してみましょう。

set global slow_query_log=1;
set global slow_query_log_file='/tmp/slow.log';
set global long_query_time=1;

設定ファイルの場合

my.cntへ下記を追加し、mysqldの再起動を行います。

[mysqld]
slow_query_log=1
slow_query_log_file=’/tmp/slow.log’
long_query_time=1

2．スロークエリを解析する

スロークエリの見方

スロークエリは次のように出力されます。下記は3秒スリープするSQLを流してみた場合のスロークエリログです。

# Time: 2020-05-12T01:49:18.135730Z
# User@Host: root[root] @ localhost []  Id:     9
# Query_time: 3.000270  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1589248155;
select sleep(3);

スロークエリの集計

「mysqldumpslow」コマンドを利用することで、スロークエリファイルの集計をすることができます。「-s」オプションで、発生頻度が多いクエリや長時間かかっているクエリを効率よく抽出することが可能です。

発生回数でソート

# mysqldumpslow -s c /tmp/slow.log

Reading mysql slow query log from /tmp/slow.log
Count: 4  Time=5.00s (20s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

Count: 2  Time=16.00s (32s)  Lock=0.00s (0s)  Rows=4.0 (8), root[root]@localhost
  select sleep(N) from mysql.user

合計処理時間でソート

# mysqldumpslow -s t /tmp/slow.log

Reading mysql slow query log from /tmp/slow.log
Count: 2  Time=16.00s (32s)  Lock=0.00s (0s)  Rows=4.0 (8), root[root]@localhost
  select sleep(N) from mysql.user

Count: 4  Time=5.00s (20s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

参考－＞4.6.9 mysqldumpslow — スロークエリーログファイルの要約

スロークエリの解析

スロークエリの解析は「Explain」コマンドを利用します。explainの後にクエリを入れることでクエリの実行パスの情報を取得できます。

mysql> explain select sleep(2);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

サンプルのSQLはスリープさせているだけなので、値がほとんどの値がNULLとなっています。特に注意する箇所は「type」で、「ALL」になっている場合はフルスキャンをしているので修正が必須となります。

参考－＞8.8.2 EXPLAIN 出力フォーマット

3．スロークエリを改修する

スロークエリの改修は次のことに注意して実施しましょう。

  • まずは、インデックス追加で解消しないか検討する
  • SQLを改修する場合は、取得結果が変わらないように慎重に
  • 必要に応じてアプリケーションの改修も視野に入れる

まとめ

スロークエリはシステムの性能に大きな影響を与えます。そのため、スロークエリを取得し、影響度の大きいものを特定し改修することで大幅な性能改善が見込めるため覚えておきましょう。

コメントは受け付けていません。