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の部分)のインデックスの作り方が非常に勉強になった。

ISUCON5予選を復習してみた

環境