データベースの肥大化を防ぐ戦略：テーブルサイズを最適に保ち、データ膨張を回避する方法

多くのWebアプリケーションは、何らかのデータストア、多くの場合リレーショナルデータベースを使用しています。Webアプリが成功すると、気づかないうちにデータベースへ「データを溜め込む」状態になりがちです。しかし、このデータの溜め込みは、テーブルの行数と保存データサイズの両方における無制限な増大につながります。

ある程度までは問題なく動作しますが、データ肥大化を未然に防ぐこと、あるいは防げない場合でもインフラを事前に計画して成長に適切に対処することが非常に重要です。

本題に入る前に、アプリケーションがどのように肥大化していくのかを見てみましょう。

データは多ければ良いというものではない

私たちが手がけるアプリケーションのほとんどは、時間とともに規模が大きくなる傾向があります。

クラウドプロバイダーでデータベースを運用している場合、割り当てられたストレージ上限に達することがあります。その場合、別のインスタンスタイプへのアップグレードが必要になります。たとえばHeroku PostgreSQLでは、hobbyプランのインスタンスは1GBまでしかデータを保存できません。

さらに、データ量の増加はクエリ速度にも影響します。インデックスなしで実行できていたクエリも、テーブルが大きくなると実行できなくなります。行範囲スキャンは遅くなり、UPDATEやDELETE操作にはより多くのロックが必要になります。

データベーステーブルはどうやって大きくなるのか

データの溜め込みは徐々に進行します。今日は問題でないことが、1ヶ月後、6ヶ月後には簡単に問題になり得ます。データ溜め込みの最も厄介な点は、非常に見落としやすいことです。典型的なシナリオをいくつか挙げてみます。

• コンプライアンス対応のためpaper_trailのようなgemを導入し、audit_log_entriesテーブルができる。アプリ内の重要な操作のたびに行が作られ、監査ログは永遠にアーカイブされない。
• アップロードを受け付けており、ActiveStorageを使用している。アップロードファイルを削除しないため、activestorage_blobsテーブルが際限なく大きくなる。
• Web公開用の共有CMSを運用しており、記事セグメントをデータベースに保存している。プラットフォームは成功したものの、著者の多くが書籍並みの長文を書くため、pagesテーブルは数千ページ程度しかないのに非常に大きなサイズになる。
• ユーザー投稿コンテンツを許可しているが、コンプライアンス上の理由でデータを物理削除せず、paranoiaなどでフラグによる論理削除を行っている。user_itemsテーブルは無限に成長し、知らぬ間に1,000万行を突破する。

これらのパターンは、早期に発見しないと深刻な影響をもたらします。テーブルサイズを常に把握していれば、アップグレード時期を予測し、ユーザー影響の少ないオフピーク時間帯にメンテナンスを計画できます。

将来予測も格段に容易になります。例えば:

• 現在eventsテーブルはメモリに収まっている。現在の成長率なら7ヶ月後に収まりきらなくなる。
• RDSインスタンスタイプのストレージ使用率は30%。来年1月には90%に達する。
• paymentsテーブルに対して全行をスキャンする集計クエリがある。paymentsテーブルは3週間で200万行、来年1月には2,000万行を超える見込みだ。

これらはいずれもインシデントや障害として顕在化する可能性があります。しかし早期に対処すれば、比較的簡単に緩和できます。例えば:

• eventsテーブルの30日より古いデータに対してアーカイブを設定する。
• 4ヶ月以内にRDSインスタンスをアップグレードする。
• 全表スキャンを抑止するため、クエリに追加のWHERE条件を加えて、はるかに小さい行サブセットに対してのみ関数を計算するようにする。

データベースの成長を可視化する

データベースの成長を監視するには、2つの方法があります。

• 専用ツール(MySQL向けstatsなど)をインストールし、PrometheusやTelegraphなどのツール経由でメトリクス収集エンジンに接続する。
• AppSignalを使う。特に、アプリケーションですでに利用しているのであれば効果的です。

AppSignalは複数種類のメトリクスを保存でき、そのひとつがgaugeです。gaugeは環境ごと(production、staging、developmentなど)の時系列データで、適宜更新できます。AppSignalのメトリクスはタグもサポートしているため、データベーステーブルごとにタグ付きgaugeメトリクスを自動生成できます。次のように定義しましょう:

• db.row_count:テーブルごとの概算行数(近似値については後述)
• db.data_size_bytes:テーブルが使用しているバイト数
• db.index_size_bytes:テーブルのインデックスが使用しているバイト数

メトリクス名に値の型をサフィックスとして付けている点に注目してください。これは後でメトリクスの表示方法を定義する際に役立ちます。「data」と「indices」のメトリクスを分けていることも重要です。行数が多くインデックスを数個以上持つテーブルでは、インデックスサイズが保存データ本体の2〜3倍になることもあります(各インデックスは独自の派生データを保持するため、ストレージオーバーヘッドが発生するからです)。

高速にテーブル行数をカウントする

