private-isuを初めてやったときのメモ



# やったこと

## 初期設定チックなもの

### インスタンス設定

- terraform×github actions
    - https://github.com/ppmasa8/isucon-knowledge/blob/main/.github/workflows/terraform.yml
    - https://github.com/ppmasa8/isucon-knowledge/blob/main/private-isu/main.tf

### ssh接続設定

- awsのdownloadしたキーペアをmvする

```bash
$ mv ~/Downloads/private_isu_key.pem ~/.ssh/
```

- 権限付与

```bash
$ chmod 400 ".ssh/private-isu-key.pem"
```

- ssh config

```bash
Host private-isu-competition-server
  User ubuntu
  Port 22
  IdentityFile ~/.ssh/private-isu-key.pem
  HostName 3.113.23.89
  TCPKeepAlive yes
  ServerAliveInterval 5
  ServerAliveCountMax 12

Host private-isu-benchmark-server
  User ubuntu
  Port 22
  IdentityFile ~/.ssh/private-isu-key.pem
  HostName 35.77.24.183
  TCPKeepAlive yes
  ServerAliveInterval 5
  ServerAliveCountMax 12
```

HostNameはec2インスタンスのpublic IPv4

### 初期値計測

```bash
$ sudo su - isucon
$ /home/isucon/private_isu.git/benchmarker/bin/benchmarker -u /home/isucon/private_isu.git/benchmarker/userdata -t http://<target IP>
```

Ruby→Golangにしただけで4倍も変わる

![Screenshot 2024-07-28 at 01.58.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/8f374b01-cb89-4499-bb01-1248a558baf7.png)


## チューニング

初期値計測の際にtopコマンドを走らせてみていたところ、圧倒的にmysqldがリソースを食いつぶしていたので、一旦そこから手をいれる。欲をいうとmysqlは完全に分離してあげたい。

### スロークエリ

```bash
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

sudo systemctl restart mysql

```

↑なんか出力されてなかった、

### commentsテーブルにインデックス追加

post_idカラムに対してインデックスを追加

```bash
ALTER TABLE comments ADD INDEX idx_post_id (post_id);
```

スコア：4458→35351

![Screenshot 2024-07-28 at 02.23.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/612112b0-2542-4341-8e77-b57fb1dfd02a.png)


### アプリケーション負荷分散

```bash
sudo vim /etc/nginx/sites-enabled/isucon.conf

upstream goapp {
    server localhost:8080;
    server localhost:8081;
    server localhost:8082;
    server localhost:8083;
}

server {
    listen 80;

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

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

sudo nginx -t
sudo systemctl restart nginx
```

![Screenshot 2024-07-28 at 02.32.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/bb3caf3b-d3ff-4322-b5e0-461a73e4446c.png)


変わらん。

### 静的ファイルをnginx(プロキシサーバー)で配信する

```bash
sudo vim /etc/nginx/sites-enabled/isucon.conf

upstream goapp {
    server localhost:8080;
    server localhost:8081;
    server localhost:8082;
    server localhost:8083;
}

server {
    listen 80;

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

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

sudo nginx -t
sudo systemctl restart nginx
```

変わらん。

![Screenshot 2024-07-28 at 02.42.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/8bfa37f4-b1ff-45a8-9b75-6185aa22de0b.png)


### nginx秘伝のタレ投入

