Lock in $30 Savings on PRO—Offer Ends Soon! ⏳

データベース書き込み性能劣化の原因を調査する

SansanTech
October 21, 2024

 データベース書き込み性能劣化の原因を調査する

■ イベント
Kyoto Tech Talk #6
https://sansan.connpass.com/event/329246/

■ 発表者
技術本部 Bill One Engineering Unit Core Business APグループ 向井 悠祐

■ Bill Oneエンジニア 採用情報
https://media.sansan-engineering.com/billone-engineer

■ Sansan Tech Blog
https://buildersbox.corp-sansan.com/

SansanTech

October 21, 2024
Tweet

More Decks by SansanTech

Other Decks in Technology

Transcript

  1. 写真が入ります 向井 悠祐 Sansan 株式会社 技術本部 Bill One Engineering Unit

    Core Business AP グループ 2021 年 Sansan 中途入社。 入社以来、Web アプリケーション開発エンジニアとして Bill One の開発に従事。現在は請求書受領領域の開発に 加えて、同領域における技術マネジメントに携わる。 前職の SIer では技術検証や開発プロジェクトへの技術 サポート、PoC 開発、社内システム開発などを担当。 1
  2. 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
  3. 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 イベント発行予約 テーブル イベント発行済 テーブル イベント発行後 に移動する トランザクション トランザクション
  4. - データベースは 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;
  5. 書き込み遅延の原因調査 Cloud Run - 参照系の API では悪化の傾向は 見られなかった - 更新系の

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

    API のレイテンシが悪化した - 一部のテーブルへの書き込みが ボトルネックとなっていた - 非同期処理に関する API に 集中していた
  7. 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;
  8. 1. テーブルのデータ量を削減する ◦ バッチ処理としてシンプルに実現できる ◦ 定期的に REINDEX しなければ、インデックスの断片化が進む 2. UUID

    v7 や ULID などの時系列でソート可能な ID を主キーに使用する ◦ UUID v7 の場合、すでに主キーの型に UUID を使用していれば移行コストは低い 3. Partition Table を使って古いデータと新しいデータを分割する ◦ 既存テーブルから変換することはできない ◦ パーティション追加の運用が発生する 4. リードレプリカを使って読み込みと書き込みをわける ◦ アプリケーションの実装変更が必要になる 5. (RDB 以外の分散したキーに強いデータベースを使用する) 対策
  9. - 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