リアクションごとにノートのリアクション情報が更新されるのは重い

[syuilo]

Summary

Issueあった気がしたけど見当たらなかった

例えば1秒間に100回リアクションがつく場合リアクションレコードの挿入に加えて、100回ノートレコードの更新(=PostgreSQLでは実質的にノートテーブルの新規挿入&削除)が走ることになりかなり負荷が高い

そのためノートレコードごとにリアクション情報を非正規化して持つのをやめたいが、ノート取得するたびにリアクションを集計してくるのもかなり負荷が高いと思われるので、何かいい解決策を考える必要がある

例えばRedisにうまいことキャッシュできないかしら

案

• Redisにキャッシュして定期的にDBにベイクする(つまりRedis→DB)
  • 懸念: 結局ベイクが重そう、実装が難しそう
• Redisにキャッシュしてキャッシュがある場合はそれを使い、ない場合はDBから集計+キャッシュ追加(つまりDB→Redis)
  • 懸念: DBから集計が重そう
• 非正規化リアクション情報用DBを別に用意できるようにする

[syuilo]

もしくは非正規化して持っててもいいけど、更新は定期的にまとめて行うようにするとか

[syuilo]

SlackとかDiscordがどういう実装してるのか気になるな

[yuriha-chan]

投稿とかリノートされた時にブワーってつく傾向にあるから、Redisで短期間のキャッシュを持ち、反応が落ち着いたらDBに反映というのが良さそう

[syuilo]

問題は、RedisはJSONに対応してないからオブジェクトの特定のキーの値をアトミックにインクリメントするみたいな処理がおそらく出来ないこと

[syuilo]

reactionsCache:<noteId>:<reaction>
みたいなキーで持っておけば良いかも

[syuilo]

1. Redisにキーを追加する
2. 定期的にRedisからキーを読み出し、DBに反映する
3. Redisからキーを削除する

という流れになると思うけど、2を処理している最中に新しくキーが追加されたらそれが処理されないまま3に進んでキーが削除され、間違った値がDBにベイクされてしまう問題が発生するなど、意外と面倒かも

[syuilo]

良い実装方法募集中

[syuilo]

あとノートの数が多い場合、今よりだいぶマシになるとはいえやっぱり2の負荷が高そう
それを考えると究極的には「非正規化して持つのをやめる」しないといけないんだけどなかなか難しい

[tamaina]

PostgreSQLにおいて、あるレコードに対して1秒間に大量の書き換えがある場合の、一般的なパフォーマンス改善策ってなんだろう

[syuilo]

ノート取得するたびにリアクションを集計してくるのもかなり負荷が高いと思われる

クエリの書き方によっては別にそうでもない可能性はあるな

[syuilo]

Redisにキャッシュしつつ、Redisにキャッシュがない場合だけDBから集計する感じでいけないかな

[syuilo]

reactionsテーブルに対してノートIDの配列を渡したら以下のような結果が返ってくるSQL募集中

[{
  noteId: "hoge",
  reactions: {
    'foo': 10,
    'bar': 5,
  }
}, {
  noteId: "huga",
  reactions: {
    'foo': 20,
    'buzz': 4,
    'bar': 2,
  }
}, {
  ...
}]

[syuilo]

投稿日時が今からT以内の投稿であれば、リアクションされていれば高確率でRedisにキャッシュされているはずなのでRedisからキャッシュを読み、全面的にそれを採用しRedisになかったとしてもDBには問い合わせない(「その投稿にリアクションがされていない」ことと「リアクションされているがRedisにキャッシュされていない」ことの区別が不可能なため、パフォーマンスを優先しRedisにない場合はリアクションされていないと見做す)
投稿日時がTより前の投稿であれば、Redisにキャッシュされていないかキャッシュが消えている可能性が高まってくるため、Redisにキャッシュがあるか確認し、なければDBから集計+キャッシュ更新

が良いかも
ここで T = 3時間とか？

[syuilo]

問題としては

投稿日時がTより前の投稿であれば、Redisにキャッシュがあるか確認し、なければDBから集計+キャッシュ更新

の負荷が高い可能性があるという点

[syuilo]

実際にいっぱいリアクション付いた投稿に対してreactionsテーブルに対してノートIDの配列を渡したら以下のような結果が返ってくるSQLを投げてみてどれくらいの負荷になるか確認したい

[tamaina]

以下のような結果が

SELECT "noteId", "reaction", COUNT(*) AS "reactionCount"
FROM note_reaction
WHERE "noteId" IN ('9dh0bkw15q', '9dmmip9y48')
GROUP BY "noteId", "reaction";

で[noteId, reaction, reactionCount][]が返ってくるけどそれ以上は私はわからなかった（しSQLの仕事ではないかも？）

[syuilo]

で[noteId, reactionName, reactionCount][]が返ってくる

よさそう

[syuilo]

同じ種類のリアクション100個にたいして100レコードが返ってくるというのを避けられればどんな形式でも良い

