第5回 PostgreSQL でのデータベース構築の際に必要となる物理設計のポイント

第5回 PostgreSQL でのデータベース構築の際に必要となる物理設計のポイント

NTT オープンソースソフトウェアセンタ
板垣貴裕

データベース・サイジング

サイジングとは、サービスの開始前に、想定される負荷や格納されるデータ量を見積り、十分な性能や規模のサーバおよびストレージを用意することです。 今回は、サイジングの要素のうち、ストレージサイズの計算方法を紹介します。

データファイルの構成

PostgreSQL はデータベース・クラスタと呼ばれるディレクトリの下に、複数のディレクトリやファイルを作成します。 容量の多くを占めるのはアプリケーションが使うテーブルやインデックスになるでしょうが、それ以外にも管理領域やログのためのディスク領域が必要になります。

表1 データファイルの構成と容量
要素 容量 説明
テーブルインデックス (用途依存) データを保持できる容量の他、更新がある場合にはマルチバージョン管理を行なうための領域を加味します。
詳細な計算方法は後述します。
管理情報 10MB 程度 システムカタログ等の管理情報が含まれます。
トランザクションログ(pg_xlog) 100MB ~ 数GB 停電などの異常停止でもコミット結果を保証するためのログです。更新処理が多い場合には増やすとスループットが向上します。
checkpoint_segments 変数で設定します。ログアーカイブが失敗するとファイルサイズが増加するので注意しましょう。
アーカイブログ(archive_command) (用途依存) アーカイブリカバリのためにトランザクションログをアーカイブする場合に必要です。 サイズはバックアップ保存期間中に更新される領域と同じ程度です。
参照主体では容量は小さくて済みますが、更新が多くバックアップ保存期間が長い場合は、データベースサイズ以上のサイズになることもあります。
コミットログ(pg_clog) 50MB ~ 512MB トランザクションがコミットしたかロールバックしたかを管理するログです。
autovacuum_freeze_max_age 変数で設定します。
サーバログ(pg_log) (用途依存) サーバログはエラーや警告が発生した際にメッセージが記録されます。
デフォルトの設定では古いログは削除されません。 繰り返し発生するエラーに気づかずに大量のメッセージが出力されないよう注意しましょう。

テーブルサイズの計算

テーブルサイズを計算する際には、ページと管理ヘッダを加味するとより正確な値が求められます。 まず、行ごとに 28byte の管理ヘッダが付与され、その後ページに収まるよう行データが配置されます。 ページには 24byte のヘッダ領域と、fillfactor (10 〜 100%) で指定する空き領域があることを加味します。

8KB × ceil(行数 / floor(floor(8KB × fillfactor - 24) / (28 + 行データ長)))

インデックスサイズの計算

よく使われる btree インデックスについて説明します。インデックスもテーブルと同様ですが、管理ヘッダは 12byte です。 また、fillfactor のデフォルト値は 90% です。 これ以外にも、木構造である btree の上層ページが必要ですが、影響は小さいので省略します。

8KB × ceil(行数 / floor(floor(8KB × fillfactor - 24) / (12 + キー長)))

計算例

pgbenchベンチマークのaccountsテーブルに対して、この計算方法を適用してみます。pgbench_accountsテーブルは以下のように定義されています。

   Table "public.pgbench_accounts"
  Column  |     Type      | Modifiers
----------+---------------+-----------
 aid      | integer       | not null
 bid      | integer       |
 abalance | integer       |
 filler   | character(84) | (文字列は長さ管理の 1byte を加える)
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

“行データ長” や “キー長” は 8byte 単位で切り上げて計算します。 この場合、行データ長は 104byte、キー長は 8byte になります。 100,000 行の場合に、上記の計算式を当てはめると表2のようになり、おおむね正しい値が得られることがわかります。 更新を行う場合には、計算した推定値にさらに安全率を20%程度加えると良いでしょう。

