pgpool-II のチュートリアルにようこそ。 ここでは、pgpool-II のインストールから基本的な設定、レプリケーションおよびパラレルクエリの実行を行うまでの手順について説明します。 また、PostgreSQL の基本的な操作に関する説明は行いませんので、必要であれば PostgreSQL のドキュメントを参照してください。
ここでは、レプリケーションおよびパラレルクエリの実行を行うための準備として、pgpool-II のインストールや設定、データベースノードの準備について説明します。
pgpool-II をインストールするにはソースコードを展開したディレクトリで以下のようにコマンドを実行します。
$ ./configure $ make $ make install
configure スクリプトでは pgpool-II をインストールする環境に合わせた設定が行われます。
また、configure スクリプトを実行する際にコマンドライン引数を指定することにより、pgpool-II のインストール先の変更などを行うことができます。
コマンドライン引数を指定しなければ、pgpool-II は /usr/local ディレクトリ以下にインストールされます。
make コマンドを実行すると pgpool-II のソースコードがコンパイルされ、make install コマンドでは実際にインストールされます。
なお、make install コマンドを実行する際に pgpool-II をインストールするディレクトリへの書き込み権限が必要です。
ここでは、pgpool-II を /usr/local ディレクトリ以下にインストールします。
注意: pgpool-II のソースコードをコンパイルするには PostgreSQL 7.4 以降で実装された libpq ライブラリ (3.0 プロトコル) が必要です。
configure スクリプトを実行した際に以下のエラーメッセージが表示された場合、libpq ライブラリがインストールされていないか、インストールされていてもプロトコルのバージョンが 3.0 でない可能性があります。
configure: error: libpq is not installed or libpq is old
また、プロトコルのバージョンが 3.0 の libpq ライブラリがインストールされているにも係わらず、上記のエラーメッセージが表示される場合、configure スクリプトを実行した際に libpq ライブラリが認識されていない可能性があります。
configure スクリプトは標準では /usr/local/pgsql ディレクトリ以下からヘッダファイルや libpq ライブラリを検索します。
PostgreSQL のインストール先が /usr/local/pgsql ディレクトリ以下でなければ、configure スクリプトを実行する際にコマンドライン引数として --with-pgsql や --with-pgsql-includedir、--with-pgsql-libdir オプションを指定してください。
pgpool-II についての設定は pgpool.conf ファイルに記述します。
pgpool.conf ファイルの書式は 1 行ごとにパラメータ名と値を = で区切ったものです。
pgpool-II をインストールするとサンプルとして pgpool.conf.sample ファイルが作成されるので、それを pgpool.conf というファイル名にコピーしてから編集するといいでしょう。
$ cp /usr/local/etc/pgpool.conf.sample /usr/local/etc/pgpool.conf
pgpool.conf ファイルの初期設定では、pgpool-II は pgpool-II と同じホストからのポート番号 9999 への接続を受け付けます。
pgpool-II と異なるホストからの接続を受け付ける場合は listen_addresses パラメータに * を設定します。
listen_addresses = 'localhost' port = 9999
ここでは、pgpool.conf ファイルの初期設定をそのまま使用します。
pgpool-II では PCP コマンドと呼ばれるインタフェースを通して pgpool-II の停止やデータベースノードに関する情報の表示を行います。
PCP コマンドを使用するにはユーザ認証が必要になるので、ユーザ名とパスワードを pcp.conf ファイルに設定します。
pcp.conf ファイルの書式は以下のように 1 行ごとにユーザ名と MD5 ハッシュに変換されたパスワードを : で区切ったものです。
postgres:e8a48653851e28c69d0506508fb27fc5
pgpool-II をインストールするとサンプルとして pcp.conf.sample ファイルが作成されるので、それを pcp.conf というファイル名にコピーしてから編集するといいでしょう。
$ cp /usr/local/etc/pcp.conf.sample /usr/local/etc/pcp.conf
なお、パスワードを MD5 ハッシュに変換する際には pgpool-II とともにインストールされる pg_md5 コマンドを使用します。
pg_md5 コマンドは、コマンドライン引数としてパスワードを指定すると、それを MD5 ハッシュに変換したものを表示します。
例えば、以下のように pg_md5 コマンドのコマンドライン引数として postgres を指定して実行すると、postgres を MD5 ハッシュに変換したものが表示されます。
$ /usr/bin/pg_md5 postgres e8a48653851e28c69d0506508fb27fc5
また、PCP コマンドはネットワークを通して実行されるので、pgpool-II が PCP コマンドを受け付けるポート番号を pgpool.conf ファイルの pcp_port パラメータに設定します。
ここでは、pcp_port パラメータに最初から設定されているポート番号 9898 をそのまま使用します。
pcp_port = 9898
データベースノードとして使用するデータベースサーバを準備します。 データベースサーバは、pgpool-II と同じホストで起動しても、異なるホストであっても構いません。 もちろん、pgpool-II と同じホストで起動する場合は異なるポート番号を割り合て、異なるホストで起動する場合は pgpool-II が起動するホストからデータベースサーバに接続できるように設定する必要があります。 pgpool-II ではデータベースサーバごとにレプリケーションを行うので、チュートリアルのためのデータベースクラスタを作成したほうがいいでしょう。
ここでは、3 台のデータベースサーバを pgpool-II と同じホストの異なるポート番号 5432、5433、5434 で起動します。
データベースサーバをデータベースノードとして使用するには、pgpool.conf ファイルに以下のようにパラメータを設定します。
backend_hostname0 = 'localhost' backend_port0 = 5432 backend_weight0 = 1 backend_hostname1 = 'localhost' backend_port1 = 5433 backend_weight1 = 1 backend_hostname2 = 'localhost' backend_port2 = 5434 backend_weight2 = 1
backend_hostname、backend_port、backend_weight パラメータには、データベースノードのホスト名、ポート番号、負荷分散する際の重み付けを設定します。
パラメータ名の後ろには 0、1、2、… というように複数のデータベースノードを区別するための数字を指定します。
backend_weight パラメータは、複数のデータベースノードに問い合わせを負荷分散する際、どのデータベースノードにどのくらいの割合で問い合わせを行うかということを設定するパラメータです。
ここでは、3 台のデータベースノードの重み付けがすべて 1 に設定してあるので、問い合わせは 1 対 1 対 1 の割り合いで負荷分散されることになります。
pgpool-II を起動するには以下のように pgpool コマンドを実行します。
$ pgpool
ただし、このままでは pgpool プロセスは制御端末を切り離
すため、ログが出力されなくなります(エラーは標準エラーに出力されます)。
制御端末を切り離さないで起動する場合は -n オプションを指定します。
$ pgpool -n &
コマンドを実行した端末にログメッセージが表示されるので、以下のようにログメッセージをファイルに保存するように実行することをお勧めします。
$ pgpool -n -d > /var/log/pgpool/pgpool.log 2>&1 &
-d オプションはデバッグメッセージの出力を有効にします。
上記の例はファイルにリダイレクトさせているため、ログが追加され続けます。 ログをローテートさせたい場合は、ローテート機能を持ったコマンドにログを 渡してください。 たとえば、Apache2に付属するrotatelogsを使うのであれば、
$ pgpool -n 2>&1 | /usr/local/apache2/bin/rotatelogs \ -l -f /var/log/pgpool/pgpool.log.%A 86400 &とすれば毎日夜中の0時にログがローテートされ、pgpool.log.Thursday のような名前のログファイルが毎日作成されます。 ただし、すでに同じ名前のファイルがある場合にはログがそのファイルに追加されてしまうので、cronを使って古いログファイルを消去する設定を併せて行っておく方が良いでしょう。 例を示します。
55 23 * * * /usr/bin/find /var/log/pgpool -type f -mtime +5 -exec /bin/rm -f '{}' \;
注意: Linuxディストリビューションによっては、rotatelogs は /usr/sbin/rotatelogs2 のような名前でインストールされているかも知れません。 -f オプションは rotatelogs が起動された直後に直ちにログファイルを作るオプションで、apache2 2.2.9 以降でのみ有効です。
cronolog を使う場合であれば、以下のようにパイプでログメッセージを渡してください。
$ pgpool -n 2>&1 | /usr/sbin/cronolog \ --hardlink=/var/log/pgpool/pgpool.log \ '/var/log/pgpool/%Y-%m-%d-pgpool.log' &
pgpool-II を停止するには以下のように pgpool コマンドを実行します。
$ pgpool stop
pgpool-II を停止する際にクライアントが接続している場合、その接続が切断されるまで待ってから停止します。
クライアントの接続が切断されるまで待たずに停止するには以下のように pgpool コマンドを実行します。
$ pgpool -m fast stop
レプリケーションでは複数のデータベースノードに同じデータを複製して格納します。
ここでは、「1. さあ始めましょう」で準備した 3 台のデータベースノードを使用し、pgbench が作成するデータベースのレプリケーションを行うまでの手順について説明します。
データベースノードのレプリケーションを有効にするには、pgpool.conf ファイルの replication_mode パラメータを true に設定します。
replication_mode = true
上記のように replication_mode パラメータを true に設定することにより、pgpool-II への問い合わせがすべてのデータベースノードに対して実行され、同じデータが複製されて格納されるようになります。
さらに、load_balance_mode パラメータを true に設定することにより、pgpool-II に対する SELECT 文を複数のデータベースノードに対して振り分け、負荷分散を行うことができます。
load_balance_mode = true
ここでは、replication_mode、load_balance_mode パラメータを true に設定します。
レプリケーションの設定を pgpool-II に反映させるには pgpool-II を再起動する必要があります。 pgpool-II の再起動については「1.5. pgpool-II の起動と停止」を参照してください。
レプリケーションを有効にして pgpool-II を起動できたら、実際に pgbench を使用してレプリケーションが行われていることを確認しましょう。
まず、pgbench が使用するデータベース bench_replication を作成します。
createdb コマンドを pgpool-II に対して実行すると、すべてのデータベースノードに対してデータベース bench_replication が作成されます。
$ createdb -p 9999 bench_replication
そして、pgbench コマンドに -i オプションを指定して実行することにより、データベース bench_replication に対して pgbench で使用するテーブルを作成し、データを初期化します。
$ pgbench -i -p 9999 bench_replication
pgbench コマンドに -i オプションを指定して実行した際に作成されるテーブルとそれぞれのテーブルの行数は以下のとおりです。
すべてのデータベースノードのデータベース bench_replication に以下の行数のデータが格納されていれば、正常にレプリケーションが行われていることになります。
| テーブル名 | 行数 |
|---|---|
| branches | 1 |
| tellers | 10 |
| accounts | 100000 |
| history | 0 |
例えば、以下のようにコマンドを実行すると、すべてのデータベースノード (ポート番号 5432、5433、5434) のデータベース bench_replication に含まれるテーブル branches、tellers、accounts、history の行数が表示されます。
$ for port in 5432 5433 5434; do > echo $port > for table_name in branches tellers accounts history; do > echo $table_name > psql -c "SELECT count(*) FROM $table_name" -p $port bench_replication > done > done
パラレルクエリでは複数のデータベースノードに異なる範囲のデータを格納します。これをパーティショニングと呼びます。またパーティションニングと共に複数のデータベースノードに同じデータを複製することもできます。つまりパーティショニングしているテーブルとレプリケーションしているテーブルを共存させることができます。
パラレルクエリを使用するにはシステムデータベースと呼ばれる特別なデータベースが必要です。
システムデータベースでは、どのデータベースノードに対してどのデータを格納するかという分散ルールを格納しており、それによってデータを複数のデータベースノードに分散させます。 また、システムデータベースは、dblink を使用することによって複数のデータベースノードに問い合わせを振り分け、それぞれのデータベースノードで実行された結果を 1 つにまとめます。
ここでは、「1. さあ始めましょう」で準備した 3 台のデータベースノードを使用し、pgbench が作成するデータベースに対してパラレルクエリを実行するまでの手順について説明します。
パラレルクエリを有効にするには pgpool.conf ファイルの parallel_mode パラメータを true に設定します。
parallel_mode = true
ただし、上記のように parallel_mode パラメータを true に設定しただけでは、複数のデータベースノードに対してデータを分散できません。
さらに、システムデータベースを作成し、分散ルールの定義と登録を行う必要があります。
また、dblink ではシステムデータベースから pgpool-II への TCP/IP 接続が行われるため、listen_addresses パラメータを適切に設定する必要があります。
listen_addresses = '*'
注意: パラレルクエリとレプリケーションを同時に有効にすることができますが、パーティショニングしているテーブルに対しては、レプリケーションされません。 また、パラレルクエリとレプリケーションではデータベースに格納されるデータの構成が異なるため、「2. 初めてのレプリケーション」で作成したデータベース bench_replication をそのまま使用することはできません。
replication_mode = true load_balance_mode = false
または、
replication_mode = false load_balance_mode = true
ここでは、parallel_mode パラメータを true に、listen_addresses パラメータを * に、replication_mode、load_balance_mode パラメータを false に設定します。
システムデータベースと通常のデータベースに違いはありません。 ただし、システムデータベースには、dblink の関数が定義されており、分散ルールを格納するテーブル dist_def が定義されている必要があります。 また、データベースノードの 1 台にシステムデータベースを作成することもできますし、pgpool-IIをカスケード接続することで負荷分散することもできます。
ここでは、以下の pgpool.conf ファイルの初期設定に従ってシステムデータベースを作成します。
system_db_hostname = 'localhost' system_db_port = 5432 system_db_dbname = 'pgpool' system_db_schema = 'pgpool_catalog' system_db_user = 'pgpool' system_db_password = ''
pgpool.conf ファイルの初期設定では、システムデータベースは pgpool-II と同じホストのポート番号 5432 で起動するデータベースサーバ、つまり、1 台目のデータベースノードに作成することになります。
また、システムデータベースのデータベース名は pgpool に、システムデータベースに接続するユーザ名も pgpool に設定されているので、pgpool というユーザを作成してからユーザ pgpool を所有者としてデータベース pgpool を作成します。
$ createuser -p 5432 pgpool $ createdb -p 5432 -O pgpool pgpool
3.2.1. dblink のインストール
システムデータベースとしてデータベース pgpool が作成できたら、dblink をインストールします。
dblink は PostgreSQL のソースコードの contrib ディレクトリに含まれるツールの 1 つです。
dblink をインストールするには PostgreSQL のソースコードを展開したディレクトリで以下のようにコマンドを実行します。
$ USE_PGXS=1 make -C contrib/dblink $ USE_PGXS=1 make -C contrib/dblink install
データベース pgpool に対して dblink の関数を定義します。
PostgreSQL のインストール先が /usr/local/pgsql ディレクトリ以下であれば、/usr/local/pgsql/share/contrib ディレクトリに dblink の関数を定義するための dblink.sql ファイルが存在します。
それを使用して以下のように psql コマンドを実行します。
$ psql -f /usr/local/pgsql/share/contrib/dblink.sql -p 5432 pgpool
3.2.2. テーブル dist_def の定義
分散ルールを格納するテーブル dist_def をシステムデータベース pgpool に定義します。
pgpool-II をインストールするとテーブル dist_def を含めてシステムデータベースを作成するための system_db.sql ファイルが作成されるので、それを使用して以下のように psql コマンドを実行します。
$ psql -f /usr/local/share/system_db.sql -p 5432 -U pgpool pgpool
system_db.sql ファイルでは、スキーマ pgpool_catalog にテーブル dist_def などを定義しています。
従って、pgpool.conf ファイルの system_db_schema パラメータで pgpool_catalog でないスキーマ名を指定している場合、system_db.sql ファイルを編集してスキーマ名を変更する必要があります。
テーブル dist_def は以下のように定義されており、テーブル名を変更することはできません。
CREATE TABLE pgpool_catalog.dist_def (
dbname text, -- データベース名
schema_name text, -- スキーマ名
table_name text, -- テーブル名
col_name text NOT NULL CHECK (col_name = ANY (col_list)), -- 分散キー列名
col_list text[] NOT NULL, -- テーブルの列名
type_list text[] NOT NULL, -- テーブルのデータ型名
dist_def_func text NOT NULL, -- 分散ルール関数
PRIMARY KEY (dbname, schema_name, table_name)
);
テーブル dist_def に格納されるデータは大きく分けて以下の 2 つです。
まず、分散ルールはどのデータをどのデータベースノードに格納するかということを決定するためのデータです。 col_name 列にはテーブルのどの列の値によって格納するデータベースノードを決定するかということを指定します。 dist_def_func 列には、col_name 列に指定された列の値を引数として受け取り、データベースノードの番号を返す関数を指定します。
テーブルのメタ情報は問い合わせの書き換えを行う際に使用されるデータです。 パラレルクエリでは、問い合わせを複数のデータベースノードに振り分け、それぞれのデータベースノードで実行された結果を 1 つにまとめるため、問い合わせの書き換えを行います。
3.2.3. テーブル replicate_def の定義
一つのSQL文にテーブルの結合等でdist_defに登録したテーブルと共にレプリケーションを行うテーブルを指定する場合には、 レプリケーションを行うテーブルの情報(複製ルール)をあらかじめ、replicate_def というテーブルに登録しておきます。 テーブル dist_def の定義の際に、system_db.sqlファイルから作成した場合には、すでにreplicate_defテーブルが作成されています。 replicate_defテーブルは以下のように定義されています。
CREATE TABLE pgpool_catalog.replicate_def( dbname TEXT, -- データベース名 schema_name TEXT, -- スキーマ名 table_name TEXT, -- テーブル名 col_list TEXT[] NOT NULL, -- テーブルの列名 type_list TEXT[] NOT NULL, -- テーブルのデータ型名 PRIMARY KEY (dbname,schema_name,table_name) );
テーブル replicate_def に格納されるデータはテーブルのメタ情報(dbname、schema_name、table_name、col_list、type_list)となります。
pgpool-ll は、クエリに使われている、すべてのテーブル、カラム、型情報をdist_defまたは、replicate_defテーブルに登録している情報 を用いて、クエリの解析とクエリの書き換えを行います。そのため replicate_defテーブルに正しい情報を登録しておかないと、正しい実行 結果が得られない可能性があります。
分散ルールはどのデータをどのデータベースノードに格納するかということを決定するものです。
ここでは、スケールファクター 3 を指定してデータを初期化した pgbench のテーブルを 3 台のデータベースノードに振り分けるための分散ルールを定義します。 なお、データベースは「2. 初めてのレプリケーション」で使用したデータベース bench_replication とは別にデータベース bench_parallel を作成することにします。
なお、ソースコードの sample ディレクトリには以下の説明で使用する分散ルールが定義されたファイル dist_def_pgbench.sql が準備されています。
これを使用して分散ルールを定義するにはソースコードを展開したディレクトリで以下のように psql コマンドを実行します。
$ psql -f sample/dist_def_pgbench.sql -p 5432 pgpool
まず、分散ルールとテーブルのメタ情報をシステムデータベース pgpool のテーブル dist_def に格納します。 ここではaccountsテーブルを各データベースノードに対してデータ分割を行います。 分散キー列名は、accounts についてはプライマリキー制約が指定された列 aid に指定します。
INSERT INTO pgpool_catalog.dist_def VALUES (
'bench_parallel',
'public',
'accounts',
'aid',
ARRAY['aid', 'bid', 'abalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)'],
'pgpool_catalog.dist_def_accounts'
);
次に、分散ルール関数をテーブルごとにシステムデータベース pgpool に定義します。 分散ルール関数は、必ずしもテーブルごとに作成する必要はなく、内部関数を使用することもできます。 また、分散ルール関数は SQL でなくとも PL/pgSQL や PL/Tcl で作成しても構いません。
スケールファクター 3 を指定してデータを初期化した場合、accounts テーブルの aid 列の値は 1 から 300000 までになるので、これらの値をもとに 3 台のデータベースノードに対してデータが均等に分散されるように関数を定義します。
ここでは、引数として受け取った値から WHEN 式によって 0 から 2 までのデータベースノードの番号を返す単純な SQL 関数を定義することにします。
CREATE OR REPLACE FUNCTION pgpool_catalog.dist_def_accounts(anyelement)
RETURNS integer AS $$
SELECT CASE WHEN $1 > 0 AND $1 <= 100000 THEN 0
WHEN $1 > 100000 AND $1 <= 200000 THEN 1
ELSE 2
END;
$$ LANGUAGE sql;
複製ルールはどのテーブルがレプリケーションされているかどうかを決定するものです。
ここでは、pgbenchで作成される branches テーブルと tellers を登録しておきます。これにより、accountsテーブル、branchesテーブルとtellersテーブルを 使った問い合わせが可能となります。
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'branches',
ARRAY['bid', 'bbalance', 'filler'],
ARRAY['integer', 'integer', 'character(88)']
);
INSERT INTO pgpool_catalog.replicate_def VALUES (
'bench_parallel',
'public',
'tellers',
ARRAY['tid', 'bid', 'tbalance', 'filler'],
ARRAY['integer', 'integer', 'integer', 'character(84)']
);
なお、ソースコードの sample ディレクトリには上記の説明で使用する複製ルールが定義されたファイル replicate_def_pgbench.sql が準備されています。
これを使用して分散ルールを定義するにはソースコードを展開したディレクトリで以下のように psql コマンドを実行します。
$ psql -f sample/replicate_def_pgbench.sql -p 5432 pgpool
パラレルクエリの設定を pgpool-II に反映させるには pgpool-II を再起動する必要があります。なお、テーブル dist_def、replicate_def を更新した場合も pgpool-II の再起動が必要です。 pgpool-II の再起動については「1.5. pgpool-II の起動と停止」を参照してください。
パラレルクエリを有効にして pgpool-II を起動できたら、実際に pgbench を使用してパラレルクエリが実行されることを確認しましょう。
まず、pgbench が使用するデータベース bench_parallel を作成します。
createdb コマンドを pgpool-II に対して実行すると、すべてのデータベースノードに対してデータベース bench_parallel が作成されます。
$ createdb -p 9999 bench_parallel
そして、以下のようにスケールファクター 3 を指定して pgbench で使用するテーブルを作成し、データを初期化します。
$ pgbench -i -p 9999 -s 3 bench_parallel
スケールファクター 3 を指定してデータを初期化した pgbench のテーブルとそれぞれの行数は以下のとおりです。 データを初期化することによって格納されるデータは、テーブル dist_def に登録されているテーブルに対しては分散ルールに従って 3 台のデータベースノードに分散されます。
| テーブル名 | 行数 |
|---|---|
| branches | 3 |
| tellers | 30 |
| accounts | 300000 |
| history | 0 |
データが 3 台のデータベースノードに分散されていることは、pgpool-II に対して問い合わせを実行した結果とデータベースノードに直接問い合わせを実行した結果を比較すれば確認できます。 例えば、以下のようにコマンドを実行すると、すべてのデータベースノード (ポート番号 5432、5433、5434、9999) のデータベース bench_parallel のテーブル accounts の最小値、最大値が表示されます。
$ for port in 5432 5433 5434 9999; do > echo $port > psql -c "SELECT min(aid), max(aid) FROM accounts" -p $port bench_parallel > done