## 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
Sign in with Wallet
Connect another wallet