DBのクエリが多すぎる #6813

SanMurakami · 2020-11-07T07:21:46Z

💡 Summary

毎秒800〜1500ぐらいのクエリがPostgreSQLに飛んでいて、PostgreSQLとMisskeyを違うネットワークに置くと極端に重くなる。

参考GIF（内容が見えないようにリサイズしてます）

🙂 Expected Behavior

DB内部である程度処理してから結果を返す

☹️ Actual Behavior

全部Misskey側で処理している

📝 Steps to Reproduce

PostgreSQLのログを見れるようにする
ユーザーが多いインスタンスを作る

📌 Environment

Misskey.io v12.54.0

syuilo · 2020-11-07T07:26:45Z

全部Misskey側で処理しているの詳細が知りたい

syuilo · 2020-11-07T07:31:35Z

例えばタイムラインを取得するクエリを例にすると、取得されることになる各ノートのユーザー情報を予めJOINするようにしているからDBで出来る限りのことはやっている状態ではなかろうか
https://github.com/syuilo/misskey/blob/30c9c3739f086a7f9df2c4d8ccae78c605a25bb9/src/server/api/endpoints/notes/timeline.ts#L125

rinsuki · 2020-11-07T07:42:00Z

packManyがpackを全部並列で呼び出してるのでfindOneが呼ばれまくってると思う
本来はpackManyでよしなにやってpackがpackManyのラッパになっているべき

別プロジェクトだけど例 https://github.com/rinsuki/sea/blob/5766344c7463dd856ea27d83a47245d764b14cac/src/db/repositories/post.ts (これも application は手抜いてfindOneを投稿数ぶん呼んでるのでよくない)

syuilo · 2020-11-07T07:44:04Z

packMany はそれぞれに pack を呼び出していて、pack は渡されたものがオブジェクトなら find せずにそのまま利用しているから新たなクエリは発生しないように見える
https://github.com/syuilo/misskey/blob/30c9c3739f086a7f9df2c4d8ccae78c605a25bb9/src/models/repositories/note.ts#L95

rinsuki · 2020-11-07T07:45:29Z

noteのrepositoryだとpopulateEmojisとかだめそう

syuilo · 2020-11-07T07:49:16Z

populateEmojisはカスタム絵文字が含まれてない限りクエリ発火しなさそうだけど populateMyReaction は毎回発火してそう

mei23 · 2020-11-07T07:49:34Z

大きいインスタンスでクエリ数稼いでるのがたぶんこの辺
https://github.com/mei23/misskey-v12/pull/686/files
↑はsaveで無駄にトランザクションとSELECT流してるところを修正しているけど
この辺をクエリ的に減らすのは難しいかも

rinsuki · 2020-11-07T07:50:47Z

populateEmojisはカスタム絵文字が含まれてない限りクエリ発火しなさそう

それはそうなんだけどカスタム絵文字がめっちゃ付いてるとだいたいおもしろいのでRNされまくって刺さり度上がりそう (まあコーナーケースではある)

syuilo · 2020-11-07T07:52:48Z

大きいインスタンスでクエリ数稼いでるのがたぶんこの辺

ノート作成はいろいろやることあるからクエリ多いだろうなぁ

syuilo · 2020-11-07T07:55:09Z

write時のクエリを減らすのはめいめいが言っているように厳しそうだけど、read時のクエリはなんか工夫すれば減らせそうな雰囲気はするな

syuilo · 2020-11-07T07:57:49Z

populateEmojis は見る感じやってることが複雑だからDBサイドでやるのは難しそう

rinsuki · 2020-11-07T07:59:07Z

packManyの時notesの分集約して一回のクエリで問い合わせればマシになりそう

syuilo · 2020-11-07T08:00:55Z

あと気になっているのはデータベースという割と頻繁にアクセスされるであろうシステムを地理的に離れたサーバーで動かすのは割と良くあることなんだろうか

rinsuki · 2020-11-07T08:01:42Z

DB分けないとアプリサーバーの数増やせなくない？

mei23 · 2020-11-07T08:03:08Z

