Software Design Postgres8.4特集の気になったメモ

http://gihyo.jp/magazine/SD/archive/2010/201001
Software Design 2010年1月号のpostgres8.4特集が興味深かったので、
その中から特に気になった部分をピックアップしてメモ。



1章:PostgreSQL 8.4の新機能

再帰SQL

その名の通り、クエリの中で再帰的な処理を記述することが可能。
例えば、親子関係を持つ木構造テーブルに対して自己結合を行ったりとか。*1

Window関数

テーブルを区間ごとに集計する機能で、OLAPなどで使われる複雑な集計クエリを
効率よく処理するための構文。
SQLで連番付与とか、歯抜けIDの検索とかも。

パーティション管理の簡略化

EXECUTE USING句を用いることにより、動的にパーティション振り分けが可能になった。

プロファイル系の機能

pg_stat_user_functions
ユーザが実行したsql/plpgsql/C言語(ecpg?)などの関数をプロファイルする。
初期化パラメータ「track_funstions」の設定が必要。


contrib/pg_stat_statements
SQLのプロファイリングを行う。
contribのインストールと初期化パラメータ「shared_preload_libraries」の設定が必要。


contrib/auto_explain
スロークエリの実行計画をサーバログに出力。
contribのインストールと初期化パラメータ「shared_preload_libraries」の設定が必要。



2章:〜OracleからPostgreSQLへ〜商用データベースからの移行

互換性向上
  • 列別名のAS句が不要に
  • distinctの処理速度改善*2
  • PL/pgSQLでのCASE文
orafce

Oracleの関数をPostgresの関数として使うための拡張ツール。

ストレージのスナップショット機能を利用したオンラインバックアップ

Tバイト以上のDBでスナップショットを実行するためには、
「ストレージへの書き込みを停止させてからスナップショットする」
方式をとる。*3

  1. postgresでpg_start_backup()を発行し、バックアップ開始を宣言
  2. postgresの監視スクリプトを停止
  3. ext3に対しioctl()でファイルシステムフリーズ発行
  4. ストレージスナップショット実行
  5. ext3に対しioctl()でファイルシステムフリーズ停止
  6. postgresの監視スクリプトを再開
  7. postgresでpg_stop_backup()を発行し、バックアップ停止を宣言


個人的に一つわからないというか疑問点。
監視スクリプト止める場合は
「マスタ→スタンバイ構成になっている場合の、「スタンバイ側を監視してるスクリプト」を止める」
ってことかな?



3章:PostgreSQLのパラメータチューニング

メモリ関係のチューニング

shared_buffers

DBが小さくて物理メモリに乗りきってしまう場合 DBサイズ程度を割り当てる
物理メモリと比べてDBサイズがずいぶんと大きい場合 OSのページキャッシュ機能を考慮して物理メモリの1−2割を割り当てる


work_mem
ソートやハッシュ処理のための作業メモリサイズ。
プロセス数×work_mem(以上)のメモリが消費されるため
max_connectionsの設定と兼ね合い。

メモリ量の計算式

物理メモリサイズ >= shared_buffer + (max_connestions * work_mem) + maintenance_work_mem + その他ごにょごにょ*4

WAL関連のチューニング

checkpoint_segments
デフォルトでは
「WALセグメントサイズ=16M」×「checkpoint_segments=3」 = 48M
なので、48M分のWALが書き出されるとチェックポイント発生。

チェックポイントの頻発はパフォーマンスに影響するため、
16〜64程度まで引き上げておくとよい。


checkpoint_completion_target
チェックポイント処理をゆっくりと行い、
I/O負荷をやわらげるためのパラメータ。
デフォルトでは「0.5」。
これが「0」に近いほど全力でチェックポイントを行い、
「1」に近いほどゆっくりとチェックポイントを行う。
ゆっくりにするほどクラッシュリカバリによる復旧時間が長くなるが、
それをあまり考慮しないのであれば「0.9」くらいまで引き上げるといいかも。


wal_buffers
WAL専用のバッファ。
wal_buffersを多めにしておくとWALの同期書き込みの頻度を抑えられるので
4M程度を設定しておくといいかも。


full_page_writes
WALの量を減らすパラメータ。
これをoffにすることによってWALの発生量が抑えられ、IO負荷も軽減される。
ただ故障時のリカバリが出来なくなる恐れがあるので
それでも問題ない要件の時に適用するとよい


synchronous_commit & wal_writer_delay
WALの同期書き込みを緩くするパラメータ。
synchronous_commitをoffにすると遅延してWALの同期書き込みを行う。
遅延程度はwal_writer_delayに依存する。
ただし同期書き込みを緩くすると故障時に
コミットしたものが反映されなくなる可能性も出てくる。
トランザクション消失)

vacuum関連のパラメータ

maintenance_wark_mem
VACUUM対象のごみレコードを記録するための領域に使う。
多いほどパフォーマンスアップ。

autovacuum_vacuum_cost_limit
VACUUMが消費するIO帯域を抑えることができる。
いくつかのパラメータが関連しているが、
autovacuum_vacuum_cost_delayやcost_limitのみで十分チューニング可能。

その他Tips

ユーザやデータベース個別に初期化パラメータを設定できる。

4章:大規模システムへの適用

バッチ処理での通信チューニング

複数行INSERT構文
insert into table values (?,?), (?,?), ...


参考URLまとめ

ここに挙げたものはあくまで個人的に気になった部分なので
本当はもっとたくさん書いてあります。
気になった人はバックナンバー購入するといいですよ!

*1:postgresql8.3以前はcontrib/tablefuncモジュールで実現可能

*2:8.3以前は distinct より group byの方が速かったらしい。体感ではそんなに気になるほどでもなかったような

*3:ext3ファイルシステムでのIOフリーズは、2009/9にリリースされた「RedHatEnterprise Linux5 Update4」 から可能

*4:詳しくは書籍参照