はじめに
※公式の記事が投稿される可能性もありますが許可も取れたので備忘録を兼ねて記事を作成します。なお、公式による動画公開はないようです。
座学の方は資料公開されています。
きっかけ
【事前講習開催決定】ISUCON 事前講習2021 座学&ハンズオンを開催しますのような募集を見かけたので応募したら当たったので参加しました。
資材情報
https://github.com/rosylilly/isucon11_prior
https://github.com/isucon/isucon11-prior
実施環境
ドワンゴ社が受講者分のサーバを用意してくれたとのこと。
itamaeのコードを見ると実施環境相当が作れると思われる。
CPU情報
$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 85
model name : Intel(R) Xeon(R) Gold 6248 CPU @ 2.50GHz
stepping : 7
microcode : 0x500002c
cpu MHz : 2494.140
cache size : 28160 KB
physical id : 0
siblings : 1
core id : 0
cpu cores : 1
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 22
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon nopl xtopology tsc_reliable nonstop_tsc cpuid pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single ssbd ibrs ibpb stibp ibrs_enhanced fsgsbase tsc_adjust bmi1 avx2 smep bmi2 invpcid mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xsaves arat pku ospke md_clear flush_l1d arch_capabilities
bugs : spectre_v1 spectre_v2 spec_store_bypass swapgs itlb_multihit
bogomips : 4988.28
clflush size : 64
cache_alignment : 64
address sizes : 43 bits physical, 48 bits virtual
power management:
メモリ情報
$ free -h
total used free shared buff/cache available
Mem: 3.8Gi 644Mi 810Mi 1.0Mi 2.4Gi 2.9Gi
Swap: 0B 0B 0B
ストレージ情報
$ df -h
Filesystem Size Used Avail Use% Mounted on
udev 2.0G 0 2.0G 0% /dev
tmpfs 394M 1.1M 393M 1% /run
/dev/sda1 39G 3.7G 35G 10% /
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 5.0M 0 5.0M 0% /run/lock
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/loop0 56M 56M 0 100% /snap/core18/2066
/dev/loop1 33M 33M 0 100% /snap/snapd/12057
/dev/loop2 68M 68M 0 100% /snap/lxd/20326
/dev/sda15 105M 7.9M 97M 8% /boot/efi
tmpfs 394M 0 394M 0% /run/user/1000
/dev/loop3 33M 33M 0 100% /snap/snapd/12159
tmpfs 394M 0 394M 0% /run/user/1001
事前講習で実施したこと
ベンチマークを確認
# cd /home/isucon/webapp/tools
./restart-and-bench
:: CLEAR LOGS ====>
:: RESTART SERVICES ====>
:: BENCHMARK ====>
17:28:33.794854 score: 473(474 - 1) : pass
17:28:33.794864 deduction: 0 / timeout: 13
:: ACCESS LOG ====>
+-------+-----+-----+-----+-----+-----+--------+-----------------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+--------------+------------+
| COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD | URI | MIN | MAX | SUM | AVG | P1 | P50 | P99 | STDDEV | MIN(BODY) | MAX(BODY) | SUM(BODY) | AVG(BODY) |
+-------+-----+-----+-----+-----+-----+--------+-----------------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+--------------+------------+
| 356 | 0 | 345 | 0 | 11 | 0 | GET | /api/schedules/[0-9a-zA-Z]+ | 0.004 | 1.164 | 50.540 | 0.142 | 0.000 | 0.016 | 0.136 | 0.271 | 6938.000 | 18880.000 | 2682754.000 | 7535.826 |
| 1 | 0 | 1 | 0 | 0 | 0 | POST | /initialize | 0.040 | 0.040 | 0.040 | 0.040 | 0.040 | 0.040 | 0.040 | 0.000 | 19.000 | 19.000 | 19.000 | 19.000 |
| 338 | 0 | 338 | 0 | 0 | 0 | POST | /api/reservations | 0.004 | 0.956 | 9.949 | 0.029 | 0.012 | 0.008 | 0.004 | 0.113 | 157.000 | 157.000 | 53066.000 | 157.000 |
| 285 | 0 | 285 | 0 | 0 | 0 | GET | /api/schedules | 0.004 | 0.920 | 6.224 | 0.022 | 0.004 | 0.056 | 0.012 | 0.078 | 2.000 | 982.000 | 211155.000 | 740.895 |
| 65 | 0 | 65 | 0 | 0 | 0 | POST | /api/signup | 0.004 | 0.108 | 1.092 | 0.017 | 0.012 | 0.000 | 0.016 | 0.021 | 129.000 | 161.000 | 9267.000 | 142.569 |
| 66 | 0 | 59 | 0 | 7 | 0 | POST | /api/login | 0.004 | 0.156 | 0.736 | 0.011 | 0.004 | 0.004 | 0.004 | 0.024 | 55.000 | 161.000 | 8798.000 | 133.303 |
| 7 | 0 | 7 | 0 | 0 | 0 | POST | /api/schedules | 0.004 | 0.016 | 0.076 | 0.011 | 0.016 | 0.008 | 0.004 | 0.004 | 123.000 | 129.000 | 876.000 | 125.143 |
| 351 | 0 | 351 | 0 | 0 | 0 | GET | / | 0.004 | 0.080 | 1.684 | 0.005 | 0.012 | 0.008 | 0.004 | 0.011 | 195.000 | 195.000 | 68445.000 | 195.000 |
| 350 | 0 | 350 | 0 | 0 | 0 | GET | /favicon.ico | 0.000 | 0.140 | 1.528 | 0.004 | 0.000 | 0.000 | 0.000 | 0.014 | 195.000 | 195.000 | 68250.000 | 195.000 |
| 350 | 0 | 66 | 284 | 0 | 0 | GET | /esm/index.js | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 0.000 | 1442198.000 | 95185068.000 | 271957.337 |
+-------+-----+-----+-----+-----+-----+--------+-----------------------------+-------+-------+--------+-------+-------+-------+-------+--------+-----------+-------------+--------------+------------+
netdataを確認
netdataについてはこちら
Ubuntuならaptでインストールできるとのこと。
benchmarkの実装を確認
$ cd /home/isucon/webapp/tools
$ cat restart-and-bench
#!/bin/bash
set -e
# nginx のログを削除
echo ":: CLEAR LOGS ====>"
sudo truncate -s 0 -c /var/log/nginx/access.log
# 各種サービスの再起動
echo
echo ":: RESTART SERVICES ====>"
sudo systemctl restart mysql
sudo systemctl restart web-ruby
sudo systemctl restart nginx
sleep 5
# ベンチマークの実行
echo
echo ":: BENCHMARK ====>"
/home/isucon/.x /home/isucon/bin/benchmarker
# alp で解析
echo
echo ":: ACCESS LOG ====>"
sudo cat /var/log/nginx/access.log | alp ltsv -m "/api/schedules/[0-9a-zA-Z]+" --sort avg -r
isucon2021_prior@isucon.netユーザでアプリにログイン確認
レギュレーションを確認
$ cat /home/isucon/webapp/doc/MANUAL.md
レギュレーションの内容はこちら。
sudoできるか確認
$ sudo echo test
初期状態のgit commit
isucon@isucon-002:~/webapp$ cd ~/webapp/
isucon@isucon-002:~/webapp$ git init
Initialized empty Git repository in /home/isucon/webapp/.git/
isucon@isucon-002:~/webapp$ git add .
isucon@isucon-002:~/webapp$ git commit -m "init"
[master (root-commit) 4fa4f4e] init
55 files changed, 4560 insertions(+)
create mode 100644 .gitignore
create mode 100644 README.md
create mode 100644 doc/MANUAL.md
create mode 100644 doc/REGULATION.md
create mode 100644 frontend/.gitignore
create mode 100644 frontend/.node-version
create mode 100644 frontend/package.json
create mode 100755 frontend/script/build.mjs
create mode 100644 frontend/script/package.json.cjs
create mode 100644 frontend/src/app.tsx
create mode 100644 frontend/src/context.tsx
create mode 100644 frontend/src/index.html
create mode 100644 frontend/src/index.tsx
create mode 100644 frontend/src/loginPage.tsx
create mode 100644 frontend/src/model.ts
create mode 100644 frontend/src/rootPage.tsx
create mode 100644 frontend/src/schedulePage.tsx
create mode 100644 frontend/src/signupPage.tsx
create mode 100644 frontend/tsconfig.json
create mode 100644 frontend/yarn.lock
create mode 100644 golang/.gitignore
create mode 100644 golang/.go-version
create mode 100644 golang/Makefile
create mode 100644 golang/app.go
create mode 100644 golang/db.go
create mode 100644 golang/go.mod
create mode 100644 golang/go.sum
create mode 100644 golang/main.go
create mode 120000 golang/public
create mode 100644 nodejs/.gitignore
create mode 100644 nodejs/.node-version
create mode 100644 perl/.gitignore
create mode 100644 perl/.perl-version
create mode 100644 perl/README.md
create mode 100644 php/.gitignore
create mode 100644 php/.php-version
create mode 100644 php/README.md
create mode 100644 python/.gitignore
create mode 100644 python/.python-version
create mode 100644 ruby/.bundle/config
create mode 100644 ruby/.gitignore
create mode 100644 ruby/.ruby-version
create mode 100644 ruby/Gemfile
create mode 100644 ruby/Gemfile.lock
create mode 100644 ruby/app.rb
create mode 100644 ruby/config.ru
create mode 100644 ruby/config/puma.rb
create mode 100644 ruby/db.rb
create mode 120000 ruby/public
create mode 100644 ruby/tmp/.keep
create mode 100644 sql/00_setup.sql
create mode 100644 sql/01_schema.sql
create mode 100755 tools/initdb
create mode 100755 tools/restart-and-bench
create mode 100755 tools/switch-lang
isucon@isucon-002:~/webapp$
SQLの中身を確認
isucon@isucon-002:~/webapp/sql$ cat 01_schema.sql
USE `isucon2021_prior`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` VARCHAR(255) PRIMARY KEY NOT NULL,
`email` VARCHAR(255) NOT NULL DEFAULT '',
`nickname` VARCHAR(120) NOT NULL DEFAULT '',
`staff` BOOLEAN NOT NULL DEFAULT false,
`created_at` DATETIME(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
DROP TABLE IF EXISTS `schedules`;
CREATE TABLE `schedules` (
`id` VARCHAR(255) PRIMARY KEY NOT NULL,
`title` VARCHAR(255) NOT NULL DEFAULT '',
`capacity` INT UNSIGNED NOT NULL DEFAULT 0,
`created_at` DATETIME(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
DROP TABLE IF EXISTS `reservations`;
CREATE TABLE `reservations` (
`id` VARCHAR(255) PRIMARY KEY NOT NULL,
`schedule_id` VARCHAR(255) NOT NULL,
`user_id` VARCHAR(255) NOT NULL,
`created_at` DATETIME(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4;
アプリの内容を確認
$ cd /home/isucon/webapp/ruby
$ ls
$ cat app.rb
require 'sinatra/json'
require 'active_support/json'
require 'active_support/time'
require_relative 'db'
Time.zone = 'UTC'
class App < Sinatra::Base
enable :logging
set :session_secret, 'tagomoris'
set :sessions, key: 'session_isucon2021_prior', expire_after: 3600
set :show_exceptions, false
set :public_folder, './public'
set :json_encoder, ActiveSupport::JSON
helpers do
def db
DB.connection
end
def transaction(name = :default, &block)
DB.transaction(name, &block)
end
def generate_id(table, tx)
id = ULID.generate
while tx.xquery("SELECT 1 FROM `#{table}` WHERE `id` = ? LIMIT 1", id).first
id = ULID.generate
end
id
end
def required_login!
halt(401, JSON.generate(error: 'login required')) if current_user.nil?
end
def required_staff_login!
halt(401, JSON.generate(error: 'login required')) if current_user.nil? || !current_user[:staff]
end
def current_user
db.xquery('SELECT * FROM `users` WHERE `id` = ? LIMIT 1', session[:user_id]).first
end
def get_reservations(schedule)
reservations = db.xquery('SELECT * FROM `reservations` WHERE `schedule_id` = ?', schedule[:id]).map do |reservation|
reservation[:user] = get_user(reservation[:user_id])
reservation
end
schedule[:reservations] = reservations
schedule[:reserved] = reservations.size
end
def get_reservations_count(schedule)
reservations = db.xquery('SELECT * FROM `reservations` WHERE `schedule_id` = ?', schedule[:id])
schedule[:reserved] = reservations.size
end
def get_user(id)
user = db.xquery('SELECT * FROM `users` WHERE `id` = ? LIMIT 1', id).first
user[:email] = '' if !current_user || !current_user[:staff]
user
end
end
error do
err = env['sinatra.error']
$stderr.puts err.full_message
halt 500, JSON.generate(error: err.message)
end
post '/initialize' do
transaction do |tx|
tx.query('TRUNCATE `reservations`')
tx.query('TRUNCATE `schedules`')
tx.query('TRUNCATE `users`')
id = generate_id('users', tx)
tx.xquery('INSERT INTO `users` (`id`, `email`, `nickname`, `staff`, `created_at`) VALUES (?, ?, ?, true, NOW(6))', id, 'isucon2021_prior@isucon.net', 'isucon')
end
json(language: 'ruby')
end
get '/api/session' do
json(current_user)
end
post '/api/signup' do
id = ''
nickname = ''
user = transaction do |tx|
id = generate_id('users', tx)
email = params[:email]
nickname = params[:nickname]
tx.xquery('INSERT INTO `users` (`id`, `email`, `nickname`, `created_at`) VALUES (?, ?, ?, NOW(6))', id, email, nickname)
created_at = tx.xquery('SELECT `created_at` FROM `users` WHERE `id` = ? LIMIT 1', id).first[:created_at]
{ id: id, email: email, nickname: nickname, created_at: created_at }
end
json(user)
end
post '/api/login' do
email = params[:email]
user = db.xquery('SELECT `id`, `nickname` FROM `users` WHERE `email` = ? LIMIT 1', email).first
if user
session[:user_id] = user[:id]
json({ id: current_user[:id], email: current_user[:email], nickname: current_user[:nickname], created_at: current_user[:created_at] })
else
session[:user_id] = nil
halt 403, JSON.generate({ error: 'login failed' })
end
end
post '/api/schedules' do
required_staff_login!
transaction do |tx|
id = generate_id('schedules', tx)
title = params[:title].to_s
capacity = params[:capacity].to_i
tx.xquery('INSERT INTO `schedules` (`id`, `title`, `capacity`, `created_at`) VALUES (?, ?, ?, NOW(6))', id, title, capacity)
created_at = tx.xquery('SELECT `created_at` FROM `schedules` WHERE `id` = ?', id).first[:created_at]
json({ id: id, title: title, capacity: capacity, created_at: created_at })
end
end
post '/api/reservations' do
required_login!
transaction do |tx|
id = generate_id('reservations', tx)
schedule_id = params[:schedule_id].to_s
user_id = current_user[:id]
halt(403, JSON.generate(error: 'schedule not found')) if tx.xquery('SELECT 1 FROM `schedules` WHERE `id` = ? LIMIT 1 FOR UPDATE', schedule_id).first.nil?
halt(403, JSON.generate(error: 'user not found')) unless tx.xquery('SELECT 1 FROM `users` WHERE `id` = ? LIMIT 1', user_id).first
halt(403, JSON.generate(error: 'already taken')) if tx.xquery('SELECT 1 FROM `reservations` WHERE `schedule_id` = ? AND `user_id` = ? LIMIT 1', schedule_id, user_id).first
capacity = tx.xquery('SELECT `capacity` FROM `schedules` WHERE `id` = ? LIMIT 1', schedule_id).first[:capacity]
reserved = 0
tx.xquery('SELECT * FROM `reservations` WHERE `schedule_id` = ?', schedule_id).each do
reserved += 1
end
halt(403, JSON.generate(error: 'capacity is already full')) if reserved >= capacity
tx.xquery('INSERT INTO `reservations` (`id`, `schedule_id`, `user_id`, `created_at`) VALUES (?, ?, ?, NOW(6))', id, schedule_id, user_id)
created_at = tx.xquery('SELECT `created_at` FROM `reservations` WHERE `id` = ?', id).first[:created_at]
json({ id: id, schedule_id: schedule_id, user_id: user_id, created_at: created_at})
end
end
get '/api/schedules' do
schedules = db.xquery('SELECT * FROM `schedules` ORDER BY `id` DESC');
schedules.each do |schedule|
get_reservations_count(schedule)
end
json(schedules.to_a)
end
get '/api/schedules/:id' do
id = params[:id]
schedule = db.xquery('SELECT * FROM `schedules` WHERE id = ? LIMIT 1', id).first
halt(404, {}) unless schedule
get_reservations(schedule)
json(schedule)
end
get '*' do
File.read(File.join('public', 'index.html'))
end
end
GitHub上にpushのためのプライベートリポジトリを作成
GitHub上でDeploy keysを追加＆push
Deploy keysの追加はWeb上で行う。書き込み権を付与する。
$ git remote add origin git@github.com:<account>/<privaterepo>.git
$ git branch -M main
$ git push origin main
リファクタリングの解説を聞きながら写経
リファクタリング→ベンチマークの繰り返し。
リファクタリング内容の模範解答はこちら。
ほぼ写経になってしまった。。。
アプリの修正と動作確認
$ sudo systemctl stop web-ruby
$ cd /home/isucon/webapp/ruby
$ MYSQL_QUERY_LOGGER=1 bundle exec rackup
リファクタリング中に良く打つコマンド
CPU リソース: htop
アクセスログ: sudo tail -f /var/log/nginx/access.log
cd /home/isucon/webapp
ベンチ: ./tools/restart-and-bench
ベンチマークの並列数を上げる
$ /home/isucon/.x /home/isucon/bin/benchmarker -parallelism=5
リファクタリング内容の理解その1
すでにメモリに載っている（変数に値がある）場合のSQLクエリ発行を省略している
https://github.com/rosylilly/isucon11_prior/commit/c4209d14d9481da4627082a7f81e945541b9501c
リファクタリング内容の理解その2
join句を活用することで「get_userメソッド」を呼ばなくて済むようになり結果的にSQLクエリ発行を省略している
リファクタリング内容の理解その3
気が向いたら内容理解する...