ISUCON8 予選問題やってみた

はじめに

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

参考にさせていただいたページ

公式情報

• 予選レギュレーション
• 予選マニュアル
• コード群
• 解説と講評
• 関連エントリまとめ

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

• くじらにっき++ ~ISUCON8 の予選問題を復習した~
• REQLY開発ブログ ~初出場3人でISUCON8予選を学生枠1位(?)で通過しました~
• ぶていのログでぶログ ~ISUCON8に参加した~
• k5342.hatenablog.com ~ISUCON8 予選に参加した~
• Qiita ~死闘の果てに ISUCON 8 予選を全体７位で突破した記録~
• 己の不学を恥じる

前提

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

ベンチマーク初回実行

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

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 サーバのアクセスログを集計するツールを作りました

[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位(?)で通過しました
参考：ISUCON8 の予選問題を復習した

修正箇所

• 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を使い倒す】意外と知らない？？引数の様々な使い方とは？

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を使ってスローログを集計・解析する。
何度かベンチマークを実行したのち、以下の通りpt-query-digestを実行する。
(参考：スローログの集計に便利な「pt-query-digest」を使ってみよう)

$ 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は何ソートで行われているのか）
（参考：漢のコンピュータ道 Using filesort）
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を利用