isucon練習記録2

[練習記録1（環境構築・ベンチマーク実行）](https://qiita.com/marogoma/items/256af6d569b51440db74)の続き

## 改善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が採用されている
1. 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
    略
    ```
1. `dnf install mysql-server`
1. `mysqldumpslow /var/log/mysql/mysql-slow.log` を実行してもmysqldumpコマンドが見つからない
1. よく見ると下記のようなエラーが出ている。これは既存の `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
    ```
1. 既存パッケージ削除 `microdnf remove mysql-community-server-minimal`
1. `dnf install mysql-server -y`
1. `dnf install less`
1. `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`

1. `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)
    ```
1. 実行計画 `EXPLAIN SELECT * FROM `comments` WHERE `post_id` = 9999 ORDER BY `created_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);`
1. 再度実行計画 `EXPLAIN SELECT * FROM `comments` WHERE `post_id` = 9999 ORDER BY `created_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`
1. `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から伸びた