Skip to content

Instantly share code, notes, and snippets.

@takeokunn

takeokunn/isucon14.org

Last active December 19, 2024 23:32
Show Gist options
  • Save takeokunn/002b687d985b5b2af30feeadac892352 to your computer and use it in GitHub Desktop.
Save takeokunn/002b687d985b5b2af30feeadac892352 to your computer and use it in GitHub Desktop.
Download ZIP
isucon14.org
Raw
isucon14.org

isucon14(本番)

nix-shell

{ pkgs ? import <nixpkgs> { } }:
pkgs.mkShell { buildInputs = with pkgs; [ gh ]; }

機密情報

github token

ghp_xxx

Project Name

"isucon14"

接続情報

server ip address

isucon-1

3.114.140.17

isucon-2

35.74.161.58

isucon-3

54.95.129.135

事前準備

昼御飯準備

部屋の掃除や十分な睡眠

Before Setup

repo

(format "OL001-isucon/%s" repo)

check

cat ~/.ssh/config.d/isucon14

Host isucon-2 HostName 35.74.161.58 User isucon

Host isucon-3 HostName 54.95.129.135 User isucon

SSH Key生成

prepare

(expand-file-name "~/.ssh/isucon14")

2. public/private keyをscpする

public key

scp "${keyfile}.pub" isucon-1:~/.ssh/id_ed25519.pub
scp "${keyfile}.pub" isucon-2:~/.ssh/id_ed25519.pub
scp "${keyfile}.pub" isucon-3:~/.ssh/id_ed25519.pub

private key

scp "${keyfile}" isucon-1:~/.ssh/id_ed25519
scp "${keyfile}" isucon-2:~/.ssh/id_ed25519
scp "${keyfile}" isucon-3:~/.ssh/id_ed25519

各サーバで確認

ssh -T [email protected]

3. 動いてるものを確認する

system information

isucon-1
echo "=== System Information ==="
echo "Hostname: $(hostname)"
echo "Kernel: $(uname -r)"
echo "Uptime: $(uptime -p)"
echo "OS: $(lsb_release -d | cut -f2)"
echo "CPU: $(lscpu | grep 'Model name' | awk -F ': ' '{print $2}')"
echo "Memory: $(free -h | grep Mem | awk '{print $3 "/" $2}')"
echo "Disk Usage: $(df -h / | grep / | awk '{print $5 " (" $3 "/" $2 ")"}')"
echo "GPU: $(lspci | grep -i 'vga\|3d\|2d')"
isucon-2
echo "=== System Information ==="
echo "Hostname: $(hostname)"
echo "Kernel: $(uname -r)"
echo "Uptime: $(uptime -p)"
echo "OS: $(lsb_release -d | cut -f2)"
echo "CPU: $(lscpu | grep 'Model name' | awk -F ': ' '{print $2}')"
echo "Memory: $(free -h | grep Mem | awk '{print $3 "/" $2}')"
echo "Disk Usage: $(df -h / | grep / | awk '{print $5 " (" $3 "/" $2 ")"}')"
echo "GPU: $(lspci | grep -i 'vga\|3d\|2d')"
isucon-3
echo "=== System Information ==="
echo "Hostname: $(hostname)"
echo "Kernel: $(uname -r)"
echo "Uptime: $(uptime -p)"
echo "OS: $(lsb_release -d | cut -f2)"
echo "CPU: $(lscpu | grep 'Model name' | awk -F ': ' '{print $2}')"
echo "Memory: $(free -h | grep Mem | awk '{print $3 "/" $2}')"
echo "Disk Usage: $(df -h / | grep / | awk '{print $5 " (" $3 "/" $2 ")"}')"
echo "GPU: $(lspci | grep -i 'vga\|3d\|2d')"

middleware

sudo systemctl list-unit-files --type=service | grep -E "nginx|apache"
sudo systemctl list-unit-files --type=service | grep -E "mysql|postgresql"
sudo systemctl list-unit-files --type=service | grep -E "redis|memcached"

4. git pushする

git config

git config --global user.email "[email protected]"
git config --global user.name "isucon"

5. 各サーバでgit管理をできるようにする

git fetch

git init
git remote add origin [email protected]:OL001-isucon/isucon14.git
git fetch
git reset --hard remotes/origin/main

6. localでgit cloneする

ghq get "[email protected]:${qualified_repo}.git"

Ansible Setup

1. org-tangleしてansible fileを出力してcommitする

2. 必要ならhosts.ymlを修正する

3. sandbox playbookを動かす

4. install_tools playbookを動かす

5. nginx.conf修正

値を確認する

nginx.conf
cat /etc/nginx/nginx.conf

events { worker_connections 768;

}

