isucon

Makefile

SHELL=/bin/bash

USER=$(shell whoami)
NOW:=$(shell date +%H:%M:%S)
BIN_NAME:=isuports
P_LANGUAGE=rust
SERVICE_NAME:=$(BIN_NAME).$(P_LANGUAGE).service

DB_PATH:=/etc/mysql
NGINX_PATH:=/etc/nginx
SYSTEMD_PATH:=/etc/systemd/system

NGINX_LOG:=/var/log/nginx/access.log
SLOW_LOG:=/tmp/slow.log

DB_USER:=isucon
DB_PW:=isucon
DB_NAME:=isuports

.PHONY: setup
setup: install-tools git-setup

install-tools:
	sudo apt update
	sudo apt upgrade -y
	sudo apt install -y vim curl wget unzip htop git percona-toolkit dstat graphviz tree zsh

	# alpのインストール
	wget https://github.com/tkuchiki/alp/releases/download/v1.0.21/alp_linux_amd64.zip
	unzip alp_linux_amd64.zip
	sudo install alp /usr/local/bin/alp
	rm alp_linux_amd64.zip alp
	# slpのインストール
	wget https://github.com/tkuchiki/slp/releases/download/v0.2.0/slp_linux_amd64.zip
	unzip slp_linux_amd64.zip
	sudo install slp /usr/local/bin/slp
	rm slp_linux_amd64.zip slp

	# gh コマンドインストール
	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

	curl -L https://raw.github.com/robbyrussell/oh-my-zsh/master/tools/install.sh | sh


mysql/client:
	@mysql -h 127.0.0.1 -P 3306 -u $(DB_USER) $(DB_NAME) -p$(DB_PW) 2>/dev/null

mysql/query: QUERY=
mysql/query:
	echo "$(QUERY)" | $(MAKE) mysql/client

mysql/query/gh: QUERY=
mysql/query/gh:
	$(MAKE) mysql/query QUERY="$(QUERY)" | tee >(gh issue comment $(ISSUE) -F -)

.PHONY: mysql/slow-query-setting
mysql/slow-query-setting:
	echo "show variables like 'slow_query%';" | $(MAKE) mysql/client

PHONY: mysql/dump-tables
mysql/dump-tables:
	mysqldump -u $(DB_USER) -p$(DB_PW) $(DB_NAME) -d

.PHONY: git-setup
git-setup:
	gh auth setup-git
	git config --global user.email "[email protected]"
	git config --global user.name "isucon"
	git config --global core.editor 'vim -c "set fenc=utf-8"'
	git remote add origin https://github.com/tamanobi/isucon13

.PHONY: alp
alp:
	sudo cat $(NGINX_LOG) | alp ltsv --reverse --sort=sum

.PHONY: slp
slp:
	sudo cat $(SLOW_LOG) | slp my --reverse --sort=count
	# cat example/mysql.slow.log | slp my
	# cat example/postgresql.slow.log | slp pg

.PHONY: watch-service-log
watch-service-log:
	sudo journalctl -u $(SERVICE_NAME) -n10 -f