```bash
isucon@ip-172-31-16-86:~$ sudo vim /etc/nginx/nginx.conf 

user www-data;
worker_processes auto;
pid /run/nginx.pid;
error_log /var/log/nginx/error.log;
include /etc/nginx/modules-enabled/*.conf;

# 大量の同時接続を処理するための設定
worker_rlimit_nofile 90000;

events {
    worker_connections 768;
    # multi_accept on;
    use epoll;
}

http {
    ##
    # Basic Settings
    ##

    sendfile on;
    tcp_nopush on;
    tcp_nodelay 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;

    # キープアライブ設定
    keepalive_timeout 15;
    keepalive_requests 10000;

    # クライアントのリクエスト設定
    client_max_body_size 20M;
    client_body_buffer_size 16k;
    client_header_buffer_size 2k;

    # タイムアウト設定
    client_body_timeout 30;
    client_header_timeout 30;
    send_timeout 60;

    # ファイルキャッシュ設定
    open_file_cache max=10000 inactive=60s;
    open_file_cache_valid 120s;

    ##
    # SSL Settings
    ##

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

    ##
    # Logging Settings
    ##

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

    ##
    # Gzip Settings
    ##

    gzip on;
    gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript;

    # gzip_vary on;
    # gzip_proxied any;
    # gzip_comp_level 6;
    # gzip_buffers 16 8k;
    # gzip_http_version 1.1;

    ##
    # Virtual Host Configs
    ##

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

sudo nginx -t
sudo systemctl restart nginx
```

1000位伸びた？？？

![Screenshot 2024-07-28 at 02.50.50.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/d4991357-48c1-4fe4-86de-508a031d1636.png)


### mysql秘伝のタレ投入

```bash
isucon@ip-172-31-16-86:~$ cd /etc/mysql/conf.d/
isucon@ip-172-31-16-86:/etc/mysql/conf.d$ sudo vim my_settings.cnf

[mysqld]
# InnoDB設定
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1

# 接続設定
max_connections = 1000
thread_cache_size = 100

# クエリキャッシュとテーブルキャッシュ
table_open_cache = 4000
key_buffer_size = 128M

# 一時テーブルとソート設定
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M

# バッファサイズ設定
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 1M

# パフォーマンススキーマ
performance_schema = OFF

isucon@ip-172-31-16-86:/$ sudo systemctl restart mysql

{"pass":true,"score":37186,"success":34980,"fail":0,"messages":[]}
```

そんな変わらん

```bash
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2

{"pass":true,"score":38214,"success":35916,"fail":0,"messages":[]}
```

ちょい伸びた

### 再度インデックス

```bash
-- user_id にインデックスを追加
ALTER TABLE posts ADD INDEX idx_user_id (user_id);

-- created_at にインデックスを追加
ALTER TABLE posts ADD INDEX idx_created_at (created_at);

-- user_id と created_at の複合インデックスを追加
ALTER TABLE posts ADD INDEX idx_user_id_created_at (user_id, created_at);

-- user_id にインデックスを追加
ALTER TABLE comments ADD INDEX idx_user_id (user_id);

-- created_at にインデックスを追加
ALTER TABLE comments ADD INDEX idx_created_at (created_at);

-- post_id と created_at の複合インデックスを追加
ALTER TABLE comments ADD INDEX idx_post_id_created_at (post_id, created_at);

ちょい伸び
{"pass":true,"score":40269,"success":37903,"fail":0,"messages":[]}
```

### pprof設定

https://zenn.dev/team_soda/articles/d4701665e8a3a7

- このときセキュリティグループのインバウンドルールの6060と1080は開けておく

![Screenshot 2024-07-29 at 20.39.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/648231/50169ee8-3224-45f5-8e67-6f3b9d11feb8.png)


### N+1問題解決