http {

##

##

sendfile on; tcp_nopush on; types_hash_max_size 2048;

include /etc/nginx/mime.types; default_type application/octet-stream;

##

##

ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3; # Dropping SSLv3, ref: POODLE ssl_prefer_server_ciphers on;

##

##

access_log /var/log/nginx/access.log;

##

##

gzip on;

##

##

include /etc/nginx/conf.d/*.conf; include /etc/nginx/sites-enabled/*; }

#mail {

#

#

#

#}

sites-enabled
ls -la /etc/nginx/sites-enabled
cat /etc/nginx/sites-enabled/isucon.conf

client_max_body_size 10m; root home/isucon/private_isu/webapp/public;

location / { proxy_set_header Host $host; proxy_pass http://localhost:8080; } }

ansibleを修正する

  • [X] ansible/roles/before_bench/nginx.yml
  • [X] ansible/etc/nginx/nginx.dev.conf
  • [X] ansible/etc/nginx/sites-enabled/isuride.dev.conf
  • [X] ansible/etc/nginx/sites-enabled/isupipe.prod.conf

json log出力に修正する

log_format json escape=json '{"time":"$time_local",'
' "host":"$remote_addr",'
' "forwardedfor":"$http_x_forwarded_for",'
' "req":"$request",'
' "status":"$status",'
' "method":"$request_method",'
' "uri":"$request_uri",'
' "body_bytes":$body_bytes_sent,'
' "referer":"$http_referer",'
' "ua":"$http_user_agent",'
' "request_time":$request_time,'
' "cache":"$upstream_http_x_cache",'
' "runtime":"$upstream_http_x_runtime",'
' "response_time":"$upstream_response_time",'
' "vhost":"$host"'
'}';

access_log /var/log/nginx/access.log json;

6. before_benchを気合で動かす

7. benchを動かしてafter_benchにする

DB Setup

1. mysqldefを本当に使うか検討する

2. mysqldefを実行してcommitする

server

mysqldef -u isucon -p isucon isupipe --export > schema.sql

local

scp isucon@isucon-3:/home/isucon/schema.sql ./schema.sql

3. before_benchでsqldefを動かせるようにする

4. GitHub Actionsのdbdocが正常に動いているかどうか

README追記系

1. DB接続情報

## DB
### 接続情報

```
host: 127.0.0.1
port: 3306
user: isucon
password: isucon
database: isuride
```

2. レコード数をREADME.mdに書く

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'isuride';
### レコード数

```sql
<結果を貼る>
```

3. isucrud追記

link: https://github.com/mazrean/isucrud/

定型行動

1. go mod tidyをMakefileに追記

2. pprofを有効にする

PRを出す

link: https://github.com/OL001-isucon/isucon9-qualify/pull/54 link: https://isucon-workshop.trap.show/text/chapter-3/3-pprof.html

diff --git a/webapp/go/main.go b/webapp/go/main.go
index 6a9f192..ee3a892 100755
--- a/webapp/go/main.go
+++ b/webapp/go/main.go
@@ -9,6 +9,8 @@ import (
        "log"
        "net"
        "net/http"
+       _ "net/http/pprof"
+
        "os"
        "os/exec"
        "path/filepath"
@@ -351,6 +353,12 @@ func writeQRCodeImgBinary(transactionEvidenceID int64, imgBinary []byte) error {
 }

 func main() {
+       runtime.SetBlockProfileRate(1)
+       runtime.SetMutexProfileFraction(1)
+       go func() {
+               log.Println(http.ListenAndServe("0.0.0.0:6060", nil))
+       }()
+
        host := os.Getenv("MYSQL_HOST")
        if host == "" {
                host = "127.0.0.1"

3. go-jsonライブラリの差し替え

link: https://github.com/goccy/go-json#how-to-use

go.mod/go.sumもpushする

4. golangのconnection option設定

db.SetMaxOpenConns(50)
db.SetMaxIdleConns(50*2)
db.SetConnMaxLifetime(5* time.Minute)
db.SetConnMaxIdleTime(2 * time.Minute)

5. ベンチが周ることを確認

link: https://portal.isucon.net/contestant/benchmark_jobs/3370

pprof利用手順を共有

1. before_benchを実行
2. benchを回す前に以下を実行

```
$ go tool pprof http://localhost:6060/debug/pprof/profile?seconds=120
Saved profile in /home/isucon/pprof/pprof.isucari.samples.cpu.001.pb.gz (これを控える)
```

3. http://localhost:6070/ui/ から見れるようになる

```
$ ssh -L 6070:localhost:6070 isucon-3

# サーバ内で実行する
$ go tool pprof -http=localhost:6070 /home/isucon/pprof/pprof.isuride.samples.cpu.001.pb.gz
```

マニュアルを熟読する

link: https://gist.github.com/wtks/0a3268de13856ed6e18c6560023ec436

本番用 Setup

1. nginx.confをチューニングする

link: https://github.com/OL001-isucon/isucon9-qualify/tree/main/ansible/etc/nginx

2. mysql.cnfを一応確認する

2. env=prodでも流れるか確認する

提出準備

link: https://github.com/OL001-isucon/isucon14/pull/16

logをoffにする

diff --git a/webapp/go/main.go b/webapp/go/main.go
index ec33433..f9f1ecc 100755
--- a/webapp/go/main.go
+++ b/webapp/go/main.go
@@ -6,6 +6,7 @@ import (
        "fmt"
        "html/template"
        "io"
+       "io/ioutil"
        "log"
        "net"
        "net/http"
@@ -334,6 +335,7 @@ func init() {
        store = cs

        log.SetFlags(log.Ldate | log.Ltime | log.Lshortfile)
+       log.SetOutput(ioutil.Discard)

        templates = template.Must(template.ParseFiles(
                "../public/index.html",

pprofをrevertする

対応項目

画像周りをnginxからちゃんと配信する

link: https://github.com/OL001-isucon/isucon14/pull/22

通知周りをSSEに変換するを書く

link: マニュアル#通知エンドポイント

マニュアル

通知エンドポイント
ISURIDEではクライアントにライドの状態の変化を通知するための2つのエンドポイントが実装されています。

- ユーザー向け通知： /api/app/notification
- 椅子向け通知： /api/chair/notification

これらはリファレンス実装では通常のJSONレスポンスを返すエンドポイントですが、SSE(Server-Sent Events)を利用してリアルタイム通知を実装することも可能です。 どちらの実装においても、状態が変更されてから3秒以内に通知されていることが期待されます。

JSONレスポンス
サーバーがJSONレスポンスを返す場合、クライアントはポーリングによってライドの状態の変化を取得します。
Content-Typeは application/json です。
クライアントはレスポンスの retry_after_ms で指定された時間(ms)が経過した後に再度リクエストを送信します。
リファレンス実装では30ms後に再度リクエストを送信するようになっています。

SSE(Server-Sent Events)
サーバーがSSEを利用してリアルタイム通知を行う場合、クライアントはSSEのコネクションからライドの状態の変化を取得します。
Content-Typeは text/event-stream です。
通知メッセージは data:  に続けて、webappディレクトリに存在するopenapi.yamlの components.schemas.UserNotificationData または components.schemas.ChairNotificationData のJSON文字列を返します。
これはJSONレスポンスの data に相当します。
通知メッセージは1行で記述し、最後に改行を入れる必要があります。
実際のレスポンス例は以下を参照してください。
クライアントとの間にSSEのコネクションを確立した後、即座に最新のライドの状態を送信しなければなりません
その後は随時最新のライドの状態を送信します。
状態が変わった時のみ即座に送信することが望ましいです。
以下はSSEでの通知レスポンスの例です。

ユーザー向け通知
```
data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"ENROUTE","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"PICKUP","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"CARRYING","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"CARRYING","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"CARRYING","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"CARRYING","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"ARRIVED","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":1,"total_evaluation_avg":5}},"created_at":1733561322336,"updated_at":1733561322690}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"COMPLETED","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":2,"total_evaluation_avg":4.5}},"created_at":1733561322336,"updated_at":1733561370916}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"COMPLETED","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":2,"total_evaluation_avg":4.5}},"created_at":1733561322336,"updated_at":1733561370916}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"COMPLETED","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":2,"total_evaluation_avg":4.5}},"created_at":1733561322336,"updated_at":1733561370916}

data: {"ride_id":"01JEG4X2TZSE169T99XERS990M","pickup_coordinate":{"latitude":0,"longitude":0},"destination_coordinate":{"latitude":20,"longitude":20},"fare":1500,"status":"COMPLETED","chair":{"id":"01JDFEF7MGXXCJKW1MNJXPA77A","name":"QC-L13-8361","model":"クエストチェア Lite","stats":{"total_rides_count":2,"total_evaluation_avg":4.5}},"created_at":1733561322336,"updated_at":1733561370916}
```

WAIT サーバ分割

envの確認

env.shのpath確認

isucon@ip-172-31-22-126:/etc/systemd/system$ cat /etc/systemd/system/isuride-go.service

[Unit]
Description=isuride-go
After=syslog.target
After=mysql.service
Requires=mysql.service

[Service]
WorkingDirectory=/home/isucon/webapp/go
EnvironmentFile=/home/isucon/env.sh

User=isucon
Group=isucon
ExecStart=/home/isucon/webapp/go/isuride
ExecStop=/bin/kill -s QUIT $MAINPID

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target

env.sh中身

isucon-1
cat /home/isucon/env.sh

ISUCON_MATCHING_INTERVAL=0.5

isucon-2
cat /home/isucon/env.sh

ISUCON_MATCHING_INTERVAL=0.5

isucon-3
cat /home/isucon/env.sh

ISUCON_MATCHING_INTERVAL=0.5

mysql権限付与

GRANT ALL PRIVILEGES ON *.* TO `isucon`@`%` WITH GRANT OPTION;
FLUSH PRIVILEGES;

サーバレイアウトを考える

  • isucon-1: nginx, app(/api/app/notification, /api/chair/notification)
  • isucon-2: app(それ以外)
  • isucon-3: mysql

WAIT 手順書作成

# サーバ分割

## レイアウト

- isucon-1: nginx, app(/api/app/notification, /api/chair/notification)
- isucon-2: app(それ以外)
- isucon-3: mysql

## 作業

1. isucon-1の ~/env.sh を以下のように修正

isucon-1 before:

```
ISUCON_DB_HOST="127.0.0.1"
ISUCON_DB_PORT="3306"
ISUCON_DB_USER="isucon"
ISUCON_DB_PASSWORD="isucon"
ISUCON_DB_NAME="isuride"

# マッチング間隔（秒）
ISUCON_MATCHING_INTERVAL=0.5
```

isucon-1 after:

```
ISUCON_DB_HOST="192.168.0.13"
ISUCON_DB_PORT="3306"
ISUCON_DB_USER="isucon"
ISUCON_DB_PASSWORD="isucon"
ISUCON_DB_NAME="isuride"

# マッチング間隔（秒）
ISUCON_MATCHING_INTERVAL=0.5
```

この変更でnginxを分割してる
https://github.com/OL001-isucon/isucon14/pull/28

2. isucon-1, isucon-3にbefore_bench prodを流す
3. isucon-1に対してベンチを回す

Programs

ansible

README.md

# Ansible
## 開発者向け
### isucon-1

before_bench:

```console
$ export BRANCH_NAME=main
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-1 ./ansible/playbook/before_bench.yml --extra-vars "env=dev" --extra-vars "branch=$BRANCH_NAME" --verbose
```

after_bench:

```console
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-1 ./ansible/playbook/after_bench.yml --verbose
```

### isucon-2

before_bench:

```console
$ export BRANCH_NAME=main
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-2 ./ansible/playbook/before_bench.yml --extra-vars "env=dev" --extra-vars "branch=$BRANCH_NAME" --verbose
```

after_bench:

```console
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-2 ./ansible/playbook/after_bench.yml --verbose
```

### isucon-3

before_bench:

```console
$ export BRANCH_NAME=main
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-3 ./ansible/playbook/before_bench.yml --extra-vars "env=dev" --extra-vars "branch=$BRANCH_NAME" --verbose
```

after_bench:

```console
$ ansible-playbook -i ./ansible/hosts.yml -l isucon-3 ./ansible/playbook/after_bench.yml --verbose
```

## インフラ担当者向け
## install tools

```console
$ ansible-playbook -i ./ansible/hosts.yml ./ansible/playbook/install_tools.yml --verbose
```

## before bench

for specific:

```console
$ ansible-playbook -i ./ansible/hosts.yml -l server ./ansible/playbook/before_bench.yml --extra-vars "env=dev" --extra-vars "branch=main" --verbose
$ ansible-playbook -i ./ansible/hosts.yml -l server ./ansible/playbook/before_bench.yml --extra-vars "env=prod" --extra-vars "branch=main" --verbose
```

for all:

```console
$ ansible-playbook -i ./ansible/hosts.yml ./ansible/playbook/before_bench.yml --extra-vars "env=dev" --extra-vars "branch=main" --verbose
$ ansible-playbook -i ./ansible/hosts.yml ./ansible/playbook/before_bench.yml --extra-vars "env=prod" --extra-vars "branch=main" --verbose
```

## after_bench

```console
$ ansible-playbook -i ./ansible/hosts.yml -l server ./ansible/playbook/after_bench.yml --verbose
```

## sandbox

```console
$ ansible-playbook -i ./ansible/hosts.yml -l server ./ansible/playbook/sandbox.yml --verbose
```

hosts.yml

---
all:
  hosts:
    isucon-1:
      ansible_host: <<isucon-server-ip-address-1>>
      ansible_user: isucon
    isucon-2:
      ansible_host: <<isucon-server-ip-address-2>>
      ansible_user: isucon
    isucon-3:
      ansible_host: <<isucon-server-ip-address-3>>
      ansible_user: isucon

ansible.cfg

[defaults]
inventory_unparsed_warning=False
host_key_checking=False

[ssh_connection]
control_path = %(directory)s/%%h-%%r
ssh_args = -o ControlMaster=auto -o ControlPersist=60s -F ssh_config
pipelining = True

etc

alp
config.yml
---
reverse: true
sort: sum
matching_groups:
  - /api/user/\w+/theme
output: count,method,uri,1xx,2xx,3xx,4xx,5xx,sum,avg,min,max
gh
hosts.yml
github.com:
  user: isucon
  oauth_token: <<github-token>>
git
.gitconfig
[core]
quotepath = off
ignorecase = false
safecrlf = true
autocrlf = false
precomposeunicode = true

[alias]
st = status
br = branch
co = commit
ch = checkout
ad = add
fix = commit --amend --no-edit

[user]
name = isucon
email = [email protected]

[fetch]
prune = true

[pull]
rebase = false

[diff]
patience = true

[color]
ui = auto
status = auto
diff = auto
branch = auto
interactive = auto
grep = auto

[init]
defaultBranch = main
mysql
mysqld.dev.cnf
[mysqld]

#
# Basic Settings
#
user = mysql
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0

#
# Basic Params
#
key_buffer_size = 4G
max_connections = 1024

#
# innodb params
#
innodb_buffer_pool_size = 1G
innodb_log_file_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip_innodb_doublewrite

#
# Logging
#
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 0
log-queries-not-using-indexes

#
# binlog
#
disable-log-bin
mysqld.prod.cnf
[mysqld]

#
# Basic Settings
#
user = mysql
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
skip-name-resolve

#
# Basic Params
#
key_buffer_size = 4G
max_connections = 1024

#
# innodb params
#
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip_innodb_doublewrite

#
# Logging
#
general_log = 0

#
# binlog
#
disable-log-bin
nginx
nginx.dev.conf
user www-data;
worker_processes auto;
pid /run/nginx.pid;
include /etc/nginx/modules-enabled/*.conf;

events {
    worker_connections 768;
    # multi_accept on;
}

http {

    ##
    # Basic Settings
    ##

    sendfile on;
    tcp_nopush on;
    types_hash_max_size 2048;
    # server_tokens off;

    # server_names_hash_bucket_size 64;
    # server_name_in_redirect off;

    include /etc/nginx/mime.types;
    default_type application/octet-stream;

    ##
    # SSL Settings
    ##

    ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3; # Dropping SSLv3, ref: POODLE
    ssl_prefer_server_ciphers on;

    ##
    # Logging Settings
    ##

    log_format json escape=json '{"time":"$time_local",'
        ' "host":"$remote_addr",'
        ' "forwardedfor":"$http_x_forwarded_for",'
        ' "req":"$request",'
        ' "status":"$status",'
        ' "method":"$request_method",'
        ' "uri":"$request_uri",'
        ' "body_bytes":$body_bytes_sent,'
        ' "referer":"$http_referer",'
        ' "ua":"$http_user_agent",'
        ' "request_time":$request_time,'
        ' "cache":"$upstream_http_x_cache",'
        ' "runtime":"$upstream_http_x_runtime",'
        ' "response_time":"$upstream_response_time",'
        ' "vhost":"$host"'
        '}';

    access_log /var/log/nginx/access.log json;
    error_log /var/log/nginx/error.log;

    ##
    # Gzip Settings
    ##

    gzip on;

    # gzip_vary on;
    # gzip_proxied any;
    # gzip_comp_level 6;
    # gzip_buffers 16 8k;
    # gzip_http_version 1.1;
    # gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript;

    ##
    # Virtual Host Configs
    ##

    include /etc/nginx/conf.d/*.conf;
    include /etc/nginx/sites-enabled/*;
    include /etc/nginx/sites-available/*;
}


#mail {
#       # See sample authentication script at:
#       # http://wiki.nginx.org/ImapAuthenticateWithApachePhpScript
#
#       # auth_http localhost/auth.php;
#       # pop3_capabilities "TOP" "USER";
#       # imap_capabilities "IMAP4rev1" "UIDPLUS";
#
#       server {
#               listen     localhost:110;
#               protocol   pop3;
#               proxy      on;
#       }
#
#       server {
#               listen     localhost:143;
#               protocol   imap;
#               proxy      on;
#       }
#}
nginx.prod.conf
user www-data;
worker_processes auto;

include /etc/nginx/modules-enabled/*.conf;
error_log /dev/null crit;
pid /run/nginx.pid;

worker_rlimit_nofile  65536;

events {
    worker_connections  16384;
}


http {
    include  /etc/nginx/mime.types;
    default_type  application/octet-stream;

    http2_max_requests 1000000;
    keepalive_requests 1000000;
    keepalive_timeout 65;

    access_log  off;

    include  /etc/nginx/conf.d/*.conf;
    include  /etc/nginx/sites-enabled/*.conf;
    include  /etc/nginx/sites-available/*.conf;

    ssl_protocols TLSv1 TLSv1.1 TLSv1.2 TLSv1.3; # Dropping SSLv3, ref: POODLE
    ssl_prefer_server_ciphers on;

    gzip on;
    gzip_vary on;
    gzip_proxied any;
    gzip_comp_level 6;
    gzip_buffers 16 8k;
    gzip_http_version 1.1;
}
systemd
mysql.service.d/limits.conf
[Service]
LimitNOFILE=65535
vim
.vimrc
set encoding=utf-8
set fileencodings=utf-8,euc-jp,cp932
set clipboard+=unnamed
set backspace=2
set tabstop=2
set shiftwidth=2
set laststatus=2
set statusline=%y
set showmatch
set wrapscan
set hlsearch
set showcmd
set title
set number relativenumber
set cursorline
set nofoldenable
set noswapfile
set expandtab
set splitbelow
set splitright
set incsearch
set ignorecase
set smartcase

nmap / /\v
nmap <Leader><Leader> V
nmap <Esc><Esc> :nohlsearch<CR><Esc>

syntax on
filetype plugin indent on
bottom
bottom.toml
[colors]
avg_cpu_color = "Red"
border_color = "White"
graph_color = "Gray"
highlighted_border_color = "LightMagenta"
selected_bg_color = "Magenta"
selected_text_color = "Black"
table_header_color = "Blue"
text_color = "White"
widget_title_color = "Cyan"

[disk_filter]
is_list_ignored = true
list = ["/dev/loop\\d+"]
regex = true

[flags]
basic = false
case_sensitive = false
dot_marker = false
group_processes = true
hide_table_gap = true
rate = 700

[[row]]
[[row.child]]
ratio = 2
type = "cpu"

[[row.child]]
type = "mem"

[[row]]
[[row.child]]
ratio = 2
type = "net"

[[row.child]]
ratio = 2
type = "disk"

[[row.child]]
type = "temp"

[[row]]
ratio = 3
[[row.child]]
default = true
type = "proc"

playbook

after_bench.yml
---
- name: After benchmark
  hosts: all
  tasks:
    - name: Copy alp config
      import_tasks: ../roles/after_bench/alp.yml
      become: true
    - import_tasks: ../roles/after_bench/result.yml
before_bench.yml
---
- name: Before benchmark
  hosts: all
  tasks:
    - name: Git Pull
      import_tasks: ../roles/before_bench/git-pull.yml
    - name: Prepare golang
      import_tasks: ../roles/before_bench/prepare.yml
    - name: Truncate Task
      import_tasks: ../roles/before_bench/truncate.yml
      become: true
    - name: Nginx Task
      import_tasks: ../roles/before_bench/nginx.yml
      become: true
    - name: Mysql Task
      import_tasks: ../roles/before_bench/mysql.yml
      become: true
    - name: Redis Task
      import_tasks: ../roles/before_bench/redis.yml
      become: true
    # - name: Run migrate
    #   import_tasks: ../roles/before_bench/sqldef.yml
install_tools.yml
---
- name: Install tools
  hosts: all
  become: true
  tasks:
    - import_tasks: ../roles/install_tools/bottom.yml
    - import_tasks: ../roles/install_tools/alp.yml
    - import_tasks: ../roles/install_tools/dotfiles.yml
    - import_tasks: ../roles/install_tools/gh.yml
    - import_tasks: ../roles/install_tools/percona-toolkit.yml
    - import_tasks: ../roles/install_tools/script.yml
    - import_tasks: ../roles/install_tools/sqldef.yml
    - import_tasks: ../roles/install_tools/dstat.yml
    - import_tasks: ../roles/install_tools/graphviz.yml
    - import_tasks: ../roles/install_tools/tig.yml
    - import_tasks: ../roles/install_tools/redis.yml
    - import_tasks: ../roles/before_bench/sysctl.yml
sandbox.yml
---
- name: Sandbox
  hosts: all
  tasks:
    - name: echo 'Hello world'
      shell: echo 'Hello world'

roles

after_bench
alp.yml
---
- name: Copy alp config.yml
  copy:
    src: ../../etc/alp/config.yml
    dest: /etc/alp/config.yml
result.yml
---
- name: Copy result.sh
  copy:
    src: ../../shell/result.sh
    dest: /home/isucon/result.sh
    mode: 0755

- name: Aggregate result && Report to github issue
  shell: bash /home/isucon/result.sh
before_bench
git-pull.yml
- name: Pull From Git
  git:
    repo: [email protected]:<<qualified-repo()>>.git
    dest: /home/isucon
    update: yes
    version: "{{ branch }}"
    force: yes
mysql.yml
---
- name: Create directory
  file:
    path: /etc/systemd/system/mysql.service.d
    state: directory

- name: Copy systemd mysql.service.d limits.conf
  copy:
    src: ../../etc/systemd/mysql.service.d/limits.conf
    dest: /etc/systemd/system/mysql.service.d/limits.conf
    mode: 0644

- name: Copy mysqld.cnf
  copy:
    src: ../../etc/mysql/mysqld.{{ env }}.cnf
    dest: /etc/mysql/mysql.conf.d/mysqld.cnf
    mode: 0644

- name: Change file ownership /var/log/mysql/error.log
  ansible.builtin.file:
    path: /var/log/mysql/error.log
    owner: mysql
    group: adm
    mode: 0644

- name: Change file ownership /var/log/mysql/mysql-slow.log
  ansible.builtin.file:
    path: /var/log/mysql/mysql-slow.log
    owner: mysql
    group: adm
    mode: 0644

- name: Restart mysql
  service:
    name: mysql
    state: restarted
    enabled: yes
nginx.yml
---
# - name: Copy nginx.conf
#   copy:
#     src: ../../etc/nginx/nginx.{{ env }}.conf
#     dest: /etc/nginx/nginx.conf
#     mode: 0644

# - name: Copy isucon.conf
#   copy:
#     src: ../../etc/nginx/sites-enabled/isucon.{{ env }}.conf
#     dest: /etc/nginx/sites-enabled/isucon.conf
#     mode: 0644

- name: Change file ownership /var/log/nginx/access.log
  ansible.builtin.file:
    path: /var/log/nginx/access.log
    owner: www-data
    group: adm
    mode: 0640

- name: Change file ownership /var/log/nginx/error.log
  ansible.builtin.file:
    path: /var/log/nginx/error.log
    owner: www-data
    group: adm
    mode: 0640

- name: Restart nginx
  service:
    name: nginx
    state: restarted
    enabled: yes
prepare.yml
---

- name: Build go
  environment:
    PATH: /home/isucon/local/golang/bin:{{ ansible_env.PATH }}
  make:
    chdir: /home/isucon/webapp/golang
    target: build
    params:
      DEST: /home/isucon/webapp/golang

- name: Restart go
  become: true
  service:
    name: isuride-go.service
    state: restarted
    enabled: yes
redis.yml
---
- name: Restart redis
  service:
    name: redis
    state: restarted
    enabled: yes
sqldef.yml
---
# - name: Migrate down from trigger
#   shell: mysql -u isucon --password="isucon" <<project-name>> < trigger_down.sql

# - name: Migrate from sqldef(<<project-name>>)
#   shell: mysqldef -u isucon -p isucon <<project-name>> < schema.sql

# - name: Migrate from sqldef(isudns)
#   shell: mysqldef -u isucon -p isucon isudns < schema_dns.sql

# - name: Migrate up from trigger
#   shell: mysql -u isucon --password="isucon" <<project-name>> < trigger_up.sql
systemd.yml
---
- name: Create directory
  file:
    path: /etc/systemd/system/mysql.service.d
    state: directory

- name: Copy systemd mysql.service.d limits.conf
  copy:
    src: ../../etc/systemd/mysql.service.d/limits.conf
    dest: /etc/systemd/system/mysql.service.d/limits.conf
    mode: 0644

- name: Restart mysql
  systemd:
    name: mysql.service
    state: restarted
    daemon_reload: yes
    enabled: yes
truncate.yml
---
- name: Truncate /var/log/nginx/access.log
  community.general.filesize:
    path: /var/log/nginx/access.log
    size: 0

- name: Truncate /var/log/nginx/error.log
  community.general.filesize:
    path: /var/log/nginx/error.log
    size: 0

- name: Truncate /var/log/mysql/mysql-slow.log
  community.general.filesize:
    path: /var/log/mysql/mysql-slow.log
    size: 0
install_tools
alp.yml
---
- name: Download alp tar.gz archive
  get_url:
    url: https://github.com/tkuchiki/alp/releases/download/v1.0.21/alp_linux_amd64.tar.gz
    dest: /tmp/alp.tar.gz
    mode: 0644

- name: Extract alp tar.gz archive
  unarchive:
    src: /tmp/alp.tar.gz
    dest: /usr/local/bin
    remote_src: true
    mode: 0755

- name: Create alp directory
  file:
    path: /etc/alp
    state: directory

- name: Copy config.yml
  copy:
    src: ../../etc/alp/config.yml
    dest: /etc/alp/config.yml

- name: Copy alp.sh
  copy:
    src: ../../shell/alp.sh
    dest: /home/isucon/alp.sh
    mode: 0755
bottom.yml
---
- name: Download bottom package
  ansible.builtin.get_url:
    url: https://github.com/ClementTsang/bottom/releases/download/0.10.2/bottom_0.10.2-1_amd64.deb
    dest: /tmp/bottom_0.10.2-1_amd64.deb

- name: Install bottom package
  ansible.builtin.apt:
    deb: /tmp/bottom_0.10.2-1_amd64.deb
    state: present

- name: Create directory for bottom configuration
  ansible.builtin.file:
    path: /home/isucon/.config/bottom
    state: directory

- name: Copy bottom.toml
  copy:
    src: ../../etc/bottom/bottom.toml
    dest: /home/isucon/.config/bottom/bottom.toml
dotfiles.yml
---
- name: Copy .vimrc
  copy:
    src: ../../etc/vim/.vimrc
    dest: /home/isucon/.vimrc

- name: Copy .gitconfig
  copy:
    src: ../../etc/git/.gitconfig
    dest: /home/isucon/.gitconfig
dstat.yml
---
- name: Install dstat
  apt:
    name: dstat
    state: latest
graphviz.yml
---
- name: Install graphviz
  apt:
    name: graphviz
    state: latest
gh.yml
---
- name: Install gh command
  shell: |
    type -p curl >/dev/null || (sudo apt update && sudo apt install curl -y)
    curl -fsSL https://cli.github.com/packages/githubcli-archive-keyring.gpg | sudo dd of=/usr/share/keyrings/githubcli-archive-keyring.gpg \
        && sudo chmod go+r /usr/share/keyrings/githubcli-archive-keyring.gpg \
        && echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/githubcli-archive-keyring.gpg] https://cli.github.com/packages stable main" | sudo tee /etc/apt/sources.list.d/github-cli.list > /dev/null \
        && sudo apt update \
        && sudo apt install gh -y

- name: Create gh directory
  file:
    path: /home/isucon/.config/gh
    state: directory
    owner: isucon
    group: isucon
    mode: '777'

- name: Copy gh hosts.yml
  copy:
    src: ../../etc/gh/hosts.yml
    dest: /home/isucon/.config/gh/hosts.yml
    owner: isucon
    group: isucon
    mode: '777'
percona-toolkit.yml
---
- name: Install percona-toolkit
  apt:
    name: percona-toolkit
    state: latest

- name: Copy pt-query-digest.sh
  copy:
    src: ../../shell/pt-query-digest.sh
    dest: /home/isucon/pt-query-digest.sh
    mode: 0755
redis.yml
---
- name: Install redis
  apt:
    name: redis
    state: latest

- name: Restart redis
  service:
    name: redis
    state: restarted
    enabled: yes
script.yml
---
- name: Copy alp.sh
  copy:
    src: ../../shell/alp.sh
    dest: /home/isucon/alp.sh
    mode: 0755

- name: Copy pt-query-digest.sh
  copy:
    src: ../../shell/pt-query-digest.sh
    dest: /home/isucon/pt-query-digest.sh
    mode: 0755

- name: Copy result.sh
  copy:
    src: ../../shell/result.sh
    dest: /home/isucon/result.sh
    mode: 0755

- name: Copy before_bench.sh
  copy:
    src: ../../shell/before_bench.sh
    dest: /home/isucon/before_bench.sh
    mode: 0755
sqldef.yml
---
- name: Download mysqldef tar.gz archive
  get_url:
    url: https://github.com/k0kubun/sqldef/releases/latest/download/mysqldef_linux_amd64.tar.gz
    dest: /tmp/mysqldef.tar.gz
    mode: 0644

- name: Extract mysqldef tar.gz archive
  unarchive:
    src: /tmp/mysqldef.tar.gz
    dest: /usr/local/bin
    remote_src: true
    mode: 0755

- name: Download psqldef tar.gz archive
  get_url:
    url: https://github.com/k0kubun/sqldef/releases/latest/download/psqldef_linux_amd64.tar.gz
    dest: /tmp/psqldef.tar.gz
    mode: 0644

- name: Extract psqldef tar.gz archive
  unarchive:
    src: /tmp/psqldef.tar.gz
    dest: /usr/local/bin
    remote_src: true
    mode: 0755

- name: Download sqlite3def tar.gz archive
  get_url:
    url: https://github.com/k0kubun/sqldef/releases/latest/download/sqlite3def_linux_amd64.tar.gz
    dest: /tmp/sqlite3def.tar.gz
    mode: 0644

- name: Extract sqlite3def tar.gz archive
  unarchive:
    src: /tmp/sqlite3def.tar.gz
    dest: /usr/local/bin
    remote_src: true
    mode: 0755
tig.yml
---
- name: Install tig
  apt:
    name: tig
    state: latest
sysctl.yml
---
- name: Update net.core.somaxconn
  sysctl:
    name: net.core.somaxconn
    value: 10000
    reload: true

- name: Update net.ipv4.ip_local_port_range
  sysctl:
    name: net.ipv4.ip_local_port_range
    value: 10000 65535
    reload: true

shell

alp.sh
sudo cat /var/log/nginx/access.log | alp json --config /etc/alp/config.yml
pt-query-digest.sh
sudo pt-query-digest /var/log/mysql/mysql-slow.log
result.sh
# for gh command
REPO="${qualified_repo()}"
TITLE=$(date -u -d '+9 hours' +"%Y/%m/%d(%a)%H:%M:%S")
ISSUE_URL=$(gh issue create --repo $REPO --title $TITLE --body "")

# for alp command
echo "alp:" > /tmp/alp
echo "\`\`\`" >> /tmp/alp
sudo cat /var/log/nginx/access.log | alp json --config /etc/alp/config.yml >> /tmp/alp
echo "\`\`\`" >> /tmp/alp
gh issue comment $ISSUE_URL --body-file /tmp/alp

# for pt-query-digest command
echo "pt-query-digest:" > /tmp/pt-query-digest
sudo pt-query-digest /var/log/mysql/mysql-slow.log| dd bs=1 count=65500 of=/tmp/pt-query-digest-raw

echo "\`\`\`" >> /tmp/pt-query-digest
cat /tmp/pt-query-digest-raw >> /tmp/pt-query-digest
echo "\`\`\`" >> /tmp/pt-query-digest

gh issue comment $ISSUE_URL --body-file /tmp/pt-query-digest
before_bench.sh
#!/bin/bash

# truncate logs
echo "Truncate Logs..."
sudo truncate -s 0 /var/log/nginx/access.log
sudo truncate -s 0 /var/log/nginx/error.log
sudo truncate -s 0 /var/log/mysql/mysql-slow.log

# truncate middleware
echo "Truncate Middleware..."
sudo systemctl restart mysql &
sudo systemctl restart nginx &
sudo systemctl restart redis &
wait

# build golang
echo "Building Go application..."
export PATH=/home/isucon/local/golang/bin:$PATH
make -C /home/isucon/webapp/go

echo "Restarting isuride-go.service..."
sudo systemctl restart isuride-go.service && sudo systemctl enable isuride-go.service

scripts

deploy.sh

#!/bin/bash

set -euox pipefail

ssh_host=${1:-isucon-2}

cd "$(dirname "$0")"
cd ..

# deploy
rsync -av -e ssh ./webapp/go/ $ssh_host:/home/isucon/webapp/go/

# restart
ssh "$ssh_host" "bash /home/isucon/before_bench.sh"

.github

workflows

db_tbls.yml
name: Run db_tbls

on:
  workflow_dispatch:
  push:
    paths:
      - schema.sql

permissions:
  contents: write

jobs:
  db-tbls:
    if: ${{ github.ref == 'refs/heads/main' }}
    runs-on: ubuntu-latest
    timeout-minutes: 300
    services:
      mysql:
        image: mysql:8
        options: --health-cmd "mysqladmin ping -h localhost" --health-interval 20s --health-timeout 10s --health-retries 10
        ports:
          - 3306:3306
        env:
          MYSQL_DATABASE: isucon
          MYSQL_ROOT_PASSWORD: isucon
          MYSQL_ROOT_HOST: '%'
    steps:
      - uses: actions/checkout@v4
      - uses: k1low/setup-tbls@v1

      - name: Run schema.sql
        run: mysql --host="127.0.0.1" --port=3306 --user="root" --password="isucon" isucon < schema.sql

      - name: Run tbls for generate database document
        run: tbls doc --dsn "mysql://root:[email protected]:3306/isucon"

      - name: Deploy dbdob
        uses: peaceiris/actions-gh-pages@v4
        with:
          github_token: ${{ secrets.GITHUB_TOKEN }}
          publish_dir: ./dbdoc
          publish_branch: dbdoc

.editorconfig

[*]
charset = utf-8
end_of_line = lf
insert_final_newline = true
trim_trailing_whitespace = true
indent_style = space
indent_size = 2

[*.conf]
indent_size = 4
indent_style = space

[.go]
indent_size = 2
indent_style = tab

[{Makefile, *.mk}]
indent_style = tab
indent_size = 4

README.md

# ISUCON14(isuride)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment