isucon練習記録2

練習記録1（環境構築・ベンチマーク実行）の続き

改善1 slow_query対策

slow_query出力設定追加

1. 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としている
2. docker-compose.ymlのmysqlサービスに追記

       volumes:
         - ./etc/mysql/conf.d:/etc/mysql/conf.d
   

mysqldumpslowのインストール

1. microdnf install dnf
   • mysqlのDockerイメージではサイズ小さくするためにdnfではなくmicrodnfが採用されている
2. 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
   略
   

3. dnf install mysql-server
4. mysqldumpslow /var/log/mysql/mysql-slow.log を実行してもmysqldumpコマンドが見つからない
5. よく見ると下記のようなエラーが出ている。これは既存の 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
   

6. 既存パッケージ削除 microdnf remove mysql-community-server-minimal
7. dnf install mysql-server -y
8. dnf install less
9. 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万行読み込んでいる。

テーブル構造調査

1. docker compose exec mysql mysql -uroot -proot -Disuconp

2. 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)
   

3. 実行計画 EXPLAIN SELECT * FROM commentsWHEREpost_id= 9999 ORDER BYcreated_at DESC 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)

1. index追加 ALTER TABLE comments ADD INDEX post_id_idx(post_id);
2. 再度実行計画 EXPLAIN SELECT * FROM commentsWHEREpost_id= 9999 ORDER BYcreated_at DESC 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)

ベンチマーク実行

1. cd benchmarker
2. 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から伸びた