```bash
sudo chmod a+w /home/isucon/private_isu/webapp/golang/app.go
app

func makePosts(results []Post, csrfToken string, allComments bool) ([]Post, error) {
	var posts []Post

	// postIdsだけの配列
	postIds := make([]int, len(results))
	postUserIds := make([]int, len(results))
	for _, v := range results {
		postIds = append(postIds, v.ID)
		postUserIds = append(postUserIds, v.UserID)
	}

	// 全postのコメント一覧取得しておく
	sql, params, err := sqlx.In("SELECT * FROM `comments` WHERE `post_id` IN (?) ORDER BY `created_at` DESC ", postIds)
	if err != nil {
		return nil, err
	}
	var postComments []Comment
	if err := db.Select(&postComments, sql, params...); err != nil {
		return nil, err
	}

	// postIdごとのcomments
	postIdToComments := map[int][]Comment{}
	for _, v := range postComments {
		curComments := postIdToComments[v.PostID]
		curComments = append(curComments, v)
		postIdToComments[v.PostID] = curComments
	}

	// userIdsだけの配列
	userIds := make([]int, len(postComments)+len(postUserIds))
	for _, v := range postComments {
		userIds = append(userIds, v.UserID)
	}
	userIds = append(userIds, postUserIds...)
	// userIdごとのuser
	userIdToUser := map[int]User{}
	if len(userIds) > 0 {
		// user取得
		sql2, params2, err := sqlx.In("SELECT * FROM `users` WHERE `id` IN (?)", userIds)
		if err != nil {
			return nil, err
		}
		var usrs []User
		if err := db.Select(&usrs, sql2, params2...); err != nil {
			return nil, err
		}
		for _, v := range usrs {
			userIdToUser[v.ID] = v
		}
	}

	for _, p := range results {
		comments := postIdToComments[p.ID]
		p.CommentCount = len(comments)
		if !allComments && len(comments) > 3 {
			comments = comments[:3]
		}

		for i := 0; i < len(comments); i++ {
			comments[i].User = userIdToUser[comments[i].UserID]
		}

		// reverse
		for i, j := 0, len(comments)-1; i < j; i, j = i+1, j-1 {
			comments[i], comments[j] = comments[j], comments[i]
		}

		p.Comments = comments
		p.User = userIdToUser[p.UserID]
		p.CSRFToken = csrfToken

		posts = append(posts, p)
	}

	return posts, nil
}

func getIndex(w http.ResponseWriter, r *http.Request) {
	me := getSessionUser(r)

	results := []Post{}

	err := db.Select(&results,
		"SELECT posts.id, posts.user_id, posts.body, posts.mime, posts.created_at FROM posts"+
			" STRAIGHT_JOIN users ON posts.user_id = users.id"+
			" WHERE users.del_flg = 0"+
			" ORDER BY posts.created_at DESC LIMIT 20")
	if err != nil {
		log.Print(err)
		return
	}

	posts, err := makePosts(results, getCSRFToken(r), false)
	if err != nil {
		log.Print(err)
		return
	}

	fmap := template.FuncMap{
		"imageURL": imageURL,
	}

	template.Must(template.New("layout.html").Funcs(fmap).ParseFiles(
		getTemplPath("layout.html"),
		getTemplPath("index.html"),
		getTemplPath("posts.html"),
		getTemplPath("post.html"),
	)).Execute(w, struct {
		Posts     []Post
		Me        User
		CSRFToken string
		Flash     string
	}{posts, me, getCSRFToken(r), getFlash(w, r, "notice")})
}

激アツ
{"pass":true,"score":63974,"success":58706,"fail":0,"messages":[]}
```

### アップロード画像を静的ファイル化する

