ISUCON4の予選問題をやってみる。(その2)

前回 基本的なチューニングまでやったのでその続きをやってみる。

MySQLのSQL発行状況を出力しEXPLAINする

ログ出力

一時的にMySQLに発行されたSQLを出力するのは my.cnf に以下を追記してMySQLを再起動する

# sudo vim /etc/my.cnf

[mysqld]
general-log=TRUE
general-log-file=/tmp/mysql_sql.log

$ sudo service mysqld restart

EXPLAIN

とあるuser_idについて前回ログイン成功以降からのログイン失敗数を求めるSQLは

ELECT COUNT(1) AS failures FROM login_log WHERE user_id = '12345' AND id > IFNULL((select id from login_log where user_id = '155328' AND succeeded = 1 ORDER BY id DESC LIMIT 1), 0);

であり、これをEXPLAINすると

mysql> EXPLAIN SELECT COUNT(1) AS failures FROM login_log WHERE user_id = '12345' AND id > IFNULL((select id from login_log where user_id = '155328' AND succeeded = 1 ORDER BY id DESC LIMIT 1), 0);
+----+-------------+-----------+------+---------------------+-------------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys       | key         | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------------+-------------+---------+-------+------+--------------------------+
|  1 | PRIMARY     | login_log | ref  | PRIMARY,idx_user_id | idx_user_id | 5       | const |    1 | Using where; Using index |
|  2 | SUBQUERY    | login_log | ref  | idx_user_id         | idx_user_id | 5       |       |    1 | Using where              |
+----+-------------+-----------+------+---------------------+-------------+---------+-------+------+--------------------------+
2 rows in set (0.00 sec)

というふうに前回のチューニングでつけたindexを使っていることがわかる(Using index, rows=1)。ちなみにindexつける前は以下であり、Tableのフルスキャンが走っていることがわかる (row=37327)

mysql> EXPLAIN SELECT COUNT(1) AS failures FROM login_log WHERE user_id = '12345' AND id > IFNULL((select id from login_log where user_id = '155328' AND succeeded = 1 ORDER BY id DESC LIMIT 1), 0);
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows  | Extra       |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
|  1 | PRIMARY     | login_log | range | PRIMARY       | PRIMARY | 8       | NULL | 37327 | Using where |
|  2 | SUBQUERY    | login_log | index | NULL          | PRIMARY | 8       | NULL |     1 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+-------+-------------+
2 rows in set (0.30 sec)

さらにIndexを複合indexにすることでSUBQUERYでもusing indexとなる。

mysql> CREATE INDEX idx_user_id on login_log(user_id, succeeded);

Redis

MySQLでやっている部分はRedisに変更することでさらにチューニングが期待できる。また、それ以上に、セッション管理している部分

# vim app.psgi
  enable 'Session',
    state => Plack::Session::State::Cookie->new(
      httponly    => 1,
      session_key => "isu4_session",
    ),
    store => Plack::Session::Store::File->new(
      dir         => $session_dir,
    ),

がセッションごとにファイル作成を行いパフォーマンスを劣化させているのでこれをまずなんとかする。 ( $session_dir をみるとえらい状態なのがわかる)

redis インストール & 起動

$ sudo rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
$ sudo yum --enablerepo=remi install redis
$ sudo service redis start

perlのバインディングもinstall

$ cpanm install Redis
$ cpanm install Plack::Session::Store::Redis

セッション管理をredisに変更

# vim app.psgi
...
use Plack::Session::Store::Redis;
...
    store => Plack::Session::Store::Redis->new(
      prefix => "isucon",
      host => "localhost",
      port => 6379,
    ),
...

$ sudo /etc/init.d/supervisord stop

再びベンチマーク

$ ./benchmarker bench --workload 10
13:32:43 type:score     success:130710  fail:0  score:28238

これで28238点。

やっぱり中身もMySQLからRedis化せんとだめかな。。。

次回に続く