100日でPostgreSQLをマスター(97日目)~表・索引サイズの見積~

PostgreSQL
※アフィリエイト広告を利用しています

ITエンジニアのYukiです。

現在、認定教材「PostgreSQL高度技術者育成テキスト」巻末の模擬問題集を解いてます。

模擬問題に出題された、表・索引サイズの見積の解き方をまとめたいと思います。

模擬問題集ということで、実戦さながら試験時間内に解く形式と思っていたのですが、以下の章末問題が最後にまとまっている形式でした。途中で解いてしまえばよかったです。

  • 運用管理(3章)
  • 性能監視(4章)
  • パフォーマンスチューニング(4章)
  • 障害対応(5章)

今回は、運用管理で出題されていた表・索引サイズの見積について解説します。

いきなり解いたら全滅でした。

計算も面倒なので、本番で出題されないとよいのですが・・・

模擬問題では4問も出題されていたので、そうは言ってられないかも。

Photo by AJ Robbie on Unsplash

2023/9/29追記
容量見積もり関連の問題は、OSS-DB Gold V3.0で試験範囲から削除されました。
(参考)LPI-Japan公式サイト Ver.2.0とVer.3.0の比較 G 1.1 データベースサーバ構築

Ver. 3.0になって、面倒な計算問題が試験範囲から外れました。

計算方法を暗記しておく必要はなくなりましたが、知識として知っておくと役に立つことがあると思います。

広告

表・索引サイズ見積の問題

サイズの見積問題は以下の形式で出題されます。

  • 表や索引の定義がDDLで示される
  • n件のレコードが登録された場合、サイズは何MBか?
  • また逆に、サイズが XX GBのとき、何万レコード登録可能か?
  • ただし、ページサイズは8192バイト、FILLFACTORはxx%とする。

選択肢から選びますが、模擬問題ではきちんと計算しないと、正しい答えは選べなくなってました。

明らかに桁が違う選択肢ばかりだと簡単なんですが。

見積に必要な知識

見積には、以下を覚えておく必要があります。

  • 各データ型のサイズ
  • ページの内部構造
  • ヘッダなどのサイズ

サイズを見積もるには、まずデータ型のサイズが必要です。模擬問題では各データ型のサイズは示されていませんでした。そうなると受験者は暗記が必要です。

次にページ内のデータの格納方法を知っている必要があります。FILLFACTORについての理解だけでなく、ページ内の構造の知識も求められます。さらに模擬問題では、ヘッダ等のサイズも示されないので、覚えておく必要がありました。

サイズまで暗記というのは厳しいですね。業務では資料を見ながら見積ればすむ話なので、ここまで必要なんでしょうか。

ただ、出題された際に捨てるのはもったいないので、覚えておこうと思います。

各データ型のサイズ

代表的なデータ型のサイズをまとめました。

これを覚える必要がありそうです。

数値型、日付時刻型のサイズ

サイズ整数浮動小数点日付時刻
2バイトsmallint
4バイトintegerrealdate
8バイトbigintdouble precisiontime, timestamp
16バイトinterval

(参考)PostgreSQL 10.5文書 > パート II. SQL言語 > 第8章 データ型

上の表は、サイズを2→4→8→16と倍々にしています。

各種、4バイト、8バイトのデータ型を中心にすると、覚えやすそうです。

文字列型のサイズ

文字列型は格納する文字のバイト長によって変わります。

varchar(n), char(n), textともに、バイト長がNのとき、おおむね以下のサイズになります。

  • [N < 126の場合] N + 1 バイト
  • [N >= 126の場合] N + 4 バイト

マニュアルには以下のように書かれています。

短い文字列(126バイトまで)の保存には、実際の文字列に1バイト加えたサイズが必要です。 characterでは空白埋め込み分もこれに含まれます。 より長い文字列では1バイトではなく4バイトのオーバーヘッドになります。 長い文字列はシステムにより自動的に圧縮されますので、ディスク上の物理的必要容量サイズはより小さくなるかもしれません。

(PostgreSQL 10.5文書 > 第8章 データ型 > 8.3. 文字型 より)

 

長い文字列は圧縮されるんですね。

表のページレイアウト

表のページレイアウト

