資料庫連線

listen_addresses

• 預設只允許本地連線
• 常見作法是允許任意連線，並用 pg_hba.conf 來建立白名單
• 但是白名單會有效能影響，縮小聽的位址和使用防火牆擋在前面比較實在

max_connections

• 同時連線數，一般來說是 100
• 設定時要記得
  • 每條連線都會占用 shared memory
  • 同時查詢會消耗更多記憶體
• 資料庫的設計不擅長處理大量連線
• 不要把這個值設超過你需要的!
• 應用端應該用連線池來重用連線

Shared Memory

• 這類設定需要重啟服務

shared_buffers

• 共用的緩衝區，預設是 32 MB
• 在超過 1GB RAM 的硬體下，建議配置 25% 的 RAM，但不要超過 40% (留給 OS cache)
• 書的後面章節會有更詳細說明

Logging

log_line_prefix

• 預設值是空的 (怎麼可能?!?!)
• 建議可以設定為 
  
  
  
   
• 或是設定成相容於其他 log
  分析軟體的輸入格式 
  • 例如: pgFouine 

log_line_prefix='%t:%r:%u@%d:[%p]: ' 
# %t: Timestamp 
# %u: Database user name 
# %r: Remote host connection is from 
# %d: Database connection is to 
# %p: Process ID of connection

log_statement

• 決定要記錄的語句
• 保留足夠的資訊可以用來找到效能問題
• 一樣可以找工具分析

log_min_duration_statement

• 紀錄超過時間的查詢
• 非常方便用來找 "outlier"
• 8.4 後可以搭配 auto_explain 模組

Vacuuming
and Statistics

關於 VACUUM

• n. 吸塵器
• 清理舊 transaction 的垃圾
• 回收硬碟空間
• 有做 UPDATE 和 DELETE 的話，
  應該要定期 VACUUM

關於 ANALYZE

• 收集資料庫中 table 的統計資料
  • 例如: 有多少筆資料
  • 例如: 有多少唯一的值
• 用於讓 query planner 做出好規劃

autovacuum

• 在長期運作的狀況下，VACUUM 很重要
• 8.1 開始有 autovacuum daemon 在背景執行
• 執行時間由異動的資料筆數和 table 大小算
• 8.3 開始預設啟用
• 建議升級到新版本不要自己 tuning 設定 ლ(ﾟдﾟლ)

maintainance_work_mem

• 大型維護操作的可用記憶體
  • VACUUM
  • CREATE INDEX
  • ALTER TABLE ADD FOREIGN KEY
• 很少同時有 session 會一起做這些操作，
  設定比 work_mem 大很多是沒問題的。
• 一般來說 5% RAM 大小是 OK 的

default_statistics_target

• PostgreSQL 依據收集的統計資訊決定查詢如何執行
• 統計資訊是由 ANALYZE 或 autovacuum 進行
• 這個值決定要收集的資料量!
• 設太高 ANALYZE 會跑比較久，太低會查詢效率不好
• 8.4 開始是 100，建議至少超過 100。
• 可以針對單獨 column 設定較高的值 (eg. 10,000)

Checkpoints

checkpoint_segments

• 在 autocheckpoint 間最多允許的 log file segment 數量，超過就會 autocheckpoint
• 預設是 3，建議增加到最少 10
• 對於回復時間較不重要的高附載狀況，可以增加到 32 以上。

checkpoint_timeout

• 多久做一次 checkpoint
• 預設是 5 分鐘
• 可以設為 10 分鐘或更高
• 越高會影響回復時間，但可以減少頻繁寫入

checkpoint_completion_target

• 完成 checkpoint 的時間比例，預設是 0.5
  • 時間 = 這個選項 * checkpoint_timeout
• 若已經把 checkpoint_segments 調高，
  建議把此選項設到 0.9，可以換取平滑的 I/O

WAL Settings

wal_buffers

• 預設是 -1 (1/32 的 share_buffer)
• 最大不要超過 WAL segment (通常 16 MB)
• 建議使用大量寫入的 benchmark 測試來做設定

wal_sync_method

• 設定用來避免 write-back cache 
• 編譯時會偵測環境來自動決定方法
• 需要看 OS 和硬體來決定
• 這個設定是安全和速度的 trade off 
• write/fsync, write/fdatasync, open_data_sync, fsync_writethrough, open_sync
• 可以使用"拔插頭"測試法

Per-Client Settings

• 開啟 session 後使用 SET 指令設定

effective_cache_size

• PostgreSQL 會用自己的 shared_buffers 也會用檔案系統的 cache。這個值就是期望的有效 cache 總和。
• 若 shared_buffers 是 25 % RAM，則此值是 50-75 % RAM。
• 此值只是用來作為 query plain 的參考，不會配置記憶體。

synchronous_commit

• Commit transaction 到硬碟通常是個瓶頸
• 關閉此選項等於啟用異步 commit
• 可以在 wal_writer_delay 後批次寫入 (e.g 600ms)
• 但這個中間若 crash，資料救不回來。
• 可以針對不同 client 提供不同的 commit 保證。
  • 例如 log, monitoring table 通常有很多 INSERT 但允許掉資料。

SET LOCAL synchronous_commit TO OFF;

work_mem

• 單一 client 在做查詢時，可用的記憶體
• 預設只有 1MB
• 例如查詢需要排序時，若記憶體不足會改用硬碟做排序，造成嚴重效能下降。
• 若很常做排序，應該要把此值設大一些。
• 建議設定
  • (RAM - shared_bufers) / max_connections / F
  • eg. F = 2

random_page_cost

• 用來告知 random page 操作是否代價高
• 建議減小這個設定，例如 4.0 -> 2.0
• 越低會讓 query planner 偏好使用 index 而不是 sequential scan
• 新的 planner 比較聰明可能不會看這個值，建議最後再來 tuning。

有些選項不建議改

Σ(・ω・ノ)ノ ┴─┴

fsync

• 只有在 Crash 後不想回復時使用
• 關閉後 wal_sync_method 就沒用了
• 建議用 synchronous_commit=off 來代替
• 唯二建議使用的時間點
  • DB 倒資料的時候
    • 步驟可以重做，不需要回復
  • Slave 可以爛掉無所謂
    • 可以重新 sync

full_page_writes

• Checkpoint 後的第一個修改是否要寫入整個 Disk page
• 關掉可能會造成新舊資料混合，會造成異常
• 只有在對使用的檔案系統和硬體有深入研究後再說

max_prepared_transactions

• 同時允許的最多 two-phase commit (2PC) 數量
  • PREPARE TRANSACTION

enable_seqscan

• 關閉可以強制 planner 使用 index 而不用循序掃描
• 爛主意! 應該要提供 query optimizer 更多資訊來做決定

Guidelines

轉角遇到新 server (1)

• 第一步：把 log 開得足夠詳細
• 第二步：決定 shared_buffers。
  • 從 25% RAM 開始往上調
• 第三步：決定最大的連線數
• 第四步：啟動服務，看剩下多少記憶體

轉角遇到新 server (2)

• 第五步：依據 shared_buffers 和 OS cache 調整 effective_cache_size 
• 第六步：調整work_mem
  • OS cache / max_conenction / 2
• 第七步：調整 maintenance_work_mem to 5% per GB
• 第八步：增加 checkpoint_segments 至少到 10，若有好硬體則可以調到 32 或以上

轉角遇到新 server (3)

• 第九步：若使用的平台用預設的 wal_sync_method 不安全，可以考慮修改
• 第十步：增加 wal_buffers 到 16 MB
• 最後：實際跑一下應用程式，並觀察運行狀況繼續微調