表2 テーブルとインデックスサイズの推定値と実測値
  名前 推定値 実測値
テーブル pgbench_accounts 13120 KB 13120 KB
インデックス pgbench_accounts_pkey 2184 KB 2208 KB

インデックスの張り方

インデックスは検索処理の高速化に非常に有効ですが、インデックスが多すぎるとディスクやメモリを消費し、更新処理も遅くなります。 検索パターンに応じて使い分け、適切な列に対してインデックスを定義するコツを紹介します。

インデックスの使い分け

PostgreSQL では、btree インデックスの他にも、gist や gin インデックスをサポートしています。 用途の一覧を 表3 に示します。 一般的な用途では btree インデックスが最適だと思いますが、たとえば、gist は地理情報や、開始時間~終了時間の重なりを調べるような用途で役立ちます。 また、全文検索や配列内の要素検索など、1 行から複数のキーが抽出される場合には gin を使います。

表3 インデックスの使い分け
  スカラー値 多次元/範囲
1キー/行 btree gist
多キー/行 gin -

複数列インデックスを使う判断

PostgreSQL は非常に柔軟なインデックスの使い方をします。 かなり多くのクエリパターンで複数列インデックスを使うことができますし、逆に必ずしも複数列インデックスが必要無い場合もあります。

複数列インデックスでは、キーの順番によらずインデックスを使うことができます。以下の例では、全てインデックスが利用可能です。 ただし、最初のキーから順に条件を指定したほうが処理は高速です。 その差は cost の値にも表れています。

=# CREATE INDEX idx_abc ON tbl (a, b, c);
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND b = 2;
  => Index Scan on idx_abc (cost=8.27)
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND c = 3;
  => Index Scan on idx_abc (cost=8.36)
=# EXPLAIN SELECT * FROM tbl WHERE b = 2 AND c = 3;
  => Bitmap Index Scan on idx_abc (cost=14.91)

また、必ずしも複数列インデックスを定義しなくても、各列ごとのインデックスを同時に使うこともできます。 それぞれのインデックスの結果をマージしてビットマップ・スキャン (Bitmap Scan) による処理を行ないます。 以下の例でも、idx_a と idx_b の共通部分 (BitmapAnd) をスキャンしていることがわかります。

=# CREATE INDEX idx_a ON tbl (a);
=# CREATE INDEX idx_b ON tbl (b);
=# EXPLAIN SELECT * FROM tbl WHERE a = 1 AND b = 2;
  => Bitmap Heap Scan (cost=12.92)
       ->  BitmapAnd
             ->  Bitmap Index Scan on idx_a
             ->  Bitmap Index Scan on idx_b

インデックスを多くの検索パターンで使うことができるので、インデックスの数を減らしても検索性能が満足できるかもしれません。インデックス数が多いと更新性能に悪影響があるため、特に更新処理が多い場合には有効なチューニングになります。

なるべくキーの更新を避ける

インデックスのキーは、なるべく変更しないようにしましょう。 PostgreSQL には "HOT" と呼ばれる更新処理の最適化機能があるのですが、インデックスのキーに含まれる列が更新されると、この最適化が行なわれなくなります。 更新性能が低下するため注意しましょう。

また、キーでない列はインデックスに含めてはいけません。 他の DBMS 製品では “covering index”()と呼ばれる最適化手法をサポートしているものがありますが、PostgreSQL ではサポートしていません(v8.4 現在)。 インデックスには必要なキー値のみを含むようにしましょう。

今回は、PostgreSQL の物理設計に関するポイントを解説しました。テーブルやインデックスの定義はデータベースの性能に大きな影響を与えます。スキーマ設計を行なう際の参考にしてください。


※)
検索処理にて取得するフィールドが全てインデックスに含まれている場合、インデックススキャンの際にはテーブルにはアクセスせずに処理を高速化する機能です。

(2009 年 10 月 27 日公開 / gihyo.jp にて)