pack系は非アクティブユーザーの分は処理しないからさほど増大しないけど
Note作成後にミュートやアンテナ見る所は休眠ユーザー分も処理するから登録ユーザーが増えるに従って増大していきそう

syuilo · 2020-11-07T08:03:41Z

DB分けないとアプリサーバーの数増やせなくない？

サーバー自体を分けるのはあると思うけど、それを地理的に離れている場合でもやるのかなって思った

SanMurakami · 2020-11-07T08:05:16Z

離れてると言っても国内だから20msぐらいの遅延だし、地理的に離れてないと以前の震災みたいに影響を受ける

rinsuki · 2020-11-07T08:09:28Z

サーバー自体を分けるのはあると思うけど、それを地理的に離れている場合でもやるのかなって思った

2リージョン以上で同じアプリ動かす時には素直にやるとDBをどっちかのリージョンに寄せないといけない気がするけどあんまり詳しくない (まあリードレプリカとかでマシにはなるだろうけどそれでもwriteはプライマリに行くはずだし)

SanMurakami · 2020-11-07T08:12:37Z

2リージョン以上で同じアプリ動かす時には素直にやるとDBをどっちかのリージョンに寄せないといけない気がする

元々はPgpool-IIでロードバランシングしてたけど、クエリが多すぎるせいかPgpool-II噛ませるだけでかなりパフォーマンスが下がる
今回の構成も最後の方にPgpool-II入れてPostgreSQLでストリーミングレプリケーションしている同一ネットワーク内のDBにウェイトを置いてたけどそれでもめちゃめちゃ重かった

syuilo · 2020-11-07T08:14:09Z

Note作成後にミュートやアンテナ見る所は休眠ユーザー分も処理するから登録ユーザーが増えるに従って増大していきそう

一定期間使用がなかったアカウントのミュートとかアンテナは削除するか一時停止みたいな処理しないとダメかなぁ
まあMisskeyくらいの規模ならデータベースに負荷がかかるほどのミュート/アンテナ設定済み休眠アカウントはなかなか発生しない気はする

syuilo · 2020-11-07T08:15:50Z

負荷を考える上では休眠かどうかは考慮しなくて良いか

syuilo · 2020-11-08T07:56:47Z

需要の無さそうなautoWatch機能を削除したのでノート作成時のクエリが若干減った
syuilo/Misskey.old@9d1fa3f

syuilo · 2020-11-08T07:57:19Z

(需要があったとしてもサーバーサイドでやる必要はない)

syuilo · 2021-03-18T04:16:45Z

syuilo/Misskey.old@4f24915 とか syuilo/Misskey.old@8aa0891 あたりでクエリ数まあまあ減った

syuilo · 2021-03-18T12:06:05Z

packManyの時notesの分集約して一回のクエリで問い合わせればマシになりそう

これやるか

syuilo · 2021-03-20T02:07:49Z

populateEmojis もまとめて行うようにするか

Related: #6813

syuilo · 2021-03-20T04:55:16Z

やった

syuilo · 2021-03-20T05:32:01Z

ここまでの諸々の改修の結果

タイムラインを100ノート分、通知を100個分読み込んだ時の総発行クエリ

1クエリで3msのオーバーヘッドがあると仮定した場合の時間も併記する

before

797 (2391ms)

notifications: 542クエリ
timeline: 255クエリ

after

25 (75ms)

notifications: 11クエリ
timeline: 14クエリ

mei23 · 2021-03-20T16:05:51Z

フォロワー検索の場合
src/server/api/endpoints/users/followers.ts

	.innerJoinAndSelect('following.follower', 'follower')

フォローされてる検索の場合
src/server/api/endpoints/users/following.ts

	.innerJoinAndSelect('following.followee', 'followee')

あたりを表結合するといいはず

mei23 · 2021-03-20T16:30:20Z

あと、API (TL/show) 系でクエリされる場合、必ず (デフォルトのDetailedPackedNoeでは) reply, renote, app は解決されるので

.leftJoinAndSelect('note.reply', 'reply')
.leftJoinAndSelect('note.renote', 'renote')
.leftJoinAndSelect('note.app', 'app')

