pixiv private isucon 2016 鐃緒申略 (2/5)
鐃緒申略鐃緒申鐃緒申鐃緒申鐃緒申:
鐃緒申鐃竣ワ申鐃緒申匹鐃�
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃宿わ申鐃宿みなわ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申旅鐃緒申鐃緒申鐃緒申聴鐃緒申鐃緒申討鐃緒申鐃緒申泙鐃緒申鐃�
- 鐃春レー鐃緒申錙種申鐃緒申鐃� goji 鐃緒申箸辰討鐃緒申襦� goji 鐃塾ワ申鐃緒申鐃夙を見わ申鐃銃縁申鐃縮常申鐃緒申膿鐃緒申鐃緒申鐃緒申侫譟種申鐃緒ー鐃緒申鐃緒申鐃緒申直鐃緒申鐃緒申鐃淳わ申鐃緒申鐃緒申鐃緒申鐃宿￥申鐃緒申鐃緒申聾鼎鐃緒申鐃緒申鐃夙ってわ申澆鐃緒申鐃緒申任鐃緒申鐃�
- 鐃緒申鐃獣ワ申鐃緒申鐃緒申 Gorilla 鐃塾ライ鐃瞬ワ申鐃緒申 memcached store 鐃緒申鐃夙み刻申錣誌申討鐃緒申鐃�
- 鐃銃ワ申廛譟種申箸鐃緒申鐃緒申鐃緒申鵐阿里鐃緒申咾縫鐃緒申鐃術ワ申鐃暑し鐃銃わ申里鐃緒申鼎鐃緒申鐃緒申鐃�
- 鐃緒申鐃緒申鐃夙のユー鐃緒申鐃緒申鐃薯いわ申鐃緒申鐃緒申鐃醇ー鐃緒申鐃緒申鐃銃￥申鐃瞬ルか鐃緒申鐃緒申鐃縦ワ申辰討鐃塾わ申 JOIN 鐃緒申鐃緒申鐃緒申奪鐃緒申紊�鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申
- 鐃緒申鐃緒申鐃叔￥申鐃緒申鐃緒申 MySQL 鐃緒申鐃粛っ刻申鐃緒申任鐃塾は￥申鐃春ワ申鐃緒申鐃緒申暴个鐃宿�鐃竣わ申鐃緒申鐃所そ鐃緒申鐃緒申(鐃春ワ申鐃緒申鐃緒申覆鐃緒申鐃緒申KB鐃緒申鐃緒申鐃宿わ申任鐃緒申屬鐃緒申襪縁申鐃宿￥申DB鐃緒申鐃夙器申単鐃緒申鐃緒申法鐃緒申鐃夙逸申鐃駿わ申鐃緒申鐃塾わ申鐃緒申鐃緒申鐃宿わ申任鐃緒申泙鐃緒申里如鐃緒申鐃緒申鐃祝ワ申鐃緒申鐃緒申鐃緒申鐃緒申鐃処た鐃夙わ申鐃祝ワ申鐃所が足鐃緒申覆鐃緒申覆鐃殉わ申鐃緒申鐃緒申
鐃宿わ申任鐃緒申鐃夙駕申鐃緒申鐃緒申鐃緒申鐃緒申鐃楯ワ申鐃緒申箸鐃緒申鐃緒申鐃緒申鐃緒申鐃出てわ申鐃緒申鐃塾の￥申鐃緒申鐃緒申鐃淑りあ鐃緒申鐃緒申鐃緒申鐃緒申造鐃緒申鐃緒申鐃緒申鐃夙バワ申鐃獣わ申鐃夙わ申鐃祝削申鐃緒申泙鐃緒申鐃緒申呂鐃緒申鐃熟器申鐃緒申的鐃緒申鐃緒申鐃緒申肇椒肇鐃粛ッワ申鐃祝絞っわ申鐃緒申鐃遵ー鐃祝ングでわ申鐃緒申
鐃叔緒申鐃�DB鐃塾ワ申鐃粛ワ申鐃緒申鐃緒申鐃竣￥申鐃緒申鐃緒申鐃緒申任鐃緒申討覆鐃緒申里鐃緒申鐃緒申蠅件申討鐃緒申鐃緒申泙鐃緒申腓�鐃緒申2鐃緒申鐃緒申鐃淑わ申任鐃緒申鐃淑にわ申鐃緒申鐃緒申鐃藷コネワ申鐃緒申鐃緒申鵑△辰討鐃緒申鐃緒申鐃淑わ申鐃熟ワ申鐃緒申鐃夙りあ鐃緒申鐃緒申鐃緒申鐃順い多鐃緒申鐃�8鐃祝わ申鐃銃わ申鐃緒申鐃殉わ申鐃緒申
+ db.SetMaxOpenConns(8)
+ db.SetMaxIdleConns(8)
defer db.Close()
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃宿わ申
鐃緒申鐃緒申鐃緒申鐃術意わ申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃竣ツ￥申鐃緒申兵鐃緒申遒件申討發わ申鐃緒申任鐃緒申鐃緒申鐃緒申鐃緒申里鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃�ISUCON鐃塾居申鐃緒申鐃緒申探鐃緒申鐃銃みわ申鐃緒申匹鐃緒申帖鐃緒申襪�鐃述介さわ申討鐃緒申鐃熟わ申鐃叔わ申鐃祝を、ワ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃巡ア鐃竣ワ申鐃�URL鐃暑ー鐃銃ワ申鐃藷グわ申鐃緒申鐃緒申鮖温佑縫鐃緒申鐃緒申鐃緒申泪鐃緒申鐃緒申鐃緒申泙鐃緒申腓�鐃緒申
鐃粛の常申鐃熟種申鐃庶ス鐃緒申鐃緒申廛箸如鐃緒申鐃緒申里茲�鐃淑緒申鐃竣をしまわ申鐃緒申鐃緒申
Request by count 8028 GET /image/* 644 GET / 551 GET /js/jquery-2.2.0.js 551 GET /css/style.css 551 GET /js/jquery.timeago.ja.js 551 GET /favicon.ico 551 GET /js/jquery.timeago.js 551 GET /js/main.js 450 POST /login 299 GET /posts/* 235 GET /@user 130 POST / 98 GET /login 90 POST /comment 79 POST /register 78 GET /admin/banned 56 GET /posts?max_created_at= 50 GET /logout 1 GET /initialize Request by total time (total avg [sec]) 198.379 0.308041925466 GET / 168.858 0.021033632287 GET /image/* 47.605 0.202574468085 GET /@user 34.491 0.0766466666667 POST /login 30.444 0.543642857143 GET /posts?max_created_at= 15.095 0.0504849498328 GET /posts/* 7.068 0.0894683544304 POST /register 6.7 0.0515384615385 POST / 6.51 0.0118148820327 GET /favicon.ico 6.483 0.0117658802178 GET /js/jquery-2.2.0.js 5.784 0.010497277677 GET /js/jquery.timeago.js 5.213 0.0094609800363 GET /js/jquery.timeago.ja.js 5.095 0.00924682395644 GET /css/style.css 4.997 0.00906896551724 GET /js/main.js 2.478 0.0275333333333 POST /comment 2.257 0.0230306122449 GET /login 1.314 0.0168461538462 GET /admin/banned 1.131 0.02262 GET /logout 0.061 0.061 GET /initialize Request by out bytes (total avg) 2485959550 309661 GET /image/* 142499069 258619 GET /js/jquery-2.2.0.js 12206011 18953 GET / 4287247 18243 GET /@user 4105501 7451 GET /js/jquery.timeago.js 1912146 34145 GET /posts?max_created_at= 1108574 3707 GET /posts/* 980229 1779 GET /css/style.css 487635 885 GET /js/main.js 357048 648 GET /js/jquery.timeago.ja.js 195356 434 POST /login 177772 1814 GET /login 149872 272 GET /favicon.ico 34286 434 POST /register 23530 181 POST / 22200 444 GET /logout 16833 187 POST /comment 13104 168 GET /admin/banned 161 161 GET /initialize
鐃緒申鐃緒申嚢佑鐃緒申鐃塾は￥申鐃緒申房鐃緒申鐃�2鐃緒申鐃叔わ申鐃緒申
- 鐃夙ップペ￥申鐃緒申鐃緒申
/image/鐃祝掛わ申鐃緒申鐃緒申屬鐃緒申鐃緒申崕鼎鐃緒申鐃緒申辰縫肇奪廛據鐃緒申鐃緒申鐃叔緒申縫鐃緒申紂種申縫鵐阿鐃緒申鐃塾わ申鐃宿わ申鐃緒申鐃緒申鐃緒申 - 鐃俊逸申鐃�
/image/鐃緒申鐃緒申鐃緒申的鐃緒申鐃俊逸申優奪鐃緒申砲覆鐃処う鐃緒申鐃獣わ申鐃初、鐃緒申鐃緒申鐃熟再逸申鐃縮わ申鐃祝わ申鐃緒申鐃塾で￥申鐃舜ッワ申鐃緒申鐃緒申適鐃准わ申鐃緒申鐃所す鐃緒申丱鐃緒申薀わ申鐃緒申鐃夙わ申 "304 Not Modified" 鐃緒申鐃瞬わ申鐃緒申茲�鐃祝なっわ申鐃順き鐃淑ブレー鐃緒申鐃緒申鐃暑ー鐃緒申鐃緒申鐃暑か鐃盾し鐃緒申覆鐃緒申鐃�
pprof
鐃夙りあ鐃緒申鐃緒申 app 鐃緒申 CPU 鐃緒申鐃重わ申鐃塾で￥申CPU鐃竣ワ申鐃春ワ申鐃緒申鐃暑か鐃緒申呂鐃殉わ申鐃順う鐃緒申 pprof 鐃塾器申鐃緒申的鐃淑使わ申鐃緒申鐃熟￥申鐃緒申鐃緒申鐃緒申 Blog 鐃緒申颪い鐃緒申里任鐃緒申鐃薯参照わ申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申
diff --git a/app.go b/app.go
index 861ff03..eb52f47 100644
--- a/app.go
+++ b/app.go
@@ -9,6 +9,7 @@ import (
"io/ioutil"
"log"
"net/http"
+ _ "net/http/pprof"
"net/url"
"os"
"os/exec"
@@ -826,6 +827,8 @@ func main() {
+
+ db.SetMaxOpenConns(8)
+ db.SetMaxIdleConns(8)
defer db.Close()
+ go http.ListenAndServe(":3000", nil)
+
goji.Get("/initialize", getInitialize)
goji.Get("/login", getLogin)
goji.Post("/login", postLogin)
$ go tool pprof app http://localhost:3000/debug/pprof/profile
Fetching profile from http://localhost:3000/debug/pprof/profile
Please wait... (30s)
Saved profile in /home/isucon/pprof/pprof.localhost:3000.samples.cpu.001.pb.gz
Entering interactive mode (type "help" for commands)
(pprof) top 10
14440ms of 29650ms total (48.70%)
Dropped 546 nodes (cum <= 148.25ms)
Showing top 10 nodes out of 214 (cum >= 7370ms)
flat flat% sum% cum cum%
2270ms 7.66% 7.66% 8650ms 29.17% runtime.mallocgc
2220ms 7.49% 15.14% 2220ms 7.49% runtime.memclr
2150ms 7.25% 22.39% 2840ms 9.58% runtime.scanobject
1930ms 6.51% 28.90% 2060ms 6.95% syscall.Syscall
1280ms 4.32% 33.22% 1280ms 4.32% runtime.heapBitsSetType
1240ms 4.18% 37.40% 1270ms 4.28% runtime.(*mspan).sweep.func1
1000ms 3.37% 40.78% 1000ms 3.37% runtime.memmove
930ms 3.14% 43.91% 5070ms 17.10% database/sql.convertAssign
750ms 2.53% 46.44% 2240ms 7.55% time.parse
670ms 2.26% 48.70% 7370ms 24.86% github.com/go-sql-driver/mysql.(*textRows).readRow
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鼎鐃緒申任鐃緒申諭鐃緒申鐃緒申鐃� MySQL 鐃緒申鐃緒申鐃緒申鐃緒申鐃獣わ申鐃緒申未鐃術￥申鐃緒申鐃緒申鐃銃わ申鐃緒申分鐃緒申鐃重わ申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鼎鐃緒申鐃順、鐃緒申鐃緒申離鐃緒申鐃緒申鐃緒申鐃緒申箸鐃緒申討鐃緒申鐃淑�鐃薯見つわ申鐃緒申鐃駿わ申鐃銃わ申鐃緒申鐃緒申法鐃盾あ鐃緒申泙鐃緒申鐃緒申鐃緒申鐃獣と高い鐃曙イ鐃巡ー鐃叔のワ申鐃竣ワ申離鐃緒申紂種申縫鵐阿鐃緒申鐃緒申鐃緒申鐃叔わ申鐃緒申鐃緒申鐃塾常申鐃� -cum 鐃緒申鐃竣ワ申鐃緒申鐃緒申箸辰謄肇奪廛鐃緒申鐃緒申鐃祝プワ申鐃春ワ申鐃緒申鐃緒申鮓�まわ申鐃緒申
(pprof) top -cum 40
10.25s of 29.65s total (34.57%)
Dropped 546 nodes (cum <= 0.15s)
Showing top 40 nodes out of 214 (cum >= 2.22s)
flat flat% sum% cum cum%
0 0% 0% 28.99s 97.77% runtime.goexit
0 0% 0% 25.69s 86.64% net/http.(*conn).serve
0 0% 0% 25.40s 85.67% net/http.(*ServeMux).ServeHTTP
...
0 0% 0% 25.08s 84.59% github.com/zenazn/goji/web/middleware.Recoverer.func1
0 0% 0% 22.42s 75.62% github.com/zenazn/goji/web.netHTTPHandlerFuncWrap.ServeHTTPC
0 0% 0% 20.61s 69.51% main.getIndex
0 0% 0% 20.26s 68.33% github.com/jmoiron/sqlx.(*DB).Select
0 0% 0% 20.26s 68.33% github.com/jmoiron/sqlx.Select
...
鐃緒申未砲鐃緒申鐃所ク鐃緒申鐃緒申鐃夙ハワ申疋蕁種申鐃� getIndex 鐃緒申鐃緒申鐃叔わ申鐃緒申鐃緒申鐃緒申鐃祝緒申鐃醇し鐃殉わ申鐃順う鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃宿の刻申単鐃縮のプワ申鐃春ワ申鐃緒申鐃緒申鮓�わ申砲魯妊丱奪鐃緒申鐃緒申鐃宿�鐃竣なので￥申 pprof 鐃緒申動鐃緒申鐃祝ワ申鐃竣ワ申離丱鐃緒申淵鐃緒申鐃緒申蠅件申討鐃緒申鐃宿�鐃竣わ申鐃緒申鐃緒申泙鐃緒申鐃�
鐃出ワ申鐃淑ワ申鐃緒申鐃所し忘鐃緒申鐃� URL 鐃緒申鐃緒申鐃叔プワ申鐃春ワ申鐃緒申鐃緒申鐃緒申辰討鐃緒申泙辰鐃緒申鐃緒申癲�鐃緒申動鐃緒申鐃緒申鐃夙わ申鐃緒申 "Saved profile in ..." 鐃緒申表鐃緒申鐃緒申鐃緒申討鐃緒申鐃術ワ申鐃祝プワ申鐃春ワ申鐃緒申鐃緒申鐃縮わ申鐃緒申存鐃緒申鐃緒申討鐃緒申鐃塾で￥申鐃緒申鐃駿プワ申鐃春ワ申鐃緒申鐃緒申鵐阿鐃緒申覆鐃緒申討鐃�
go tool pprof app <鐃竣ワ申鐃春ワ申鐃緒申鐃緒申鐃縮のパワ申> 鐃緒申鐃緒申鐃緒申廚任鐃緒申鐃�
isucon:~/private_isu/webapp/golang$ go tool pprof app /home/isucon/pprof/pprof.localhost:3000.samples.cpu.001.pb.gz
Entering interactive mode (type "help" for commands)
(pprof) list main.getIndex
Total: 29.65s
ROUTINE ======================== main.getIndex in /home/isucon/private_isu/webapp/golang/app.go
0 20.61s (flat, cum) 69.51% of Total
. . 388:
. . 389: http.Redirect(w, r, "/", http.StatusFound)
. . 390:}
. . 391:
. . 392:func getIndex(w http.ResponseWriter, r *http.Request) {
. 120ms 393: me := getSessionUser(r)
. . 394:
. . 395: results := []Post{}
. . 396:
. 18.39s 397: err := db.Select(&results, "SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC")
. . 398: if err != nil {
. . 399: fmt.Println(err)
. . 400: return
. . 401: }
. . 402:
. 1.34s 403: posts, merr := makePosts(results, getCSRFToken(r), false)
. . 404: if merr != nil {
. . 405: fmt.Println(merr)
. . 406: return
. . 407: }
. . 408:
. . 409: fmap := template.FuncMap{
. . 410: "imageURL": imageURL,
. . 411: }
. . 412:
. . 413: template.Must(template.New("layout.html").Funcs(fmap).ParseFiles(
. . 414: getTemplPath("layout.html"),
. . 415: getTemplPath("index.html"),
. . 416: getTemplPath("posts.html"),
. . 417: getTemplPath("post.html"),
. 70ms 418: )).Execute(w, struct {
. . 419: Posts []Post
. . 420: Me User
. . 421: CSRFToken string
. . 422: Flash string
. 690ms 423: }{posts, me, getCSRFToken(r), getFlash(w, r, "notice")})
. . 424:}
. . 425:
. . 426:func getAccountName(c web.C, w http.ResponseWriter, r *http.Request) {
. . 427: user := User{}
. . 428: uerr := db.Get(&user, "SELECT * FROM `users` WHERE `account_name` = ? AND `del_flg` = 0", c.URLParams["accountName"])
鐃緒申鐃緒申竜鐃緒申鐃緒申虜埜鐃緒申 myprofiler 鐃叔醐申鐃縦わ申鐃緒申鐃緒申 posts 鐃緒申鐃緒申鐃� LIMIT 鐃淑わ申鐃緒申鐃緒申鐃所が鐃重わ申鐃緒申鐃緒申任鐃緒申諭鐃�MySQL 側鐃緒申鐃緒申鐃叔なわ申鐃緒申鐃緒申鐃緒申鐃緒申鐃縮の件申未鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃銃パ￥申鐃緒申鐃緒申鐃銃わ申 app 側鐃叔わ申鐃緒申崕鼎鐃緒申覆辰討鐃緒申鐃処う鐃叔わ申鐃緒申
鐃緒申鐃塾ワ申鐃緒申鐃緒申侶鐃縮わ申鐃緒申鐃術わ申鐃銃わ申鐃緒申 makePosts() 鐃緒申鐃緒申鮓�てみわ申函鐃緒申襦種申廚僚鐃獣種申砲鐃緒申鐃緒申 if 文鐃緒申鐃緒申鐃緒申泙鐃緒申鐃�
if len(posts) >= postsPerPage {
break
}
鐃緒申鐃塾種申鐃緒申鐃緒申 continue 文鐃緒申覆鐃緒申里如鐃緒申鐃緒申里泙鐃� postsPerPage 鐃緒申 LIMIT 鐃緒申鐃殉わ申鐃順う鐃緒申 (鐃緒申: 鐃緒申鐃曙が鐃緒申認鐃緒申足鐃叔醐申妊魯泙鐃殉わ申)
diff --git a/app.go b/app.go
index eb52f47..3dd9804 100644
--- a/app.go
+++ b/app.go
@@ -394,7 +394,7 @@ func getIndex(w http.ResponseWriter, r *http.Request) {
results := []Post{}
- err := db.Select(&results, "SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC")
+ err := db.Select(&results, "SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC LIMIT 20")
if err != nil {
fmt.Println(err)
return
鐃緒申鐃銃￥申鐃緒申測鐃叔わ申鐃緒申鐃緒申鐃緒申鐃熟件申測鐃夙プワ申鐃春ワ申鐃緒申鐃緒申鐃緒申未砲鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申任鐃緒申鐃緒申鐃緒申鐃緒申屬鐃緒申鐃緒申鐃縮居申鐃緒申里妊廛鐃緒申侫鐃緒申鐃緒申襪件申覆鐃緒申鐃緒申測鐃叔わ申鐃緒申
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":12848,"success":22272,"fail":1201,"messages":["1鐃准￥申鐃緒申鐃緒申表鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃塾随申鐃緒申足鐃緒申泙鐃緒申鐃� (GET /)","鐃緒申鐃緒申鐃緒申譽�鐃緒申鐃緒申URL鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申泙鐃緒申鐃�: expected '^/$', got '/login' (GET /login)"]}
鐃緒申鐃緒申蕁ｏ申鐃緒申蕁種申鐃緒申任討鐃緒申泙鐃緒申鐃緒申鐃緒申鐃緒申廛鐃塾ワ申鐃緒申鐃薯見わ申函鐃�
fork/exec /bin/bash: cannot allocate memory
鐃緒申鐃所が足鐃緒申覆鐃緒申鐃� fork 鐃祝種申鐃峻わ申鐃銃まわ申鐃緒申鐃緒申
top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11931 isucon 20 0 406248 183372 9052 S 74.8 17.9 0:19.55 app 720 mysql 20 0 1226416 215076 10404 S 65.5 21.0 3:24.11 mysqld
CPU 鐃緒申鐃緒申率鐃緒申 app 鐃緒申 mysql 鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃叔わ申鐃粛￥申足鐃緒申鐃銃わ申200鐃祝わ申鐃緒申鐃緒申鐃熟わ申鐃淑わ申鐃塾で￥申CPU鐃淑鰹申鐃緒申鐃緒申分鐃緒申鐃粛ッワ申鐃祝わ申呂鐃緒申鐃銃わ申鐃緒申鐃緒申鐃叔わ申鐃緒申
myprofiler:
17 SELECT * FROM `posts` WHERE `id` = N 12 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC 4 SELECT * FROM `users` WHERE `id` = N 3 SELECT * FROM `users` WHERE `id` = ?
posts 鐃緒申鐃緒申 id 鐃緒申鐃緒申鐃�1鐃曙コ鐃緒申鐃宿種申鐃緒申鐃緒申鐃重わ申鐃淑わ申泙鐃緒申鐃緒申鐃� N+1 鐃緒申鐃淑￥申
鐃緒申鐃緒申鐃竣ワ申鐃緒申鐃緒申鐃銃び出わ申鐃緒申鐃緒申鐃�
fork 鐃祝種申鐃峻わ申鐃緒申里蓮鐃渋進�鐃淑ワ申鐃緒申奪廚鐃緒申覆鐃緒申鐃緒申鐃緒申如鐃緒申縫鐃緒申鐃緒申弌鐃緒申鐃緒申潺奪箸鐃緒申鐃緒申佞砲覆辰討鐃緒申董鐃�fork 鐃緒申鐃緒申鐃瞬間わ申 app 鐃緒申同鐃緒申鐃緒申鐃緒申鐃塾ワ申鐃緒申鐃緒申鐃獣てわ申發�1鐃竣ワ申鐃緒申鐃緒申鐃緒申鐃緒申覆鐃緒申鐃緒申鐃緒申鐃夙思わ申鐃殉わ申鐃緒申
CPU鐃緒申鐃緒申率鐃緒申2鐃緒申鐃緒申鐃淑のわ申200%鐃緒申鐃緒申鐃緒申鐃熟わ申鐃淑わ申鐃塾わ申 fork 鐃塾わ申鐃緒申 (Go 鐃塾ワ申鵐織鐃緒申鐃緒申鐃峻刻申鐃� fork 鐃緒申鐃暑た鐃緒申砲鐃緒申鐃� goroutine 鐃緒申鐃獣駈申澆鐃淑わ申鐃夙わ申鐃緒申鐃淑わ申鐃緒申鐃峻わ申鐃緒申鐃瞬わ申発鐃緒申鐃緒申鐃緒申) 鐃緒申鐃盾し鐃緒申覆鐃緒申里如鐃緒申鐃緒申鐃緒申鐃緒申鐃縮削減鐃塾わ申鐃緒申鵬鐃緒申鐃緒申侫鐃緒申鐃緒申鐃緒申DB鐃緒申鐃緒申鐃准わ申个鐃緒申鐃緒申盂逸申鐃緒申廛鐃緒申鐃緒申鐃緒申討喀个鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申優鐃処し鐃殉わ申鐃順う鐃緒申
鐃緒申鐃緒申鐃竣ワ申鐃緒申鐃緒申鐃緒申鐃宿わ申任鐃夙わ申鐃緒申鐃薯見わ申函鐃� openssl dgst -sha512 鐃緒申鐃殉ワ申匹鐃� sha512 鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃夙わ申彁鐃緒申鐃緒申討鐃緒申鐃処う鐃叔わ申鐃緒申 Go 鐃緒申 sha512 鐃緒申彁鐃緒申鐃緒申鐃緒申鐃祝￥申鐃縦器申戮董鐃銃縁申鐃緒申鐃緒申呂鬚垢鐃竣ワ申鐃緒申鐃緒申鐃緒申颪い討澆泙鐃緒申鐃�
$ cat t.go < app.go
package main
import (
"crypto/sha512"
"fmt"
"io/ioutil"
"os"
)
func main() {
data, _ := ioutil.ReadAll(os.Stdin)
fmt.Printf("%x\n", sha512.Sum512(data))
}
isucon@ip-10-0-1-202:~/private_isu/webapp/golang$ go run t.go < t.go
b252b55576a4f4e321d5761b6dc980b8ee41fd9767765c785861a73b736f6a59aa7f40f7ba0d92c508dea32a20bc800e0198056c3dc8b33d5ac445f27b163d17
isucon@ip-10-0-1-202:~/private_isu/webapp/golang$ openssl dgst -sha512 < t.go
(stdin)= b252b55576a4f4e321d5761b6dc980b8ee41fd9767765c785861a73b736f6a59aa7f40f7ba0d92c508dea32a20bc800e0198056c3dc8b33d5ac445f27b163d17
鐃緒申鐃緒申鮖温佑鐃� app.go 鐃緒申颪�器申鐃緒申鐃殉わ申鐃緒申
@@ -125,14 +125,7 @@ func escapeshellarg(arg string) string {
}
func digest(src string) string {
- // openssl鐃塾バ￥申鐃緒申鐃緒申鐃祝わ申辰討鐃� (stdin)= 鐃夙わ申鐃緒申鐃塾わ申鐃縦わ申鐃塾で種申鐃�
- out, err := exec.Command("/bin/bash", "-c", `printf "%s" `+escapeshellarg(src)+` | openssl dgst -sha512 | sed 's/^.*= //'`).Output()
- if err != nil {
- fmt.Println(err)
- return ""
- }
-
- return strings.TrimSuffix(string(out), "\n")
+ return fmt.Sprintf("%x", sha512.Sum512([]byte(src)))
}
鐃緒申鐃銃￥申鐃竣ワ申鐃春ワ申鐃緒申鐃緒申鐃緒申鐃渋�鐃叔わ申鐃緒申鐃緒申鐃竣ワ申離鐃緒申鐃緒申砲鐃� fork 鐃緒申鐃初ー鐃緒申鐃獣わ申鐃殉わ申鐃緒申鐃緒申
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":26755,"success":24050,"fail":119,"messages":["1鐃准￥申鐃緒申鐃緒申表鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃塾随申鐃緒申足鐃緒申泙鐃緒申鐃� (GET /)"]}
鐃緒申鐃緒申鐃藷、まわ申鐃緒申鐃初ー鐃出てまわ申鐃粛￥申鐃緒申鐃緒申鐃� LIMIT 足鐃緒申鐃緒申鐃塾わ申鐃殉ワ申鐃緒申鐃緒申鐃淑￥申
top:
Tasks: 84 total, 1 running, 83 sleeping, 0 stopped, 0 zombie %Cpu(s): 54.2 us, 20.7 sy, 0.0 ni, 15.0 id, 1.4 wa, 0.0 hi, 8.3 si, 0.4 st KiB Mem: 1022972 total, 958404 used, 64568 free, 18292 buffers KiB Swap: 0 total, 0 used, 0 free. 425600 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 720 mysql 20 0 1357488 210336 4476 S 78.6 20.6 5:19.05 mysqld 763 isucon 20 0 619612 198632 9120 S 70.2 19.4 0:13.54 app 2904 www-data 20 0 92028 4088 1928 S 11.3 0.4 0:29.40 nginx 2572 isucon 20 0 34344 11148 2160 S 7.3 1.1 0:28.48 tmux
idle 鐃緒申 15% 鐃殉で駕申鐃緒申鐃獣てまわ申鐃緒申鐃緒申鐃緒申鐃縮り。 鐃緒申鐃夙￥申 tmux 鐃緒申 7% 鐃殉わ申鐃緒申泙鐃緒申鐃緒申鐃� Goji 鐃塾ワ申鐃緒申鐃緒申鐃緒申奪鐃緒申鐃緒申鐃緒申鐃塾で￥申鐃叔醐申砲鐃緒申擇鐃淑わ申鐃夙わ申鐃緒申鐃殉わ申鐃緒申諭鐃�
myprofiler:
## 2016-05-25 20:18:44.06 +0900 13 SELECT * FROM `posts` WHERE `id` = N 9 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC 9 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N 6 SELECT * FROM `users` WHERE `id` = ? 6 SELECT `id` FROM `posts` WHERE `user_id` = N 4 SELECT * FROM `users` WHERE `id` = N 4 SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = ? 2 SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N 2 SELECT * FROM `comments` WHERE `post_id` = ? ORDER BY `created_at` DESC LIMIT N 2 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `user_id` = N ORDER BY `created_at` DESC
鐃出ワ申鐃緒申鐃緒申
makePosts() 鐃緒申直鐃緒申鐃銃みわ申函鐃緒申鐃銃わ申鐃緒申鐃醇ー鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申悗鐃緒申媛辰鬚靴覆鐃緒申茲�鐃淑ワ申鐃緒申鐃宿になってわ申鐃殉わ申鐃緒申鐃緒申
(Go 鐃緒申鐃緒申 early return (continue) 鐃緒申鐃叔ワ申鐃緒申鐃緒申鐃夙わ申鐃緒申鐃夙わ申多鐃緒申鐃塾でわ申鐃緒申鐃緒申分鐃緒申逃鐃緒申鐃銃わ申鐃殉わ申鐃緒申)
if p.User.DelFlg == 0 {
posts = append(posts, p)
}
SELECT 鐃緒申鐃淑鰹申鐃叔駈申鐃緒申鐃緒申 JOIN 鐃緒申鐃銃わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申離侫鐃緒申襯随申鐃藷グわ申孫圓鐃緒申討鐃緒申泙鐃緒申泙鐃緒申鐃�
@@ -394,7 +387,7 @@ func getIndex(w http.ResponseWriter, r *http.Request) {
results := []Post{}
- err := db.Select(&results, "SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC LIMIT 20")
+ err := db.Select(&results, "SELECT posts.`id`, `user_id`, `body`, `mime`, posts.`created_at` FROM `posts` INNER JOIN `users` ON posts.user_id=users.id WHERE users.del_flg = 0 ORDER BY `created_at` DESC LIMIT 20")
if err != nil {
fmt.Println(err)
return
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":27399,"success":23615,"fail":0,"messages":[]}
鐃緒申鐃駿はわ申鐃緒申鐃緒申鐃縮わ申泙鐃緒申鐃緒申鐃�
top:
Tasks: 83 total, 4 running, 79 sleeping, 0 stopped, 0 zombie %Cpu(s): 53.9 us, 23.6 sy, 0.0 ni, 9.8 id, 4.2 wa, 0.0 hi, 8.2 si, 0.2 st KiB Mem: 1022972 total, 948340 used, 74632 free, 12604 buffers KiB Swap: 0 total, 0 used, 0 free. 486056 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 720 mysql 20 0 1359224 209144 4576 S 85.9 20.4 6:06.96 mysqld 910 isucon 20 0 487224 139892 8884 S 67.9 13.7 0:09.58 app 2904 www-data 20 0 91852 2828 844 R 10.7 0.3 0:35.40 nginx 2572 isucon 20 0 34472 10100 928 S 6.7 1.0 0:33.64 tmux
鐃緒申鐃緒申辰鐃� mysql 鐃緒申鐃重わ申鐃叔わ申鐃粛￥申
myprofiler:
15 SELECT * FROM `posts` WHERE `id` = N 7 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC 6 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N 5 SELECT * FROM `users` WHERE `id` = ? 5 INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES (?,?,?) 5 SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N 5 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `user_id` = N ORDER BY `created_at` DESC 4 INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (?,?,?,?) 4 INSERT INTO `users` (`account_name`, `passhash`) VALUES (?,?) 3 SELECT * FROM `users` WHERE `id` = N
JOIN 鐃緒申足鐃緒申鐃緒申鐃緒申鐃宿￥申鐃緒申鐃緒申任鐃殉わ申 post 鐃緒申 id 鐃叔逸申鐃緒申鐃塾わ申鐃重わ申鐃淳わ申鐃緒申鐃叔わ申鐃緒申
pprof top40 -cum:
...
0 0% 0.38% 13.88s 76.18% github.com/zenazn/goji/web/middleware.AutomaticOptions.func1
0.01s 0.055% 0.44% 8.85s 48.57% github.com/zenazn/goji/web.netHTTPHandlerFuncWrap.ServeHTTPC
0 0% 0.44% 6.11s 33.53% main.getIndex
0.02s 0.11% 0.55% 4.92s 27.00% github.com/jmoiron/sqlx.(*DB).Get
0.01s 0.055% 0.6% 4.90s 26.89% github.com/jmoiron/sqlx.Get
0 0% 0.6% 4.78s 26.23% github.com/zenazn/goji/web.handlerFuncWrap.ServeHTTPC
0.02s 0.11% 0.71% 4.65s 25.52% main.makePosts
3.82s 20.97% 21.68% 4.06s 22.28% syscall.Syscall
...
pprof list makePosts:
ROUTINE ======================== main.makePosts in /home/isucon/private_isu/webapp/golang/app.go
20ms 4.65s (flat, cum) 25.52% of Total
. . 173:}
. . 174:
. . 175:func makePosts(results []Post, CSRFToken string, allComments bool) ([]Post, error) {
. . 176: var posts []Post
. . 177:
10ms 10ms 178: for _, p := range results {
. 1.05s 179: err := db.Get(&p.CommentCount, "SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = ?", p.ID)
. . 180: if err != nil {
. . 181: return nil, err
. . 182: }
. . 183:
. . 184: query := "SELECT * FROM `comments` WHERE `post_id` = ? ORDER BY `created_at` DESC"
. . 185: if !allComments {
. 20ms 186: query += " LIMIT 3"
. . 187: }
. . 188: var comments []Comment
. 1.38s 189: cerr := db.Select(&comments, query, p.ID)
. . 190: if cerr != nil {
. . 191: return nil, cerr
. . 192: }
. . 193:
. . 194: for i := 0; i < len(comments); i++ {
. 770ms 195: uerr := db.Get(&comments[i].User, "SELECT * FROM `users` WHERE `id` = ?", comments[i].UserID)
. . 196: if uerr != nil {
. . 197: return nil, uerr
. . 198: }
. . 199: }
. . 200:
. . 201: // reverse
. . 202: for i, j := 0, len(comments)-1; i < j; i, j = i+1, j-1 {
. . 203: comments[i], comments[j] = comments[j], comments[i]
. . 204: }
. . 205:
10ms 10ms 206: p.Comments = comments
. . 207:
. 1.35s 208: perr := db.Get(&p.User, "SELECT * FROM `users` WHERE `id` = ?", p.UserID)
. . 209: if perr != nil {
. . 210: return nil, perr
. . 211: }
. . 212:
. . 213: p.CSRFToken = CSRFToken
. . 214:
. . 215: if p.User.DelFlg == 0 {
. 60ms 216: posts = append(posts, p)
. . 217: }
. . 218: if len(posts) >= postsPerPage {
. . 219: break
. . 220: }
. . 221: }
鐃緒申鐃瞬のわ申鐃緒申鐃獣てるク鐃緒申鐃所が鐃緒申鐃緒申鐃夙種申鐃緒申鐃緒申鐃緒申鐃緒申鐃�1鐃曙コ鐃緒申鐃宿種申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申離鐃緒申鐃緒申鐃淑ので￥申鐃緒申単鐃淑ワ申鐃緒申鐃緒申鐃緒申鐃緒申未鐃緒申蠅駕申討鐃塾わ申鐃緒申鐃緒申能鼎鐃緒申里任鐃緒申腓�鐃緒申
鐃竣レー鐃緒申鐃循ワ申鐃緒申鐃緒申鐃緒申襯�鐃緒申鐃緒申蓮鐃緒申妊侫鐃緒申鐃夙でわ申 prepared statement 鐃緒申箸鐃緒申里討砲鐃緒申討鐃緒申鐃� (prepare, execute, close 鐃緒申3鐃緒申鐃殉ワ申鐃�) 鐃塾で￥申鐃緒申鐃緒申的鐃緒申 prepared statement 鐃緒申鐃緒申鐃緒申僂鐃緒申襯鰹申鐃緒申匹鐃書くわ申鐃緒申 prepared statement 鐃緒申箸鐃淑わ申鐃処う鐃祝わ申鐃緒申里鐃緒申鐃緒申鐃緒申任鐃緒申鐃�
鐃叔刻申鐃緒申能鐃緒申鐃旬指わ申鐃淑わ申鐃緒申鐃緒申僂鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申任鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃獣縁申覆里鐃� prepared statement 鐃緒申箸鐃淑わ申鐃緒申鐃叔わ申鐃緒申 dsn 鐃緒申 interpolateParams=true 鐃緒申足鐃緒申鐃緒申鐃緒申鐃緒申 (鐃粛わ申鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申能鐃叔わ申鐃緒申 鐃緒申鐃緒申)
diff:
@@ -805,7 +805,7 @@ func main() {
}
dsn := fmt.Sprintf(
- "%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=true&loc=Local",
+ "%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=true&loc=Local&interpolateParams=true",
user,
password,
host,
鐃緒申鐃緒申鐃緒申:
{"pass":true,"score":30076,"success":25687,"fail":0,"messages":[]}
30k 鐃緒申鐃緒申鐃緒申達鐃緒申
top:
Tasks: 83 total, 5 running, 78 sleeping, 0 stopped, 0 zombie %Cpu(s): 63.1 us, 17.4 sy, 0.0 ni, 8.5 id, 3.4 wa, 0.0 hi, 7.4 si, 0.2 st KiB Mem: 1022972 total, 960140 used, 62832 free, 13192 buffers KiB Swap: 0 total, 0 used, 0 free. 472760 cached Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 720 mysql 20 0 1359224 209132 4128 S 79.7 20.4 6:56.71 mysqld 1010 isucon 20 0 514368 157192 8888 S 66.8 15.4 0:08.93 app 2904 www-data 20 0 91876 2824 844 R 11.3 0.3 0:41.86 nginx 1015 isucon 20 0 21176 18648 3444 R 8.3 1.8 0:01.31 myprofiler 2572 isucon 20 0 34920 10472 916 S 7.3 1.0 0:38.28 tmux
myprofiler:
18 SELECT * FROM `posts` WHERE `id` = N 11 INSERT INTO `posts` (`user_id`, `mime`, `imgdata`, `body`) VALUES (N,S,_binaryS,S) 9 SELECT * FROM `users` WHERE `id` = N 7 SELECT * FROM `comments` WHERE `post_id` = N ORDER BY `created_at` DESC LIMIT N 6 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `user_id` = N ORDER BY `created_at` DESC 5 SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= S ORDER BY `created_at` DESC 4 SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = N 2 INSERT INTO `users` (`account_name`, `passhash`) VALUES (S,S) 1 SELECT * FROM `users` WHERE `account_name` = S AND `del_flg` = N 1 INSERT INTO `comments` (`post_id`, `user_id`, `comment`) VALUES (N,N,S)
pprof:
(pprof) top40 -cum
4.23s of 18.58s total (22.77%)
Dropped 583 nodes (cum <= 0.09s)
Showing top 40 nodes out of 317 (cum >= 2.07s)
flat flat% sum% cum cum%
0 0% 0% 17.10s 92.03% runtime.goexit
...
0 0% 0.22% 8.84s 47.58% github.com/zenazn/goji/web.netHTTPHandlerFuncWrap.ServeHTTPC
0 0% 0.22% 5.66s 30.46% main.getIndex
0 0% 0.22% 4.90s 26.37% github.com/zenazn/goji/web.handlerFuncWrap.ServeHTTPC
0 0% 0.22% 4.04s 21.74% github.com/jmoiron/sqlx.(*DB).Get
0.04s 0.22% 0.43% 4.04s 21.74% github.com/jmoiron/sqlx.Get
0 0% 0.43% 3.73s 20.08% html/template.(*Template).Execute
0.03s 0.16% 0.59% 3.50s 18.84% main.makePosts
0 0% 0.59% 3.32s 17.87% github.com/jmoiron/sqlx.(*DB).Select
0 0% 0.59% 3.32s 17.87% github.com/jmoiron/sqlx.Select
0.93s 5.01% 5.60% 3.23s 17.38% runtime.mallocgc
0 0% 5.60% 3.12s 16.79% text/template.(*Template).Execute
0.08s 0.43% 6.03% 3.12s 16.79% text/template.(*state).walk
0.01s 0.054% 6.08% 3.07s 16.52% text/template.(*state).walkTemplate
0 0% 6.08% 3.02s 16.25% text/template.(*state).walkRange
0 0% 6.08% 3.02s 16.25% text/template.(*state).walkRange.func1
0.13s 0.7% 6.78% 2.96s 15.93% runtime.systemstack
鐃緒申鐃緒申鐃緒申鐃緒申鐃銃ワ申廛譟種申箸僚鼎鐃緒申鐃緒申鐃縮に醐申鐃緒申鐃熟わ申泙鐃緒申鐃緒申鐃緒申鐃緒申泙鐃�DB鐃緒申鐃緒申鐃緒申鐃重わ申鐃緒申
鐃殉とわ申
鐃緒申鐃緒申鐃緒申: 4745 (鐃緒申鐃緒申鐃緒申鐃�) -> 14208 (鐃緒申鐃緒申) -> 30076 (鐃緒申鐃緒申)
鐃緒申鐃銃￥申鐃緒申鐃緒申鐃緒申鐃緒申鐃竣ワ申鐃春ワ申鐃緒申鐃緒申能鼎鐃緒申箸鐃緒申鐃緒申鐃緒申戮鐃緒申鐃緒申紂種申縫鵐阿蓮鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申匚覆鐃祝駈申鼎鐃緒申討鐃緒申泙鐃緒申鐃緒申鐃� 鐃緒申鐃緒申呂鐃獣とドラス鐃銃ワ申鐃獣ワ申鐃淑ワ申鐃遵ー鐃祝ングに移ってわ申鐃緒申鐃緒申鐃緒申鐃夙思わ申鐃殉わ申鐃緒申
鐃緒申鐃緒申鐃� pprof 鐃緒申 myprofiler 鐃緒申箸辰鐃緒申鐃緒申紂種申縫鵐阿両匆鐃塾意図わ申鐃緒申鐃獣わ申鐃塾でわ申鐃緒申辰箸鐃所す鐃緒申鐃銃わ申鐃殉わ申鐃殉わ申鐃緒申鐃緒申 鐃渋際にはわ申辰鐃緒申鐃緒申鐃緒申鐃緒申造鐃緒申必鐃竣とわ申鐃緒申茲�鐃淑ワ申鐃遵ー鐃祝ングわ申佑鐃緒申銅孫圓鐃緒申覆鐃緒申函鐃緒申鐃夙常申忙鐃緒申屬鐃渋�鐃緒申覆鐃緒申謄鐃緒申鐃緒申呂砲覆辰討鐃緒申泙鐃緒申里乃鐃緒申鐃縦わ申鐃殉わ申鐃順う鐃緒申
@methane