[tamaina]

でもCOUNTって絶対遅いよね

[syuilo]

インデックスあっても遅いかしら

[tamaina]

note_reactionが762万レコードあるけど↑のようなSQLだと一瞬だったわ（INだと流石にインデックスが効いてるか）

[yuriha-chan]

問題は、RedisはJSONに対応してないからオブジェクトの特定のキーの値をアトミックにインクリメントするみたいな処理がおそらく出来ないこと

できるっぽい
https://redis.io/commands/hincrby/

[syuilo]

以下のような結果が

SELECT "noteId", "reaction", COUNT(*) AS "reactionCount"
FROM note_reaction
WHERE "noteId" IN ('9dh0bkw15q', '9dmmip9y48')
GROUP BY "noteId", "reaction";

で[noteId, reaction, reactionCount][]が返ってくるけどそれ以上は私はわからなかった（しSQLの仕事ではないかも？）

ioのDBでめちゃくちゃリアクション付いてる投稿とかめちゃくちゃ古い投稿とか全くリアクション付いてない投稿などいろいろ指定してクエリしてみてどれくらい時間かかるか計測しようとしている

[syuilo]

ちなみにioではnote_reactionレコードは1億くらいあるっぽい

[tamaina]

（ちなみにSQLはChatGPT頼り）

[pumpkin2048b]

PostgreSQLにおいて、あるレコードに対して1秒間に大量の書き換えがある場合の、一般的なパフォーマンス改善策ってなんだろう

#10173
ここらへんで触れてますが、1度の処理で書き込める状況ならBulk updateがいいかと思われます。
一般的なデータ投入でもパフォーマンスの改善として使われる手法だと認識してます。

[syuilo]

ioのDBでめちゃくちゃリアクション付いてる投稿とかめちゃくちゃ古い投稿とか全くリアクション付いてない投稿などいろいろ指定してクエリしてみてどれくらい時間かかるか計測しようとしている

150msくらいだった
この速度だとちょっと現実的ではないな(数十msであってほしい)

[syuilo]

今後もリアクションのレコード数は増加していくことを考えると、やっぱりノートからreactionsを消すことは現実的ではなさそうで、「noteにreactionsは持ちつつもその更新は一定間隔でbulk updateする」が落としどころかもなあ

[syuilo]

おごごごご

[tamaina]

Redisのリアクションイベントを拾って一定間隔（実行完了から1秒後みたいなインターバルにする？）でDBに適用するというDBキューがあればいいのか

[tamaina]

リアクションイベントをメモリに保持しておく必要があるんだからキューじゃダメでデーモンじゃないとか

[syuilo]

それが今の状態ですね

[syuilo]

あーテーブルは同じか

[syuilo]

テーブル分けたとしても結局そのテーブルの更新が重くなる

[tamaina]

テーブルを分けるならそれをRedisに保持しておくほうがいいんじゃないかと（整合性は知らない）

[yuriha-chan]

要するに以下のような要求がある。
「アクティブ状態」のときにはRedisのHashを参照すれば正確なカウントが得られる。
「非アクティブ状態」ときにはPostgreSQLの非正規化テーブルを参照すれば正確なカウントが得られる。

これを実現するには以下のようにすればよいはず。

読み出し方法
Redis にキーが存在するかを確認し、存在すればRedisから読み、存在しなければPostgreSQLの非正規化テーブルを読む。

書き込み方法
書き込み時にRedisのhashreactions:<noteId>が存在しない場合は、ノートの年齢がT未満なら、Redisのハッシュテーブルを作成する。そうでないなら、非正規化テーブルをRedisへとコピーする。
Redis のhashのリアクションカウントをインクリメントし、hash全体のexpireをTに更新する。
expireする前に余裕をもって、PostgreSQLの非正規化テーブルへ値をコピーする操作をスケジュールする。すでにその操作が予定されている場合は、その操作を延期する。
（特殊な事情により、書き込み操作を行う前にRedisのhashreactions:<noteId>がexpireしてしまっていたら、このスケジュールされた操作は、PostgreSQLの正規化テーブルから非正規化テーブルを更新する。）

この方法の肝は、PostgreSQLの非正規化テーブルへ値をコピーする操作の後にリアクションが入って、Redisのexpireが延期された（「Redisからの削除がやっぱりなしで」になった）としても、PostgreSQLの非正規化テーブルが予定より早く更新されたというだけで、全体の不整合を引き起こさない点にあります。

[syuilo]

Redisにキャッシュして定期的にDBにベイクする(つまりRedis→DB)

PostgreSQL(というかTypeORM)、それぞれ異なる値でのbulk updateに対応してない可能性があるな

[syuilo]

やっぱり対応してなかった
typeorm/typeorm#7326

[syuilo]

やる必要がある

[syuilo]

問題は、RedisはJSONに対応してないからオブジェクトの特定のキーの値をアトミックにインクリメントするみたいな処理がおそらく出来ないこと

hash使えそう