ITエンジニアのYukiです。
現在、認定教材「PostgreSQL高度技術者育成テキスト」巻末の模擬問題集を解いてます。
模擬問題に出題された、表・索引サイズの見積の解き方をまとめたいと思います。
模擬問題集ということで、実戦さながら試験時間内に解く形式と思っていたのですが、以下の章末問題が最後にまとまっている形式でした。途中で解いてしまえばよかったです。
- 運用管理（３章）
- 性能監視（４章）
- パフォーマンスチューニング（４章）
- 障害対応（５章）
今回は、運用管理で出題されていた表・索引サイズの見積について解説します。
いきなり解いたら全滅でした。
計算も面倒なので、本番で出題されないとよいのですが・・・
模擬問題では4問も出題されていたので、そうは言ってられないかも。
Photo by AJ Robbie on Unsplash
（参考）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バイト | integer | real | date |
| 8バイト | bigint | double precision | time, 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バイトまで）の保存には、実際の文字列に１バイト加えたサイズが必要です。
characterでは空白埋め込み分もこれに含まれます。 より長い文字列では１バイトではなく４バイトのオーバーヘッドになります。 長い文字列はシステムにより自動的に圧縮されますので、ディスク上の物理的必要容量サイズはより小さくなるかもしれません。
（PostgreSQL 10.5文書 > 第8章 データ型 > 8.3. 文字型 より）
長い文字列は圧縮されるんですね。
表のページレイアウト
表のページレイアウト
表のページレイアウトは上記のようになっています。以下、説明します。
- 通常、ページサイズは8192バイトです。
- ページヘッダが24バイトあります。
- データ位置を指すラインポインタが、タプルごとに４バイトあります。
- 各タプルのデータはページ末尾から格納されます。
- 各タプルのデータのサイズは、23バイトのタプルヘッダと格納データのサイズの和です。
- 計算の際は、タプルヘッダは４バイト単位に切り上げて、24バイトで計算します。
索引のページレイアウト
索引のページレイアウト
索引のページレイアウトは上記のようになっています。表との差分を説明します。
- タプルヘッダは、８バイトです。
- 16バイトのスペシャルスペースという領域があります。
- B-tree索引の見積の際はリーフページだけで計算可能です。
- ルートおよび、インターナルページは、リーフページに比べて少ないので概算の際は無視できます。
計算方法
では、表定義から表のサイズを見積もる方法を説明します。
- １ページに格納できる行数を計算※
- 必要なページ数（切上げ） ＝全行数 ÷ １ページに格納できる行数
- 表サイズ ＝ 必要なページ数 × ページサイズ
※１ページに格納できる行数は以下の式で計算します。
表１ページに格納できる行数（切捨て）＝
{ページサイズ(8192バイト) * (fillfactor / 100)
- ページヘッダ(24バイト)}
/ {ラインポインタ(4バイト) + タプルヘッダ(24バイト) + 各列のデータ型サイズ合計}
索引の見積の場合は、分子からスペシャルスペース分のサイズ(16バイト)も引きます。また、タプルヘッダのサイズが8バイトに変わります。
索引１ページに格納できる行数（切捨て）＝
{ページサイズ(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バイトです。
１ページに格納できる行数を計算します。
１ページに格納できる行数
＝ {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の意味が分かれば、難しくはありません。ただ、各サイズを暗記しておく必要がありました。
試験で筆算するとなると計算が大変ですね。
もし出題されたら落としたくないものです。
コメント