ISUCON8 予選問題やってみた

# はじめに
- ISUCON8 予選問題を自前環境で解いてみたメモ
- 他の方のブログなどを参考に進めてます

# 参考にさせていただいたページ
公式情報

- [予選レギュレーション](http://isucon.net/archives/52445389.html)
- [予選マニュアル](https://github.com/isucon/isucon8-qualify/blob/master/doc/MANUAL.md)
- [コード群](https://github.com/isucon/isucon8-qualify)
- [解説と講評](http://isucon.net/archives/52520045.html)
- [関連エントリまとめ](http://isucon.net/archives/52459762.html)

以下、出場された方や過去問を解いている方のページを参考にしました

- [くじらにっき++ ~ISUCON8 の予選問題を復習した~](https://kujira16.hateblo.jp/entry/2018/10/22/013611)
- [REQLY開発ブログ ~初出場3人でISUCON8予選を学生枠1位(?)で通過しました~](https://reqly.hatenablog.com/entry/2018/09/18/235726?utm_source=feed)
- [ぶていのログでぶログ  ~ISUCON8に参加した~](https://tech.buty4649.net/entry/2018/09/17/202927)
- [k5342.hatenablog.com ~ISUCON8 予選に参加した~](https://k5342.hatenablog.com/entry/2018/09/17/140049)
- [Qiita ~死闘の果てに ISUCON 8 予選を全体７位で突破した記録~](https://qiita.com/najeira/items/527e1f54fa417fd20b65)
- [己の不学を恥じる](https://garasubo.github.io/hexo/2018/09/26/isucon-extra.html)



# 前提

- 環境構築は[こちら](https://qiita.com/s_suzaku/items/ff1855839c0ddd49ca9a)でやってみました
- 言語はpythonを選択
- サーバはbench(ベンチマーク用),webapp1/2/3(アプリ用)の4台を構築
- webapp1/2/3にportal,webapがデプロイされている状態
- benchにベンチマークツールがデプロイされている状態

<!--------- コメントアウト 始まり------------------
# githubへバックアップ
複数人での作業の場合を想定してgithubへのバックアップを試した
gitをインストール

```
[isucon@webapp1 ~]$ sudo yum install -y git
```

事前に用意したgitリポジトリをクローン

```
[isucon@webapp1 ~]$ git clone https://github.com/hogehoge.git
[isucon@webapp1 ~]$ ls
go  local  hogehoge  torb  xbuild
```

/etc(設定ファイル)と/home/isucon/torb（アプリ）をローカルリポジトリ配下にコピー

```
[isucon@webapp1 ~]$ sudo rsync -a /etc/ /home/isucon/hogehoge/etc/
[isucon@webapp1 ~]$ rsync -a /home/isucon/torb /home/isucon/hogehoge/torb/
[isucon@webapp1 ~]$ ls -l /home/isucon/hogehoge/
total 12
drwxr-xr-x. 81 root   root   8192 Aug 16 00:41 etc
drwxr-xr-x.  6 isucon isucon   70 Aug 15 15:22 torb
```

ローカルレポジトリでadd & commit & push

```
[isucon@webapp1 hogehoge]$ git config --global user.email "hogehoge@fuga"
[isucon@webapp1 hogehoge]$ git config --global user.name "hogehoge"
[isucon@webapp1 hogehoge]$ git add -A .
[isucon@webapp1 hogehoge]$ git commit
[isucon@webapp1 hogehoge]$ git push origin
```
-------- コメントアウト 終わり------------------->

# ベンチマーク初回実行
benchにSSHログインしてベンチマークツールを実行
自分の環境では初期構築の状態で実行した場合、負荷走行前のバリデーション（GET /admin/のタイムアウト）によりスコア0でした・・・
ブラウザではアクセスできたので性能によるものと思われます

```
[isucon@webapp1 ~]$ cd torb/bench/
[isucon@webapp1 bench]$ ./bin/bench -remotes=192.168.100.1 -output result.json
[isucon@webapp1 bench]$ jq . < result.json 
```


# h2oをnginxに変更
Webサーバとして動いているh2oをnginxに変更する
※静的ファイルはWebサーバ機能に変えさせるべきだが、今回は元々その設定がされていた

nginxをインストール

```
[isucon@webapp1 ~]$ sudo yum install -y nginx
```

<!-- 
/etc配下をレポジトリにコピー
```
[isucon@webapp1 ~]$ rsync -a /etc/nginx/ /home/isucon/hogehoge/etc/nginx/
```


nginx設定ファイル(/etc/nginx/nginx.conf)をレポジトリ上のファイルへのシンボリックリンクに変更

```
[isucon@webapp1 ~]$ sudo rm /etc/nginx/nginx.conf
[isucon@webapp1 ~]$ sudo ln -s /home/isucon/hogehoge/etc/nginx/nginx.conf /etc/nginx/nginx.conf
```
-->

SELinux無効化

```
[isucon@webapp1 ~]$ getenforce 
Enforcing
[isucon@webapp1 ~]$ sudo setenforce 0
[isucon@webapp1 ~]$ sudo vi /etc/selinux/config 
*SELINUX=disabled に修正
```

初期状態のh2o設定ファイルは以下のとおり

```
[isucon@webapp1 ~]$ cat /etc/h2o/h2o.conf 
user: isucon

access-log: /var/log/h2o/access.log
error-log: /var/log/h2o/error.log
pid-file: /var/run/h2o/h2o.pid

hosts:
  "localhost:80":
    listen:
      port: 80
      host: 0.0.0.0
    paths:
      "/favicon.ico":
        file.file: /home/isucon/torb/webapp/static/favicon.ico
      "/css":
        file.dir: /home/isucon/torb/webapp/static/css
      "/img":
        file.dir: /home/isucon/torb/webapp/static/img
      "/js":
        file.dir: /home/isucon/torb/webapp/static/js
      "/":
        proxy.reverse.url: http://127.0.0.1:8080/
        proxy.preserve-host: ON
```

h2oの設定をnginx設定ファイルを以下の通り修正 （変更点のみ抜粋）

```
[isucon@webapp1 ~]$ vi /etc/nginx/nginx.conf
-user nginx;
+user isucon;

-    log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
-                      '$status $body_bytes_sent "$http_referer" '
-                      '"$http_user_agent" "$http_x_forwarded_for"';
-
-    access_log  /var/log/nginx/access.log  main;
+   log_format  ltsv "time:$time_local"
+       "\thost:$remote_addr"
+        "\tforwardedfor:$http_x_forwarded_for"
+        "\treq:$request"
+        "\tmethod:$request_method"
+        "\turi:$request_uri"
+        "\tstatus:$status"
+        "\tsize:$body_bytes_sent"
+        "\treferer:$http_referer"
+        "\tua:$http_user_agent"
+        "\treqtime:$request_time"
+        "\truntime:$upstream_http_x_runtime"
+        "\tapptime:$upstream_response_time"
+        "\tcache:$upstream_http_x_cache"
+        "\tvhost:$host";
+
+    access_log  /var/log/nginx/access.log  ltsv;


+        location /favicon.ico {
+               root /home/isucon/torb/webapp/static;
+        }
+
+        location /css {
+               root /home/isucon/torb/webapp/static;
+        }
+
+        location /img {
+               root /home/isucon/torb/webapp/static;
+        }
+
+        location /js {
+               root /home/isucon/torb/webapp/static;
+        }
+
         location / {
+               proxy_pass http://127.0.0.1:8080;
+               proxy_redirect     off;
+               proxy_set_header   X-Forwarded-Proto $http_x_forwarded_proto;
+               proxy_set_header   X-Forwarded-Port  $http_x_forwarded_port;
+               proxy_set_header   Host              $http_host;
+               proxy_set_header   X-Real-IP         $http_x_forwarded_for;
+               proxy_set_header   X-Forwarded-For   $http_x_forwarded_for;
         }
```

h2oを停止して、nginxを起動

```
[isucon@webapp1 ~]$ sudo systemctl stop h2o
[isucon@webapp1 ~]$ sudo systemctl start nginx
```

# alp(ログファイルの解析ツール)を使ってみる
参考：[LTSV 形式の Web サーバのアクセスログを集計するツールを作りました](https://tkuchiki.hatenablog.com/entry/2015/09/25/130900)

```
[isucon@webapp1 ~]$ sudo yum install -y wget
[isucon@webapp1 ~]$ wget https://github.com/tkuchiki/alp/releases/download/v0.4.0/alp_linux_amd64.zip
[isucon@webapp1 ~]$ unzip alp_linux_amd64.zip 
```

#####alpの使い方

- ログに残るアクセスURL毎に応答ステータスコード（1XX~5XX）、応答時間(最小、最大、平均、合計)などを視覚化
-  --min、--avg、--sum、--methodなどオプションでソート可能

```
[isucon@webapp1 ~]$ sudo /home/isucon/alp -f /var/log/nginx/access.log --avg -r
+-------+--------+-----------------------------------------+-----+-----+-----+-----+-----+-------+--------+--------+-------+--------+--------+-------+--------+------------+------------+------------+------------+
| COUNT | METHOD |                   URI                   | 1XX | 2XX | 3XX | 4XX | 5XX |  MIN  |  MAX   |  SUM   |  AVG  |   P1   |  P50   |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  | SUM(BODY)  | AVG(BODY)  |
+-------+--------+-----------------------------------------+-----+-----+-----+-----+-----+-------+--------+--------+-------+--------+--------+-------+--------+------------+------------+------------+------------+
|     6 | GET    | /admin/                                 |   0 |   2 |   0 |   4 |   0 | 0.150 | 10.020 | 50.135 | 8.356 | 10.004 | 10.005 | 9.952 |  3.670 |      0.000 |  21281.000 |  32717.000 |   5452.833 |
|     7 | GET    | /initialize                             |   0 |   6 |   0 |   1 |   0 | 5.008 | 10.070 | 43.670 | 6.239 |  5.674 |  6.924 | 5.008 |  1.668 |      0.000 |      0.000 |      0.000 |      0.000 |
|     1 | DELETE | /api/events/11/sheets/D/456/reservation |   0 |   0 |   0 |   1 |   0 | 3.049 |  3.049 |  3.049 | 3.049 |  3.049 |  3.049 | 3.049 |  0.000 |      0.000 |      0.000 |      0.000 |      0.000 |
|     1 | GET    | /api/events/19                          |   0 |   1 |   0 |   0 |   0 | 2.407 |  2.407 |  2.407 | 2.407 |  2.407 |  2.407 | 2.407 |  0.000 |  11946.000 |  11946.000 |  11946.000 |  11946.000 |
|     6 | GET    | /                                       |   0 |   6 |   0 |   0 |   0 | 1.277 |  5.571 | 13.769 | 2.295 |  5.571 |  1.455 | 1.389 |  1.499 |  14437.000 |  14923.000 |  87245.000 |  14540.833 |
(以下省略)
```

#appソースコード確認&修正

- alpの解析結果やブラウザの検証モードで確認した結果、POST(/admin/api/actions/login)の後リダイレクトのGET(/admin)でタイムアウトが発生していた
- GET(/admin/api/events)へのアクセスも遅く、この中で`get_events`の関数が重いことが原因だった模様
- `get_events`の中では`get_event`を繰り返し呼んでいた
    - `get_event`の中でN+1問題
    - `get_event`の中で各座席のdetail情報を取得している（`event['sheets'][sheet['rank']]['detail'].append(sheet`の部分）が、`get_events`ではdel（削除）して使っていなかった
    

###get_event, get_eventsの修正

※以下のブログを参考（というかほぼパクリ）にさせていただきましたmm
参考：[初出場3人でISUCON8予選を学生枠1位(?)で通過しました](https://reqly.hatenablog.com/entry/2018/09/18/235726?utm_source=feed)
参考：[ISUCON8 の予選問題を復習した](https://kujira16.hateblo.jp/entry/2018/10/22/013611)

修正箇所

- `get_event`に引数wo_detail(without_detailの意味)を追加してdetailを取るか取らないかを制御できるようにする
- `get_events` と`get_users`の中で`get_event` 実行時にdetail取得の処理を実行しないように引数追加
- `for sheet in sheets:`のループの中で毎回クエリを発行しており、N+1問題となっているのを解消する
- 座席のrankや数は固定のためハードコーディング
- 残席数(remains)は座席総数から予約数を引いていく形式で計算

調べた中でわかったこと（知ったこと）

- `cursorclass=MySQLdb.cursors.DictCursor`の部分でDB cursorをdict（辞書）モードに設定している
- そのため複数行返るクエリをfetchall()した場合（`sheets = cur.fetchall()`の部分）
    - [{key1:val1, key2:val2}, {key1:val1´, key2:val2´}, …]
    - といったdict型の要素を並べたリストが返る
- `event = get_event(event_id, wo_detail=True)` の引数指定方法について
    - pythonでは引数の変数名を使って、実引数の順番を入れ替えることができる
    - 参考:[【Pythonを使い倒す】意外と知らない？？引数の様々な使い方とは？](https://attracter.tokyo/tech/python23/)


```app.pyの修正前後diff
$diff -u app.py.bef app.py.aft
--- app.py.bef	2020-04-06 11:50:29.000000000 +0900
+++ app.py.aft	2020-04-06 11:49:55.000000000 +0900
@@ -105,9 +105,7 @@
         event_ids = [row['id'] for row in rows if filter(row)]
         events = []
         for event_id in event_ids:
-            event = get_event(event_id)
-            for sheet in event['sheets'].values():
-                del sheet['detail']
+            event = get_event(event_id, wo_detail=True)
             events.append(event)
         conn.commit()
     except MySQLdb.Error as e:
@@ -115,45 +113,46 @@
         raise e
     return events
 
-
-def get_event(event_id, login_user_id=None):
+def get_event(event_id, login_user_id=None, wo_detail=False):
     cur = dbh().cursor()
     cur.execute("SELECT * FROM events WHERE id = %s", [event_id])
     event = cur.fetchone()
     if not event: return None
 
-    event["total"] = 0
-    event["remains"] = 0
+    event["total"] = 1000
+    event["remains"] = 1000
     event["sheets"] = {}
-    for rank in ["S", "A", "B", "C"]:
-        event["sheets"][rank] = {'total': 0, 'remains': 0, 'detail': []}
-
-    cur.execute("SELECT * FROM sheets ORDER BY `rank`, num")
-    sheets = cur.fetchall()
-    for sheet in sheets:
-        if not event['sheets'][sheet['rank']].get('price'):
-            event['sheets'][sheet['rank']]['price'] = event['price'] + sheet['price']
-        event['total'] += 1
-        event['sheets'][sheet['rank']]['total'] += 1
-
+    ranks = ["S", "A", "B", "C"]
+    sheet_total = {'S': 50,   'A': 150,  'B': 300,  'C': 500}
+    sheet_price = {'S': 5000, 'A': 3000, 'B': 1000, 'C': 0}
+    for rank in ranks:
+        event['sheets'][rank] = {'total': sheet_total[rank], 'remains': sheet_total[rank], 'detail':[], 'price': event['price'] + sheet_price[rank]}
+
+    if wo_detail:
+        cur.execute('SELECT sheets.`rank`, COUNT(*) AS reserved FROM reservations INNER JOIN sheets ON reservations.sheet_id = sheets.id WHERE event_id = %s AND canceled_at IS NULL GROUP BY sheets.`rank`', [event['id']])
+        reservations = cur.fetchall()
+        for reservation in reservations:
+            event['sheets'][reservation['rank']]['remains'] -= reservation['reserved']
+            event['remains'] -= reservation['reserved']
+    else:
         cur.execute(
-            "SELECT * FROM reservations WHERE event_id = %s AND sheet_id = %s AND canceled_at IS NULL GROUP BY event_id, sheet_id HAVING reserved_at = MIN(reserved_at)",
-            [event['id'], sheet['id']])
-        reservation = cur.fetchone()
-        if reservation:
-            if login_user_id and reservation['user_id'] == login_user_id:
-                sheet['mine'] = True
-            sheet['reserved'] = True
-            sheet['reserved_at'] = int(reservation['reserved_at'].replace(tzinfo=timezone.utc).timestamp())
-        else:
-            event['remains'] += 1
-            event['sheets'][sheet['rank']]['remains'] += 1
-
-        event['sheets'][sheet['rank']]['detail'].append(sheet)
+            "SELECT * FROM sheets LEFT JOIN ( SELECT sheet_id, user_id, reserved_at FROM reservations WHERE event_id = %s AND canceled_at IS NULL ORDER BY sheet_id) AS sub ON sheets.id = sub.sheet_id", [event_id])
+        sheets = cur.fetchall()
 
-        del sheet['id']
-        del sheet['price']
-        del sheet['rank']
+        for sheet in sheets:
+            if sheet['reserved_at']:
+                if login_user_id and sheet['user_id'] == login_user_id:
+                    sheet['mine'] = True
+                sheet['reserved'] = True
+                sheet['reserved_at'] = int(sheet['reserved_at'].replace(tzinfo=timezone.utc).timestamp())
+                event['remains'] -= 1
+                event['sheets'][sheet['rank']]['remains'] -= 1
+            event['sheets'][sheet['rank']]['detail'].append(sheet)
+            del sheet['id']
+            del sheet['price']
+            del sheet['rank']
+            del sheet['sheet_id']
+            del sheet['user_id']
 
     event['public'] = True if event['public_fg'] else False
     event['closed'] = True if event['closed_fg'] else False
```

# ワーカー数の変更
（本来はじめに確認すべきなのかもしれませんが…）
ベンチマーク実行時に`dstat`コマンドで負荷を確認するとCPUが50%程度（1コア分）しか使われていない。
アプリケーションのワーカー数が適切でないためと考えられるため、サービス起動のユニットファイルを以下の通り修正する。
ここでスコア8000~10000くらい出るようになった。
参考ブログをみるともっと高いスコアが出るはずのようなので、自分の環境にどこか別のボトルネックがあるようだが、スコアを上げていく過程を実践できたのでよしとする（いろいろ調べて解明したいが、完全に実力不足・・・）

```
$ diff torb.python.service.org torb.python.service
8c8
< ExecStart = /home/isucon/torb/webapp/python/venv/bin/gunicorn app:app -b '0.0.0.0:8080'
---
> ExecStart = /home/isucon/torb/webapp/python/venv/bin/gunicorn app:app -w 2 -b '0.0.0.0:8080'
```

# スロークエリの確認
Mariadbで実行時間のかかるクエリを調べてDB側での改善を図る。
デフォルトでは設定ファイルは空ファイルとなっているので、以下の通り修正して再起動する。

やっていること

- スロークエリのログの出力をON
- `/var/log/mariadb/mariadb-slow.log`にスロークエリログを出力
- スロークエリと判定する実行時間を指定せず、インデックスを使用しないクエリを全て記録する


```
[mysqld]
# slow query
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/mariadb-slow.log
#long_query_time
log-queries-not-using-indexes = 1

# innodb
innodb_file_format = Barracuda
innodb_file_per_table
innodb_large_prefix
innodb_buffer_pool_size = 384MB
innodb_log_file_size = 384MB
innodb_flush_log_at_trx_commit = 0
loose_innodb_buffer_pool_dump_at_shutdown
loose_innodb_buffer_pool_load_at_startup
innodb_fast_shutdown=0
```


[pt-query-digest](https://www.percona.com/downloads/percona-toolkit/LATEST/)を使ってスローログを集計・解析する。
何度かベンチマークを実行したのち、以下の通りpt-query-digestを実行する。
(参考：[スローログの集計に便利な「pt-query-digest」を使ってみよう](https://thinkit.co.jp/article/9617))

```
$ sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ sudo yum install -y percona-toolkit
$ sudo pt-query-digest /var/log/mariadb/mariadb-slow.log | less
# 2.2s user time, 10ms system time, 26.09M rss, 220.54M vsz
# Current date: Tue Apr  7 11:40:51 2020
# Hostname: webapp1
# Files: /var/log/mariadb/mariadb-slow.log
# Overall: 6.02k total, 6 unique, 6.56 QPS, 0.14x concurrency ____________
# Time range: 2020-04-07 11:24:54 to 11:40:11
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           127s    68us      4s    21ms    30ms   123ms     5ms
# Lock time          270ms    13us     3ms    44us    76us    42us    40us
# Rows sent          5.74M       1 187.86k  999.34  964.41   7.44k  964.41
# Rows examine      79.93M      18 751.46k  13.60k  13.78k  45.51k   2.16k
# Query size         1.56M      36     376  271.29  363.48  148.10  363.48

# Profile
# Rank Query ID                       Response time Calls R/Call V/M   Ite
# ==== ============================== ============= ===== ====== ===== ===
#    1 0x106928EA1279DE6B3455E9098... 49.6569 39.0%  4059 0.0122  0.01 SELECT sheets reservations
#    2 0x4D389B62F801B1B5A5A259F6E... 28.7682 22.6%    10 2.8768  0.11 SELECT reservations sheets events
#    3 0x7AEE5F6CB97279972DAB6385B... 19.9705 15.7%    80 0.2496  0.00 SELECT reservations sheets events
#    4 0xC622688A0CBAF834501E39FDE... 19.6552 15.4%    80 0.2457  0.00 SELECT reservations
#    5 0x913E4129111DC84B146C822DC...  9.1855  7.2%    80 0.1148  0.00 SELECT reservations sheets
# MISC 0xMISC                          0.2160  0.2%  1710 0.0001   0.0 <1 ITEMS>

# Query 1: 4.44 QPS, 0.05x concurrency, ID 0x106928EA1279DE6B3455E90984B94D6B at byte 542904
# Scores: V/M = 0.01
# Time range: 2020-04-07 11:24:54 to 11:40:08
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         67    4059
# Exec time     38     50s     4ms    70ms    12ms    24ms     9ms     6ms
# Lock time     73   198ms    35us     3ms    48us    80us    49us    40us
# Rows sent     67   3.87M    1000    1000    1000    1000       0    1000
# Rows examine  35  28.62M   1.95k  14.32k   7.22k  13.78k   5.75k   2.27k
# Query size    93   1.46M     376     376     376     376       0     376
# String:
...skipping...
#    SHOW CREATE TABLE `torb`.`reservations`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT sheets.id AS id, sheets.`rank` AS `rank`, sheets.num AS num, sheets.price AS price, res
ervations.user_id AS user_id, reservations.reserved_at AS reserved_at
        FROM sheets LEFT OUTER JOIN reservations ON sheets.id = reservations.sheet_id
        AND reservations.event_id = 10
        AND reservations.canceled_at IS NULL
        ORDER BY sheets.`rank`, sheets.num\G

# Query 2: 0.01 QPS, 0.03x concurrency, ID 0x4D389B62F801B1B5A5A259F6E743725C at byte 1649912
# Scores: V/M = 0.11
# Time range: 2020-04-07 11:24:59 to 11:40:11
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      10
# Exec time     22     29s      2s      4s      3s      3s   558ms      3s
# Lock time      0   647us    41us   185us    64us    89us    40us    44us
# Rows sent     31   1.83M 187.32k 187.86k 187.59k 182.98k       0 182.98k
# Rows examine   9   7.33M 749.28k 751.46k 750.37k 717.31k       0 717.31k
# Query size     0   2.50k     256     256     256     256       0     256
# String:
# Databases    torb
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `torb` LIKE 'reservations'\G
#    SHOW CREATE TABLE `torb`.`reservations`\G
#    SHOW TABLE STATUS FROM `torb` LIKE 'sheets'\G
#    SHOW CREATE TABLE `torb`.`sheets`\G
#    SHOW TABLE STATUS FROM `torb` LIKE 'events'\G
#    SHOW CREATE TABLE `torb`.`events`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.id AS event_id, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id ORDER BY reserved_at ASC FOR UPDATE\G
（以下、省略）
```

Query1,2についてEXPLAINしてみる

```
$ mysql torb -uroot

MariaDB [torb]> EXPLAIN SELECT sheets.id AS id, sheets.`rank` AS `rank`, sheets.num AS num, sheets.price AS price, reservations.user_id AS user_id, reservations.reserved_at AS reserved_at         FROM sheets LEFT OUTER JOIN
reservations ON sheets.id = reservations.sheet_id         AND reservations.event_id = 10         AND reservations.canceled_at IS NULL         ORDER BY sheets.`rank`, sheets.num;
+------+-------------+--------------+------+---------------------------+---------------------------+---------+----------------------+------+----------------+
| id   | select_type | table        | type | possible_keys             | key                       | key_len | ref                  | rows | Extra          |
+------+-------------+--------------+------+---------------------------+---------------------------+---------+----------------------+------+----------------+
|    1 | SIMPLE      | sheets       | ALL  | NULL                      | NULL                      | NULL    | NULL                 |  974 | Using filesort |
|    1 | SIMPLE      | reservations | ref  | event_id_and_sheet_id_idx | event_id_and_sheet_id_idx | 8       | const,torb.sheets.id |    6 | Using where    |
+------+-------------+--------------+------+---------------------------+---------------------------+---------+----------------------+------+----------------+

MariaDB [torb]> EXPLAIN SELECT r.*, s.rank AS sheet_rank, s.num AS sheet_num, s.price AS sheet_price, e.id AS event_id, e.price AS event_price FROM reservations r INNER JOIN sheets s ON s.id = r.sheet_id INNER JOIN events e ON e.id = r.event_id ORDER BY reserved_at ASC FOR UPDATE;
+------+-------------+-------+------+---------------------------+---------------------------+---------+---------------------+------+------------------------------------+
| id   | select_type | table | type | possible_keys             | key                       | key_len | ref                 | rows | Extra                              |
+------+-------------+-------+------+---------------------------+---------------------------+---------+---------------------+------+------------------------------------+
|    1 | SIMPLE      | e     | ALL  | PRIMARY                   | NULL                      | NULL    | NULL                |   21 | Using temporary; Using filesort    |
|    1 | SIMPLE      | s     | ALL  | PRIMARY                   | NULL                      | NULL    | NULL                |  974 | Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | r     | ref  | event_id_and_sheet_id_idx | event_id_and_sheet_id_idx | 8       | torb.e.id,torb.s.id |    6 |                                    |
+------+-------------+-------+------+---------------------------+---------------------------+---------+---------------------+------+------------------------------------+
```

`Using filesort`から、Query1のsheetsテーブル、Query2のeventsテーブルでインデックスが使われずクイックソートされているのがわかる。
（参考：[MySQLのfilesortは何ソートで行われているのか](https://blog.shibayu36.org/entry/2017/04/12/193000)）
（参考：[漢のコンピュータ道 Using filesort](http://nippondanji.blogspot.com/2009/03/using-filesort.html)）
sheets, events, reservationsテーブルのインデックスを確認してみるが、以下の通りにすでに張られていた。
上記二つ目の参考によると「全てのテーブルをJOINしてからFilesortをするパターン」に該当しているとみられる。
そのため、sheetsのrank,numの複合インデックスはすでに張られていたが、1つ目のEXPLAINで利用されてなかった（と思う）。
対応としてはクエリの変更が必要となるので、DB側でのインデックス作成などでの対応は諦める。

>Filesortは複数のテーブルを一度にソートする事は出来ない。従って複数のテーブルをJOINする場合には、ソートを行うタイミングには2つの場合が考えられる。JOINの最初のテーブルをソートしてからJOINするか、もしくはJOINをした結果をソートするかである。前者の場合はさらにソート処理をFilesortを用いるかインデックスを利用するかという2通りに分けられるので、計3通りの手法がMySQLには実装されている。（JOINした結果にはインデックスがないので、JOINした結果に対してソートするときは常にFilesortである。）

```
MariaDB [torb]> show index from sheets;
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sheets |          0 | PRIMARY       |            1 | id          | A         |         974 |     NULL | NULL   |      | BTREE      |         |               |
| sheets |          0 | rank_num_uniq |            1 | rank        | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| sheets |          0 | rank_num_uniq |            2 | num         | A         |         974 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [torb]> show index from events;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| events |          0 | PRIMARY  |            1 | id          | A         |          21 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

MariaDB [torb]> show index from reservations;
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name                  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| reservations |          0 | PRIMARY                   |            1 | id          | A         |      191277 |     NULL | NULL   |      | BTREE      |         |               |
| reservations |          1 | event_id_and_sheet_id_idx |            1 | event_id    | A         |         199 |     NULL | NULL   |      | BTREE      |         |               |
| reservations |          1 | event_id_and_sheet_id_idx |            2 | sheet_id    | A         |         199 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+---------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
```


# 複数台構成にする
webapp1: Web/APサーバ（nginx, webapp)
webapp2: APサーバ（webapp)
webapp3: DBサーバ
としてwebapp1へのアクセスをnginxがwebapp1/2へ振り分け、DBはwebapp3（名前が適切じゃないですが）へアクセスするようにする

webapp1のnginxの設定変更（ロードバランシング）

```/etc/nginx/nginx.conf(変更点のみ)
 http {
+    upstream webapps {
+        least_conn;
+        server 192.168.100.21:8080;
+        server 192.168.100.22:8080;
+    }
----
-               proxy_pass http://127.0.0.1:8080;
+               proxy_pass http://webapps;
```

DB接続先（env.sh）の修正

```
[isucon@webapp1 ~]$ vi /home/isucon/torb/webapp/env.sh 
DB_HOST=192.168.100.3
[isucon@webapp2 ~]$ vi /home/isucon/torb/webapp/env.sh 
DB_HOST=192.168.100.3
```

DB初期化スクリプトの修正
ベンチマークの中で実行されるDB初期化スクリプトがローカルホストに対して実行になっていたので、DBサーバ（webapp3）に向くように修正

```
[isucon@webapp1 ~]$ pwd
/home/isucon/torb/db
[isucon@webapp1 ~]$ diff init.sh.org init.sh
2c2
<
---
> DB_HOST=192.168.100.3
9,10c9,10
< mysql -uisucon -e "DROP DATABASE IF EXISTS torb; CREATE DATABASE torb;"
< mysql -uisucon torb < "$DB_DIR/schema.sql"
---
> mysql -h $DB_HOST -uisucon -e "DROP DATABASE IF EXISTS torb; CREATE DATABASE torb;"
> mysql -h $DB_HOST -uisucon torb < "$DB_DIR/schema.sql"
18,20c18,20
< mysql -uisucon torb -e 'ALTER TABLE reservations DROP KEY event_id_and_sheet_id_idx'
< gzip -dc "$DB_DIR/isucon8q-initial-dataset.sql.gz" | mysql -uisucon torb
< mysql -uisucon torb -e 'ALTER TABLE reservations ADD KEY event_id_and_sheet_id_idx (event_id, sheet_id)'
---
> mysql -h $DB_HOST -uisucon torb -e 'ALTER TABLE reservations DROP KEY event_id_and_sheet_id_idx'
> gzip -dc "$DB_DIR/isucon8q-initial-dataset.sql.gz" | mysql -h $DB_HOST -uisucon torb
> mysql -h $DB_HOST -uisucon torb -e 'ALTER TABLE reservations ADD KEY event_id_and_sheet_id_idx (event_id, sheet_id)'
```


mysqlのアクセス許可設定

```
[isucon@webapp3 ~]$ mysql -u root
MariaDB [(none)]> grant all privileges  on *.* to 'isucon'@'192.168.100.1' identified by 'isucon';
MariaDB [(none)]> grant all privileges  on *.* to 'isucon'@'192.168.100.2' identified by 'isucon';
```

結果としてはスコアは上がらず、この状態ではまだDB接続回りにボトルネックがあると思われる(?)
むしろ、複数台にした後はベンチマーク中のバリデーションチェックでエラーとなることが多かった。
DB接続のロック競合や複数サーバからのクエリの不整合によるものか(?)
とりあえず今回はここまで。

#他にできること
いろいろ調べたところ皆さん以下のポイントを改善・確認したりしていたようです

- MariaDB
    - 基本的なチューニング
    - MariaDB 5.5からMySQL 8.0にアップグレード
- app.py関連
    - `render_report_csv(reports)`のCSV書き出し処理をStringIOからFlaskのストリーミングテンプレートへ変更
    - シートランク毎の予約済み席数を返すクエリが遅いため、新たにテーブルを作成（event_id, rank, reserved_sheets_numといったカラム）
    - それに合わせてreservationsテーブルでINSERT, UPDATE, DELETE している箇所は上の新しいテーブルで処理するように修正
    - sheetのidからrank, num(ランク毎のid)を返す関数を定義して、 sheetsテーブルの内容をアプリに埋め込む
    - jsonを返す時のパーサを`return flask.jsonify(user)`からpython-rapidjsonへ変更(?)
    - 予約時の席のランダム選択の際のFOR UPDATE（ロック）の範囲修正（該当event_idの空き席すべてをロックしているので、ランダムな一つのみロックすればよい）
    - `SELECT * FROM sheets WHERE id NOT IN (SELECT sheet_id FROM reservations WHERE event_id = ? AND canceled_at IS NULL FOR UPDATE) AND rank = ? ORDER BY RAND() LIMIT 1` の部分
 - curlで返るデータの確認 （`curl 127.0.0.1/api/events | jq '.'`など）
- メモリキャッシュ(redisなど)の活用（セッション）
- 複数台構成（役割だけでなくAPのエンドポイント単位で分けたり）
- 複数台のタスク実行に[Fabric](http://www.fabfile.org/)を利用