SMTP
テキストチャンネル / chat
ssh -fN -L 19999:localhost:19999 isucon@163.43.144.13
ssh -fN -L 8090:localhost:8090 isucon@163.43.144.13
ターミナルで叩いといて
Host s1 3.114.98.61
HostName 3.114.98.61
IdentityFile ~/.ssh/id_ed25519
ServerAliveInterval 30
ServerAliveCountMax 3
User isucon
Host s2 35.73.29.38
HostName 35.73.29.38
IdentityFile ~/.ssh/id_ed25519
ServerAliveInterval 30
ServerAliveCountMax 3
User isucon
Host s3 3.115.91.158
HostName 3.115.91.158
IdentityFile ~/.ssh/id_ed25519
ServerAliveInterval 30
ServerAliveCountMax 3
User isucon
1ssh -fN -L 19999:localhost:19999 isucon@163.43.144.13
ssh -fN -L 8090:localhost:8090 isucon@163.43.144.13
ターミナルで叩いといて 
1 2 1
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIC/X2el2n+F4nn+h5pbWILBp6WbqKLz2hKQX6vgQ0dCv isucon@ip-192-168-0-11ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIFEPXCGPCLThu0eO3NITGeff/Oooi6YV7KLxVPs7iVjx isucon@ip-192-168-0-13ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIGCPTp9Q4rUVnrLcbTOrV3Nta85+wtChJwxDMFU4iQUe isucon@ip-192-168-0-12
SELECT * FROM livestream_tags WHERE livestream_id = N: livestream_tagsテーブルで、WHEREとかで使われないidカラムがprimary keyになっているので、残りの2つで複合primary keyにすれば改善できそう
SELECT image FROM icons WHERE user_id = N: さっきの304返すやつで改善できそう。画像をそのままDBに入れてるのはよくないことにはよくないけど改善大変だし多分304返すようにするだけで十分 (edited)
1
1
1 
users ( id int(10) unsigned …
1livestream_id, tag_id)この二つの組は必ずユニークですか? 1
3XXが0だから304返せてなさそうではあるCREATE TABLE `livestream_tags` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`livestream_id` BIGINT NOT NULL,
`tag_id` BIGINT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY (`livestream_id`, `tag_id`)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
https://discord.com/channels/1146422358974931065/1146422942809456651/1177818816898678794
https://discord.com/channels/1146422358974931065/1146422942809456651/1177818816898678794 
for _, keyTaggedLivestream := range keyTaggedLivestreams {
ls := LivestreamModel{}
if err := tx.GetContext(ctx, &ls, "SELECT * FROM livestreams WHERE id = ?", keyTaggedLivestream.LivestreamID); err != nil {
return echo.NewHTTPError(http.StatusInternalServerError, "failed to get livestreams: "+err.Error())
}
livestreamModels = append(livestreamModels, &ls)
}
getUserStatisticsHandlerのSQL重そうだからcopilotくんに聞いたらこうしたらいいって返ってきたからこれで合ってるか確かめる仕事くらい
query := `
SELECT
u.name AS username,
(SELECT COUNT(*) FROM livestreams l INNER JOIN reactions r ON r.livestream_id = l.id WHERE l.user_id = u.id) AS reactions,
(SELECT IFNULL(SUM(lc.tip), 0) FROM livestreams l INNER JOIN livecomments lc ON lc.livestream_id = l.id WHERE l.user_id = u.id) AS tips
FROM users u
`
var rankings []UserRankingEntry
if err := tx.SelectContext(ctx, &rankings, query); err != nil {
return echo.NewHTTPError(http.StatusInternalServerError, "failed to get rankings: "+err.Error())
}
for i, ranking := range rankings {
rankings[i].Score = ranking.Reactions + ranking.Tips
}
sort.Slice(rankings, func(i, j int) bool {
return rankings[i].Score > rankings[j].Score
})
var tagIDList []int
if err := tx.SelectContext(ctx, &tagIDList, "SELECT id FROM tags WHERE name = ?", keyTagName); err != nil {
return echo.NewHTTPError(http.StatusInternalServerError, "failed to get tags: "+err.Error())
}
query, params, err := sqlx.In("SELECT * FROM livestream_tags WHERE tag_id IN (?) ORDER BY livestream_id DESC", tagIDList)
if err != nil {
return echo.NewHTTPError(http.StatusInternalServerError, "failed to construct IN query: "+err.Error())
}
1
1 1journalctl -eでサーバーのログが見れるのでデバッグとかで使うとよさそうisucon@ip-192-168-0-13:~$ sudo /opt/isucon-env-checker/envcheck
SSH 接続が成功しました
環境をチェックしています...
全てのチェックをパスしました 1
/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
↓
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
だけやったけどできてるかわからん
. (edited)~/env.sh
ISUCON13_MYSQL_DIALCONFIG_ADDRESS="127.0.0.1"
↓
ISUCON13_MYSQL_DIALCONFIG_ADDRESS="35.73.29.38" (edited) 
Unknown column 'image_hash'って出てる
Nov 25 08:12:50 ip-192-168-0-11 isupipe[54794]: {"time":"2023-11-25T08:12:50.925471028Z","id":"","remote_ip":"127.0.0.1","host":"pipe.u.isucon.dev","method":"POST","uri":"/api/icon","user_agent":"isucandar","status":500,"error":"code=500, message=failed to insert new user icon: sql: converting argument $3 type: unsupported type [32]uint8, a array","latency":1452794,"latency_human":"1.452794ms","bytes_in":92680,"bytes_out":137}
10min にしてある
1 