ISUCON2遊び -2-
動作確認
ベンチマークを実行 #=> OK
結果: 200 tickets とかその程度。
リバースプロキシ（RP）サーバへ移せるものを移す
foreverログでレスポンスサイズを確認
幾つかサイズが重いものがあるのでチェックする。
GET /ticket/1 200 1257ms - 415.87kb GET /js/jquery-ui-1.8.24.custom.min.js 200 1ms - 200.23kb GET /images/isucon_title.jpg 200 1ms - 31.18kb
画像、jsライブラリをRPへ移す。/ticket/1 もどうやらcssやjsの合計として重くなっているようなのでアプリケーションに含まれる public/ をRPサーバへ移す。物理的に移した後、nginxの設定を変更する。
# nginx.conf server { listen 80; server_name 172.30.0.242; # add this configuration location ~* .*\.(jpg|JPG|gif|GIF|png|PNG|css|CSS|js|JS|html|HTML|ico|ICO) { root /var/www/html/public; } location / {
結果そんなに変わらず 205 tickets ...
レスポンス圧縮
public/ を移したものの、結局のレスポンスサイズが大きいのは間違いないのでnginxで圧縮をオン
gzip on;
SQL調査
MySQL
スロークエリを確認するために次の設定を行う。
# my.cnf
slow_query_log = ON
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 0.5
実行後のスローログに対して↓を実行。
# mysqldumpslow -s t /tmp/mysql-slow.log Reading mysql slow query log from /tmp/mysql-slow.log Count: 1001 Time=0.93s (934s) Lock=0.01s (9s) Rows=8.1 (8109), isucon2app[isucon2app]@2hosts SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock JOIN variation ON stock.variation_id = variation.id JOIN ticket ON variation.ticket_id = ticket.id JOIN artist ON ticket.artist_id = artist.id WHERE order_id IS NOT NULL ORDER BY order_id DESC LIMIT N ... (a) Count: 14 Time=0.66s (9s) Lock=0.00s (0s) Rows=1.0 (14), isucon2app[isucon2app]@2hosts SELECT COUNT(*) AS count FROM variation INNER JOIN stock ON stock.variation_id = variation.id WHERE variation.ticket_id = N AND stock.order_id IS NULL Count: 6 Time=0.81s (4s) Lock=0.00s (0s) Rows=4096.0 (24576), isucon2app[isucon2app]@2hosts SELECT seat_id, order_id FROM stock WHERE variation_id = N Count: 4 Time=0.54s (2s) Lock=0.00s (0s) Rows=1.0 (4), isucon2app[isucon2app]@[172.30.0.94] SELECT COUNT(*) AS count FROM stock WHERE variation_id = N AND order_id IS NULL Count: 1 Time=0.53s (0s) Lock=0.27s (0s) Rows=0.0 (0), isucon2app[isucon2app]@[172.30.0.94] UPDATE stock SET order_id = N WHERE variation_id = 'S' AND order_id IS NULL ORDER BY RAND() LIMIT N ... (b)
(a), (b)に注目
(a)
mysql> explain SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock JOIN variation ON stock.variation_id = variation.id JOIN ticket ON variation.ticket_id = ticket.id JOIN artist ON ticket.artist_id = artist.id WHERE order_id IS NOT NULL ORDER BY order_id DESC LIMIT 100; +----+-------------+-----------+--------+----------------+----------------+---------+-----------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+----------------+----------------+---------+-----------------------------+------+---------------------------------+ | 1 | SIMPLE | artist | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using temporary; Using filesort | | 1 | SIMPLE | variation | ALL | PRIMARY | NULL | NULL | NULL | 10 | Using join buffer | | 1 | SIMPLE | ticket | eq_ref | PRIMARY | PRIMARY | 4 | isucon2.variation.ticket_id | 1 | Using where | | 1 | SIMPLE | stock | ref | variation_seat | variation_seat | 4 | isucon2.variation.id | 1084 | Using where | +----+-------------+-----------+--------+----------------+----------------+---------+-----------------------------+------+---------------------------------+ 4 rows in set (0.00 sec)
stockテーブルの戻りrowsが多いので確認
mysql> show index from stock; +-------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | stock | 0 | PRIMARY | 1 | id | A | 41414 | NULL | NULL | | BTREE | | | | stock | 0 | variation_seat | 1 | variation_id | A | 38 | NULL | NULL | | BTREE | | | | stock | 0 | variation_seat | 2 | seat_id | A | 41414 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
variation.idのみを元にしたインデクスを作成
mysql> create index idx_variation_id on stock(variation_id); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from stock; +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | stock | 0 | PRIMARY | 1 | id | A | 41414 | NULL | NULL | | BTREE | | | | stock | 0 | variation_seat | 1 | variation_id | A | 14 | NULL | NULL | | BTREE | | | | stock | 0 | variation_seat | 2 | seat_id | A | 41414 | NULL | NULL | | BTREE | | | | stock | 1 | idx_variation_id | 1 | variation_id | A | 200 | NULL | NULL | | BTREE | | | +-------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
そして再びexplain
mysql> explain SELECT stock.seat_id, variation.name AS v_name, ticket.name AS t_name, artist.name AS a_name FROM stock JOIN variation ON stock.variation_id = variation.id JOIN ticket ON variation.ticket_id = ticket.id JOIN artist ON ticket.artist_id = artist.id WHERE order_id IS NOT NULL ORDER BY order_id DESC LIMIT 100; +----+-------------+-----------+--------+---------------------------------+------------------+---------+-----------------------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------------------------+------------------+---------+-----------------------------+------+---------------------------------+ | 1 | SIMPLE | artist | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using temporary; Using filesort | | 1 | SIMPLE | variation | ALL | PRIMARY | NULL | NULL | NULL | 10 | Using join buffer | | 1 | SIMPLE | ticket | eq_ref | PRIMARY | PRIMARY | 4 | isucon2.variation.ticket_id | 1 | Using where | | 1 | SIMPLE | stock | ref | variation_seat,idx_variation_id | idx_variation_id | 4 | isucon2.variation.id | 207 | Using where | +----+-------------+-----------+--------+---------------------------------+------------------+---------+-----------------------------+------+---------------------------------+ 4 rows in set (0.00 sec)
stockテーブルから取得するrowsが減ってる。良かった。
(b)
変なので、アプリケーションから除去。
--- a/webapp/nodejs/routes/index.js
+++ b/webapp/nodejs/routes/index.js
@@ -133,7 +133,7 @@ exports.buy = function (req, res) {
function (info, callback) {
order_id = info.insertId;
client.query(
- 'UPDATE stock SET order_id = ? WHERE variation_id = ? AND order_id IS NULL ORDER BY RAND() LIMIT 1',
+ 'UPDATE stock SET order_id = ? WHERE variation_id = ? AND order_id IS NULL LIMIT 1',
改修後ベンチマーク流して 268 tickets
DBのCPUネック
100%になってる。あとのサーバは余裕
SQLの処理の話なので、先ほどのスロークエリで不足していると考えられる箇所にインデクスを作成
mysql> create index idx_order_id on stock(order_id); mysql> create index idx_ticket_id on variation(ticket_id);
一時的にスコアは落ちたが、CPUネックはDBからAPサーバへ移動した。 DBサーバは30-40%に落ち着いた。
おそらく今後はAPサーバでアプリケーションのキャッシュを付ければ劇的にスコアが伸びる気がする。
