第5回では、PostgreSQL でのデータベース構築の際に必要となる物理設計のポイントとして、データ容量の計算方法とインデックスの張り方を解説していきます。
データベース・サイジング
サイジングとは、サービスの開始前に、想定される負荷や格納されるデータ量を見積り、十分な性能や規模のサーバおよびストレージを用意することです。今回は、サイジングの要素のうち、ストレージサイズの計算方法を紹介します。
データファイルの構成
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%) で指定する空き領域があることを加味します。
インデックスサイズの計算
よく使われるbtreeインデックスについて説明します。インデックスもテーブルと同様ですが、管理ヘッダは12byteです。また、fillfactorのデフォルト値は90%です。これ以外にも、木構造であるbtreeの上層ページが必要ですが、影響は小さいので省略します。
計算例
pgbenchベンチマークのaccountsテーブルに対して、この計算方法を適用してみます。pgbench_accountsテーブルは以下のように定義されています。
“行データ長”や“キー長”は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の値にも表れています。
また、必ずしも複数列インデックスを定義しなくても、各列ごとのインデックスを同時に使うこともできます。それぞれのインデックスの結果をマージしてビットマップ・スキャン (Bitmap Scan) による処理を行います。以下の例でも、idx_a と idx_b の共通部分 (BitmapAnd) をスキャンしていることがわかります。
インデックスを多くの検索パターンで使うことができるので、インデックスの数を減らしても検索性能が満足できるかもしれません。インデックス数が多いと更新性能に悪影響があるため、特に更新処理が多い場合には有効なチューニングになります。
なるべくキーの更新を避ける
インデックスのキーは、なるべく変更しないようにしましょう。PostgreSQL には "HOT" と呼ばれる更新処理の最適化機能があるのですが、インデックスのキーに含まれる列が更新されると、この最適化が行われなくなります。更新性能が低下するため注意しましょう。
また、キーでない列はインデックスに含めてはいけません。他のDBMS製品では“covering index”(※)と呼ばれる最適化手法をサポートしているものがありますが、PostgreSQL ではサポートしていません (v8.4 現在)。インデックスには必要なキー値のみを含むようにしましょう。
今回は、PostgreSQL の物理設計に関するポイントを解説しました。テーブルやインデックスの定義はデータベースの性能に大きな影響を与えます。スキーマ設計を行う際の参考にしてください。