LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kiyokiyopip56(pp masa8)

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

Last updated at Posted at 2025-02-18

やったこと

初期設定チックなもの

インスタンス設定

ssh接続設定

  • awsのdownloadしたキーペアをmvする
$ mv ~/Downloads/private_isu_key.pem ~/.ssh/
  • 権限付与
$ chmod 400 ".ssh/private-isu-key.pem"
  • ssh config
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

初期値計測

$ 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

チューニング

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

スロークエリ

$ 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カラムに対してインデックスを追加

ALTER TABLE comments ADD INDEX idx_post_id (post_id);

スコア：4458→35351

Screenshot 2024-07-28 at 02.23.27.png

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

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

変わらん。

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

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

nginx秘伝のタレ投入

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

mysql秘伝のタレ投入

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":[]}

そんな変わらん

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":[]}

ちょい伸びた

再度インデックス

-- 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設定

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

Screenshot 2024-07-29 at 20.39.35.png

N+1問題解決

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":[]}

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

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導入方法

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?