ISUCON5予選を復習してみた

ISUCON5予選の復習をしてみる。
リポジトリはhttps://github.com/nel215/isucon5q-practice

# 環境

途中まで1CPU/1GB、細かい修正は本番と同じスペック。

# 初期状態

```
    "success" : 133
    "redirect" : 47
```

# SQLの改善

pt-query-digestでスロークエリを集計して重い順に改善していく。

## firendsMap

最初は`GetIndex`の`friendsMap`を取ってくる部分。

```
    rows, err = db.Query(`SELECT * FROM relations WHERE one = ? OR another = ? ORDER BY created_at DESC`, user.ID, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    friendsMap := make(map[int]time.Time)
    for rows.Next() {
        var id, one, another int
        var createdAt time.Time
        checkErr(rows.Scan(&id, &one, &another, &createdAt))
        var friendID int
        if one == user.ID {
            friendID = another
        } else {
            friendID = one
        }
        if _, ok := friendsMap[friendID]; !ok {
            friendsMap[friendID] = createdAt
        }
    }
```

INSERT文 `db.Exec(`INSERT INTO relations (one, another) VALUES (?,?), (?,?)`, user.ID, another.ID, another.ID, user.ID)` を見ると関係が対象になるように入っている。本番はこちらを`one`<`another`となるようにSQLを変更したが、このままにしておくとSELECTの方が`one`だけでよくなる。また`SELECT *`ではなく`another`と`createdAt`があればよいこともわかるのでそうする。初期状態だとINDEXが`(one, another)`しかないので、ソートが発生しないように`(one, created_at)`を追加する。以下、変更後。

```
    rows, err = db.Query(`SELECT another, created_at FROM relations WHERE one = ? ORDER BY created_at DESC`, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    friendsMap := make(map[int]time.Time)
    for rows.Next() {
        var friendID int
        var createdAt time.Time
        checkErr(rows.Scan(&friendID, &createdAt))
        if _, ok := friendsMap[friendID]; !ok {
            friendsMap[friendID] = createdAt
        }
    }
```
スコアは再起動忘れていたので不明。
## footprints
```
    rows, err = db.Query(`SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) AS updated
FROM footprints
WHERE user_id = ?
GROUP BY user_id, owner_id, DATE(created_at)
ORDER BY updated DESC
LIMIT 10`, user.ID)
    if err != sql.ErrNoRows {
        checkErr(err)
    }
    footprints := make([]Footprint, 0, 10)
    for rows.Next() {
        fp := Footprint{}
        checkErr(rows.Scan(&fp.UserID, &fp.OwnerID, &fp.CreatedAt, &fp.Updated))
        footprints = append(footprints, fp)
    }
    rows.Close()
```

とりあえずINDEXがない状態だったので`(user_id, owner_id)`を追加する。

スコア
```
    "success" : 214,
    "redirect" : 78,
```

## isFriend

```
func isFriend(w http.ResponseWriter, r *http.Request, anotherID int) bool {
    session := getSession(w, r)
    id := session.Values["user_id"]
    row := db.QueryRow(`SELECT COUNT(1) AS cnt FROM relations WHERE (one = ? AND another = ?) OR (one = ? AND another = ?)`, id, anotherID, anotherID, id)
    cnt := new(int)
    err := row.Scan(cnt)
    checkErr(err)
    return *cnt > 0
}
```
これも`friendsMap`と同様に`(one = ? AND another = ?)`片方だけでよい。
スコア(app再起動していないことに気付いたのでfrinedsMapの変更もここで反映)
```
    "success" : 376,
    "redirect" : 130,
```
# GetFriends

friendsMapと同じなので省略

スコア
```
    "success" : 469,
    "redirect" : 151,
```

# isFriend再び

```
SELECT COUNT(1) AS cnt FROM relations WHERE (one = 4949 AND another = 4909)\G
```

クエリは上記だが、これ以上は厳しいので結果をキャッシュする。これは本番もやった。
キャッシュにはhttp://github.com/pmylund/go-cache を使った。
`key`は`fmt.Sprintf("rel-%d-%d", min(one, another), max(one, another))`とし、`isFriend`で`Get`と`Set`、`PostFriends`で`Set`するようにした。

スコア
```
    "success" : 1423,
    "redirect" : 412,
```

# entriesOfFriends

```
SELECT * FROM entries ORDER BY created_at DESC LIMIT 1000\G
```

1000件取得した後に`entries.user_id`と関係のある`entry`だけをフィルタリングしてるため、そこをSQLだけでできるようにする。随時JOINしていると遅すぎるので、以下の様なテーブルを用意して、初期化時とINSERT時に更新するようにする。

```
CREATE TABLE IF NOT EXISTS entry_ids_of_friends (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int NOT NULL,
  `entry_id` int NOT NULL,
  `created_at` timestamp NOT NULL,

  KEY `user_id` (`user_id`,`created_at`)
) DEFAULT CHARSET=utf8;
```

`entries`を取得する際のSQLは下記の様になる。

```
    rows, err = db.Query(`
SELECT entries.id, entries.user_id, private, body, entries.created_at FROM entries
JOIN entry_ids_of_friends ON entries.id = entry_ids_of_friends.entry_id
WHERE entry_ids_of_friends.user_id = ?
ORDER BY entry_ids_of_friends.created_at DESC LIMIT 10`, user.ID)
```

スコア
```
    "success" : 2257,
    "redirect" : 627,
```

# getUser

```
SELECT * FROM users WHERE id = 3994\G
```

これもキャッシュする。

スコア
```
    "success" : 3610,
    "redirect" : 1017,
```

# commentsOfFriends

友達かどうかで弾いているところはentriesOfFriendsと同じ。
N+1クエリになっているのでJOINする

スコア1
```
    "success" : 5522,
    "redirect" : 1531,
```

# commentsForMe

```
SELECT c.id AS id, c.entry_id AS entry_id, c.user_id AS user_id, c.comment AS comment, c.created_at AS created_at
FROM comments c
JOIN entries e ON c.entry_id = e.id
WHERE e.user_id = 4885
ORDER BY c.created_at DESC
LIMIT 10\G
```

これも(user_id, created_at)をKEYにした関連テーブルを用意する。

スコア
```
    "success" : 6267,
    "redirect" : 1762,
```

# ミドルウェア周りの修正

ここから本番と同じスペックのサーバーにした。

## my.cnf、nginx.conf、sysctl.conf変更
秘伝のタレに置き換える。

スコア
```
    "success" : 15436,
    "redirect" : 4237,
```

## mysqlにsocketで繋ぐ
`tcp`から`unix domain socket`に変更する。
スコア
```
    "success" : 17437,
    "redirect" : 4839,
```

## GOGC=1000, GOMAXPROC=16
環境変数で設定する。
スコア
```
    "success" : 17948,
    "redirect" : 4969,
```

# まとめ

まだ改善の余地はいろいろあるけど時間がかけられなくなったので一旦終了。
`success+0.1*redirect`がランキングのスコアになるので約18500点で予選は突破できる点数になった。
親-孫の関係があったときに親のIDで取ってきて孫の要素で並べ替える場合(entriesOfFriendsの部分)のインデックスの作り方が非常に勉強になった。