スロークエリを出す

[BBCapMegane]

まずは、mysqlのconfig ファイルを書き換え

[BBCapMegane]

mysqldumpslow -s t slow.log > out.log

Count: 312  Time=0.28s (86s)  Lock=0.00s (0s)  Rows=199.8 (62330), isucon[isucon]@localhost
  SELECT * FROM relations WHERE one = N OR another = N ORDER BY created_at DESC

Count: 228  Time=0.22s (50s)  Lock=0.00s (0s)  Rows=10.0 (2280), isucon[isucon]@localhost
  SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) AS updated
  FROM footprints
  WHERE user_id = N
  GROUP BY user_id, owner_id, DATE(created_at)
  ORDER BY updated DESC
  LIMIT N

Count: 64  Time=0.22s (14s)  Lock=0.00s (0s)  Rows=50.0 (3200), isucon[isucon]@localhost
  SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) as updated
  FROM footprints
  WHERE user_id = N
  GROUP BY user_id, owner_id, DATE(created_at)
  ORDER BY updated DESC
  LIMIT N

Count: 109  Time=0.02s (2s)  Lock=0.00s (0s)  Rows=1000.0 (109000), isucon[isucon]@localhost
  SELECT * FROM entries ORDER BY created_at DESC LIMIT N

Count: 74  Time=0.02s (1s)  Lock=0.00s (0s)  Rows=10.0 (740), isucon[isucon]@localhost
  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 = N
  ORDER BY c.created_at DESC
  LIMIT N

Count: 52  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1000.0 (52000), isucon[isucon]@localhost
  SELECT * FROM comments ORDER BY created_at DESC LIMIT N

Count: 2  Time=0.35s (0s)  Lock=0.00s (0s)  Rows=20.0 (40), isucon[isucon]@localhost
  SELECT * FROM entries WHERE user_id = N ORDER BY created_at DESC LIMIT N

Count: 2  Time=0.31s (0s)  Lock=0.00s (0s)  Rows=20.0 (40), isucon[isucon]@localhost
  SELECT * FROM entries WHERE user_id = N AND private=N ORDER BY created_at DESC LIMIT N

Count: 13  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  #

Count: 7  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=1.0 (7), isucon[isucon]@localhost
  SELECT COUNT(N) AS cnt FROM relations WHERE (one = N AND another = N) OR (one = N AND another = N)

Count: 4  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1.0 (4), isucon[isucon]@localhost
  SELECT * FROM entries WHERE id = N

Count: 5  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO footprints (user_id,owner_id) VALUES (N,N)

Count: 2  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO entries (user_id, private, body) VALUES (N,N,'S')

Count: 1  Time=0.02s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@localhost
  SELECT * FROM entries WHERE id = 'S'

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO comments (entry_id, user_id, comment) VALUES (N,N,'S')

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), isucon[isucon]@localhost
  SELECT * FROM users WHERE id = N

Count: 13  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Prepare

[ShuzoN]

1個目 https://github.com/BBCapMegane/waiwai_isucon5/blob/8e3d4c5183e8ef624b3f2ed34592c719289f65b4/webapp/go/app.go#L673

order by する必要なさそう of the year one, another にindexはる？

    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
        }
    }
    friends := make([]Friend, 0, len(friendsMap))
    for key, val := range friendsMap {
        friends = append(friends, Friend{key, val})
    }
    rows.Close()

[ShuzoN]

top3 GetIndex 関数なのでここが重いのでは

https://github.com/BBCapMegane/waiwai_isucon5/blob/8e3d4c5183e8ef624b3f2ed34592c719289f65b4/webapp/go/app.go#L293

[ShuzoN]

GetProfile も重めのクエリ投げているが2回しか呼び出しないので無視して良さそう

[ShuzoN]

おっせぇ

mysql> explain SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) AS updated FROM footprints WHERE user_id = 1 GROUP BY user_id, owner_id, DATE(created_at) ORDER BY updated DESC LIMIT 100;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                        |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | footprints | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 452002 |    10.00 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

[ShuzoN]

一旦はindexはるとかでいいかな

alter table footprints add index bakusoku_na_user_index(user_id);

[ShuzoN]

using temporary

http://aeroastro.hatenablog.com/entry/2016/11/16/014338

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

クエリを解決するために、MySQLは結果を保持するための一時テーブルを作成する必要があります。これは通常、クエリに列のリストが異なるGROUP BY句とORDER BY句が含まれている場合に発生します。

[ShuzoN]

Using filesort と Using temporary 周りの話

http://nippondanji.blogspot.com/2009/03/using-filesort.html

[ShuzoN]

テンポラリー使ってるのはメモリを多く使うのでfileに書き出してソート回すかららしい

[ShuzoN]

mysql> explain SELECT user_id, owner_id, DATE(created_at) AS date, MAX(created_at) AS updated FROM footprints WHERE user_id = 1 GROUP BY user_id, owner_id, DATE(created_at) ORDER BY updated DESC LIMIT 100;

これさ、452002件に対してorder byするの無駄だよな

limitかかるの最後だし。

https://qiita.com/suzukito/items/edcd00e680186f2930a8

[ShuzoN]

goでorder byすればよくね？

[ShuzoN]

1. user_id にindex
2. order byをgoで実装

この2つかなぁ

[ShuzoN]

alter table footprints add index bakusoku_index_user_id(user_id);
alter table footprints add index bakusoku_index_created_at(created_at);