Zenn
Open3

private-isu での素振り

ISUCON
kotapjpkotapjp

最初のスコアの確認

/home/isucon/private_isu.git/benchmarker/bin/benchmarker -u /home/isucon/private_isu.git/benchmarker/userdata -t http://XX

{"pass":true,"score":1016,"success":879,"fail":0,"messages":[]}

まずは計測をしてみる

mac からベンチマーカー

$ ab -c 1 -t 30 http://XX

競技用インスタンスでtopで計測
MySQLが90％になっていることを確認

MySQLの調査をする

MySQL でスロークエリを把握できるようにする

設定をする

競技用インスタンスで

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

負荷をかける

$ ab -c 1 -t 30 http://XX

スロークエリログを確認

sudo mysqldumpslow  /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 714  Time=0.03s (20s)  Lock=0.00s (0s)  Rows=1.9 (1326), isuconp[isuconp]@localhost
  SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N

Count: 34  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=10008.0 (340272), isuconp[isuconp]@localhost
  SELECT `id`, `user_id`, `body`, `created_at`, `mime` FROM `posts` ORDER BY `created_at` DESC

Count: 714  Time=0.01s (7s)  Lock=0.00s (0s)  Rows=1.0 (714), isuconp[isuconp]@localhost
  SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isuconp[isuconp]@localhost
  SET NAMES utf8mb4

Count: 2040  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2040), isuconp[isuconp]@localhost
  SELECT * FROM `users` WHERE `id` = N

Count: 6732  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=4852.4 (32666112), isuconp[isuconp]@localhost
  #

Count: 3264  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Close stmt

Count: 3468  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Prepare

MySQL に入って事情確認

mysql -uisuconp -pisuconp isuconp
use isuconp

mysql> show create table comments;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                            |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| comments | 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=100025 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

index は特になし

mysql> EXPLAIN SELECT * FROM `comments` WHERE `post_id` = 9995 ORDER BY `created_at`
DESC LIMIT 3;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | comments | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99666 |    10.00 | Using where; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

ためしのEXPEXPLAIN

key がなくて大量の行の読み込み

INDEX を貼る

mysql> ALTER TABLE comments ADD INDEX post_id_idx(post_id);
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

EXPLAIN

おー劇的な変化

チューニングの成果確認

ログの削除

sudo rm /var/log/mysql/mysql-slow.log
sudo mysqladmin flush-logs

これをしないと結果が混ざってしまうらしい

ベンチマーカーインスタンスより実行
結果

{"pass":true,"score":12093,"success":10232,"fail":0,"messages":[]}

1016→12093

初手感想

ほー、こんなかんじでやっていくのか
スコアが劇的に上がったり、すごく読み込みが早くなるのはいいね
基本的には計測→実行→計測 というような流れか
今日はここまででインスタンスを終了しておく