改善1 slow_query対策
slow_query出力設定追加
-
webapp/etc/conf.d/my.cnf
作成[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 0
- ここで long_query_time = 0 とすることですべてのクエリが記録される
- 実運用なら1秒以上などにするべきだろうが、高速なクエリが大量に発行されているケースを把握するために0としている
- docker-compose.ymlのmysqlサービスに追記
volumes: - ./etc/mysql/conf.d:/etc/mysql/conf.d
mysqldumpslowのインストール
-
microdnf install dnf
- mysqlのDockerイメージではサイズ小さくするためにdnfではなくmicrodnfが採用されている
- mysql-serverパッケージをインストールする前に、その中にmysqldumpslowが入っているか確認する。以下のように出るのでちゃんと入っている。
bash-5.1# dnf repoquery -l mysql-server | grep dumpslow Last metadata expiration check: 0:03:08 ago on Tue Feb 11 13:33:08 2025. /usr/bin/mysqldumpslow /usr/share/man/man1/mysqldumpslow.1.gz /usr/bin/mysqldumpslow /usr/share/man/man1/mysqldumpslow.1.gz 略
dnf install mysql-server
-
mysqldumpslow /var/log/mysql/mysql-slow.log
を実行してもmysqldumpコマンドが見つからない - よく見ると下記のようなエラーが出ている。これは既存の
mysql-community-server-minimal-8.4.4
と新しく入れようとしているmysql-server-8.0.36
がコンフリクトしているfile /var/lib/mysql from install of mysql-server-8.0.36-1.el9_3.aarch64 conflicts with file from package mysql-community-server-minimal-8.4.4-1.el9.aarch64
- 既存パッケージ削除
microdnf remove mysql-community-server-minimal
dnf install mysql-server -y
dnf install less
-
mysqldumpslow /var/log/mysql/mysql-slow.log
を実行すると結果取得できたReading mysql slow query log from /var/log/mysql/mysql-slow.log Count: 22 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@[192.168.227.4] # Count: 11 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.1 (1), root[root]@[192.168.227.4] SELECT * FROM `posts` WHERE `id` = N Count: 11 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Close stmt Count: 11 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts administrator command: Prepare
計測結果
slow_query
# administrator command: Prepare;
# Time: 2025-02-12T12:43:31.801827Z
# User@Host: root[root] @ [192.168.227.4] Id: 9
# Query_time: 0.027134 Lock_time: 0.000001 Rows_sent: 3 Rows_examined: 100003
SET timestamp=1739364211;
SELECT * FROM `comments` WHERE `post_id` = 9999 ORDER BY `created_at` DESC LIMIT 3;
Rows_sent: 3 Rows_examined: 100003
を見ると、3行の結果を得るのに10万行読み込んでいる。
テーブル構造調査
-
docker compose exec mysql mysql -uroot -proot -Disuconp
-
show create table comments\G
でみるとpost_id
にindex無い*************************** 1. row *************************** Table: comments Create Table: CREATE TABLE `comments` ( `id` int NOT NULL AUTO_INCREMENT, `post_id` int NOT NULL, `user_id` int NOT NULL, `comment` text NOT NULL, `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=100068 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
-
実行計画
EXPLAIN SELECT * FROM
commentsWHERE
post_id= 9999 ORDER BY
created_atDESC LIMIT 3\G
rowsが10万行読んでる
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99035
filtered: 10.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)
- index追加
ALTER TABLE comments ADD INDEX post_id_idx(post_id);
- 再度実行計画
EXPLAIN SELECT * FROM
commentsWHERE
post_id= 9999 ORDER BY
created_atDESC LIMIT 3\G
rowsが11になり改善した事がわかる
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: comments
partitions: NULL
type: ref
possible_keys: post_id_idx
key: post_id_idx
key_len: 4
ref: const
rows: 11
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
ベンチマーク実行
cd benchmarker
docker run --network host -i private-isu-benchmarker /bin/benchmarker -t http://localhost -u /opt/userdata
{"pass":true,"score":16644,"success":14009,"fail":0,"messages":[]}
となりindexない状態のscore 1241から伸びた