性能関連パラメター
max_connections = 128 # (change requires restart) shared_buffers = 8GB # min 128kB huge_pages = try # on, off, or try work_mem = 32MB # min 64kB maintenance_work_mem = 2GB # min 1MB effective_io_concurrency = 2 # 1-1000; 0 disables prefetching max_worker_processes = 4 # (change requires restart) max_parallel_maintenance_workers = 2 # taken from max_parallel_workers max_parallel_workers_per_gather = 2 # taken from max_parallel_workers max_parallel_workers = 4 # maximum number of max_worker_processes that wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers max_wal_size = 4GB min_wal_size = 1GB checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 random_page_cost = 4.0 # same scale as above effective_cache_size = 24GB default_statistics_target = 100 # range 1-10000
PGTuneで算出。
wal_buffersは-1でいいんじゃないかって気もするけど、とりあえずPGTuneの値で設定してる。
ありがとうPGTune。
算出の際、「Total Memory」「Number of CPUs」はWebサーバと同居している場合は半分にしてます。
「Number of Connections」は最大同時使用数の+30%を指定しています。（計算値が100以下は100で指定）
ログ関連パラメター
log_filename = 'postgresql-%d.log' # log file name pattern, log_min_error_statement = warning # values in order of decreasing detail: log_min_duration_statement = 2000 # -1 is disabled, 0 logs all statements log_line_prefix = '%t %u %d %p[%l:%i]' # special values:
ログの保存期間は1ヶ月、レベルはwarning以上。
DBログは大事。