TL;DR
- 参照・更新の責務が巨大かつ主要なテーブルに集中したことでスケーラビリティに懸念が生じた
- 参照・更新をテーブルレベルで分離することで責務に合わせた性能の最適化が可能になる
- 参照系の改善からスタートすることで将来的な更新系の改善を実施しやすくなる
- 参照系を改善することで、性能改善目的で作成された複雑なストアドプロシージャ/ファンクションを廃止しやすくなる
- ストアド機能を廃止することで参照/更新の両方でスケールするNewSQLへ移行できる選択肢が取れる
はじめに
Wallet Stationのバックエンドエンジニアの荒川です。
今回は、「参照/更新系が1つのテーブルに集中したことで発生した諸問題」を「参照テーブル導入」というアプローチで改善している事についてお伝えしたいと思います。
「参照系と更新系を分離する事」は昨今のデータアーキテクチャ設計の基本的な手法ではありますが、これに違反することで「スケーラビリティ」や「テスタビリティ」に関する様々な問題に発展し得るという事についても併せてお伝えできればと思います。
背景
Wallet Stationとは何か？
本題に入る前に、Wallet Stationというプロダクトについて軽く説明します。
Wallet Station | スマホ決済プラットフォーム
Wallet Stationは「プリペイド決済機能を含んだオリジナルPayを簡単に自社アプリに搭載できるサービス」です。
一からプリペイド決済機能を作成して自社アプリに搭載しようと思うと、法規制やセキュリティへの対応が難しく、スピーディーに決済機能をリリースすることはかなり難しいです。
そんな時に、Wallet Stationを利用することで簡単にプリペイド決済機能を様々なプロダクトに埋め込めるのがこのプロダクトの大きな強みになります。
提供しているAPI
このプロダクトは以下の通り様々なAPIを内包しており、中でもプリペイドカードへのチャージ/決済時に利用する「取引系API」はプロダクトの根幹を成す最重要の機能になっています。
参照/更新系APIの両方から利用される責務が広い「取引テーブル」
このプロダクトには、先述のAPIからよく参照/更新される「取引テーブル」という「チャージ/決済などの取引の実行履歴を管理」しているテーブルがあります。
このテーブルは、「取引API」では更新され、「履歴参照系API」からは参照されるようになっていることから、更新と参照の2つの責務を持ってしまっています。
また参照の責務が存在することで、総カラム数は70カラム程度に膨れ上がっており、さらにインデックスが14~15個も作成されている状況です。
また具体的な行数は控えますが、テーブル行数もプロダクト内ではかなり多い部類になっています。
さらに、これだけのカラム数を持っていても参照時に不足しているカラムが存在しており、この巨大な取引テーブルを別テーブルと結合しつつ集計するような重い処理も走るようになっています。
問題「参照/更新の両方の責務を持った事で様々な問題が発生」
サービスリリース初期は問題になっていなかった取引テーブルですが、運用年数が経過することで「スケーラビリティの低下」「仕様の複雑化」「テスタビリティの低下」など様々な問題が顕在化してきました。
問題1「スケーラビリティの低下」
サービスの運用年数が増えるに従い、ユーザ数/取引も堅調に伸びることで、「取引テーブル」に対する検索を高速化するために、14~15個にも及ぶ様々なインデックスが貼られていきました。
それによって、参照系の処理は高速化されたものの、更新系の処理ではインデックス更新のオーバーヘッドが無視できないほどに大きくなり、性能試験で更新クエリの処理速度が低下したり、DBのCPU利用率が徐々に上昇するようになりました。
※ DBのスロークエリの上位10位はほぼ取引テーブルに関するものになっていました。
問題2「仕様の複雑化による品質低下」
またカラム数が70列程度になったことで、「あるレコードのパターンではNOT NULLであるが、あるパターンではNULLになる」ようなカラムが大量発生するようになりました。
それにより、プロダクトに詳しい一部の人間を除いて、最重要である取引テーブルの仕様を理解することは困難になりました。
これに伴い、複雑化した取引テーブルを利用する種々の機能でバグが多く発生する状況になりました。
問題3「ストアドプロシージャの増加によるテスタビリティの低下」
さらに、アクセス速度が低速になった取引テーブルに対して、集計などの複雑かつ高負荷な処理を高速に行うために「ストアドプロシージャ」「ストアドファンクション」などのRDBMSの機能を利用した処理が増加していきました。
それにより、以下のような問題が発生しました。
また副次的な問題として、ストアド機能をサポートしていないスケーラブルなRDB(NewSQL系の製品)に移行することが困難になったという点も挙げられます。
段階的なデータアーキテクチャの見直し
先述以外の問題も抱えている取引テーブルですが、プロダクトの中で最重要のテーブルで様々な機能と密結合になっており、直ぐに改善することが難しいので、以下のように段階を分けて改善を行うように計画しました。
- 【仕様の複雑化の解消】取引参照用のテーブルを作成し、参照系の機能は参照テーブルを利用するように改修 (☆ 本記事のメインテーマ)
- 【更新系の処理速度の改善 その1】参照が減った取引テーブルから不要なインデックスを削除
- 【更新系の処理速度の改善 その2】取引テーブルのレコード削除&アーカイブ
なお、第2段階以降は他の解決策もあります。
具体的には、第2段階の時点で、「更新の責務に特化した新取引テーブルを作成し、そちらにマイグレーションする」という方法もあり、こちらの手法を取ることによって「更新系の処理の性能改善」「更新系テーブルに関する複雑さの解消」などの効果も見込めます。
ただ、今回のケースでは、参照系の分離から始めているため、検討を進める中で柔軟に最適な選択肢が取れたら良いと考えています。
参照テーブルを活用した参照系の分離
今回の記事では、まずアーキテクチャの見直しの第一段階に相当する「参照テーブルを活用した参照系の分離」について書きたいと思います。
と言っても読んで字の如くで、基本的にやることは「取引テーブルの参照用テーブルを作成して、履歴系のAPIから参照用テーブルを利用する」ように改修するだけになります。
ただ様々な機能が依存しているため、このタスクについてもデグレが出ないように段階的なアプローチを取るようにしました。
1. 参照テーブル設計のための仕様調査
まずは参照テーブルを設計するために必要なカラムを調査しました。
具体的には、取引テーブルを参照している機能を全て調査し、どの機能がどのカラムを参照しているかをスプレッドシートに整理しました。
また参照テーブルになるので、取引テーブルにアクセスする際にセットでアクセスされるテーブルのカラムや集計値についても調査した上で、関係者各所にレビューをもらいつつ仕様調査を進めました。
2. 日次更新で参照テーブルを作成し、一部の機能で実験的に利用する
これだけ丁寧に仕様調査したとはいえ、「参照テーブルに必要なカラムを最初から全て網羅的に把握することは恐らく難しい」「参照テーブル作成時のオーバーヘッドがどれくらいか分からない」という状況でした。
そのため、まずは「参照テーブルを日次ジョブで作成して、一部の機能で部分的に利用する」アプローチを取り、徐々に仕様理解を深めて参照テーブルを段階的にブラッシュアップするようにしました。
結果として、一部機能で利用することで「不足しているカラムを早期発見」したり、タスクを絞ったことで「テーブルパーティションの効果検証をした上で参照テーブルに導入」してスケーラビリティを確保したり、「性能試験で最適なクエリを検討」したりといったことに注力することができました。
また、このジョブの最後で「取引テーブルと参照テーブルの整合性チェック」を入れることで、早期段階でテーブル間の不整合を検出し、直ぐにリカバリできるようにもしました。
3. 取引API実行時にリアルタイム更新で参照テーブル作成を行うようにする
次に、取引API実行時に非同期で参照テーブル作成を行うように改修を入れて、ここで性能試験を改めて行いました。
前段で、性能試験を実施してクエリのチューニングをある程度行なっていたため、DBのCPUリソースが大幅に上昇することはありませんでした。
4. 過去データ移行ツールの作成
次に、参照テーブルを導入するにあたり、取引テーブルの過去データを参照テーブルに反映する必要があるので、2で作成した日次更新ジョブを期間指定で実行できるように修正し、過去データ移行ができるようにしました。
こちらを行うことで「過去の取引テーブルにアクセスする機能からも参照テーブルを利用できる」ようになったり、参照テーブルに「カラム追加した際に過去データを全体に反映させる」ことができるようになります。
5. 参照テーブルを利用する機能の拡大
最後に、参照テーブルを利用する機能を全体に拡大することを予定しています。
具体的には、参照系の機能でも相対的に重要度の高い機能をここで移行していこうと考えています。
また、ここでストアド系の機能を廃止してテスタビリティ、DB移行の容易性を高める予定です。
Wallet Stationはプロダクトの性質上リリース頻度が高いとは言い難い状況にあるので、フィーチャーフラグ等も利用して切り戻しが簡単にできるように安全に移行を進められるようにしようかなと思っています。
課題点「過去データの移行、元テーブルとの整合性の担保、不足しているカラムの発覚」
さらっと参照テーブルを導入する方法について書きましたが、いくつか難しかったポイントがありましたので、解決策とセットでまとめておきたいと思います。 他にも色々な方法があると思いますが、参考にしていただけると幸いです。
- 元テーブルとの整合性が担保されているか不安 -> 日次のジョブで整合性チェックとリカバリを行うようにする
- 取引テーブルの過去データを参照テーブルに移行しなければならない -> 日次でN日単位でデータ移行するジョブで対応する
- 参照テーブルのカラム設計を見直したい -> 過去データ移行するジョブで定期的にクローリングして修正を全体に反映させる
展望「インデックスの見直しによる更新系の性能改善、品質改善・更新アーキテクチャの見直し」
今後は参照テーブル移行を進めるのと共に、インデックスの見直しや更新アーキテクチャ全体の見直しや、DB移行なども進めていければと思います。
おわりに
今回紹介したように、まずは「参照系の分離」という取り組みから始めるだけでも、大規模な改修をいきなり行うよりリスクが低く、スケーラビリティや品質の改善に繋がるということを紹介しました。
具体的には、参照テーブルの導入・活用を進めることで、不要なストアド機能や大量のインデックスに頼る必要がなくなるなど、ビジネスロジックの整理やテスタビリティ向上にも大きく寄与すると思います。
もっとも、本記事で触れた課題の解決はあくまでスタートラインにすぎません。
現時点での参照テーブル分離が十分に機能し始めたら、次はいよいよ「インデックスの見直し」や「更新アーキテクチャの再検討」「将来的なDB移行に向けた検討」に手を付ける予定です。
必要に応じて手法は変化させながら、継続的にシステムを最適化していければと思います。
Wallet Stationでは、これらの改善活動を通して性能面だけでなく、開発者の体験向上や、新たな機能追加のしやすさにもフォーカスしていきたいと思います。
引き続き、Wallet Stationの取り組みにご期待いただけると幸いです。
また、今後もデータアーキテクチャに関する学びや実践例を共有していきますので、どうぞよろしくお願いいたします。
補足1「リードレプリカを利用すれば参照系の処理は速くなるのでは？」
ここまで記事を読んでいた中で、「DBの参照/更新の負荷を分散して全体のスケーラビリティを上げたいなら、まずリードレプリカを使ってみてはどうか？」と思った方もいらっしゃると思います。
全くその通りだと思います。
ただWallet Stationで利用しているRDS(SQL Server)は他のOSSのRDBMSに比べると費用が高額であるため、安易にリードレプリカを利用することが難しいという事情があります。
なので、まずはテーブル設計を見直すという方法をとっていますが、費用が問題にならないのであれば、リードレプリカを使うのも一つの方法としてありかなと思います。
補足2「RDBで参照テーブルを作成するのではなく参照に特化したNoSQLを使えば良いのでは？」
参照を高速化する用途でドキュメントDB等のNoSQLを利用することは処理性能の観点からは一般的な選択肢だと思います。
ただ、今回は以下の観点からまずはRDB内に参照テーブルを作成するところからスモールスタートをすることにしました。
- 【コスト】Wallet Stationは現状テナント毎にDBを分けているため、NoSQL導入時にコスト面でのオーバーヘッドが大きい
- 【アーキテクチャの複雑度】仮にNewSQLへ移行する場合、NewSQL + ドキュメントDBを併用することになりアーキテクチャ的な複雑度が上がる懸念がある(NewSQL製品の中にはKeyValueストアを内包しているものもあるので、そちらで対応した方がアーキテクチャや運用はシンプルになる)
- 【データ量】Wallet Stationはテナント毎にDBが分かれているので、データ量的にパーティション化したRDBのテーブルで十分対応できそうだった。
また、仮にドキュメントDBを利用したくなった場合でもすぐに対応できるようにリポジトリ層を設けてデータ作成部分を抽象化しているので、状況に応じて反復的に設計をブラッシュアップしていきたいと考えています。
