Menu

Raspberry Pi チューニング - MySQLスロークエリ

Raspberry Pi で MySQL を使用しているのですが、動作がもっさりしてきた印象があるためいろいろ調査します。

今回は、Raspberry Pi 上の MySql のスロークエリを調査して改善します。

スロークエリログの出力

Cloud9のローカル開発環境で以下のコマンドを実行してスロークエリのログ出力をするようにします。

masasikatano:~/workspace (master) $ mysql -u root -ppass1234 databasename
mysql> set global slow_query_log=1;
mysql> set global long_query_time=0.5;
mysql> set global log_queries_not_using_indexes=1;
mysql> set global slow_query_log_file ='/home/ubuntu/workspace/slow_query.log';

そして、ローカル開発環境のサイトに適当にアクセスすると、ログが出力されるようになります。

とこんなログが出現

# Time: 170412  3:39:50
# User@Host: root[root] @ localhost []
# Query_time: 0.005006  Lock_time: 0.000073 Rows_sent: 13  Rows_examined: 1079
SET timestamp=1491968390;
select `monsters`.`id`, `monsters`.`name`, `monsters`.`name_english`, `monsters`.`name_awaked`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `rating`, `rank` from `monsters` inner join `attributes` on `monsters`.`attribute_id` = `attributes`.`id` order by `monsters`.`rank` asc limit 13;
# User@Host: root[root] @ localhost []
# Query_time: 0.004024  Lock_time: 0.000032 Rows_sent: 12  Rows_examined: 1078
SET timestamp=1491968390;
select `monsters`.`id`, `monsters`.`name`, `monsters`.`name_english`, `monsters`.`name_awaked`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `rating`, `rank` from `monsters` inner join `attributes` on `monsters`.`attribute_id` = `attributes`.`id` order by `pv` desc limit 12;
# User@Host: root[root] @ localhost []
# Query_time: 0.006446  Lock_time: 0.000043 Rows_sent: 6  Rows_examined: 1519
SET timestamp=1491968390;
select `monsters`.`id`, `monsters`.`name`, `monsters`.`name_english`, `monsters`.`name_awaked`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `posts`.`post`, `posts`.`youtube`, `posts`.`created_at` as `post_created_at`, `posts`.`rating` from `monsters` inner join `attributes` on `monsters`.`attribute_id` = `attributes`.`id` inner join `posts` on `monsters`.`posted_id` = `posts`.`id` order by `monsters`.`updated_at` desc limit 6;
# User@Host: root[root] @ localhost []
# Query_time: 0.002351  Lock_time: 0.000037 Rows_sent: 6  Rows_examined: 546
SET timestamp=1491968390;
select `dungeons`.`id`, `dungeons`.`name`, `dungeons`.`difficulty`, `maps`.`id` as `map_id`, `maps`.`name` as `map_name`, `posts`.`post`, `posts`.`youtube`, `posts`.`created_at` as `post_created_at`, `posts`.`rating` from `dungeons` inner join `maps` on `dungeons`.`map_id` = `maps`.`id` inner join `posts` on `dungeons`.`posted_id` = `posts`.`id` order by `dungeons`.`updated_at` desc limit 6;

sort句にIndex

sortに使用している条件にIndexを貼ります。

mysql> alter table monsters add index monsters_rank (rank);
mysql> alter table monsters add index monsters_pv (pv);
mysql> alter table monsters add index monsters_updated_at (updated_at);
mysql> alter table dungeons add index dungeons_updated_at (updated_at);

別のページでログを出力