表のページレイアウトは上記のようになっています。以下、説明します。

  • 通常、ページサイズは8192バイトです。
  • ページヘッダが24バイトあります。
  • データ位置を指すラインポインタが、タプルごとに4バイトあります。
  • 各タプルのデータはページ末尾から格納されます。
  • 各タプルのデータのサイズは、23バイトのタプルヘッダと格納データのサイズの和です。
  • 計算の際は、タプルヘッダは4バイト単位に切り上げて、24バイトで計算します。

索引のページレイアウト

索引のページレイアウト

索引のページレイアウトは上記のようになっています。表との差分を説明します。

  • タプルヘッダは、8バイトです。
  • 16バイトのスペシャルスペースという領域があります。
  • B-tree索引の見積の際はリーフページだけで計算可能です。
  • ルートおよび、インターナルページは、リーフページに比べて少ないので概算の際は無視できます。

計算方法

では、表定義から表のサイズを見積もる方法を説明します。

  1. 1ページに格納できる行数を計算※
  2. 必要なページ数(切上げ) =全行数 ÷ 1ページに格納できる行数
  3. 表サイズ = 必要なページ数 × ページサイズ

※1ページに格納できる行数は以下の式で計算します。

表1ページに格納できる行数(切捨て)=
{ページサイズ(8192バイト) * (fillfactor / 100)
  - ページヘッダ(24バイト)}
  / {ラインポインタ(4バイト) + タプルヘッダ(24バイト) + 各列のデータ型サイズ合計}

索引の見積の場合は、分子からスペシャルスペース分のサイズ(16バイト)も引きます。また、タプルヘッダのサイズが8バイトに変わります。

索引1ページに格納できる行数(切捨て)=
{ページサイズ(8192バイト) * (fillfactor / 100)
 - ページヘッダ(24バイト) - スペシャルスペース(16バイト)}
 / {ラインポインタ(4バイト) + タプルヘッダ(8バイト) + 各列のデータ型サイズ合計}

fillfactorは、ページ内にどの程度データを詰めるかのパラメータですね。

ページのレイアウトが頭に入っていれば、計算できそうです。

 

計算例

以下の例で、表のサイズを計算してみましょう。

  • 列定義(integer, date, integer, timestamp)
  • fillfactor = 90
  • ページサイズ: 8192バイト
  • レコード:30万件

まず、integerとdateは4バイト、timestampは8バイトなので、各列の合計サイズは4+4+4+8で20バイトです。

1ページに格納できる行数を計算します。

1ページに格納できる行数 
= {8192 * (90 / 100) - 24} / {4 + 24 + 20)
= 153.1 
≒ 153 (行)

必要なページ数(切上げ)は

必要なページ数 
= 300,000 ÷ 153
= 1960.7
≒ 1961 (ページ)

よって、表のサイズの見積は

表のサイズ
= 8192 * 1961
= 16,064,512 (バイト)
= 16,064,512 / 1024 / 1024 (MB)
≒ 15 (MB)

以上より、表のサイズは、約15MBと見積れました。

計算して感じたのは、タプルヘッダが意外と大きいですね。

まあ、列数が少ないからなんですが。

この例だと列のデータ(20バイト)より、タプルヘッダ(24バイト)の方が大きかったです。

まとめ

今回は、表と索引のサイズ見積について、説明しました。

各データ型のサイズとページレイアウトの把握が必要でしたね。

計算方法は、ページレイアウトの各サイズやfillfactorの意味が分かれば、難しくはありません。ただ、各サイズを暗記しておく必要がありました。

 

試験で筆算するとなると計算が大変ですね。

もし出題されたら落としたくないものです。

 

<96日目 | 目次 | 163日目>

<<1日目から読む

広告
この記事を書いた人
YukiITEngineer

ITエンジニア。
データベースの仕事が中心です。Oracleメイン。PostgreSQL勉強中。
趣味は読書、筋トレ、ランニング。投資に興味あります。
勉強したことや初心者向け記事を書いてます。

YukiITEngineerをフォローする
PostgreSQL
YukiITEngineerをフォローする
Yuki's ITエンジニア・ブログ

コメント

タイトルとURLをコピーしました