```bash
const (
    // ... 既存の定数 ...
    ImageDir = "/home/isucon/private_isu/webapp/public/img"
)

func postIndex(w http.ResponseWriter, r *http.Request) {
    // ... 既存のコード ...

    // ファイルとして保存
    filename := fmt.Sprintf("%s/%d.%s", ImageDir, pid, getExtension(mime))
    err = os.WriteFile(filename, filedata, 0644)
    if err != nil {
        log.Print(err)
        return
    }

    http.Redirect(w, r, "/posts/"+strconv.FormatInt(pid, 10), http.StatusFound)
}

func getExtension(mime string) string {
    switch mime {
    case "image/jpeg":
        return "jpg"
    case "image/png":
        return "png"
    case "image/gif":
        return "gif"
    default:
        return ""
    }
}

func getImage(w http.ResponseWriter, r *http.Request) {
    pidStr := r.PathValue("id")
    pid, err := strconv.Atoi(pidStr)
    if err != nil {
        w.WriteHeader(http.StatusNotFound)
        return
    }

    ext := r.PathValue("ext")
    filename := fmt.Sprintf("%s/%d.%s", ImageDir, pid, ext)

    // ファイルが存在しない場合、データベースから取得して保存
    if _, err := os.Stat(filename); os.IsNotExist(err) {
        post := Post{}
        err = db.Get(&post, "SELECT * FROM `posts` WHERE `id` = ?", pid)
        if err != nil {
            log.Print(err)
            w.WriteHeader(http.StatusNotFound)
            return
        }

        if ext == "jpg" && post.Mime == "image/jpeg" ||
            ext == "png" && post.Mime == "image/png" ||
            ext == "gif" && post.Mime == "image/gif" {
            err = os.WriteFile(filename, post.Imgdata, 0644)
            if err != nil {
                log.Print(err)
                w.WriteHeader(http.StatusInternalServerError)
                return
            }
        } else {
            w.WriteHeader(http.StatusNotFound)
            return
        }
    }

    // ファイルを直接配信
    http.ServeFile(w, r, filename)
}
isucon@ip-172-31-16-86:~$ sudo vim /etc/nginx/sites-enabled/isucon.conf
isucon@ip-172-31-16-86:~$ cd /home/isucon/private_isu/webapp/golang
isucon@ip-172-31-16-86:~/private_isu/webapp/golang$ go build -o app
isucon@ip-172-31-16-86:~/private_isu/webapp/golang$ sudo nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
isucon@ip-172-31-16-86:~/private_isu/webapp/golang$ sudo systemctl restart isu-go
isucon@ip-172-31-16-86:~/private_isu/webapp/golang$ sudo systemctl restart nginx

location /img/ {
    root /home/isucon/private_isu/webapp/public;
    try_files $uri @app;
}

location @app {
    proxy_pass http://app;
}

熱盛！！！
{"pass":true,"score":94060,"success":87865,"fail":0,"messages":[]}
```

### 別件:alp導入方法

```bash
EC2インスタンスにSSH接続します。

必要なパッケージをインストールします：

sudo apt-get update
sudo apt-get install -y wget build-essential
Go言語をインストールします（既にインストールされている場合はスキップ）：

wget https://go.dev/dl/go1.17.linux-amd64.tar.gz
sudo tar -C /usr/local -xzf go1.17.linux-amd64.tar.gz
echo 'export PATH=$PATH:/usr/local/go/bin' >> ~/.bashrc
source ~/.bashrc
alpをインストールします：

go install github.com/tkuchiki/alp/cmd/alp@latest
PATHを通します：

echo 'export PATH=$PATH:~/go/bin' >> ~/.bashrc
source ~/.bashrc
alpがインストールされたことを確認します：

alp --version
Nginxのログ形式を設定します。Nginxの設定ファイル（通常は /etc/nginx/nginx.conf または /etc/nginx/sites-available/default）を編集し、以下のようなlog_formatを追加します：

http {
    ...
    log_format ltsv "time:$time_local"
                    "\thost:$remote_addr"
                    "\tforwardedfor:$http_x_forwarded_for"
                    "\treq:$request"
                    "\tstatus:$status"
                    "\tmethod:$request_method"
                    "\turi:$request_uri"
                    "\tsize:$body_bytes_sent"
                    "\treferer:$http_referer"
                    "\tua:$http_user_agent"
                    "\treqtime:$request_time"
                    "\tcache:$upstream_http_x_cache"
                    "\truntime:$upstream_http_x_runtime"
                    "\tapptime:$upstream_response_time"
                    "\tvhost:$host";

    access_log /var/log/nginx/access.log ltsv;
    ...
}
Nginxを再起動します：

sudo systemctl restart nginx
alpを使ってログを解析します。例えば：

alp ltsv --file=/var/log/nginx/access.log

ex)
$ alp json --sort sum -r \
-m "/posts/[0-9]+,/@\w+, /image/\d+" \
-o count,method,uri,min,avg,max,sum \
< /var/log/nginx/access.log
```

### github
https://github.com/ppmasa8/isucon-knowledge/blob/main/README.md