.PHONY: get-db-conf
get-db-conf: check-server-id
	test -d ~/$(SERVER_ID)/etc/mysql || \
		mkdir -p ~/$(SERVER_ID)/etc/mysql
	sudo cp -R $(DB_PATH)/* ~/$(SERVER_ID)/etc/mysql
	sudo chown $(USER) -R ~/$(SERVER_ID)/etc/mysql

.PHONY: get-nginx-conf
get-nginx-conf: check-server-id
	test -d ~/$(SERVER_ID)/etc/nginx || \
		mkdir -p ~/$(SERVER_ID)/etc/nginx
	sudo cp -R $(NGINX_PATH)/* ~/$(SERVER_ID)/etc/nginx
	sudo chown $(USER) -R ~/$(SERVER_ID)/etc/nginx

.PHONY: get-service-file
get-service-file: check-server-id
	test -d ~/$(SERVER_ID)/etc/systemd/system || \
		mkdir -p ~/$(SERVER_ID)/etc/systemd/system
	sudo cp $(SYSTEMD_PATH)/$(SERVICE_NAME) ~/$(SERVER_ID)/etc/systemd/system/$(SERVICE_NAME)
	sudo chown $(USER) ~/$(SERVER_ID)/etc/systemd/system/$(SERVICE_NAME)

.PHONY: mv-log
mv-log:
	sudo test -f $(NGINX_LOG) && \
		sudo mv $(NGINX_LOG) $(NGINX_LOG).$(NOW) || echo ""
	sudo test -f $(SLOW_LOG) && \
		sudo mv $(SLOW_LOG) $(SLOW_LOG).$(NOW) || echo ""

.PHONY: restart
restart: mv-log
	sudo systemctl daemon-reload
	sudo systemctl restart $(SERVICE_NAME)
	sudo systemctl restart mysql
	sudo systemctl restart nginx

.PHONY: check-server-id
check-server-id:
ifdef SERVER_ID
	@echo "SERVER_ID=$(SERVER_ID)"
else
	@echo "SERVER_ID is unset"
	@exit 1
endif

.PHONY: set-as-s1
set-as-s1:
	echo "SERVER_ID=s1" >> env.sh

.PHONY: set-as-s2
set-as-s2:
	echo "SERVER_ID=s2" >> env.sh

.PHONY: set-as-s3
set-as-s3:
	echo "SERVER_ID=s3" >> env.sh

.PHONY: checkout
checkout:
	git fetch && \
	git reset --hard origin/$(BRANCH)  && \
	git switch -C $(BRANCH) origin/$(BRANCH)

.PHONY: deploy-conf
deploy-conf: deploy-db-conf deploy-nginx-conf deploy-service-file

.PHONY: deploy-db-conf
deploy-db-conf:
	sudo cp -R ~/$(SERVER_ID)/etc/mysql/* $(DB_PATH)

.PHONY: deploy-nginx-conf
deploy-nginx-conf:
	sudo cp -R ~/$(SERVER_ID)/etc/nginx/* $(NGINX_PATH)

.PHONY: deploy-service-file
deploy-service-file:
	sudo cp ~/$(SERVER_ID)/etc/systemd/system/$(SERVICE_NAME) $(SYSTEMD_PATH)/$(SERVICE_NAME)

.PHONY: deploy-envsh
deploy-envsh:
	cp ~/$(SERVER_ID)/home/isucon/env.sh ~/env.sh

memo.md

#!/bin/bash

sudo apt install -y vim curl wget unzip htop git percona-toolkit dstat graphviz tree
# wget https://github.com/tkuchiki/alp/releases/download/v1.0.21/alp_linux_amd64.zip
# unzip alp_linux_amd64.zip
# wget https://github.com/tkuchiki/slp/releases/download/v0.2.0/slp_linux_amd64.zip
unzip slp_linux_amd64.zip
touch ~/.ssh/authorized_keys
chmod 600 ~/.ssh/authorized_keys
curl -L https://github.com/tamanobi.keys >> authorized_keys
curl -L https://github.com/do7be.keys >> authorized_keys

# gh コマンドインストール
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

# https://techblog.cartaholdings.co.jp/entry/make-github-cli-isucon-first-push
# 
# gh auth login --with-token -p https
# ここで入力待ちになるので、ここでTokenをペーストし、ReturnKeyを押してから、Ctrl+Dで入力を終了する
# gh auth setup-git

# git config --global user.email [email protected]
# git config --global user.name isucon
# git remote add origin https://github.com/tamanobi/isucon13.git

# global IP を確認する
curl checkip.amazonaws.com

# mysql でインデックスを貼る
ALTER TABLE user ADD INDEX id_index (id);
ALTER TABLE table_name ADD INDEX index_name (column_name);


# mysql
binlogをオフにする (disable-log-bin = 1)
Disk書き込みタイミングの調整 (innodb_flush_log_at_trx_commit = 2)
BufferPoolのサイズ調整… メモリ3.7GBのインスタンスなので勢いよくデカくしてswap死しないように慎重に

# slow query を出す
# /etc/mysql/mysqld.cnf
[mysqld]
slow_query_log=1
slow_query_log_file='/tmp/slow.log'
long_query_time=0

## 合計時間
$ sudo mysqldumpslow -s t /tmp/slow.log | head
## 平均実行時間
$ sudo mysqldumpslow -s at /tmp/slow.log | head
## クエリ件数
$ sudo mysqldumpslow -s c /tmp/slow.log | head


# postgresql でインデックス貼る
CREATE INDEX [ name ] ON [ ONLY ] table_name ( column_name [, ...] )

# nginx による振り分け
  location /api {
    proxy_set_header Host $host;
    proxy_read_timeout 600;

    if ($host ~ "^[a-z0-9-]{1,9}.t.isucon.dev" ) {
      proxy_pass http://127.0.0.1:3000;
    }
    if ($host ~ "^[a-z0-9-]{10,18}.t.isucon.dev" ) {
      proxy_pass http://server2;
    }
    if ($host ~ "^[a-z0-9-]{19,}.t.isucon.dev") {
      proxy_pass http://server3;
    }
  }


echo 次にやるべきこと
echo sudo vim /etc/nginx/nginx.conf 
echo access_log /var/log/nginx/access.log の上に追記する
echo access_log /var/log/nginx/access.log ltsv と書く
cat << 'EOS'
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";
EOS
echo 追記したら
echo sudo systemctl restart nginx
echo アクセスログのクリア
echo 'echo "" | sudo tee /var/log/nginx/access.log'
echo サービスログ
echo sudo journalctl -f -x -u isuports.service
echo sudo cat /var/log/nginx/access.log | ./alp ltsv

# https://atsum.in/linux/alp/
# sudo cat /var/log/nginx/access.log | head -n 1000 | ../alp ltsv --reverse --sort count --format markdown | gh issue comment 1 -F -
# sudo cat /var/log/nginx/access.log | head -n 1000 | ../alp ltsv -m "/api/player/competition/[0-9a-zA-Z-]+/ranking" --limit 100000 --reverse --sort count --format markdown | gh issue comment 1 -F -
# gh issue comment 1 -F -

curl -L https://gist.githubusercontent.com/tamanobi/b1b7960195327cec1788967df321a3f7/raw/90b9fd9c0d0b2a6556f990a51fae9399cfde0a01/Makefile

make install-tools
# シェル変更
sudo chsh $(shell whoami) -s $(shell which zsh)
# gh コマンドの設定
# https://techblog.cartaholdings.co.jp/entry/make-github-cli-isucon-first-push
gh auth login --with-token -p https
# ここで入力待ちになるので、ここでTokenをペーストし、ReturnKeyを押してから、Ctrl+Dで入力を終了する
# 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
gh auth status
make git-setup
# ここまで全台共通
make set-as-s1
# nginx と mysql の設定を取り出す
make get-db-conf
make get-nginx-conf
make get-service-file
# DB_USER, DB_PW, DB_NAME を埋める
git add .
git commit -m"first commit"
make dump-tables
make dump-tables | gh issue comment 1 -F -
make checkout