---
# System prepended metadata

title: Untitled

---

## mysql slow log
```
root@node1:/etc/nginx# mysqldumpslow -s t /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 502  Time=0.27s (134s)  Lock=0.00s (0s)  Rows=49.0 (24598), isucari[isucari]@localhost
  SELECT * FROM items WHERE status IN
  /* QUERY:getNewItems */
  ('S','S') AND (created_at < 'S'  OR (created_at <= 'S' AND id < N))
  ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 427  Time=0.26s (112s)  Lock=0.00s (0s)  Rows=49.0 (20923), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 729  Time=0.14s (105s)  Lock=0.00s (0s)  Rows=28.1 (20499), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems2 */
  WHERE seller_id = N AND status IN ('S','S','S') ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 596  Time=0.16s (94s)  Lock=0.00s (0s)  Rows=28.2 (16807), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `seller_id` = N AND `status` IN ('S','S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 517  Time=0.15s (75s)  Lock=0.00s (0s)  Rows=33.0 (17042), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems */
  WHERE seller_id = N AND status IN ('S','S','S') AND (created_at < 'S'  OR (created_at <= 'S' AND id < N)) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 492  Time=0.14s (66s)  Lock=0.00s (0s)  Rows=8.8 (4341), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getTransactions2 */
  WHERE (seller_id = N OR buyer_id = N) AND status IN ('S','S','S','S','S') ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 432  Time=0.15s (64s)  Lock=0.00s (0s)  Rows=33.0 (14240), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `seller_id` = N AND `status` IN ('S','S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 264  Time=0.24s (63s)  Lock=0.00s (0s)  Rows=49.0 (12936), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N) AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 303  Time=0.21s (62s)  Lock=0.00s (0s)  Rows=49.0 (14847), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems2 */
  WHERE status IN ('S','S') AND category_id IN (N, N, N, N) AND (created_at < 'S'  OR (created_at <= 'S' AND id < N)) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 412  Time=0.14s (55s)  Lock=0.00s (0s)  Rows=8.7 (3597), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE (`seller_id` = N OR `buyer_id` = N) AND `status` IN ('S','S','S','S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 225  Time=0.21s (48s)  Lock=0.00s (0s)  Rows=49.0 (11025), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N, N) AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 238  Time=0.16s (38s)  Lock=0.00s (0s)  Rows=49.0 (11662), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems2 */
  WHERE status IN ('S','S') AND category_id IN (N, N, N, N, N, N) AND (created_at < 'S'  OR (created_at <= 'S' AND id < N)) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 223  Time=0.13s (29s)  Lock=0.00s (0s)  Rows=11.0 (2453), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE (`seller_id` = N OR `buyer_id` = N) AND `status` IN ('S','S','S','S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 238  Time=0.12s (27s)  Lock=0.00s (0s)  Rows=11.0 (2618), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getTransactions */
  WHERE (seller_id = N OR buyer_id = N) AND status IN ('S','S','S','S','S') AND (created_at < 'S'  OR (created_at <= 'S' AND id < N)) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 68  Time=0.15s (10s)  Lock=0.00s (0s)  Rows=49.0 (3332), isucari[isucari]@localhost
  SELECT * FROM items WHERE status
  /* QUERY:getNewItems2 */
  IN ('S','S') ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 53  Time=0.16s (8s)  Lock=0.00s (0s)  Rows=49.0 (2597), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 47  Time=0.18s (8s)  Lock=0.00s (0s)  Rows=49.0 (2303), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems3 */
  WHERE status IN ('S','S') AND category_id IN (N, N, N, N) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 45  Time=0.16s (7s)  Lock=0.00s (0s)  Rows=49.0 (2205), isucari[isucari]@localhost
  SELECT * FROM items
  /* QUERY:getNewCategoryItems3 */
  WHERE status IN ('S','S') AND category_id IN (N, N, N, N, N, N) ORDER BY created_at DESC, id DESC
  LIMIT N

Count: 39  Time=0.18s (7s)  Lock=0.00s (0s)  Rows=49.0 (1911), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N) ORDER BY created_at DESC, id DESC LIMIT N

Count: 38  Time=0.18s (6s)  Lock=0.00s (0s)  Rows=49.0 (1862), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N, N) ORDER BY created_at DESC, id DESC LIMIT N

Count: 44  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=5.2 (228), isucari[isucari]@localhost
  SELECT id FROM categories
  /* QUERY:getNewCategoryItems */
  WHERE parent_id=N

Count: 9  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=43.0 (387), isucari[isucari]@localhost
  SELECT * FROM categories
  /* QUERY:getSettings */

Count: 30  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=5.1 (154), isucari[isucari]@localhost
  SELECT id FROM `categories` WHERE parent_id=N

Count: 5  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=43.0 (215), isucari[isucari]@localhost
  SELECT * FROM `categories`
```

## クエリ統計

