ISUCON3遊び (4)

(3)は欠席したので1つスキップして(4)へ

初期スキーマを変更

• init.sql.gzを変更 (init.sqlとして保存)

     32   PRIMARY KEY (`id`),
     33   index idx_created_at_id(`created_at`, `id`) -- custom
     34 ) ENGINE=InnoDB AUTO_INCREMENT=41176 DEFAULT CHARSET=utf8;
     35 /*!40101 SET character_set_client = @saved_cs_client */;
     36

• main.goを少し変更してisucon3コマンド再作成

    255 func initializeData(initScript string) {
    256         log.Printf("initialize data...\n")
    257         sh := []string{
    258                 "#!/bin/sh",
    259                 "set -e",
    260                 "cd " + initialDataDir,
    261                 // "md5sum -c checksum",
    262                 // "pigz -dc init.sql.gz | mysql -uisucon isucon",
    263                 "cat init.sql | mysql -uisucon isucon", // 固め直すの面倒なので...

これでひとまず1秒以上のクエリは消えた

再度遅いリクエストを探る

# awk -F\" '{ print $2 $7 }' httpd/access_log  | sort -k 4 -n -r |less
GET /memo/38136 HTTP/1.1 3216113
GET /memo/28554 HTTP/1.1 2578402
GET /memo/38136 HTTP/1.1 2491016
...

コード追っかけ

# app.rb
164   get '/memo/:memo_id' do

このブロックを中心に変更

   get '/memo/:memo_id' do
-    mysql = connection
     user  = get_user
 
-    memo = mysql.xquery('SELECT id, user, content, is_private, created_at, updated_at FROM memos WHERE id=?', params[:memo_id]).first
+    memo = $mysql.xquery('SELECT id, user, content, is_private, created_at, updated_at FROM memos WHERE id=?', params[:memo_id]).first
     unless memo
       halt 404, "404 Not Found"
     end
@@ -174,20 +165,16 @@ class Isucon3App < Sinatra::Base
         halt 404, "404 Not Found"
       end
     end
-    memo["username"] = mysql.xquery('SELECT username FROM users WHERE id=?', memo["user"]).first["username"]
+    memo["username"] = $mysql.xquery('SELECT username FROM users WHERE id=?', memo["user"]).first["username"]
     memo["content_html"] = gen_markdown(memo["content"])
+    memos = []
     if user["id"] == memo["user"]
-      cond = ""
+      memos = $mysql.xquery("SELECT * FROM memos WHERE user=? ORDER BY created_at", memo["user"])
     else
-      cond = "AND is_private=0"
+      memos = $mysql.xquery("SELECT * FROM memos WHERE user=? AND is_private=0 ORDER BY created_at", memo["user"])
     end
-    memos = []
     older = nil
     newer = nil
-    results = mysql.xquery("SELECT * FROM memos WHERE user=? #{cond} ORDER BY created_at", memo["user"])
-    results.each do |m|
-      memos.push(m)
-    end
     0.upto(memos.count - 1).each do |i|
       if memos[i]["id"] == memo["id"]
         older = memos[i - 1] if i > 0
@@ -203,19 +190,18 @@ class Isucon3App < Sinatra::Base
   end

再度遅いクエリ

コードいじってる間にslow logがまた出てきてたので調査。

-- a) 1.014944
SELECT count(*) AS c FROM memos WHERE is_private=0 

-- b) 1.306586
SELECT * FROM memos WHERE user='151'  ORDER BY created_at;

a)

type:ALL になってた => create index idx_is_private on memos(is_private);

b)

type:ALL でしかも Using filesort => create index idx_user_created_at on memos(user, created_at);

上記の対応をinit.sqlに追加

     32   PRIMARY KEY (`id`),
     33   index idx_created_at_id(`created_at`, `id`), -- custom
     34   index idx_is_private(`is_private`), -- custom
     35   index idx_user_created_at(`user`, `created_at`) -- custom
     36 ) ENGINE=InnoDB AUTO_INCREMENT=41176 DEFAULT CHARSET=utf8;
     37 /*!40101 SET character_set_client = @saved_cs_client */;
     38

現時点のスコア

地味に200ほど上昇

• before: 450-500
• after: 650-700

---

次はmemcachedでも入れるか..

地味な作業に応じて地味にスコアが伸びるのはなかなか楽しい