PostgreSQLでDBバックアップのパフォーマンスチューニングをする方法

Question

デフォルトのプレーンフォーマットでpg_dumpを使っていました。

しかし、少し調べるとpg_dump -Fc | gzip -9 -c > mydumpfile.gzを使って時間とファイルサイズを向上させることが出来ると知りました。

なので、以下のように新しいデータベースを作成しようとしたのですが、

# create tablespace dbspace location '/path/dbs';
# create database testdb tablespace dbspace;
# alter database testdb set temp_tablespaces = dbspace;

% gunzip mydumpfile.gz # to evaluate restore time without a piped uncompression
% pg_restore -d dbname mydumpfile # into a new, empty database defined above

データベース作成のための復元が12時間もかかり、それは以下の全体像の一部に過ぎません。

# select pg_size_pretty(pg_database_size('testdb'));

パフォーマンスを向上させるにはどうすればいいでしょうか。

Answer 1

まず、ディスクセットアップから問題のないIOパフォーマンスを得られているかどうかを確認して下さい。

次にPostgreSQLのインストールが正しいかどうか確認して下さい。

特に以下の点が重要なので、確認してください。
・shared_buffers が正しく設定されていますか
・maintenance_work_mem が復元の最中に増大していますか
・full_page_writes が復元の最中にオフになっていますか
・wal_buffers が復元の最中に16MGに増大していますか
・checkpoint_segments は復元の最中に16のような値に増大していますか
・auto_vacuum は復元の最中に無効になっていますか
・不当なロギングがオンになっていませんか(例えば、各ロギングステートメントが実行されるような)

もし、バージョン8.4以上を使っているならば、平行で復元も試してみて下さい。