PostgreSQLパフォーマンスチューニング(負荷分散)

レプリケーションと負荷分散について

PostgreSQLデータをレプリケーション（複製）する場合、マスターサーバ（更新・検索を行う）が一つでスレーブサーバ（検索のみ行う）が複数のマスタースレーブ型と、すべてのサーバで更新・検索が自由なマルチマスター型に分けられます。

それとレプリケーションを更新されたとき同期的に行うか、非同期的に行うかでも分けられます。

同期的に行われる場合、スレーブ側で複製が完了するまでマスター側での更新が完了しません。

なので一つでも遅いサーバがあるとそれがボトルネックとなり全体の処理性能が低下する可能性があります。

非同期的に行われる場合、スレーブ側で複製が完了を待たないのでマスター側での更新が速やかに行われます。

なので処理性能の低下が起き難いのですが、タイミングによってはマスター側のデータとスレーブ側のデータが一致しないことがありえます。

ロードバランサ的にSQLクエリを分けて複数のサーバに振り分ける場合、非同期的だと処理されて返ってきたデータの間で矛盾が生じる危険性があります。

レプリケーションによる書き込みの負荷分散は基本的にありません。

レプリケーションによる検索等読み込みの負荷分散は同期・非同期どちらがよいかは案件によって一概にはいえませんが、

タイムラグがある程度可ならば非同期のほうが速度低下の心配が少ない・SQLクエリの制限が少ないなどのメリットがあります。ただしデータ同期が遅れるということだけは、アプリケーション設計で吸収しないといけません。

同期タイプの場合、速度低下（更新性能は1/2まで落ちる？）、SQLクエリの制限（一部のクエリが使用できない。シーケンスやnow(),random()のような特殊関数の取り扱いに難があり）があります。

バックアップ（可用性）を目的だけならレプリケーションソフトの代わりに

LVS+heartbeat+DRBD+PostgreSQL

ウォームスタンバイ（PostgreSQL8.2からの機能）

などの利用が考えられます。

レプリケーションソフトとして代表的なものには

slony-I（非同期タイプ）、pgpool-II（同期タイプ）、PGCluster（同期タイプ）があります。

slony-Iの特徴

ひとつのマスタと複数のスレーブで構成される、シングルマスタ、マルチスレーブのシステム。スレーブの「カスケード」接続が可能でスケーラビリティがあげやすい。マスタとスレーブの間の回線が切断してもマスタ側への更新を止める必要がなく、後でまとめてスレーブ側に更新を反映することが可能であるというメリットがある。したがって、WAN環境のように、ネットワーク接続が保証されないような環境にも適用することができる。

ある時刻を取るとマスタとスレーブの間では必ずしもデータベースの内容が一致しないことになる。また、データの更新をスレーブに対して行うことはできない。

pgpool-IIの特徴

pgpool-IIはPostgreSQLのデータベースクライアントとPostgreSQLサーバの間に割り込む形で動作します。機能としてデータベースとの接続を保持して性能を向上させるコネクション・プーリング、レプリケーション、負荷分散があります。

PGClusterの特徴

「マルチマスタ」方式のレプリケーション・システムで複数のデータベースクラスタがそれぞれ同列にマスタとなり、お互いの更新データを複製する。pgpoolのソースにロードバランサ、複数のサーバのデータの同期機能、サーバの状態監視、障害復旧機能などを追加したものらしい。ロードバランサ・クラスタサーバ・レプリケーションサーバの3台以上構成が推奨されている。大規模なシステム向け。

slony-Iとpgpool-IIのの組み合わせが人気あるようです。slony-Iにはロードバランサの機能がないためpgpool-IIのコネクション・プーリング、ロードバランサで補います。

pgpool-IIのロードバランサは、使用するPostgreSQLサーバに対する負荷分散の比率を指定することで検索クエリが割り振られます。

slony-Iと組み合わせるときはpgpool-IIをマスタースレーブモードで動作させる必要があり、このときパラレルクエリ、フェイルオーバは使用できない。

slony-Iのレプリケーションは非同期なのでスレーブサーバに振り分けたSQLの結果はマスターサーバへのSQLの結果と異なる瞬間がありうることは考慮しないといけない。それがいやなら同期レプリケーションする。

