高效能 PostgreSQL

Server Configuration Tuning

Jack Yu  2015.09.04

今天講的是

  • 性能相關的設定值
  • 建議的調整方向
  • 最佳化設定的流程

詳細可以參考

postgresql.conf

  • 路徑 $PGDATA/postgresql.conf
  • 編輯它,然後重啟服務

盡量線上修改設定檔

  • 許多設定可以不用重啟服務就生效
  • 可以減少停機時間
  • 不過請確定你知道你在幹嘛!

預設值有兩種

  • 讀設定檔前的預設值 (boot_val)
    • 在讀 postgresql.conf 前的值
  • 運行時執行 RESET 指令回去的值 (reset_val)
    • 服務啟動後的值
  • 可以用 SHOW 或是查詢 pg_settings view 取得

Allowed Change Context

  • 不同設定值有各自允許修改的時間
internal 編譯時期就要決定的設定值,修改要重編
postmaster 只有在完整服務重啟才可以修改的值
sighup 送 HUP 訊號重載 postgresql.conf 生效
backend 與 sighup 類似,但不會對已建立的 backend session 生效
superuser 任意時間可以被 superuser 修改
user 個別 user session 可以在任一時間修改,且僅對此 session 有效

重新載入 postgresql.conf

  • 方法一: 執行 query
     
  • 方法二: HUP 訊號


     
  • 方法三: reload
     
  •  
  • 但其實都是
     
postgres=# SELECT pg_reload_conf();
$ ps -eaf | grep "postgres -D" 
postgres 11185     1  0 22:21 pts/0    00:00:00 /home/postgres/inst/bin/ postgres -D /home/postgres/data/ 
$ kill -HUP 11185 
$ pg_ctl reload
server signaled
LOG:  received SIGHUP, reloading configuration files

離線改設定很簡單

  • 註解調不想要的設定
  • 重載設定 (例如 $pg_ctl reload)
  • 但注意! 應要再次確定設定有生效
    • 因為有些設定需要重啟服務才生效
LOG:  received SIGHUP, reloading configuration files 
LOG:  parameter "checkpoint_segments" removed from configuration file, reset to default

Server-wide settings

  • Database connections
  • Shared memory
  • Logging
  • Vacuuming and statistics
  • Checkpoints
  • WAL settings

資料庫連線

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
    分析軟體的輸入格式 
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

  • 決定要記錄的語句
  • 保留足夠的資訊可以用來找到效能問題
  • 一樣可以找工具分析
none 不要記錄任何語句等級的資訊
ddl 只記錄 Data Definition Language (DDL) 語句,例如 CREATE 和 DROP。
mod 紀錄所有會修改值的語句,除了 SELECT 外的那些
all 記錄所有的語句

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
  • 最後:實際跑一下應用程式,並觀察運行狀況繼續微調

或者

你也可以直接使用 pgtune
幫你產生設定檔

結論

書裡的結論

  • 預設值只適用於記憶體有限的小 server,所有 server 都應該至少 tuning 一次。
  • 調整記憶體相關的參數時,記得大量連線可能會吃光記憶體的狀況。
  • Query planner 需要有足夠的統計資訊才能做出好決定。
  • autovacuum 非常重要,除了提供 planner 資訊,也才能適當的維護 table
  • 多用 live configuration 和 per-client 設定來微調。

我的結論

  • 書中的設定檔和建議值都太舊了,建議看新版的文件

O-(/// ̄皿 ̄)☞ ─═≡☆゜★█▇▆▅▄▃▂_  

謝謝大家!

PostgreSQL High Performance: Server Configuration Tuning

By Chieh Yu

PostgreSQL High Performance: Server Configuration Tuning

  • 1,029
Loading comments...

More from Chieh Yu