Sansan株式会社 部署 名前 データベース書き込み性能劣化 の原因を調査する Sansan技術本部 Kyoto Tech Talk #6 Sansan株式会社 向井 悠祐

写真が入ります 向井 悠祐 Sansan 株式会社 技術本部 Bill One Engineering Unit Core Business AP グループ 2021 年 Sansan 中途入社。 入社以来、Web アプリケーション開発エンジニアとして Bill One の開発に従事。現在は請求書受領領域の開発に 加えて、同領域における技術マネジメントに携わる。 前職の SIer では技術検証や開発プロジェクトへの技術 サポート、PoC 開発、社内システム開発などを担当。 1

Bill Oneは、Sansan株式会社が提供するインボイス管理サービスです。 郵送やメールといったさまざまな方法・形式で届く請求書をオンラインで一括受領し、素早く正確にデータ化。請求書を クラウド上で一元管理することで、アナログで非効率な請求書業務をデジタル化します。インボイス制度や電子帳簿保存法にも対応し、月次決算業務 を効率化することで、企業経営における意思決定のスピードを加速します。 ※月次決算業務 毎月の営業成績、財政状況を明らかにするために行われる業務。経理担当者が行う業務で、毎月の数字の締め処理作業として発生します。

Bill One のアーキテクチャ Email Cloud Load Balancing Backend Cloud Run Database Cloud SQL Static Files Cloud Storage Cloud Tasks API Gateway Cloud Load Balancing API Client User Logging Error Reporting Cloud Build Bill One Entry Management / Developer Tools Cloud Functions Monitoring Authentication Login Screen Frontend / BFF Cloud Run Pub/Sub

Transactional Outbox パターン - すべての非同期イベント処理はこのパターンでイベント発行を保証している Bill One における非同期イベントの仕組み Application Cloud Run Database Cloud SQL for PostgreSQL Event Bus Pub/Sub Event Bus Cloud Tasks Application Cloud Run Application Cloud Run Event Bus Cloud Tasks イベント発行予約 テーブル イベント発行済 テーブル イベント発行後 に移動する トランザクション トランザクション

月初に一部の API でレイテンシが悪化した - イベント発行済テーブルを含む 一部のテーブルへの書き込み処理で 遅延が発生した - 非同期イベントの処理において 一時的に追加数が処理数を上回った - 読み込み処理には大きな影響はなかった パフォーマンス劣化の事象

- 書き込みのみが発生するテーブルであっても データ量が多くアクセス過多な状況では パフォーマンス問題を起こしうる - 書き込みのみを大量に行うテーブルでは キャッシュのされやすさを考慮する必要がある 結論

サービス特性 - 更新よりも参照のリクエストのほうが多い - 月初にリクエストが多くなる - 月初に非同期処理を伴うデータ書き込みが多く発生する

- データベースは Cloud SQL for PostgreSQL を採用している - インデックスは基本的に B-Tree Index を使用している - 各テーブルの主キーは基本的に UUID v4 を使用している データ特性 -- テーブルのサイズ：合計すると数百 GB 程度 SELECT objectname, PG_RELATION_SIZE(objectname::regclass) AS bytes, PG_SIZE_PRETTY(PG_RELATION_SIZE(objectname::regclass)) AS display_bytes FROM (SELECT tablename AS objectname FROM pg_tables WHERE schemaname = :schema) AS objectlist ORDER BY bytes DESC; -- インデックスのサイズ：大きいものは数十 GB 程度 SELECT objectname, PG_RELATION_SIZE(objectname::regclass) AS bytes, PG_SIZE_PRETTY(PG_RELATION_SIZE(objectname::regclass)) AS display_bytes FROM (SELECT indexname AS objectname FROM pg_indexes WHERE schemaname = :schema) AS objectlist ORDER BY bytes DESC;

書き込み遅延の原因調査 Cloud Run - 参照系の API では悪化の傾向は 見られなかった - 更新系の API のレイテンシが悪化した - 一部のテーブルへの書き込みが ボトルネックとなっていた - 非同期処理に関する API に 集中していた 月初の特定期間 月中の同じ期間

書き込み遅延の原因調査 Cloud Run - 参照系の API では悪化の傾向は 見られなかった - 更新系の API のレイテンシが悪化した - 一部のテーブルへの書き込みが ボトルネックとなっていた - 非同期処理に関する API に 集中していた

Cloud SQL for PostgreSQL - ディスク IO に顕著な劣化は見られなかった - インデックスのキャッシュヒット率 が 低くなっていた - キャッシュヒット率が低いテーブルは 書き込み専用のテーブルに集中していた - イベント管理用のテーブルも含まれる 書き込み遅延の原因調査 relname indexrelname ratio イベント発行済テーブル インデックス A 0.6018625336 イベント発行済テーブル インデックス B 0.7935121353 テーブル A インデックス C 0.9233941076 テーブル B インデックス D 0.9144033732 テーブル C インデックス E 0.8842985261 SELECT i.relname, i.indexrelname, (idx_blks_hit - idx_blks_read)::numeric / idx_blks_hit AS ratio FROM pg_statio_user_indexes i WHERE idx_blks_hit != 0 ORDER BY i.relname, i.indexrelname;

- 参照せずに書き込むだけのテーブルに対する インデックスはメモリ上に残りにくく、 キャッシュヒット率が下がりやすい - 主キーがランダムな値であったため INSERT 時の読み込み対象が分散しやすい - 読み込みの負荷が支配的であるため、 書き込みにおけるディスク IO が増加したとしても メトリクスに現れにくい 書き込み遅延の原因調査 INSERT INSERT INSERT

1. テーブルのデータ量を削減する ○ バッチ処理としてシンプルに実現できる ○ 定期的に REINDEX しなければ、インデックスの断片化が進む 2. UUID v7 や ULID などの時系列でソート可能な ID を主キーに使用する ○ UUID v7 の場合、すでに主キーの型に UUID を使用していれば移行コストは低い 3. Partition Table を使って古いデータと新しいデータを分割する ○ 既存テーブルから変換することはできない ○ パーティション追加の運用が発生する 4. リードレプリカを使って読み込みと書き込みをわける ○ アプリケーションの実装変更が必要になる 5. （RDB 以外の分散したキーに強いデータベースを使用する） 対策

- 書き込みのみが発生するテーブルであっても データ量が多くアクセス過多な状況では パフォーマンス問題を起こしうる - 書き込みのみを大量に行うテーブルでは キャッシュのされやすさを考慮する必要がある 結論

Sansan 技術本部 募集ポジション紹介 https://media.sansan-engineering.com/

No content

- PG_RELATION_SIZE - https://www.postgresql.jp/document/16/html/functions-admin.html - pg_statio_user_indexes - https://www.postgresql.jp/document/16/html/monitoring-stats.html - Table Pertitioning - https://www.postgresql.org/docs/17/ddl-partitioning.html - UUID v7 - https://datatracker.ietf.org/doc/rfc9562/ Appendix