ロードバランシングできるのは以下の条件のすべてを満たした時SELECTなどの問い合わせです。

* PostgreSQLのバージョンが7.4以降である

*問い合わせが明示的なトランクザションブロックの内側にない(つまり、BEGINを発行していない)

* SELECT文(ただし、SELECT nextvalとSELECT setvalの場合は除く)またはCOPY TO STDOUT, DECLARE..SELECT, FETCH, CLOSEのいずれかである

* SELECT INTO文ではない

* SELECT FOR UPDATE/SELECT FOR SHARE文ではない

*クエリ文字列がSELECTで始まる(空白等別の文字で始まると無視します)

以下の理由からデータベース・テーブルサイズを小さくすることがパフォーマンスアップにつながります。

サイズを小さくすることでキャッシュに収まりやすくなる｡

スキャンする範囲が小さくなり検索が速くなる。

インデックス作成のコストが小さくなる。

データベース・テーブルサイズを小さくするだけでも効果がありますが、PostgreSQL8.1から、一つの大きなテーブルをいくつかの小さなテーブルに分割して管理するテーブルパーティショニングと呼ばれる機能が使えるようになってます。

アプリケーションには複数のパーティションを1つのテーブルとして見せることができます。

パーティショニングの利点

*特定の状況において、問い合わせのパフォーマンスが劇的に改善されます。具体的には、アクセスが多いテーブル内の行を単一パーティションまたは一部のパーティション内に収めることです。パーティショニングはインデックスの先頭の列を置き換え、インデックスサイズが減少します。このため、インデックス内のよく使用される部分がメモリ内により収まりやすくなります。

*問い合わせや更新が単一パーティションの大部分にアクセスする場合、インデックススキャンとテーブル全体に渡って散乱したランダムアクセス読み取りの代わりに、そのパーティションのシーケンシャルスキャンとすることができ、性能は改善します。

*その仕様がパーティション設計に合うように計画されていれば、大量のロードや削除が、パーティションの追加や削除によってなされる可能性があります。ALTER TABLEは大量操作よりもずっと高速です。また、大量のDELETEによって発生するVACUUMのオーバーヘッドを完全に防ぎます。

*めったに使用されないデータは、安価で遅い記憶メディアに移行できます。

PostgreSQLでは、継承とCE（Constraint Exclusion）を使用することでテーブルパーティショニングを実現しています。

継承

まず親となるテーブルを定義します。

CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );

次にmeasurementの定義を継承した子テーブルを作成します。

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

注意点

*インデックスや外部キーは子テーブルには継承されません。

PRIMARY KEY NOT NULLのみ継承されるUNIQUE継承されない外部キー継承されないNOT NULL継承されるDEFAULT値継承されるCHECK制約継承される

*親テーブルにserial型を定義して継承しても、子テーブルは親テーブルのシーケンスを使用する。

*親テーブルにVACUUMを実施しても、子テーブルは実施されない。

*親テーブルにカラムを追加すると、子テーブルにも自動で追加される。（デフォルト値も入る）

*親テーブルからカラムを削除すると、子テーブルからも自動で削除される。

*親テーブルを削除すると、子テーブルも自動で削除される。

インデックスは継承されないので子テーブルにそれぞれに設定します。

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

こうして作ったテーブルは、親に対してSELECTすると子テーブルに対しても透過的に行ってくれます。

親テーブルからのみ検索したい場合は、ONLYを用います。

SELECT * FROM ONLY measurement;

CE（Constraint Exclusion）

何も設定しない場合、分割したテーブルをすべて検査しまいます。

Constraint Exclusion制約による除外を有効にするとWHERE条件をつけることによって必要なテーブルにのみアクセスすることが可能になりました。

measurementの定義を継承した子テーブルをCHECK制約（検査制約）を指定して定義しなおします。

CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement);

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); ... CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

次のSQLをEXPLAINをつけて解析すると（実行前にSET constraint_exclusion TO on;とするかpostgresql.confでconstraint_exclustion=onに設定しておく）

