isucon4 morning

metrics.md

$ time ./benchmarker bench
tag:benchmarker type:info       message:launch benchmarker
tag:benchmarker type:warning    message:Result not sent to server because API key is not set
tag:benchmarker type:info       message:init environment
tag:benchmarker type:info       message:run benchmark workload: 1
tag:benchmarker type:info       message:check banned ips and locked users report
tag:benchmarker type:report     count:banned ips        value:9
tag:benchmarker type:report     count:locked users      value:2575
tag:benchmarker type:info       message:Result not sent to server because API key is not set
tag:benchmarker type:score      success:7680    fail:0  score:1659

real    1m31.975s
user    0m5.508s
sys     0m1.412s

pprof

/debug/pprof/

profiles:
0	block
12	goroutine
97	heap
6	threadcreate

full goroutine stack dump

nginx アクセスログ集計

49.86099999999994       812     0.06140517241379303     /login
19.007  1       19.007  /report
4.347999999999992       178     0.024426966292134786    /mypage
1.4199999999999682      1624    0.0008743842364531824   /images/isucon-bank.png
1.2739999999999734      1446    0.0008810511756569664   /
1.2629999999999764      1624    0.0007777093596058967   /stylesheets/isucon-bank.css
1.2209999999999812      1624    0.0007518472906403826   /stylesheets/bootstrap.min.css
1.1919999999999826      1624    0.0007339901477832405   /stylesheets/bootflat.min.css

mysqldumpslow

Count: 812  Time=0.03s (25s)  Lock=0.00s (0s)  Rows=1.0 (812), root[root]@localhost
  SELECT COUNT(N) AS failures FROM login_log WHERE ip = 'S' AND id > IFNULL((select id from login_log where ip = 'S' AND succeeded = N ORDER BY id DESC LIMIT N), N)

Count: 805  Time=0.03s (20s)  Lock=0.00s (0s)  Rows=1.0 (805), root[root]@localhost
  SELECT COUNT(N) AS failures FROM login_log WHERE user_id = N AND id > IFNULL((select id from login_log where user_id = N AND succeeded = N ORDER BY id DESC LIMIT N), N)

Count: 4037  Time=0.00s (17s)  Lock=0.00s (0s)  Rows=1.0 (4037), root[root]@localhost
  SELECT COUNT(N) AS cnt FROM login_log WHERE user_id = N AND N < id

Count: 1  Time=4.19s (4s)  Lock=0.66s (0s)  Rows=0.0 (0), root[root]@localhost
  INSERT INTO `users` (`id`, `login`, `password_hash`, `salt`) VALUES
  (N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
  ,(N, 'S', SHA2('S', N), 'S')
...

Count: 15278  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.5 (8036), 2users@localhost
  #

Count: 1  Time=0.39s (0s)  Lock=0.00s (0s)  Rows=7.0 (7), root[root]@localhost
  SELECT ip FROM (SELECT ip, MAX(succeeded) as max_succeeded, COUNT(N) as cnt FROM login_log GROUP BY ip) AS t0 WHERE t0.max_succeeded = N AND t0.cnt >= N

Count: 1  Time=0.24s (0s)  Lock=0.00s (0s)  Rows=295.0 (295), root[root]@localhost
  SELECT ip, MAX(id) AS last_login_id FROM login_log WHERE succeeded = N GROUP by ip

Count: 812  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (812), root[root]@localhost
  SELECT id, login, password_hash, salt FROM users WHERE login = 'S'

Count: 1  Time=0.06s (0s)  Lock=0.00s (0s)  Rows=1171.0 (1171), root[root]@localhost
  SELECT user_id, login FROM (SELECT user_id, login, MAX(succeeded) as max_succeeded, COUNT(N) as cnt FROM login_log GROUP BY user_id) AS t0 WHERE t0.user_id IS NOT NULL AND t0.max_succeeded = N AND t0.cnt >= N

Count: 1  Time=0.05s (0s)  Lock=0.00s (0s)  Rows=4037.0 (4037), root[root]@localhost
  SELECT user_id, login, MAX(id) AS last_login_id FROM login_log WHERE user_id IS NOT NULL AND succeeded = N GROUP BY user_id

Count: 178  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (178), root[root]@localhost
  SELECT id, login, password_hash, salt FROM users WHERE id = 'S'

Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `login` varchar(N) NOT NULL UNIQUE,
  `password_hash` varchar(N) NOT NULL,
  `salt` varchar(N) NOT NULL
  ) DEFAULT CHARSET=utf8


Count: 1  Time=0.01s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  CREATE TABLE IF NOT EXISTS `login_log` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `created_at` datetime NOT NULL,
  `user_id` int,
  `login` varchar(N) NOT NULL,
  `ip` varchar(N) NOT NULL,
  `succeeded` tinyint NOT NULL
  ) DEFAULT CHARSET=utf8

Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select @@version_comment limit N

Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  SELECT @@max_allowed_packet

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  CREATE DATABASE isu4_qualifier

Count: 1  Time=0.00s (0s)  Lock=0.01s (0s)  Rows=0.0 (0), root[root]@localhost
  DROP DATABASE IF EXISTS isu4_qualifier

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Ping

Count: 7636  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Close stmt

Count: 7636  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Prepare

Count: 5  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  administrator command: Quit

テーブルスキーマ

mysql> show tables;
+--------------------------+
| Tables_in_isu4_qualifier |
+--------------------------+
| login_log                |
| users                    |
+--------------------------+
2 rows in set (0.00 sec)

mysql> show create table login_log;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table
                                                                                                                                        |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------+
| login_log | CREATE TABLE `login_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `login` varchar(255) NOT NULL,
  `ip` varchar(255) NOT NULL,
  `succeeded` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=70769 DEFAULT CHARSET=utf8 |
----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table users;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                             |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `salt` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from login_log;
+----------+
| count(*) |
+----------+
|    70768 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0.02 sec)