データベースの増大を制御:テーブルのサイズを小さく保ち、データの肥大化を回避する戦略

ほとんどの Web アプリケーションは、何らかの種類のデータ ストア (多くの場合、リレーショナル データベース) を使用します。 Web アプリが成功すると、データベースにデータを「溜め込み」始めるのが非常に簡単になることがあります。しかし、データを溜め込むと、データベース テーブル (行数と保存されるデータ サイズの両方) が際限なく増大することになります。

これはある程度まではうまく機能しますが、データの肥大化を防ぐのに非常に役立ちます。あるいは、それを防ぐことができない場合は、成長を適切に管理するために事前にインフラストラクチャの計画を立てることができます。

本題に入る前に、アプリケーションが肥大化する可能性があることを見てみましょう。

データ量が多いほど良いとは限りません

私たちが取り組んでいるアプリケーションのほとんどは、時間の経過とともに大きくなる傾向があります。

データベースにクラウド プロバイダーを使用している場合は、割り当てられたストレージ制限に達する可能性があります。その場合は、別のインスタンス タイプにアップグレードする必要があります。 Heroku PostgreSQL データベースには制限があります。たとえば、「ホビー」層のインスタンスのデータは 1 GB に制限されます。

より多くのデータがあると、クエリの速度にも影響します。インデックスなしで可能であったことは、テーブルが大きくなると不可能になります。一部の行範囲スキャンは遅くなります。データベース UPDATE を実行するために、さらに多くのロックが取得されます。 と DELETE

データベース テーブルが増加する仕組み

データの蓄積は徐々に起こります。今日は問題にならないことでも、1 か月または半年後には簡単に問題になる可能性があります。データの蓄積で最も危険なのは、それが非常に見落とされやすいことです。非常に古典的なシナリオをいくつか考えてみましょう。

• コンプライアンス上の理由から、paper_trail のような gem を採用します。 audit_log_entries を取得します テーブル。アプリケーションで重要なモードを持つすべての操作は、audit_log_entries に行を作成します。 テーブル。これらの監査ログ エントリは決してアーカイブされません。
• あなたはアップロードを受け入れ、ActiveStorage を使用しています。アップロードを削除することはありません。そのため、activestorage_blobs テーブルはどんどん大きくなっていきます
• あなたは、ウェブ上で公開するために共有 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 の統計など) をインストールし、Prometheus、Telegraph、またはその他のツールを介して指標収集エンジンに接続します。
• 特にアプリケーションですでに AppSignal を使用している場合は、AppSignal を使用してください。

AppSignal は複数のメトリクス タイプを保存でき、サポートするメトリクス タイプの 1 つは gauge と呼ばれます。 。 gauge 環境ごとに単一の時系列です (production など) 、staging 、または development ）随時更新できます。 AppSignal メトリクスではタグも使用できるため、タグ付きの gauge を自動的に作成できます。 データベーステーブルのメトリクス。やってみましょう:

• db.row_count テーブルごとのテーブルのおおよその行数（概算は後ほど説明します）
• db.data_size_bytes テーブルが使用しているバイト数
• db.index_size_bytes テーブルのインデックスが使用しているバイト数

メトリクス名の末尾に値のタイプを付けていることに注意してください。これは、後でメトリクスの表示方法を定義するときに役立ちます。個別の「データ」メトリクスと「インデックス」メトリクスも重要です。テーブルに多くの行と複数のインデックスが含まれている場合、それらのインデックスのサイズは、保存されるデータのサイズの 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 のレコード テーブル サイズ

SHOW TABLE STATUS のようなショートカット クエリがないため、PostgreSQL にはより複雑なクエリが必要です。 。内部 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 表示されるモーダルダイアログで。ダッシュボードには次のようなグラフが表示されます。

そして、この表はグラフのサイズを表します:

ワイルドカード タグを使用して、データベース内のすべてのテーブルのグラフを自動的に作成する方法に注目してください。

データの解釈

データを確認するときは、行数またはサイズの指数関数的または線形的な増加に注意してください。つまり、テーブルがどんどん大きくなっています。

これが表示された場合は、いくつかの選択肢があります。 1 つは、この際限のない成長に備えて設計することです。アップグレードするタイミングと、次に最適なサイズにアップグレードできるかどうかを把握します。もう 1 つは、定期的な削除タスクを設定することです。私の元同僚の Wander Hillen が、このテーマに関する素晴らしい記事を書いています。

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

このような減少があり、テーブル内のデータ量が一定の割合で増加する限り、データベースが突然の制限違反に驚かされることはありません。

要約:データベース テーブルのデータ肥大化を回避する

この投稿では、データベースの増大を可視化し、データの肥大化を抑える方法を検討しました。

データの肥大化は、市場で成功するアプリケーションにとって大きなリスクとなります。データベース テーブルのメトリクスを設定すると、データベースをいつ垂直方向に拡張するか、および定期的に古いデータのクリーンアップをインストールする必要があるかどうかをより適切に予測できます。タグ付きの AppSignal ゲージを少し SQL と組み合わせると、このデータを便利で快適な形式で取得できます。

データベースに驚かないでください。

ジュリク・タルハノフ

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

Julik Tarkhanov によるすべての記事