ISUCON3本戦遊び (1, 2)

セットアップ

AMI指定してm3.mediumで起動。m3.mediumでも今やhvmだから大丈夫だろう。

その他作業をし、初期起動をrubyにして最初のベンチ

$ carton exec perl bench.pl -d /home/isucon/image_source http://127.0.0.1
2015-06-03T13:39:06 [32439] [INFO] starting benchmark: concurrency: 6, time: 60

2015-06-03T13:40:11 [32439] [INFO] done benchmark: score 336.142963867919, elapsed 64.109 sec = 5.243 / sec

Apache -> nginx

+        proxy_set_header Host $host;
+        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
+        proxy_set_header X-Forwarded-Host $host;
+        proxy_set_header X-Forwarded-Server $host;
+        proxy_set_header X-Real-IP $remote_addr;
+
         #charset koi8-r;
 
         #access_log  /var/log/nginx/host.access.log  main;
 
         location / {
+          proxy_pass   http://localhost:5000;
         }

単純に切り換えただけだとさすがにスコアがそんなに変わらず。

2015-06-10T12:53:13 [3993] [INFO] done benchmark: score 311.999568758223, elapsed 60.049 sec = 5.196 / sec

遅いリクエスト探す

nginxのログフォーマットにリクエスト処理時間を出力させるようにしてベンチマーク実行

     log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
                       '$status $body_bytes_sent "$http_referer" '
-                      '"$http_user_agent" "$http_x_forwarded_for"';
+                      '"$http_user_agent" "$http_x_forwarded_for" '
+                      '$request_time ';

とはいえ、大して種類がない。

GET /timeline?latest_entry=15087 HTTP/1.1  7.877
GET /image/9cef1c58b74505dee6e2f7d478444228b23cdd3add1efe76e357109c395a43b2?size=s HTTP/1.1  5.007 
GET /image/67b8faac0b6d992dd104a9dc66b242fb456e15d2c34f2ffb4d24c3f6987f9b3c?size=s HTTP/1.1  4.130
（略）
GET /icon/de11656d4d1659c01214bdae46d7309e5aed0d1c74cf364bf3d86d0cf9292fbd?size=l HTTP/1.1  1.235
（略）
GET /follow HTTP/1.1  0.95

L.268 get '/image/:image' のロジック

entry = mysql.xquery('SELECT * FROM entries WHERE image = ?', image).first

このクエリのEXPLAINを確認

mysql> explain SELECT * FROM entries WHERE image = 'e3904db4568ff709fe08e5b30d764924516eb62faa9d4d90a263c4a23a404e68';
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | entries | ALL  | NULL          | NULL | NULL    | NULL | 8885 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

確認するとインデクスがないので作成し、再度EXPLAIN

mysql> CREATE INDEX entries_image ON entries(image);

mysql> explain SELECT * FROM entries WHERE image = 'e3904db4568ff709fe08e5b30d764924516eb62faa9d4d90a263c4a23a404e68';
+----+-------------+---------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | entries | ref  | entries_image | entries_image | 767     | const |    1 | Using where |
+----+-------------+---------+------+---------------+---------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

少しだけスコア上昇

2015-06-10T13:41:44 [8130] [INFO] done benchmark: score 411.850045647162, elapsed 66.007 sec = 6.240 / sec

---

結局画像加工をいかに早くするか勝負なのかな。