```
~/src/github.com/rhykw/isucon9 (master)> cat dump.log-1867| jq .query | grep 'QUERY:' | gsed -e 's/^.*QUERY:\([a-zA-Z0-9]*\) .*/\1/' | sort | uniq -c
   1 getReports
  52 getSettings
~/src/github.com/rhykw/isucon9 (master)> cat dump.log-1867| jq .stmt_prepare_query | grep 'QUERY:' | gsed -e 's/^.*QUERY:\([a-zA-Z0-9]*\) .*/\1/' | sort | uniq -c
79491 getCategoryByID
 721 getConfigByName
1691 getItem
 118 getItem2
 118 getItem3
 626 getNewCategoryItems
 601 getNewCategoryItems2
  74 getNewCategoryItems3
  69 getNewItems
  11 getNewItems2
  25 getQRCode
  22 getQRCode2
  48 getTransactions
  96 getTransactions2
1369 getTransactions3
 559 getTransactions4
2066 getUser
33898 getUserSimpleByID
  13 postBump
  13 postBump2
  13 postBump3
  13 postBump4
  13 postBump5
  36 postBuy
  27 postBuy2
  26 postBuy3
  27 postBuy4
  22 postBuy5
  17 postComplete
  17 postComplete2
  17 postComplete3
  17 postComplete4
  17 postComplete5
  17 postComplete6
  17 postComplete7
   7 postItemEdit
   7 postItemEdit2
   3 postItemEdit3
   3 postItemEdit4
  60 postLogin
  31 postSell
  31 postSell2
  31 postSell3
  23 postShip
  19 postShip2
  19 postShip3
  19 postShip4
  19 postShip5
  25 postShipDone
  21 postShipDone2
  21 postShipDone3
  21 postShipDone4
  18 postShipDone5
  18 postShipDone6
```


## Goアプリ

```
root@iZ6we4a6fhkx178c26pna9Z:~# cat /etc/systemd/system/isucari.golang.service
[Unit]
Description = isucon9 qualifier main application in golang

[Service]
WorkingDirectory=/home/isucon/isucari/webapp/go/
EnvironmentFile=/home/isucon/env.sh

ExecStart = /home/isucon/isucari/webapp/go/isucari

Restart   = always
Type      = simple
User      = isucon
Group     = isucon

[Install]
WantedBy = multi-user.target
```

### エンドポイント

```golang
  // API
  mux.HandleFunc(pat.Post("/initialize"), postInitialize)
  mux.HandleFunc(pat.Get("/new_items.json"), getNewItems)
  mux.HandleFunc(pat.Get("/new_items/:root_category_id.json"), getNewCategoryItems)
  mux.HandleFunc(pat.Get("/users/transactions.json"), getTransactions)
  mux.HandleFunc(pat.Get("/users/:user_id.json"), getUserItems)
  mux.HandleFunc(pat.Get("/items/:item_id.json"), getItem)
  mux.HandleFunc(pat.Post("/items/edit"), postItemEdit)
  mux.HandleFunc(pat.Post("/buy"), postBuy)
  mux.HandleFunc(pat.Post("/sell"), postSell)
  mux.HandleFunc(pat.Post("/ship"), postShip)
  mux.HandleFunc(pat.Post("/ship_done"), postShipDone)
  mux.HandleFunc(pat.Post("/complete"), postComplete)
  mux.HandleFunc(pat.Get("/transactions/:transaction_evidence_id.png"), getQRCode)
  mux.HandleFunc(pat.Post("/bump"), postBump)
  mux.HandleFunc(pat.Get("/settings"), getSettings)
  mux.HandleFunc(pat.Post("/login"), postLogin)
  mux.HandleFunc(pat.Post("/register"), postRegister)
  mux.HandleFunc(pat.Get("/reports.json"), getReports)
  // Frontend
  mux.HandleFunc(pat.Get("/"), getIndex)
  mux.HandleFunc(pat.Get("/login"), getIndex)
  mux.HandleFunc(pat.Get("/register"), getIndex)
  mux.HandleFunc(pat.Get("/timeline"), getIndex)
  mux.HandleFunc(pat.Get("/categories/:category_id/items"), getIndex)
  mux.HandleFunc(pat.Get("/sell"), getIndex)
  mux.HandleFunc(pat.Get("/items/:item_id"), getIndex)
  mux.HandleFunc(pat.Get("/items/:item_id/edit"), getIndex)
  mux.HandleFunc(pat.Get("/items/:item_id/buy"), getIndex)
  mux.HandleFunc(pat.Get("/buy/complete"), getIndex)
  mux.HandleFunc(pat.Get("/transactions/:transaction_id"), getIndex)
  mux.HandleFunc(pat.Get("/users/:user_id"), getIndex)
  mux.HandleFunc(pat.Get("/users/setting"), getIndex)
  // Assets
  mux.Handle(pat.Get("/*"), http.FileServer(http.Dir("../public")))
  log.Fatal(http.ListenAndServe(":8000", mux))
 ```


### alp 統計
https://gist.github.com/rhykw/96be3eb4b10d34e0bb512a71792ce62b