あたりを最初から結合しとくといいかも。
どうせID=NULLならどれともマッチしないのでJOIN負荷は変わらない（と思う）

→ app以外は既に入ってた

mei23 · 2021-03-20T16:30:28Z

あとleftJoinAndSelect('note.user', 'user')よりは、innerJoinAndSelect('note.user', 'user')なのかも？と
今は、ユーザー→ノートでカスケード削除されるからどっちども同じだけど、ユーザーがいない時はレコードを返さないのが正しいかも。

https://github.com/syuilo/misskey/issues/6813#issuecomment-803400023

Related #6813

mei23 · 2021-03-21T02:40:23Z

syuilo/Misskey.old@d7e7848
note.user 以外はINNERにしちゃダメなはず

mei23 · 2021-03-21T02:57:15Z

.leftJoinAndSelect('reply.user', 'replyUser')
.leftJoinAndSelect('renote.user', 'renoteUser')

は使われない気がする？

syuilo · 2021-03-21T03:11:44Z

```ts
.leftJoinAndSelect('reply.user', 'replyUser')
.leftJoinAndSelect('renote.user', 'renoteUser')

は使われない気がする？

リプライとRenoteのpackするときは必ずそのユーザーも引っ張ってくるからjoinしてる

syuilo · 2021-03-21T03:13:40Z

d7e7848
note.user 以外はINNERにしちゃダメなはず

リプライとかrenoteが消えていたときも(今のところ)レコード返したくないからそうしたけど、問題ありそうかな

mei23 · 2021-03-21T03:17:37Z

replyUser, renoteUser はどこからも参照されてないし、entityにも定義なくないです？

syuilo · 2021-03-21T03:20:12Z

確認したときはこの書き方でjoinされたと思ったけど、勘違いだったかな
再度確認してみる

mei23 · 2021-03-21T03:24:30Z

リプライとかrenoteが消えていたときも(今のところ)レコード返したくないからそうしたけど、問題ありそうかな

最初からリプライでもRenoteでもない投稿がまるごと消えちゃう

rinsuki · 2021-03-21T03:25:37Z

というかappがDBに投げるクエリ数の話なのに INNER JOIN か LEFT JOIN かって関係あるの？

syuilo · 2021-03-21T03:26:32Z

クエリ数で言えば関係ない

syuilo · 2021-03-21T03:35:58Z

確認したときはこの書き方でjoinされたと思ったけど、勘違いだったかな
再度確認してみる

やはりjoinされてたので、第二引数は他のクエリ内で参照するための単なるエイリアス的な扱いでそんなに重要じゃないのかも

syuilo · 2021-03-22T10:08:54Z

出来ることはやって大分減ったはず
もう改修の余地がほぼなく、これ以上は原理的に(？)減らせないと思うのでclose

SanMurakami · 2021-03-22T18:06:54Z

かなり改善しました、ありがとうございます。

改善前

改善後

Related misskey-dev#6813

Related: misskey-dev#6813

https://github.com/syuilo/misskey/issues/6813#issuecomment-803400023

Related misskey-dev#6813

Related: misskey-dev#6813

Related misskey-dev#6813

SanMurakami added the ⚠️bug? This might be a bug label Nov 7, 2020

syuilo added the packages/backend Server side specific issue/PR label Nov 7, 2020

syuilo added 🐢Performance Efficiency related issue/PR and removed ⚠️bug? This might be a bug labels Nov 7, 2020

syuilo pinned this issue Mar 18, 2021

syuilo added a commit that referenced this issue Mar 20, 2021

perf(server): Reduce database query

f27e403

Related: #6813

syuilo referenced this issue Mar 21, 2021

fix(server): Use inner join

d7e7848

https://github.com/syuilo/misskey/issues/6813#issuecomment-803400023

syuilo added a commit that referenced this issue Mar 21, 2021

perf(server): Improve following/followers API performance

e987a50

Related #6813

syuilo closed this as completed Mar 22, 2021

syuilo unpinned this issue Mar 22, 2021

foggy1 pushed a commit to foggy-llc/pluskey that referenced this issue May 8, 2021

perf: myReaction の取得をまとめて行うように

bc482d3

Related misskey-dev#6813

foggy1 pushed a commit to foggy-llc/pluskey that referenced this issue May 8, 2021

perf(server): Reduce database query

f7b3ab3

Related: misskey-dev#6813

foggy1 referenced this issue in foggy-llc/pluskey May 8, 2021

fix(server): Use inner join

a619e59

https://github.com/syuilo/misskey/issues/6813#issuecomment-803400023

foggy1 pushed a commit to foggy-llc/pluskey that referenced this issue May 8, 2021

perf(server): Improve following/followers API performance

ad848a1

Related misskey-dev#6813

snk-lab pushed a commit to snk-lab/misskey-kusa-note that referenced this issue Sep 24, 2021

perf: myReaction の取得をまとめて行うように

f385f9c

Related misskey-dev#6813

snk-lab pushed a commit to snk-lab/misskey-kusa-note that referenced this issue Sep 24, 2021

perf(server): Reduce database query

4211bfc

Related: misskey-dev#6813

snk-lab pushed a commit to snk-lab/misskey-kusa-note that referenced this issue Sep 24, 2021

perf(server): Improve following/followers API performance

5f266ce

Related misskey-dev#6813

DBのクエリが多すぎる #6813

DBのクエリが多すぎる #6813

Comments

SanMurakami commented Nov 7, 2020

💡 Summary

🙂 Expected Behavior

☹️ Actual Behavior

📝 Steps to Reproduce

📌 Environment

syuilo commented Nov 7, 2020

syuilo commented Nov 7, 2020

rinsuki commented Nov 7, 2020

syuilo commented Nov 7, 2020 • edited Loading

rinsuki commented Nov 7, 2020

syuilo commented Nov 7, 2020 • edited Loading

mei23 commented Nov 7, 2020

rinsuki commented Nov 7, 2020 • edited Loading

syuilo commented Nov 7, 2020

syuilo commented Nov 7, 2020

syuilo commented Nov 7, 2020

rinsuki commented Nov 7, 2020

syuilo commented Nov 7, 2020 • edited Loading

rinsuki commented Nov 7, 2020

mei23 commented Nov 7, 2020

syuilo commented Nov 7, 2020

SanMurakami commented Nov 7, 2020

rinsuki commented Nov 7, 2020

SanMurakami commented Nov 7, 2020 • edited Loading

syuilo commented Nov 7, 2020 • edited Loading

syuilo commented Nov 7, 2020

syuilo commented Nov 8, 2020

syuilo commented Nov 8, 2020

syuilo commented Mar 18, 2021

syuilo commented Mar 18, 2021

syuilo commented Mar 20, 2021

syuilo commented Mar 20, 2021

syuilo commented Mar 20, 2021 • edited Loading

タイムラインを100ノート分、通知を100個分読み込んだ時の総発行クエリ

before

after

mei23 commented Mar 20, 2021

mei23 commented Mar 20, 2021 • edited Loading

mei23 commented Mar 20, 2021

mei23 commented Mar 21, 2021

mei23 commented Mar 21, 2021

syuilo commented Mar 21, 2021 • edited Loading

syuilo commented Mar 21, 2021

mei23 commented Mar 21, 2021

syuilo commented Mar 21, 2021

mei23 commented Mar 21, 2021

rinsuki commented Mar 21, 2021

syuilo commented Mar 21, 2021

syuilo commented Mar 21, 2021

syuilo commented Mar 22, 2021

SanMurakami commented Mar 22, 2021

syuilo commented Nov 7, 2020 •

edited

Loading

syuilo commented Nov 7, 2020 •

edited

Loading

rinsuki commented Nov 7, 2020 •

edited

Loading

syuilo commented Nov 7, 2020 •

edited

Loading

SanMurakami commented Nov 7, 2020 •

edited

Loading

syuilo commented Nov 7, 2020 •

edited

Loading

syuilo commented Mar 20, 2021 •

edited

Loading

mei23 commented Mar 20, 2021 •

edited

Loading

syuilo commented Mar 21, 2021 •

edited

Loading