EXPLAIN SELECT * FROM measurement WHERE logdate >= DATE '2006-02-02' AND logdate < DATE '2006-02-28';

Result (cost=0.00..46.52 rows=16 width=16) -> Append (cost=0.00..46.52 rows=16 width=16) -> Seq Scan on measurement (cost=0.00..34.45 rows=8 width=16) Filter: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2006m02 measurement (cost=2.05..12.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2006m02_logdate (cost=0.00..2.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date))

measurement_y2006m02とmeasurementしかスキャンされないことがわかります（親は必ずスキャンされる）。

制約による除外を無効にした場合は次のようになります。

Result (cost=0.00..90.79 rows=48 width=16) -> Append (cost=0.00..90.79 rows=48 width=16) -> Seq Scan on measurement (cost=0.00..34.45 rows=8 width=16) Filter: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2006m02 measurement (cost=2.05..12.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2006m02_logdate (cost=0.00..2.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2006m03 measurement (cost=1.05..11.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2006m03_logdate (cost=0.00..1.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2007m11 measurement (cost=1.05..11.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2007m11_logdate (cost=0.00..1.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2007m12 measurement (cost=1.05..11.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2007m12_logdate (cost=0.00..1.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Heap Scan on measurement_y2008m01 measurement (cost=1.05..11.07 rows=8 width=16) Recheck Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date)) -> Bitmap Index Scan on measurement_y2008m01_logdate (cost=0.00..1.05 rows=8 width=0) Index Cond: ((logdate >= '2006-02-02'::date) AND (logdate < '2006-02-28'::date))

すべての子テーブルがスキャンされてしまいます｡

SELECT文は範囲の絞込みが自動で行われますが、UPDATE/DELETE文に関してはバージョン8.2以降が必要になります。

INSERT文の振り分けはまだ自動ではできません。親テーブルに対するINSERTは親テーブルへ、子テーブルに対するINSERTは子テーブルへそのままデータが挿入されます。

SELECT/UPDATE/DELETE文と同様にINSERT文も透過的に扱いたい場合、「ルール」か「トリガ」を作成します。

CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN INSERT INTO measurement_y2008m01 VALUES (NEW.*); RETURN NULL; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER insert_measurement_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

パーティショニングの欠点

分割したテーブルのCHECK制約が順にテストされるので分割数は最大100程度に抑えておくパーティションをまたがるユニーク制約を定義できない

親テーブルへのINSERTをトリガで子テーブルへ振り分ける処理がオーバーヘッドになる

SELECT FOR SHARE/UPDATEができない

INSERT結果行数が0になる

CURRENT_TIMESTAMPと相性が悪い

GROUP BY, ORDER BY,結合, min/maxなどのクエリでインデックスが使われなかったり、データを含まないことが自明であるパーティションであってもスキャンされてしまう場合があります。

Prepared Statementと相性が悪い

絞込みを有効にするにはパーティショニングしたテーブルに対するプレースホルダの使用をしないように設定する必要がある。

Perlの場合$dbh->{pg_server_prepare} = 0としてserver-side prepareを無効にできる。

パーティションは売上の今年度分と前年度分など同時使うことはほとんど無いデータなどに有効です。

使用頻度の多いテーブルを高速なHDDに、使用頻度の少ないテーブルを安くて遅いHDDに割り当てるなどHDDを有効活用できます。

パーティションは親テーブルから透過的に子テーブルにアクセスできますが、完全ではないのでアプリケーション側の設計で変更しなければならない可能性があります。

細かい積み重ねでパフォーマンスアップ

データベースをWEBサービス毎に別々のサーバに分ける。

要求されるWEBサービスごとに独立させ使用するデータベースを切り分けて負荷分散する。

WEBサービスをXML-RPCなどAPI化して直接データベースをアクセスせずにデータの交換ができるようにする。

特にアクセス頻度の高いデータ（ユーザー管理情報など）をmemcached、Memcachedb、Tokyo Tyrantなどのキャッシュサーバを使ってデータベースへのアクセス自体を減らす。

結論としてはどの方法を採用しても、その負荷分散方法に合わせた（配慮した）アプリケーション側の設計・コードの改良が必要になってくるということです。