データセンター立ち上げの為にPostgreSQLを久しぶりにセットアップしました。
構成は2台のPostgreSQLを用意しておき、通常はプライマリで動作させつつPTR(ポイント イン タイムリカバリ)で差分バックアップをもう片方に数分置きに送ります。
こうしておけば何かの事故でプライマリが動作不能に陥ってもすぐにセカンダリに切り替える事ができます。いわゆるホットスタンバイというのかな?
以前は同期レプリケーションを使ってた頃もありましたが、これがしょっちゅうロックして音信不通になるのでこの方法に落ち着きました。
同期レプリケーションは十分考えてテストを繰り返した上で採用すべきだと反省しました。少なくとも覚えておかないと行けないのは「停止した時の自動引き継ぎは魅力だが、そもそも停止する頻度があがる」という事です。
同期レプリケーションは冗長性確保の為ではなくあくまで更新系クエリの負荷分散の為に(リスクも考えて)使うべきものです。
インストール
インストールは一般的なLinuxディストリビューションではパッケージが提供されてると思います。ただ今回は別のデータセンターとバージョンを合わせたかったのでソースからインストールしました。
ソースは日本PostgreSQLユーザ会のホームページから取得します。
$ wget wget ftp://ftp.jp.postgresql.org/source/v8.1.8/postgresql-8.1.8.tar.gz
バージョンはそのときの最新バージョンを確認して落としてください。
落としたら解凍してディレクトリ内に移動します。
$ tar xvzf postgresql-8.1.8.tar.gz
$ cd postgresql-8.1.8
postgresql用のユーザを作っておきます。
$ sudo adduser postgres
...
お決まりのパターンで
$ ./configure
$ make
$ sudo make install
インストール後の設定
インストール後は用途に応じて設定を行います。
まずはマシン起動時に自動起動する設定。自動起動の設定方法はディストリビューションによって異なります。ここに書いたのはDebianでの設定です。
以下のようなファイルを用意します。内容は適せん書き換えてください。
#! /bin/bash
export PGDATA=/var/pgsql/data
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PATH=/usr/local/pgsql/bin:$PATH
case "$1" in
start)
echo -n "Starging PostgreSQL..."
su - postgres -c "pg_ctl start -D $PGDATA > /dev/null"
echo "done"
;;
stop)
echo -n "Stopping PostgreSQL..."
su - postgres -c "pg_ctl stop -D $PGDATA > /dev/null"
echo "done"
;;
reload)
echo -n "Reloading PostgreSQL..."
su - postgres -c "pg_ctl reload -D $PGDATA > /dev/null"
echo "done"
;;
*)
echo "Usage: /etc/init.d/pgcluster {start|stop|reload}"
exit 1
;;
esac
exit 0
上記をファイルを/etc/init.d/以下に追加して自動起動の設定を行います。
$ sudo cp postgresql /etc/init.d/
$ sudo update-rc.d postgresql defaults 90
上記のようにすると全てのランレベルで自動起動自動停止し、起動順が90に設定されます。ここでの90は90番目という意味ではなく、あくまでそのランレベルでの起動順の優先度を示していますのでこの数字の小さい順に起動されるだけのものです。
あとはpostgresql関連の実行ファイルは/usr/local/pgsql/bin以下におかれるので、よく使うコマンドに関しては特別な設定をしなくても通常のコマンドと同じように使えるように/usr/local/bin以下にシンボリックリンクを張っておきます。
$ cd /usr/local/bin
$ sudo ln -s ../pgsql/bin/psql psql
$ sudo ln -s ../pgsql/bin/createdb createdb
$ sudo ln -s ../pgsql/bin/dropdb dropdb
$ sudo ln -s ../pgsql/bin/initdb initdb
$ sudo ln -s ../pgsql/bin/pg_ctl pg_ctl
そしてデータベースを作成します。今回は/var/pgsql/dataに作成します。
$ sudo mkdir /var/pgsql
$ sudo chown postgres:postgres /var/pgsql
$ sudo -u postgres initdb --encoding=EUC_JP --no-locale /var/pgsql/data
いろいろと表示されて最後に起動方法が表示された後プロンプトが帰ります。ここで表示されて起動方法は先ほどの/etc/init/postgresqlに保存した起動ファイルに書いてありますので、手動で起動するには
$ sudo /etc/init.d/postgresql start
手動で停止するには
$ sudo /etc/init.d/postgresql stop
でできます。
起動確認の為にデータベースにログインしてみましょう。
$ psql -U postgres template1
上記コマンドで
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
template1=#
のように表示されたら正常に動作しています。¥qと入力して一旦抜けます。
template1=# ¥q
ただ、初期状態ではPostgreSQLの動作しているホストからしかアクセスできません。他のホストからのアクセスも許可するには設定を変更する必要があります。
$ sudo vi /var/pgsql/data/postgresql.conf
変更箇所は"listen_addresses"の項目です。
#listen_addresses = 'localhost' #変更前
listen_addresses = '*' #変更後
このように変更するとどこからでも接続できます。ただこの時点では接続できるだけでローカル以外からログインする事はできません。
他のホストからのログインを許可するにはdataディレクトリ以下の"pg_hba.conf"を修正する必要があります。
$ sudo vi /var/pgsql/data/pg_hba.conf
設定方法に関しては
ここに詳しく書いてあります。
設定を変更したら再起動すると反映されます。
チューニング
ここでは基本的なチューニングを行います。ただしチューニングの内容は用途やアクセス頻度など様々な条件によって異なります。あくまで実際の用途に応じてチューニングしますし、現時点で問題が発生するかわからないのなら取り敢えず初期状態で運用開始してみるのも良いと思います。ただ、初期設定は低スペックマシンでも動作するように設定されていますので、すでにある程度の負荷がかかる事が想定できるなら、いくつかの設定に関しては変更した方が良いようです。
私たちの用途ではデータベースの数が多くなる事が既に解っています。別の場所で運用している同じ用途のデータベースでは既に100以上のデータベースがあり一つ一つのデータベースに多数のテーブルが存在しています。またデータベースは今後も増える可能性があります。
以前初期状態で運用していた所、Warningが発生するようになってしまいました(設定を変更して以来発生していません)。
従って、そのWarningへの対処を含めていくつかの設定値をあらかじめ変更しておきます。
まずは"shared_buffers"を増やしました。
ただし、ここでの共有メモリの指定はあくまで1プロセスがOSから使用を許可される共有メモリサイズ分ですのでOSから許可されるサイズ以上のサイズは得られません。ですので必要に応じてOS側の共有メモリのサイズを先に増やしておく必要があります。
OSの共有メモリサイズは"/proc/sys/kernel/shmmax"に記載されています。
$ cat /proc/sys/kernel/shmmax
一時的に変更したい場合はここにそのサイズをかき込めばいいです
。
# echo "400000000" > /proc/sys/kernel/shmmax
とすれば変更されます。
ただしこの値はマシンを再起動するたびに戻ってしまいますので戻らないようにする為には"/etc/sysctl.conf"に以下を追加します。
kernel.shmmax=400000000
設定変更後は一応再起動して値が反映されている事を確認してください。
その上で"/var/pgsql/data/postgresql.conf"のshared_buffersを修正します。
該当項目の先頭の#を消して値を変更します。デフォルトの1000から16000に増やしました。
shared_buffers = 16000
また、アプリケーション側で1トランザクションで数十クエリを投げる処理もしているので一応デッドロック検出時間も少し長めにしました。
deadlock_timeout = 5000
あとは最大接続数を増やしましたデフォルトの100を256に増やしています。
max_connections = 256
そして共有メモリ上に作成されるwalバッファの1ページ分のサイズを大きくする事で不必要なタイミングでトランザクションが書き出されるのを防げるようです。デフォルトの8KBを32KBに増やしました。
wal_buffers = 32
そしてトランザクションのチェックポイントセグメント数を増やしました。
checkpoint_segments = 16
上記の修正は殆ど
ここにかいてある通りに行っています。
さて、それに加えて最初に書いた、テーブル数が多すぎる際に出るWarningですが、これはどうやら"
max_fsm_relations"で設定したテーブル数より実際のテーブル数(全てのデータベースでのテーブルの総数)が上回っていた為に発生していたようです。
ここに実際の数を書くのは控えておきますが、max_fsm_relationsには全データベースのテーブルの総数を書けば良いです。もちろん将来増えて行くシステムならその分もある程度見込んでおいた方が良いでしょう。
そこでまずはmax_fsm_relationsをテーブル数に。そんでもってmax_fsm_pagesをテーブル数x32に設定しました。
AUTO VACUUMの設定
私自身が自分でソースを読んだ訳ではないのですが、PostgreSQLはdelete文などによるデータの削除時に実際にディスク上のデータの削除を行っている訳ではないようです。そのような場合、PostgreSQLはディスク上のデータに対して削除されたというマークをつけて後でその領域を再利用するらしいのですが、その為にはvacuumという処理をしてやらないといけません。
これを忘れると領域の再利用ができない為に、頻繁にデータの削除と追加を繰り返すようなシステムではデータベースのディスク上のサイズがどんどん増えていき、ディスク領域を圧迫するようになります。7系まではvacuumは自分でコマンドを発行して実行するか、cronで自動実行するように設定する必要がありましたが、8系からは別のデーモンが立ち上がって自動でやってくれる機能が追加になりました。
ただ、このauto vacuum は初期設定では無効になっているので設定で動作するようにしてやらないといけません。
編集するファイルはdataディレクトリ内のpostgresql.conf。設定変更した場所は以下です。
stats_start_collector = on # 統計情報を保存する場合は true
stats_row_level = on #行レベルのパフォーマンス統計を収集するか否か。
autovacuum = on #autovacuumを起動する
これでとりあえずのPostgreSQLの設定を完了します。あと差分バックアップによるホットスタンバイの設定がありますがそれは次回に。