EC2

AMI: https://github.com/catatsuy/private-isu#ami
spot instance https://dev.classmethod.jp/articles/create-a-spot-instance-with-the-new-wizard/
default VPC
Security group
- HTTP: Port 80, Source = My IP
- SSH: Port 22 (default), Source = My IP
- All: Source = Bench Instance Public IPv4 address

SSH

chmod 400 .ssh/private-isu-key-pair.pem

.ssh/config

Host private-isu
  HostName [EC2 Web Service Instance Public IPv4 address]
  User ubuntu
  IdentityFile ~/.ssh/private-isu-key-pair.pem
  RemoteCommand sudo su - isucon
  RequestTTY true

Host private-isu-bench
  HostName [EC2 Benchmark Instance Public IPv4 address]
  User ubuntu
  IdentityFile ~/.ssh/private-isu-key-pair.pem
  RemoteCommand sudo su - isucon
  RequestTTY true

ssh private-isu

ssh private-isu-bench

or ssh directly

ssh -i .ssh/private-isu-key-pair.pem ubuntu@[EC2 Web Service Instance Public IPv4 address]
ssh -i .ssh/private-isu-key-pair.pem ubuntu@[EC2 Benchmark Instance Public IPv4 address]

nyancat

Calculate the score

sudo su - isucon

/home/isucon/private_isu.git/benchmarker/bin/benchmarker -u /home/isucon/private_isu.git/benchmarker/userdata -t http://[WEB Service Instance Public IPv4 address]

output

{"pass":true,"score":323,"success":410,"fail":8,"messages":["リクエストがタイムアウトしました (GET /logout)","リクエストがタイムアウトしました (POST /login)","リクエストがタイムアウトしました (POST /register)"]}

while running the command, run top command on the WEB service instance to monitor processes

top

nyancat

Output slow query log

edit /etc/mysql/mysql.conf.d/mysqld.cnf to enable logging of slow queries

[mysqld]
slow_query_log=1
slow_query_log_file='/var/log/mysql/mysql-slow.log'
long_query_time=0

restart MySQL

$ sudo su - root
$ systemctl restart mysql

delete the previous log

実行する前に、MySQLのスロークエリログが前回実行時のものと混ざらないように削除しておきます。ログファイルを削除したり名前を変更した場合は、mysqladmin flush-logsを実行してファイルが更新されていることをMySQLに伝える必要があります。

rm /var/log/mysql/mysql-slow.log
mysqladmin flush-logs

mysqldumpslow

mysqldumpslow /var/log/mysql/mysql-slow.log

sort by total query time

mysqldumpslow -s t /var/log/mysql/mysql-slow.log

nyancat

 Edit codeUse VSCode extension 'Remote - SSH' and edit code directly
https://marketplace.visualstudio.com/items?itemName=ms-vscode-remote.remote-ssh
https://code.visualstudio.com/docs/remote/ssh
"remote.ssh.useLocalServer": true,
"remote.SSH.enableRemoteCommand": true,
or edit code locally and git pull in the EC2 instance
https://isucon-workshop.trap.show/text/chapter-3/0-Github.html

nyancat

Speed up

comments table

mysql> ALTER TABLE `comments` ADD INDEX `post_id_idx` (`post_id`);
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

{"pass":true,"score":8358,"success":7109,"fail":0,"messages":[]}

posts table

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 1974  Time=0.02s (32s)  Lock=0.00s (0s)  Rows=10039.6 (19818200), isuconp[isuconp]@localhost
  SELECT `id`, `user_id`, `body`, `created_at`, `mime` FROM `posts` ORDER BY `created_at` DESC

mysql> explain SELECT `id`, `user_id`, `body`, `created_at`, `mime` FROM `posts` ORDER BY `created_at` DESC;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | posts | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9169 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

webapp/ruby/app.rb

"score":8042 -> "score":16882