PostgreSQLの1日あたりのストレージ使用量を計算し、容量上限の時期を試算する
はじめに
以下の背景から調べました。
クラウド環境でアプリケーションを構築し、運用・保守フェーズに入った時に現在の契約プランでのストレージ量で後何年くらいはデータをそのまま保持できそうかとお問合せをいただいた。
この時の前提として、データの登録サイクルは不規則でなく一定の間隔でデータが送信されてくる DB テーブルだったので 1 日あたりに登録されるレコード数は平均的だった。
こんな感じのシチュエーションでした。
限定的なお話で参考にする人いるのかわからんですが、何かの役に立ったらいいなということで記事にしていきます。
この記事でわかること
この記事で取り扱わないこと
試算手順
- 対象のテーブルの 1 レコードにどのくらいの容量が使用されているのかを調べる。
- 1 日にどのくらいのレコードが登録されるのか調べる
- 1 年間での使用量を計算する
- 1 年間の使用量を元にストレージ容量の上限値に達するのが何年後かを試算する
対象のテーブルの 1 レコードにどのくらいの容量が使用されているのかを調べる。
使用量は pg_class で情報となるデータを取得できます。
今回は使うのは relpages と reltuples です。
それぞれの保持する情報は以下です。
-
relpages- ブロック数
-
reltuples- 行数
relpages は単位が 8KB ごとらしいので計算しやすいよう 8192 をかけて byte に変換します。
それを行数の reltuples で割れば 1 レコードあたりの平均使用量は算出できます。
以上を踏まえて以下の SQL を発行します
DATABASE=> SELECT relname, reltuples, (relpages / 128) as mbytes, (relpages * 8192.0 / (reltuples + 1e-10)) as average_row_size FROM pg_class ORDER BY mbytes DESC;
# 見やすいよう整形
SELECT
relname,
reltuples,
(relpages / 128) as mbytes,
(relpages * 8192.0 / (reltuples + 1e-10)) as average_row_size
FROM
pg_class
ORDER BY
mbytes DESC;
実行結果は以下のようになります。
| relname | reltuples | mbytes | average_row_size |
|---|---|---|---|
| table_a | 330420 | 45 | 145.037225349555 |
| table_b | 330420 | 34 | 108.51759578718 |
テーブル(relname)ごとにレコード数(reltuples)、MB 単位の占有サイズ(mbytes)、1 レコードの平均サイズ(average_row_size)で確認できます。
全テーブルに対しての SQL なので特定のテーブルで良いときはテーブル名で絞ってください。
SELECT
relname,
reltuples,
(relpages / 128) as mbytes,
(relpages * 8192.0 / (reltuples + 1e-10)) as average_row_size
FROM
pg_class
WHERE
relname = {table_name}
ORDER BY
mbytes DESC;
1 日に登録されるレコード件数を調べる
テーブルの作成日ベースで範囲を 1 日にし COUNT することで 1 日のレコード数を取得します。
た。
DATABASE=> SELECT COUNT(*) FROM table_name WHERE datetime >= 'YYYY/MM/DD HH:mm:ss' AND datetime <= 'YYYY/MM/DD HH:mm:ss';
# 見やすいよう整形
SELECT
COUNT(*)
FROM
table_name
WHERE
datetime >= 'YYYY/MM/DD HH:mm:ss'
AND
datetime <= 'YYYY/MM/DD HH:mm:ss';
ここで取得した件数とさっき取得した 1 レコードあたりの容量を掛ければ、1 日に使用される容量を算出できます。
計算する
後は算数ですね。
先ほど取得した 1 日に使用される容量に 365 を掛け 1 年間での使用量を取得。
1 年分の使用量と上限を比べれば何年後に要領上限に達するかが計算できます。
さいごに
さいごまで読んでいただきありがとうございます。
SQL ベースでのレコード使用量の取得なので、ローカル、オンプレ、クラウドどの環境でも共通して使えるかと思います。
運用しているとこういったコスト面でのお問い合わせはちょくちょく来たりするのでそんな時に役立ったら嬉しいです。
間違いの指摘やリクエストなどありましたら加筆していきたので是非、ご意見をいただけたらと思います。
それではまた次の記事でお会いしましょう。
Discussion