Closed47
ISUCON11-prior
myuonこれ
myuonc5.largeを2台立てて競技用に使うことにする。
ベンチは自分のマシンでいいことに
configを設定してsshで中にはいる
myuonいつもと違ってbuildを自分でしないといけない。
諸々インストール
sudo yum update && sudo yum install git make golang
clone
git clone https://github.com/isucon/isucon11-prior.git
cd isucon11-prior/webapp/golang
make all
ビルドはできてOK
起動にはMySQLが必要。
myuonAmazon Linux 2023にMySQLを入れるのはかなり骨が折れそうだったので断念してubuntu serverに切り替える。
cloneとbuildは上記と同じ。
sudo apt update && sudo apt install -y make golang mysql-server
mysql-serverが起動したら、
sudo mysql -u root
で入れる。
source /home/ubuntu/isucon11-prior/webapp/sql/00_setup.sql
source /home/ubuntu/isucon11-prior/webapp/sql/01_schema.sql
を実行して初期スクリプトを実行する。
myuonこの後の作業のためにサービスファイルを定義しておく
sudo nano /etc/systemd/system/isu-go.service
[Unit]
Description=My ISU-GO service
After=network.target
[Service]
ExecStart=/home/ubuntu/isucon11-prior/webapp/golang/bin/webapp
User=ubuntu
Restart=always
[Install]
WantedBy=multi-user.target
sudo systemctl daemon-reload
sudo systemctl start isu-go.service
フロントの準備
npmにsudoつけるの嫌だけどまあサーバーだからな〜みたいな感じ
sudo apt install nodejs npm
sudo npm i -g n yarn
sudo n stable
yarn install
yarn build
ここまできて、localhost:9292が見れるようになった。
myuonさて初回ベンチ
ローカルでmakeする。
./bin/benchmarker -target 18.183.237.49:9292
22:37:43.779448 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HD979ER2JQ70RJH79YZHV2RN
22:37:43.852306 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HD979ER2JQ70RJH79YZHV2RN
22:37:43.923355 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HD979ER2JQ70RJH79YZHV2RN
22:38:49.507296 ERR: validation: invalid: overbooking at 01HD979F1SWSXBYEE9HCERR790
22:38:49.507925 ERR: validation: invalid: overbooking at 01HD979EGX3SS6Y85SYVDJB7HC
22:38:49.523987 ERR: validation: invalid: overbooking at 01HD979ER2JQ70RJH79YZHV2RN
22:38:49.525012 score: 1412(1565 - 153) : pass
22:38:49.525026 deduction: 153 / timeout: 6
score=1412
myuonpt-query-digestを入れる
sudo apt-get install percona-toolkit
my.cnfをいじってmysqlを再起動。
[mysqld]
slow_query_log = 1
slow_query_log_file = /tmp/mysql-slow.log
long_query_time = 1
ユーザーの作成もしておく
CREATE USER 'ubuntu'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'ubuntu'@'localhost';
GRANT SELECT, INSERT, UPDATE ON *.* TO 'ubuntu'@'localhost';
flush privileges;
pt-query-digestによるといかが遅いらしい
SELECT 1 FROM `schedules` WHERE `id` = '01HD98CWM81ZT8CHYG67V8QJAT' LIMIT 1 FOR UPDATE\G
SELECT 1 FROM `reservations` WHERE `schedule_id` = '01HD98DBT0ZRCNPT3N7FTPP0T4' AND `user_id` = '01HD98DDNR41EAYAHTSTEJMR3G' LIMIT 1\G
今日は一旦ここまで
次回はnginxを入れてkataribeを通してみるところから
myuonさて続き。
myuonnginxのインストール
sudo apt install nginx
sudo systemctl status nginx で動いていることを確認。
nginx.confを設定してベンチを回す
events {
worker_connections 1024;
}
http {
log_format with_time '$remote_addr - $remote_user [$time_local] '
'"$request" $status $body_bytes_sent '
'"$http_referer" "$http_user_agent" $request_time';
access_log /var/log/nginx/access.log with_time;
server {
listen 80;
location / {
proxy_pass http://localhost:9292;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
}
}
nginxを入れて動くようになったので、kataribeを入れる。
go install github.com/matsuu/kataribe@latest
でいける。
myuontopを見ていると、mysqlが結構重そうに見える🤔
myuon重そうなクエリ
SELECT * FROM `reservations` WHERE `schedule_id` = '01HDAMENY35ASS95GH57GSRB4V'\G
SELECT 1 FROM `schedules` WHERE `id` = '01HDAMFAQNJ9A3WSD6DNJK2AP5' LIMIT 1 FOR UPDATE\G
SELECT * FROM `users` WHERE `id` = '01HDAMFG86AH56RBJJPGC0GK93' LIMIT 1\G
administrator command: Prepare\G
重そうなAPI
GET /api/schedules/*
GET /esm/index.js
GET /api/schedules
少なくとも静的ファイルのservingはnginx側でやりたいなあになっている。
myuon準備が終わったのでここからチューニングを始める。
myuonreservationsにschedule_idのインデックスを貼った。
12:03:03.427905 ERR: validation: invalid: overbooking at 01HDANA2KZJ4YYV6VMMBT28CH5
12:03:03.505456 ERR: validation: invalid: overbooking at 01HDANAJQX2M9GKDGF46VRVTH9
12:03:03.520147 score: 1638(1738 - 100) : pass
12:03:03.520171 deduction: 100 / timeout: 7
score=1638
微増？とりあえずpt-query-digestによるとresponse timeは半分くらいになっていた。
ただまだ支配的なので、多分N+1クエリがどこかにありそう。
myuonnginxで静的ファイルの配信をするようにした。
http {
include mime.types;
log_format with_time '$remote_addr - $remote_user [$time_local] '
'"$request" $status $body_bytes_sent '
'"$http_referer" "$http_user_agent" $request_time';
access_log /var/log/nginx/access.log with_time;
gzip on;
gzip_types text/plain text/css application/json application/javascript text/xml application/xml application/xml+rss text/javascript;
server {
listen 80;
location = /initialize {
proxy_pass http://localhost:9292;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
location ~ ^/api/ {
proxy_pass http://localhost:9292;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
location / {
alias /home/ubuntu/isucon11-prior/webapp/golang/public/;
index index.html;
}
}
}
スコアは減点多いのであまり上がってないかも
./bin/benchmarker -target 57.180.65.17
12:31:23.655379 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAPZYC9C7E0J6KG9Y9FNZXE
12:31:23.655456 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAPZYC80K0CN845TZN203HP
12:31:45.264128 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAQ0KCVVJAFP93GK63HGNDA
12:31:45.264122 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAQ0KCCX3BSNNV2JG7CP0CE
12:32:33.117749 ERR: validation: invalid: overbooking at 01HDAQ0AWVJP8MNHP26WBPC411
12:32:33.167005 ERR: validation: invalid: overbooking at 01HDAPZY3HBY87G4W1JJEMYRTX
12:32:33.186707 ERR: validation: invalid: overbooking at 01HDAPZYMBGCBBQTC6G027B308
12:32:33.266564 ERR: validation: invalid: overbooking at 01HDAQ0AS2BEYNSX2DWTBP489V
12:32:33.362825 ERR: validation: invalid: overbooking at 01HDAQ07VQXHWV2FJVQRN0XM3A
12:32:33.364412 ERR: validation: invalid: overbooking at 01HDAQ0KCVVJAFP93GK63HGNDA
12:32:33.364881 ERR: validation: invalid: overbooking at 01HDAPZYKF0N4KWGSJTE85DWP2
12:32:33.369743 score: 1715(2069 - 354) : pass
12:32:33.369776 deduction: 354 / timeout: 5
score=1715
myuonとりあえずAPIは GET /api/schedules/* が重いのでこれを改善してみる
myuon上記のAPIの中のgetReservationsの中でuserを取るところがN+1クエリになっていたのでそれを解消した。
./bin/benchmarker -target 57.180.65.17
12:52:36.918504 ERR: validation: invalid: overbooking at 01HDAR5089FJC4WYQKH1V1PRX8
12:52:36.975181 ERR: validation: invalid: overbooking at 01HDAR5J2WQYR7NFVHZ9CZC7P1
12:52:37.000070 ERR: validation: invalid: overbooking at 01HDAR4NYF0XAQGR9XYPQPX3SY
12:52:37.011379 ERR: validation: invalid: overbooking at 01HDAR50CJ10M3PX0YFTJP5QZY
12:52:37.014876 ERR: validation: invalid: overbooking at 01HDAR4P2864MKY01K52C69QDZ
12:52:37.038175 ERR: validation: invalid: overbooking at 01HDAR4TCBBHZ98R08YDCPXE3Z
12:52:37.038920 score: 2182(2482 - 300) : pass
12:52:37.038931 deduction: 300 / timeout: 6
score=2182
そこそこ上がった
myuonまだまだ GET /api/schedules/* が重いのでさらに改善を考える。
myuon上記の個別のscheduleを取るために叩いていると思われる、 /api/schedules でN+1クエリが別にあったのでそれを解消する。
reservationのCOUNTを順次とっていくところを一発で取れるように変更。
またsqlxでInには空のスライスを渡せないのが意外と面倒な上気が付きにくいのでちょっと時間を無駄にした。
エラーにならなそうなところについては、fatalエラーをバンバン使っていくのもアリかも(どうせログは最後に消すので)
./bin/benchmarker -target 57.180.65.17
14:34:36.920337 ERR: validation: invalid: overbooking at 01HDAXZEAW22659JEXZ9JWC27M
14:34:37.166995 ERR: validation: invalid: overbooking at 01HDAXZTTEK07EKMJ541K8NVE5
14:34:37.294228 score: 2488(2589 - 101) : pass
14:34:37.294254 deduction: 100 / timeout: 11
score=2488
myuonGET /api/schedules/* がいよいよ支配的になってきた。
N+1を解消した結果の以下のクエリが重いらしい。
SELECT `schedule_id`, COUNT(*) AS `count` FROM `reservations` WHERE `schedule_id` IN ('01HDAY0P0M7N1G0MR0HQ3TXCQ9', '01HDAY0P0H8P5X642XJ0X7YJDN', '01HDAY0NYQNJCNH7NKFCDCHNSG', '01HDAY0NYJQ2QTXKD3Z2ZBQ81N', '01HDAY0NVKRGFNYM1WZSJDNVF2', '01HDAY0GT1DHYAKDF90MSGJM1C', '01HDAY0GQM4G83N7TEF2HTME5A', '01HDAY087WZ2FQCZM5833PDTFN', '01HDAY085QWG1NQVRH90Z5KZZ4', '01HDAY0859802E5EYFA8HYD7GN', '01HDAY083XHTTEPXVNWN39483R', '01HDAY083BMX0BN42FX18Y0PJM', '01HDAY022XE0TM9TJ13EY0Y48W', '01HDAY022G5EB7PZTD3J9GT1VT', '01HDAY019Z21K84EHMP12PQ3BZ', '01HDAY019Q1NAYP13AFM855S3M', '01HDAXZTTEK07EKMJ541K8NVE5', '01HDAXZTSFWR8KS5EZEQ72KQ6J', '01HDAXZTSA57NBTZJ5RFPE7W5T', '01HDAXZEYYTD88MXMNM1JGV65V', '01HDAXZEYYTD88MXMNKZFPWNT6', '01HDAXZEYPKWDCX1A5MQRQTE1Z', '01HDAXZEVB1JHPPQP0PNA4VF1W', '01HDAXZETQ25FFBZ40KBCMHYXF', '01HDAXZETPW6AS18KF7AK94VCR', '01HDAXZETNMB1T6GC0SP8KWVN0', '01HDAXZENW0GW1N8AHCMKDVPFQ', '01HDAXZEBD4D2NQRAD4CMPD2BP', '01HDAXZEAW22659JEXZ9JWC27M') GROUP BY `schedule_id`\G
COUNT自体が重そうなのとread heavyに見えるので、COUNT用のカラム作らないとダメかも？
myuonreservedカラムを追加して事前に数を計算するようにした。
エラーは増えたが、受け入れられるリクエストの数はさらに増えた。
./bin/benchmarker -target 57.180.65.17
14:53:37.741275 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAZ4CAKSDCNGNQSABVR2Y6D
14:53:37.741290 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAZ4CAJ60CHNT5RB2WB1A14
14:53:37.741343 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDAZ4CACMVB8J8KPEEVVABMF
14:54:35.310510 ERR: validation: invalid: overbooking at 01HDAZ4PPWBAR5Y5BD83MRW1VW
14:54:35.334628 ERR: validation: invalid: overbooking at 01HDAZ40F3EEPGRH1RCANR2T0V
14:54:35.352826 ERR: validation: invalid: overbooking at 01HDAZ4PQZ89BEMGDA76W424T3
14:54:35.385949 ERR: validation: invalid: overbooking at 01HDAZ4CAJ60CHNT5RB2WB1A14
14:54:35.444269 ERR: validation: invalid: overbooking at 01HDAZ4CAKSDCNGNQSABVR2Y6D
14:54:35.455202 ERR: validation: invalid: overbooking at 01HDAZ40PNFTJT2WWX29HYBZWQ
14:54:35.468112 ERR: validation: invalid: overbooking at 01HDAZ43A3TTP1Q07H9JS6N7NJ
14:54:35.469845 ERR: validation: invalid: overbooking at 01HDAZ40P95YND7D7N30C1XYTA
14:54:35.470740 score: 2682(3085 - 403) : pass
14:54:35.470751 deduction: 403 / timeout: 0
score=2682
myuonDBのクエリを確認すると、ユーザーがstaffかどうかをチェックしているところが重くなってきている。
Staffなユーザーはadminぽい1名しかいないので、user_idを控えておけば一発で判定ができそう。
15:28:04.039678 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1VVKFEGF
15:28:04.039733 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1RA2EBDS
15:28:04.076616 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1VVKFEGF
15:28:04.076687 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1RA2EBDS
15:28:04.086762 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1VVKFEGF
15:28:04.086770 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB13E6GN3C9TR4V1RA2EBDS
15:28:12.524025 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
15:28:12.524042 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
15:28:12.524092 ERR: load: json: invalid JSON at /api/schedules
15:28:12.557643 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13KS6H7E3KDA1DDA985V2
15:28:12.557665 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13KS6H7E3KDA1DDA985V2
15:28:12.557725 ERR: load: json: invalid JSON at /api/schedules/01HDB13KS6H7E3KDA1DDA985V2
15:28:12.560456 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13KS0DK9ENC0WP9BGEDK1
15:28:12.560466 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13KS0DK9ENC0WP9BGEDK1
15:28:12.560542 ERR: load: json: invalid JSON at /api/schedules/01HDB13KS0DK9ENC0WP9BGEDK1
15:28:13.063759 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13E5EWEKNKZ5N7WJEPN1G
15:28:13.063889 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13E5EWEKNKZ5N7WJEPN1G
15:28:13.063928 ERR: load: json: invalid JSON at /api/schedules/01HDB13E5EWEKNKZ5N7WJEPN1G
15:28:13.561747 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
15:28:13.562016 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
15:28:13.562188 ERR: load: json: invalid JSON at /api/schedules
15:28:14.057499 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/signup
15:28:14.057532 ERR: load: status code: invalid status code: 500 (expected: 200) at /
15:28:14.057666 ERR: load: status code: invalid status code: 500 (expected: 200) at /
15:28:14.057675 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/signup
15:28:14.057778 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
15:28:14.057905 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
15:28:14.057980 ERR: load: json: invalid JSON at /api/schedules
15:28:14.058028 ERR: load: status code: invalid status code: 500 (expected: 200) at /
15:28:14.058163 ERR: load: json: invalid JSON at /api/signup
15:28:14.058167 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13DYYY55Q5JKYW0XYG1M6
15:28:14.058303 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13DYYY55Q5JKYW0XYG1M6
15:28:14.058434 ERR: load: json: invalid JSON at /api/schedules/01HDB13DYYY55Q5JKYW0XYG1M6
15:28:14.094445 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
15:28:15.060486 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
15:28:24.928046 ERR: load: status code: invalid status code: 500 (expected: 200) at /
15:28:24.976278 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/login
15:28:24.976308 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/login
15:28:24.976371 ERR: load: json: invalid JSON at /api/login
15:28:26.590801 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB144CAFW3F1C3SBC8QVRQE
15:28:26.590794 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB144CBMJZ0AE7Q9B1MW4DV
15:28:26.590847 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB144C88BFRN9N30FAX0XMK
15:28:28.422703 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:28.422752 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:28.422852 ERR: load: json: invalid JSON at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:28.437803 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
15:28:28.437819 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
15:28:28.437844 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144C9FBH1MTA0EJWZ2HSW
15:28:28.437876 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144C9FBH1MTA0EJWZ2HSW
15:28:28.437913 ERR: load: json: invalid JSON at /api/schedules
15:28:28.437982 ERR: load: json: invalid JSON at /api/schedules/01HDB144C9FBH1MTA0EJWZ2HSW
15:28:28.446735 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.446743 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.446855 ERR: load: json: invalid JSON at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.451021 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.451063 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.451137 ERR: load: json: invalid JSON at /api/schedules/01HDB144C88BFRN9N30FAX0XMK
15:28:28.494450 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:28.494520 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:28.494568 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:28.494584 ERR: load: json: invalid JSON at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:28.494581 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:28.494734 ERR: load: json: invalid JSON at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:28.995189 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:28.995682 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:28.996989 ERR: load: json: invalid JSON at /api/schedules/01HDB13WYYSEPJDDZXX6DKPVJW
15:28:31.448741 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144D8ZNSCT9YDS7E0ZAT1
15:28:31.448772 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144D8ZNSCT9YDS7E0ZAT1
15:28:31.448878 ERR: load: json: invalid JSON at /api/schedules/01HDB144D8ZNSCT9YDS7E0ZAT1
15:28:31.450359 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144D8ZNSCT9YDS7J99S51
15:28:31.450398 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144D8ZNSCT9YDS7J99S51
15:28:31.450464 ERR: load: json: invalid JSON at /api/schedules/01HDB144D8ZNSCT9YDS7J99S51
15:28:31.526165 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:31.526211 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:31.526299 ERR: load: json: invalid JSON at /api/schedules/01HDB141AN8M2MTV2SDWJ2B1J3
15:28:31.573322 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:31.573333 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:31.573428 ERR: load: json: invalid JSON at /api/schedules/01HDB144CAFW3F1C3SBC8QVRQE
15:28:35.727880 ERR: load: status code: invalid status code: 500 (expected: 200) at /
15:28:35.728287 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/signup
15:28:35.728550 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/signup
15:28:35.728824 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB144D5PKVQ5D32Q5X3834D
15:28:35.729023 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB144D5PKVQ5D32Q5X3834D
15:28:35.729793 ERR: load: json: invalid JSON at /api/schedules/01HDB144D5PKVQ5D32Q5X3834D
15:28:35.730142 ERR: load: json: invalid JSON at /api/signup
15:28:36.222313 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/signup
15:28:36.222365 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/signup
15:28:36.222515 ERR: load: json: invalid JSON at /api/signup
15:28:41.247341 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
15:28:42.354958 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:42.355375 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:42.356298 ERR: load: json: invalid JSON at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:42.860561 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
15:28:45.708989 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:45.709066 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:45.709114 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
15:28:45.709204 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
15:28:45.709355 ERR: load: json: invalid JSON at /api/schedules
15:28:45.709419 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB14H3HDTJ5WTHMB8S00WSR
15:28:45.716055 ERR: load: json: invalid JSON at /api/schedules/01HDB14H0MY088RY0DK0250E92
15:28:45.716111 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB14H3HDTJ5WTHMB8S00WSR
15:28:45.716136 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB14H3HDTJ5WTHMB8S00WSR
15:28:45.716156 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB14H3HDTJ5WTHMB8S00WSR
15:28:45.720535 score: 4356(4459 - 103) : pass
15:28:45.720551 deduction: 103 / timeout: 0
score=4356
めっちゃエラー出るようになったんだけどこれは何…？
myuonkataribe見ていると5xx系のエラーがちょくちょく起きている。
インスタンスの限界きている可能性あるかも？
myuonエラー少し解消はしたが、根本的な解決にはなっていない。
DBが根本的なネックになっているように見えるのでDB分割を考えたい。
テーブルを分割するのはtransactionの都合で難しいと思うが、データの一部を持っていくのはいけそう。
具体的にはschedule_idを基準に2グループに割って、reservationsもschdulesもそのIDが属する方のDBインスタンスをみるようにしたらいけそうに見える(？)
myuonよく見たらなぜかcreateReservationHandlerで予約のIDの採番ミスってそうだったので直したら500エラーがだいぶへった
(これ意図しているのだろうか？)
myuon./bin/benchmarker -target 57.180.65.17
15:59:21.514828 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB2WD7J2PF4H3YBDXHCQVR1
15:59:21.514862 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB2WD7J2PF4H3YBDXHCQVR1
15:59:21.514973 ERR: load: json: invalid JSON at /api/schedules/01HDB2WD7J2PF4H3YBDXHCQVR1
15:59:22.049103 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
15:59:41.957280 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB2XBRQQVKKDRFK8FJTEQA4
15:59:42.103140 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB2XBRQQVKKDRFK8FJTEQA4
16:00:08.725682 ERR: validation: invalid: overbooking at 01HDB2WD793V6H9CHRCZEG7E48
16:00:08.737596 ERR: validation: invalid: overbooking at 01HDB2WA7S0TXPG8F0RED7H60V
16:00:08.745658 ERR: validation: invalid: overbooking at 01HDB2X4Y02TKWC60E05R3ASBV
16:00:08.753699 ERR: validation: invalid: overbooking at 01HDB2WD7AY3FZ4P1VJ1YQ6ZEQ
16:00:08.756120 ERR: validation: invalid: overbooking at 01HDB2WD7EZVY0CHZBQAS6PD08
16:00:08.765321 ERR: validation: invalid: overbooking at 01HDB2WQXP3ZS5FTSV2N56NK9K
16:00:08.765552 ERR: validation: invalid: overbooking at 01HDB2W26NVJBRTXPXK3ZWEYK0
16:00:08.773182 ERR: validation: invalid: overbooking at 01HDB2WD7NEQQ9960JT0B8ATXH
16:00:08.773763 ERR: validation: invalid: overbooking at 01HDB2W93R8A3ST5QC73DJ36BX
16:00:08.777539 ERR: validation: invalid: overbooking at 01HDB2W2AG1J07JHWCS34XQNRS
16:00:08.779332 ERR: validation: invalid: overbooking at 01HDB2WQXK43Y2173W9JQJ1NB4
16:00:08.783006 ERR: validation: invalid: overbooking at 01HDB2WYW3V6RY0PH2CRHPRG50
16:00:08.784684 ERR: validation: invalid: overbooking at 01HDB2W2B72V3YA9KZMXWA7N92
16:00:08.788646 ERR: validation: invalid: overbooking at 01HDB2WD7J2PF4H3YBDXHCQVR1
16:00:08.791765 score: 3411(4118 - 707) : pass
16:00:08.791775 deduction: 706 / timeout: 10
score=3411
エラー直せば4000はいけそう
myuontransactionでFOR UPDATEしているところがあり、最悪ロック取らなくて不整合起きてもスコアアップの方が勝つんじゃないかと思って外したらだいぶ上がった
16:08:49.615288 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2FWEHKP7G6ZX422KJKN
16:08:49.615372 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2FT9D9DHP1J0XBZZFJY
16:08:49.615377 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2EY3QRZ0P4PTD1PHMKJ
16:08:49.697305 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2FWEHKP7G6ZX422KJKN
16:08:49.697307 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2FT9D9DHP1J0XBZZFJY
16:08:49.706328 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB3E2FWEHKP7G6ZX422KJKN
16:09:05.312193 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
16:09:05.312225 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
16:09:05.312317 ERR: load: json: invalid JSON at /api/schedules
16:09:05.358453 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
16:09:05.358828 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
16:09:05.358893 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
16:09:05.358869 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
16:09:05.359113 ERR: load: json: invalid JSON at /api/schedules
16:09:05.359293 ERR: load: json: invalid JSON at /api/schedules
16:09:05.848601 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
16:09:05.848576 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
16:09:05.849018 ERR: load: json: invalid JSON at /api/schedules
16:09:05.850974 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
16:09:05.851274 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
16:09:05.851863 ERR: load: json: invalid JSON at /api/schedules
16:09:07.597579 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB3EJKT1QQ02P07NSM34EED
16:09:07.597617 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB3EJKT1QQ02P07NSM34EED
16:09:07.597669 ERR: load: json: invalid JSON at /api/schedules/01HDB3EJKT1QQ02P07NSM34EED
16:09:19.459278 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB3ES616GY0W6KA4M37Z1PY
16:09:19.459307 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB3ES616GY0W6KA4M37Z1PY
16:09:19.459400 ERR: load: json: invalid JSON at /api/schedules/01HDB3ES616GY0W6KA4M37Z1PY
16:09:30.639502 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB3F8RZG66FKH9BDR5DG55M
16:09:30.639511 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB3F8RZG66FKH9BDR5DG55M
16:09:30.639663 ERR: load: json: invalid JSON at /api/schedules/01HDB3F8RZG66FKH9BDR5DG55M
16:09:30.666344 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
16:09:30.668894 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
16:09:30.675900 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
16:09:55.647885 ERR: validation: missmatch: all-schedules.count 49 != expected 50
16:09:55.675751 ERR: validation: invalid: overbooking at 01HDB3EHYAAWWCPGNCH5JHWK5B
16:09:55.709923 ERR: validation: invalid: overbooking at 01HDB3ECM0F6CBS058KN3H21GB
16:09:55.723397 ERR: validation: invalid: overbooking at 01HDB3DZGMNX34HS5VQQXJKQ18
16:09:55.752639 ERR: validation: invalid: overbooking at 01HDB3EXWP0YEX6N0CS8GMZANP
16:09:55.766146 ERR: validation: invalid: overbooking at 01HDB3ES616GY0W6KA4G755N9Y
16:09:55.775561 ERR: validation: invalid: overbooking at 01HDB3E9JK01D2SS3RB7AB63CY
16:09:55.776228 ERR: validation: invalid: overbooking at 01HDB3F4EG1NEPMDSD7XHG3B5G
16:09:55.788056 ERR: validation: invalid: overbooking at 01HDB3E2FWEHKP7G6ZX422KJKN
16:09:55.790064 ERR: validation: invalid: overbooking at 01HDB3DZ5SWQAYRZGTFG9BAXS1
16:09:55.795221 ERR: validation: invalid: overbooking at 01HDB3ES616GY0W6KA4M37Z1PY
16:09:55.796642 ERR: validation: invalid: overbooking at 01HDB3ECMH1JTJDVVFWNQDYD1A
16:09:55.799098 score: 4158(4791 - 633) : pass
16:09:55.799115 deduction: 633 / timeout: 0
score=4158
myuonテーブル分割するとschedule一覧とかをとるときのINクエリの書き方とかがかなり面倒になりそうなのでできればやりたくないな…
とりあえずサーバーとDBは分割するかという感じ
myuonMySQLの設定
bind-addressの追加
[mysqld]
bind-address = 172.31.38.216
port = 3306
ユーザーを新規に作る。
CREATE USER "name"@"%" IDENTIFIED BY "password";
GRANT ALL PRIVILEGES ON *.* TO "name"@"%";
FLUSH PRIVILEGES;
bind-addressを最後にかかないとincludeしているconfigか何かで上書きされているようで、結構ハマった
myuon遅くなった🤔
ということで律速しているのがほぼDBなのでやはりDB分割しないとダメかも？
あるいはアプリ2台にしてload balanceするかな
myuon17:33:10.856173 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB88H3QZDXTF5Y5B3HXEDG8
17:33:10.899913 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB88H3QZDXTF5Y5B3HXEDG8
17:33:10.928965 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB88H3QZDXTF5Y5B3HXEDG8
17:33:10.948346 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB88H3QZDXTF5Y5B3HXEDG8
17:33:21.054276 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB88QEX3M6SR7QTZ3SKM7MQ
17:33:21.054299 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB88QEX3M6SR7QTZ3SKM7MQ
17:33:21.054373 ERR: load: json: invalid JSON at /api/schedules/01HDB88QEX3M6SR7QTZ3SKM7MQ
17:33:21.100304 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB88QEY51TAD1XS1WJ9KEGQ
17:33:21.100328 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB88QEY51TAD1XS1WJ9KEGQ
17:33:21.100433 ERR: load: json: invalid JSON at /api/schedules/01HDB88QEY51TAD1XS1WJ9KEGQ
17:33:24.118950 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:24.119034 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:24.119110 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:24.119170 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:24.119233 ERR: load: json: invalid JSON at /api/schedules
17:33:24.119312 ERR: load: json: invalid JSON at /api/schedules
17:33:34.765587 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.765657 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.765718 ERR: load: json: invalid JSON at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.887424 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB891KXBT90RNNTBAE8KVTG
17:33:34.887504 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB891KXBT90RNNTBAE8KVTG
17:33:34.887518 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.887588 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.887696 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.887744 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.887788 ERR: load: json: invalid JSON at /api/schedules/01HDB891KXBT90RNNTBAE8KVTG
17:33:34.887788 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TQQ03TF4JPVCSS0NGX
17:33:34.887835 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.887848 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TQQ03TF4JPVCSS0NGX
17:33:34.887878 ERR: load: json: invalid JSON at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:34.887904 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.887971 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.888057 ERR: load: json: invalid JSON at /api/schedules/01HDB894TQQ03TF4JPVCSS0NGX
17:33:34.888067 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:34.966014 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:34.966107 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:34.966193 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:34.966234 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:34.966259 ERR: load: json: invalid JSON at /api/schedules
17:33:34.966367 ERR: load: json: invalid JSON at /api/schedules
17:33:35.440462 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.440685 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:35.440983 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.441273 ERR: load: status code: invalid status code: 500 (expected: 200) at /
17:33:35.441308 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:35.441328 ERR: load: json: invalid JSON at /api/schedules/01HDB88Y5JWHYZ65ENA58NKKMY
17:33:35.441493 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.441571 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.441619 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:35.441643 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.441778 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:35.441925 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.441935 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.442109 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.442341 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFTHYA1CB
17:33:35.442344 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFJEGNAP7
17:33:35.471029 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
17:33:36.856182 ERR: load: status code: invalid status code: 500 (expected: 200) at /
17:33:36.877167 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB894TN6FC8X57NFNM9SHSJ
17:33:36.877189 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB894TN6FC8X57NFNM9SHSJ
17:33:36.877279 ERR: load: json: invalid JSON at /api/schedules/01HDB894TN6FC8X57NFNM9SHSJ
17:33:36.896230 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
17:33:48.859209 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB89P45WS4KS1N6PTK64K7D
17:33:48.859234 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDB89P43YYYJDRKXHT99T0JS
17:33:52.143690 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:52.143689 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB89SEFETSCKDNAJKV57CXV
17:33:52.143842 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB89SEFETSCKDNAJKV57CXV
17:33:52.143779 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:52.143918 ERR: load: json: invalid JSON at /api/schedules/01HDB89SEFETSCKDNAJKV57CXV
17:33:52.143978 ERR: load: json: invalid JSON at /api/schedules
17:33:52.166164 ERR: load: status code: invalid status code: 500 (expected: 200) at /
17:33:52.213085 ERR: load: status code: invalid status code: 500 (expected: 200) at /
17:33:52.213123 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
17:33:52.267111 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
17:33:52.280940 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB89P3WSE3Q2TKQXBFAFYVZ
17:33:52.280999 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB89P3WSE3Q2TKQXBFAFYVZ
17:33:52.281076 ERR: load: json: invalid JSON at /api/schedules/01HDB89P3WSE3Q2TKQXBFAFYVZ
17:33:52.416042 ERR: load: status code: invalid status code: 500 (expected: 200) at /
17:33:53.282460 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB89SJCKDH65YQ3ZJ886YQP
17:33:53.282510 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB89SJCKDH65YQ3ZJ886YQP
17:33:53.282580 ERR: load: json: invalid JSON at /api/schedules/01HDB89SJCKDH65YQ3ZJ886YQP
17:33:53.308834 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB89SJ8X2S4B8SPB6SFCEHB
17:33:53.308860 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB89SJ8X2S4B8SPB6SFCEHB
17:33:53.308929 ERR: load: json: invalid JSON at /api/schedules/01HDB89SJ8X2S4B8SPB6SFCEHB
17:33:53.458466 ERR: load: asset: status code: invalid status code: 500 (expected: 200) at /esm/index.js
17:33:53.468016 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules/01HDB89SGQ6BG8R5MD30QXZR12
17:33:53.468051 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules/01HDB89SGQ6BG8R5MD30QXZR12
17:33:53.468133 ERR: load: json: invalid JSON at /api/schedules/01HDB89SGQ6BG8R5MD30QXZR12
17:33:54.104218 ERR: load: status code: invalid status code: 500 (expected: 200) at /api/schedules
17:33:54.104244 ERR: load: content type: invalid content type: text/html (expected: application/json) at /api/schedules
17:33:54.104330 ERR: load: json: invalid JSON at /api/schedules
17:34:19.173729 ERR: validation: missmatch: all-schedules.count 54 != expected 55
17:34:19.236891 ERR: validation: invalid: overbooking at 01HDB89GFDW5C41T3S2S3WB808
17:34:19.241102 ERR: validation: invalid: overbooking at 01HDB89P45WS4KS1N6PTK64K7D
17:34:19.270163 ERR: validation: invalid: overbooking at 01HDB88Y6JZGTAT29MQK421R6B
17:34:19.278312 ERR: validation: invalid: overbooking at 01HDB88QE4C9EDZRJF8HDRD2ZT
17:34:19.279902 ERR: validation: invalid: overbooking at 01HDB88GGY5VWVCGB6JRRMRFRP
17:34:19.282320 ERR: validation: invalid: overbooking at 01HDB88Y5JWHYZ65ENA58NKKMY
17:34:19.293536 ERR: validation: invalid: overbooking at 01HDB88KKDTY6AHNVAMH358RM4
17:34:19.298387 ERR: validation: invalid: overbooking at 01HDB88Y6KP8RR48F22E2KJEPC
17:34:19.303266 score: 4178(4719 - 541) : pass
17:34:19.303288 deduction: 541 / timeout: 7
score=4178
とりあえずnginxでload balanceのみ行う設定を維持して、MySQLはそれぞれのものを使うように。
データが完全に分割される上に整合性が死ぬので普通に考えれば許容できないと思われるが、意外とbenchmarkerが優しくてあんまり減点されない。
(あと多分2台のDBにまたがるようなリクエストがきていない)
なので、あとは元々の想定だったschedule_idでアクセス先のDBを分割する作業と、 /api/schedules でそれらを良い感じに分離する作業をすると良さそう。
myuon続きはまた次回
myuonadmin prepareがslow query logに出ていて、見様見真似でinterpolateParams=trueをつけてみる。
それと、やっぱりfor updateは戻しておいてtransactionalに実行するように
score=4181
あまり変わっていないような気はするが、pt-query-digestを見る限りではadmin prepareなどがなくなっているっぽいので良さげ？
(あるいは、タイミングの問題の可能性も一応あるのでどうだろう…たまたまかも)
myuon一応long-query-time=0にして見てみた。
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 55s 1us 78ms 976us 5ms 3ms 167us
# Lock time 11s 0 78ms 198us 1us 2ms 1us
# Rows sent 356.10k 0 127 6.48 42.48 17.65 0.99
# Rows examine 613.71k 0 358 11.18 62.76 32.48 0.99
# Query size 8.49M 6 3.76k 158.26 719.66 383.35 76.28
lock timeは支配的ではないにせよ結構ある気はするので、これはここからどうにかできるのだろうか？
重そうなのはCOMMITと以下
SELECT 1 FROM `schedules` WHERE `id` = '01HDX7EJM1ABJVFD6AGEFS3YQM' LIMIT 1 FOR UPDATE
どちらもずっと重いのではなく重い時だけ重いやつなので、少なくともwriteで競合する部分については本質的に避けられない系な気はしている。
myuonbenchmarkerが出してくるinvalid jsonとかinvalid content-typeの500エラーが一体どこからきているんだろうとずっとわからなかったが、バックエンド側でログも出ていないしstatus codeも合わないしでどうもnginxっぽい。
ということでnginx側で問題が起きていないかを調べる。
myuonnginxのエラーログを見る。
too many open filesエラーがあったので、ulimitの上限とnginxのLimitNOFILEの上限を上げておく。
[Service]
LimitNOFILE=65536
nginxのエラーを解消したらだいぶスコアが上がった。
./bin/benchmarker -target 13.231.154.64
18:24:05.417146 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBYS2M9T3CRDHC55ZVJV5P
18:24:05.444216 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBYS2M9T3CRDHC55ZVJV5P
18:24:05.455995 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBYS2M9T3CRDHC55ZVJV5P
18:24:14.981710 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZ29RB0R5MS8VKYXJ5J49
18:24:33.194360 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6Q3TWHDY1N9JYNFR6N
18:24:33.194373 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6M2JZVQMQ58YACJPZR
18:24:33.194409 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6M2JZVQMQ58XWMAX5D
18:24:33.194428 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6KQ380ZE1A83NJMVJY
18:24:33.194450 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6GS3D2FX891JCJY8Y0
18:24:33.194474 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6GS3D2FX891GTAHBJ8
18:24:33.194504 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6GS3D2FX891D057Y8Z
18:24:33.194531 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXBZM6FSAGX8D504HETADQJ
18:25:11.617993 score: 5834(5846 - 12) : pass
18:25:11.618012 deduction: 12 / timeout: 0
score=5834
myuonDB分割は結構厳しそうなので、静的ファイルの配信だけ分割できないかな？という気持ちになってきた。
これはtopを見ているとwebapp, mysql, nginxが結構均等に使われている感じがあり、nginxが忙しいならその辺り(あるいはbackendに流す部分もか？)を逃がせるならありなのではと思ったため。
webappとmysqlとnginxで分割したいので、server1はnginxと静的ファイルの配信に使い、server2にwebappとmysqlを入れて処理を流すようにしてみる。
myuonそこそこスコア上がった
./bin/benchmarker -target 13.231.154.64
18:29:28.901937 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXC8MZG31PV2TMQA9ZRR3TR
18:29:28.973277 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXC8MZG31PV2TMQA9ZRR3TR
18:29:28.981009 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXC8MZG31PV2TMQA9ZRR3TR
18:29:29.017885 ERR: load: invalid: 存在しないはずのスケジュール ID です: 01HDXC8MZG31PV2TMQA9ZRR3TR
18:30:38.380193 score: 6440(6444 - 4) : pass
18:30:38.380222 deduction: 4 / timeout: 0
score=6440
myuonSetMaxIdleConnsを10にしたらまた少しスコアが上がる
./bin/benchmarker -target 13.231.154.64
18:43:52.556797 score: 6824(6824 - 0) : pass
18:43:52.556819 deduction: 0 / timeout: 0
score=6824
myuonログを消して何回かガチャを回したが6824が一番良いスコアだった。
以上ということで。
このスクラップは2023/10/29にクローズされました