ISUCON練習日記2021-02-26 ISUCON10予選

ISUCON10予選問題で練習した。色々忘れないようにメモ。

generated columns追加

とりあえず入れてみる popularity_desc INTEGER AS (-popularity) NOT NULL,

2021/02/22 19:35:29 bench.go:102: verify failed
{"pass":false,"score":0,"messages":[{"text":"GET /api/estate/:id: レスポンスが不正です","count":5},{"text":"GET /api/estate/low_priced: レスポンスが不正です","count":1},{"text":"GET /api/estate/search: レスポンスが不正です","count":5},{"text":"GET /api/recommended_estate/:id: レスポンスが不正です","count":5},{"text":"POST /api/estate/nazotte: レスポンスが不正です","count":5},{"text":"登録済み物件の詳細取得に失敗しました","count":1}],"reason":"スコアが0点を下回りました","language":"go"}

単に入れただけだとダメ。アプリログにエラー出てる。

[19:52:02 isucon@ix ~]$ sudo systemctl status isuumo.go.service
● isuumo.go.service - isuumo.go
   Loaded: loaded (/etc/systemd/system/isuumo.go.service; enabled; vendor preset: enabled)
   Active: active (running) since Mon 2021-02-22 19:35:22 UTC; 16min ago
  Process: 25939 ExecStop=/bin/kill -s QUIT $MAINPID (code=exited, status=0/SUCCESS)
 Main PID: 25940 (isuumo)
    Tasks: 5 (limit: 2333)
   CGroup: /system.slice/isuumo.go.service
           └─25940 /home/isucon/isuumo/webapp/go/isuumo

Feb 22 19:35:29 ix isuumo[25940]: {"time":"2021-02-22T19:35:29.141867316Z","level":"ERROR","prefix":"echo","file":"main.go","line":"733","message":"Database Execution error : missing destination name popularity_desc in *main.Estate"}

733行目見ると代入失敗っぽい。struct定義にPopularityDesc int64 db:"popularity_desc" json:"-"``を追加する。

   var estate Estate
    err = db.Get(&estate, "SELECT * FROM estate WHERE id = ?", id)
    if err != nil {
        if err == sql.ErrNoRows {
            c.Echo().Logger.Infof("getEstateDetail estate id %v not found", id)
            return c.NoContent(http.StatusNotFound)
        }
        c.Echo().Logger.Errorf("Database Execution error : %v", err)
        return c.NoContent(http.StatusInternalServerError)
    }

SQLコメント

GoでSQLにトレーシングコメントを埋め込んで実行する | おそらくはそれさえも平凡な日々

これやってみたいなと思ったけど、sqlx使ってるコードに大してあまり書き換えずにやる方法あるのかな？分からなかった。記事はdatabase/sql、ISUCON10予選問題はgithub.com/jmoiron/sqlx。

generated columns使って5.7でも有効なインデックスを作成

インデックス追加

,INDEX popularity_desc_id(popularity_desc,id)

追加前後のexplain f:id:kavohtn:20210225010359j:plain

これをやったあとは"score":607くらいになって、スロークエリの上位は椅子と不動産に条件色々くっつけるやつと位置情報の３種になっている。

]$ cat ~/slowq-state.log |sort
/*[11] L1031 */SELECT * FROM estate WHERE latitude <= 36.53227509710664 AND latitude >= 35.58900608516611 AND longitude <= 137.92007832283616 AND longitude >= 137.34442150965003 ORDER BY popularity_desc ASC, id ASC\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE color = 'ピンク' AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE depth >= 80 AND depth < 110 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE height < 80 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE height >= 110 AND height < 150 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE height >= 150 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE kind = 'エルゴノミクス' AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE price < 3000 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE price >= 15000 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE price >= 9000 AND price < 12000 AND stock > 0\G
/*[3] L586 */SELECT COUNT(*) FROM chair WHERE width >= 110 AND width < 150 AND stock > 0\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_height < 80\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_height >= 150\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_height >= 80 AND door_height < 110\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_width < 80\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_width >= 110 AND door_width < 150\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE door_width >= 150\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE rent >= 150000\G
/*[7] L909 */SELECT COUNT(*) FROM estate WHERE rent >= 50000 AND rent < 100000\G