# Time: 170412  3:59:42
# User@Host: root[root] @ localhost []
# Query_time: 0.000620  Lock_time: 0.000038 Rows_sent: 1  Rows_examined: 533
SET timestamp=1491969582;
select `monsters`.`id` from `monsters` inner join `attributes` on `attributes`.`id` = `monsters`.`attribute_id` where `monsters`.`name` = '極地の女王' and `attribute_id` = 3 limit 1;
# User@Host: root[root] @ localhost []
# Query_time: 0.000841  Lock_time: 0.000026 Rows_sent: 4  Rows_examined: 540
SET timestamp=1491969582;
select `monsters`.`id` as `id`, `monsters`.`name` as `name`, `monsters`.`name_english` as `name_english`, `monsters`.`name_awaked` as `name_awaked`, `monsters`.`attribute_id` as `attribute_id`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `attributes`.`name` as `attribute_name`, `monsters`.`rating` as `rating` from `monsters` inner join `attributes` on `attributes`.`id` = `monsters`.`attribute_id` where `monsters`.`name_english` = 'Polar_Queen' and `monsters`.`id`  '524' order by `monsters`.`attribute_id` asc;
# User@Host: root[root] @ localhost []
# Query_time: 0.000129  Lock_time: 0.000016 Rows_sent: 1  Rows_examined: 533
SET timestamp=1491969582;
select count(*) as aggregate from `monsters`;
# User@Host: root[root] @ localhost []
# Query_time: 0.000343  Lock_time: 0.000014 Rows_sent: 1  Rows_examined: 533
SET timestamp=1491969582;
select count(*) as aggregate from `monsters` where `attribute_id` = '3';
# User@Host: root[root] @ localhost []
# Query_time: 0.000302  Lock_time: 0.000020 Rows_sent: 1  Rows_examined: 533
SET timestamp=1491969582;
select count(*) as aggregate from `monsters` where `star` = '5';
# User@Host: root[root] @ localhost []
# Query_time: 0.000332  Lock_time: 0.000018 Rows_sent: 1  Rows_examined: 533
SET timestamp=1491969582;
select count(*) as aggregate from `monsters` where `attribute_id` = '3' and `star` = '5';
# User@Host: root[root] @ localhost []
# Query_time: 0.004351  Lock_time: 0.000029 Rows_sent: 3  Rows_examined: 1516
SET timestamp=1491969582;
select `monsters`.`id`, `monsters`.`name`, `monsters`.`name_english`, `monsters`.`name_awaked`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `posts`.`post`, `posts`.`youtube`, `posts`.`created_at` as `post_created_at`, `posts`.`rating` from `monsters` inner join `attributes` on `monsters`.`attribute_id` = `attributes`.`id` inner join `posts` on `monsters`.`posted_id` = `posts`.`id` order by `monsters`.`updated_at` desc limit 3;
# User@Host: root[root] @ localhost []
# Query_time: 0.002842  Lock_time: 0.000015 Rows_sent: 6  Rows_examined: 1072
SET timestamp=1491969582;
select `monsters`.`id`, `monsters`.`name`, `monsters`.`name_english`, `monsters`.`name_awaked`, `attributes`.`name` as `attributes_name`, `attributes`.`name_english` as `attributes_name_english`, `rating`, `rank` from `monsters` inner join `attributes` on `monsters`.`attribute_id` = `attributes`.`id` order by `pv` desc limit 6;

where句にIndex

whereに使用している条件にIndexを貼ります。

複数のwhereを使用している場合は複合Indexになります。

mysql> alter table monsters add index monsters_name_attribute_id (name, attribute_id);
mysql> alter table monsters add index monsters_attribute_id (attribute_id);
mysql> alter table monsters add index monsters_name_english_id (name_english, id);
mysql> alter table monsters add index monsters_attribute_id (attribute_id);
mysql> alter table monsters add index monsters_star (star);
mysql> alter table monsters add index monsters_attribute_id_star (attribute_id, star);

count句にIndex

まずはexplainで確認してみます。

mysql> explain select count(*) as aggregate from `monsters`;
+----+-------------+----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | monsters | index | NULL          | id   | 4       | NULL |  581 | Using index |
+----+-------------+----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

うーん。ここらへんからは根が深そうなので、サブテーブルを用意したりする根本的な解決が必要そうです。


用語

スロークエリ

データベースにおいて、時間のかかっているSQL(遅いSQL)のことをスロークエリと呼びます。 基本的には、MySQLやPostgreSQLの設定により取得することができ、どの程度遅いものを対象とするか等を指定することが出来ます。

インデックス

データベースサーバの観点からすると、インデックスを使用する理由は、クエリの実行結果から可能性のある行を迅速に選び出せるようにするため。 インデックスがなければ、MySQLはテーブル内のすべての行を調べなければならない。 一方でMySQLは、インデックスの値を別個のリストで保守し、データに変化があるたびにインデックスを更新し続ける必要がある。 テーブルのすべてのカラムにインデックスをつけると書き込みの速度が低下するし、データを保存するために必要な容量が増えてしまう。 インデックスを使用すると、検索などの性能は基本的に向上するが、容量が犠牲になる。


もっと早くにやっとけばよかった。


参考サイト

Written by masasikatano on Wednesday April 12, 2017

« 機械学習 - Vectorization - Raspberry Pi チューニング - DBサーバの独立と移行 »


Share