行数の「概算」という部分が重要です。正確な行数が必要であれば、SELECT COUNT(1) FROM my_tableのようなクエリを実行できますが、思ったほど速くはないかもしれません。

COUNTを実行すると、データベースはクエリ実行中にテーブルの行数が変化しないことを保証する必要があり、そのためにテーブルをロックするか、クエリ実行中に独立したトランザクションを作成します。テーブルが大きいほどクエリは遅くなり、スキャンされる行も増え、ロックも蓄積していきます。

そのため、「それなりに近い」行数の近似値だけで十分な場合(パフォーマンス見積もりには前後1〜3万行程度の誤差で十分です)、データベースエンジンの内部統計情報を使ってこの種のデータを取得できます。

ほとんどのデータベースでは、行は「ページ」単位で書き出されるため、テーブルデータへの最適化されたアクセス手段を持っています。データベースエンジンは、おおよそ挿入順に、どの行がどのページに割り当てられているかを追跡しています:

• 行1〜100はページ1
• 行101〜200はページ2
• 行201〜300はページ3

以降も同様です。

エンジンはテーブルごとのページ数とページあたりのおおよその行数を把握しているため、割り当て済みページ数をカウントし、それにページサイズ(ページあたり行数)を掛けることで推定値を算出できます。これにはいくつかの利点があります。カウントが非常に高速であること、そしてクエリ実行中にテーブルをロックする必要がないことです。

MySQLでテーブルサイズを記録する

必要な作業はデータベースごとに異なります。テーブル統計を取得するために、データベースエンジン自体へ問い合わせる必要があるためです。まずはMySQLから始めましょう。実行すべきクエリは以下の通りです:

次に、出力からいくつかの列を取り出します。注目すべきはData_length、Index_length、Rowsです。テーブルサイズはData_length + Index_lengthの合計として定義され、ページベースの概算行数はRowsに入っています。

これを、データベース全体のデータを収集するコードブロックにまとめましょう。ActiveModelクラスを扱わないため、ActiveRecordが直接提供するクエリメソッドを使用します:

PostgreSQLでテーブルサイズを記録する

PostgreSQLにはSHOW TABLE STATUSのようなショートカットクエリがないため、より複雑なクエリが必要です。PostgreSQLの内部テーブルに問い合わせます:

なお、このクエリはpublicスキーマ(通常使用するデフォルトのスキーマ)のみを対象としています。他のスキーマも含めたい場合は、WHERE t.table_schema = 'public'条件を削除し、table_info.fetch('name')をtable_info.fetch('full_table_name')に置き換えてください。

メトリクスを定期的に更新する

このコードブロックは定期的に実行する必要があります。Sidekiqスケジューラや、cronから実行されるRakeタスクに組み込むのが良いでしょう。たとえばgood_jobを使用している場合は、cronセクションに次のように追加できます:

ダッシュボードを作成する

データが入り始めたら、ダッシュボードを作りましょう。以下のダッシュボードをコピーして、用途に合わせて活用できます:

「Add dashboard」をクリックし、表示されるモーダルダイアログで「Import dashboard」を選択します。このダッシュボードから、次のようなグラフが得られます:

さらに、テーブルサイズのグラフはこちらです:

ワイルドカードタグを活用することで、データベース内のすべてのテーブルのグラフを自動的に構築している点に注目してください。

データの解釈方法

データを分析するときは、行数またはサイズにおける指数的・線形的な成長、つまりどんどん大きくなり続けるテーブルに注意してください。

これが見つかった場合、選択肢がいくつかあります。ひとつは、この無制限な成長に備えたアーキテクチャ設計です。いつアップグレードすべきか、次善のサイズへアップグレード可能かをあらかじめ把握しておきます。もうひとつは、定期的な削除タスクの設定です。元同僚のWander Hillenがこのトピックについて優れた記事を執筆しています。

たとえば先ほどのスクリーンショットでは、一部のテーブルが定期的に縮小しています。これは定期的なクリーンアップタスクが実行されるタイミングです。行の流入はほぼ一定である一方で、データはあるポイントまで蓄積した後、サイズと行数が減少しているのがわかります。

こうした減少が存在し、テーブル内のデータ量が一定のペースで増加している限り、データベースが突然上限に達して障害を引き起こすことはありません。

まとめ:データベーステーブルによるデータ肥大化を避ける

本記事では、データベースの成長を可視化し、データ肥大化を抑制する方法について解説しました。

データ肥大化は、市場で成功するアプリケーションにとって現実的なリスクです。データベーステーブルのメトリクスを設定することで、いつデータベースを垂直スケールすべきか、古いデータの定期クリーンアップが必要かどうかをより的確に予測できるようになります。タグ付きAppSignal gaugeと少しのSQLを組み合わせれば、このデータを便利で扱いやすい形式で取得できます。

データベースに不意打ちされないよう、今すぐ対策を始めましょう!

Julik Tarkhanov

ゲスト著者のJulik Tarkhanovは、Cheddar Paymentsのスタッフソフトウェアエンジニアであり、複数のRubyオープンソースライブラリの作者でもあります。

Julik Tarkhanovのすべての記事を読む