PostgreSQL系統組態優化

PG的設定檔是資料庫目錄下的postgresql.conf檔，8.0以後的版本可支援K，M，G這樣的參數，只要修改相應參數後重新開機PG服務就OK了。

shared_buffers：這是最重要的參數，postgresql通過shared_buffers和內核和磁片打交道，因此應該儘量大，讓更多的資料緩存在shared_buffers中。通常設置為實際RAM的10％是合理的，比如50000(400M)。將所有的記憶體都給 shared_buffers 將導致沒有記憶體來運行程式。 unix 管理記憶體使用 swap, 當沒有記憶體可用的時候，內核會將長時間不用的記憶體挪到 swap 去，這個叫：swap pageout，當你再次使用它的時候內核又會將其挪回來，這個叫：swap pagein，這個操作有很大的不好，因為它會導致你的程式掛起直到操作完成。

正確的 Shared Buffer Cache 大小：

(1) 它應該足夠大來應付通常的表訪問操作。

(2) 它應該足夠小來避免 swap pagein 的發生。

work_mem： 在pgsql 8.0之前叫做sort_mem。postgresql在執行排序操作時，會根據work_mem的大小決定是否將一個大的結果集拆分為幾個小的和 work_mem查不多大小的暫存檔案。顯然拆分的結果是降低了排序的速度。因此增加work_mem有助於提高排序的速度。通常設置為實際RAM的2% -4%，根據需要排序結果集的大小而定，比如81920(80M)。

cache size 和 sort size 都影響記憶體的使用。記住， cache size 是在postmaster 啟動的時候就申請好的，sort size 的改變是依賴於執行多少個排序。通常，cache size 比 sort size 更有效果。 一開始調整, 如果你只有一些大的session而有更多的小session: 10% of RAM for cache size 2-4% of RAM for sort size 還有一個很有價值的參數： effective_cache_size。 優化器用這個參數來預估內核的硬碟 buffer cache. 當內核有 unified buffer cache (統一緩衝), 這個值將是沒被使用的記憶體的平均值，因為這種內核會使用所有沒有使用的記憶體來作最近硬碟訪問的緩存的；當內核用 fixed-sized disk buffer cache 時，這個參數應該設成一樣的，通常是記憶體的 10%.

    effective_cache_size：是postgresql能夠使用的最大緩存，這個數字對於獨立的pgsql伺服器而言應該足夠大，比如4G的記憶體，可以設置為3.5G(437500)

    maintence_work_mem：這裡定義的記憶體只是在CREATE INDEX, VACUUM等時用到，因此用到的頻率不高，但是往往這些指令消耗比較多的資源，因此應該儘快讓這些指令快速執行完畢：給maintence_work_mem大的記憶體，比如512M(524288)

max_connections： 通常，max_connections的目的是防止max_connections * work_mem超出了實際記憶體大小。比如，如果將work_mem設置為實際記憶體的2%大小，則在極端情況下，如果有50個查詢都有排序要求，而且都使用2％的記憶體，則會導致swap的產生，系統性能就會大大降低。當然，如果有4G的記憶體，同時出現50個如此大的查詢的幾率應該是很小的。不過，要清楚 max_connections和work_mem的關係。

maintenance_work_mem (integer)：聲明在維護性操作中使用的最大的記憶體數，比如 VACUUM， CREATE INDEX，和 ALTER TABLE ADD FOREIGN KEY 等。 數值是用千位元組計的，缺省是 16384 千位元組（16 MB）。因為在一個資料庫會話裡，任意時刻只有一個這樣的操作可以執行，並且一個資料庫安裝通常不會有太多這樣的工作併發執行，把這個數值設置得比 work_mem 更大是安全的。 更大的設置可以改進清理和恢復資料庫轉儲的速度。

max_stack_depth (integer)：聲明伺服器的執行堆疊的最大安全深度。為此設置一個參數的原因是內核強制的實際堆疊尺寸（就是 ulimit -s 或者局部等效物的設置），小於一個安全的一百萬位元組左右的範圍。 需要這麼一個安全的界限是因為在伺服器裡，並非所有過程都檢查了堆疊深度，兒只是在可能遞規的過程，比如運算式計算這樣的過程裡面進行檢查。 把這個參數設置得大於實際的內核限制講意味著一個正在跑的遞迴函數可能會導致一個獨立伺服器進程的崩潰。缺省設置是 2048 KB （兩兆），這個值相對比較小，不容易導致崩潰。 但是，這個值可能太小了，以至於無法執行複雜的函數。

自由空間映射max_fsm_pages (integer)：設置在共用的自由空間映射表裡自由空間會跟蹤的最大數目的磁片頁面數。 每個頁面槽位元需要消耗六個位元組的共用記憶體。這個設置必須大於 16 * max_fsm_relations。 缺省是 20000。這個選項只能在伺服器啟動的時候設置。

max_fsm_relations (integer)：設置自由空間將在共用地自由空間映射裡跟蹤的最大數目的關係（表和索引）。每個槽位元大概要使用五十位元組左右。缺省是 1000。這個選項只能在伺服器啟動的時候設置。

內核資源使用max_files_per_process (integer)：設置每個伺服器進程允許同時打開的最大的檔數目。缺省是 1000。 如果內核強制一個合理的每進程限制， 那麼你不用操心這個設置。但是在一些平臺上（特別指出的是，大多數 BSD 系統）， sysconf 返回一個系統真正可以支援的數目大的多的數值。如果你發現有 "Too many open files" 這樣的失敗現象，那麼就嘗試縮小這個設置。這個選項只能在伺服器啟動時設置。

preload_libraries (string)：這個變數聲明一個或者多個在伺服器啟動的時候預先裝載的共用庫。可以選擇在裝載每個庫的時候調用一個無參數的初始化函數。 要聲明這個函數，可以在庫名字後面加一個冒號，然後增加一個初始化函數名字。 比如 '$libdir/mylib:mylib_init' 會預先裝載 mylib 並且執行 mylib_init。 如果裝載了多過一個庫，用逗號分隔它們。如果沒有找到聲明的庫或者沒有找到初始化函數，那麼伺服器將啟動失敗。可以用這個方法預先裝載 PostgreSQL 的過程語言庫， 通常是使用 '$libdir/plXXX:plXXX_init' 語法，這裡的 XXX 是 pgsql，perl，tcl，或者 python。

通過預先裝載一個共用庫（以及在需要的時候初始化它）， 我們就可以避免第一次使用這個庫的那些啟動時間。不過，啟動每個伺服器進程的時間可能會增加，即使進程從來沒有使用過這些庫也這樣。因此我們只是建議對那些將被大多數會話使用的庫才使用這個選項。

基於開銷的清理延遲：在 VACUUM 和 ANALYZE 命令執行過程中， 系統維護一個內部的指標，這個指標跟蹤所執行的各種 I/O 操作的近似開銷。 如果積累的開銷達到了一個限制（通過 vacuum_cost_limit 聲明），那麼執行這個操作的進程將睡眠一會兒（用 vacuum_cost_delay 聲明）。 然後它會重置指針然後繼續執行。

這個特性的目的時允許管理員減少這些命令在併發活動的資料庫上的 I/O 影響。 有些情況下，像 VACUUM 和 ANALYZE 這樣的維護命令並不需要迅速完成；但是，通常都不希望這些命令會嚴重干擾系統執行其它資料庫操作的回應能力。 基於開銷的清理延遲為管理員提供了一個實現這個目的的手段。

缺省的時候，這個特性是關閉的。要想打開它，把 vacuum_cost_delay 變數設置為一個非零值。

vacuum_cost_delay (integer)：以毫秒計的時間長度，如果超過了開銷限制，那麼進程將睡眠一會兒。缺省值是 0，它關閉基於開銷的清理延遲特性。正數值打開基於開銷的清理。 不過，要注意在許多系統上，sleep 延遲的有效解析度是 10 毫秒； 把 vacuum_cost_delay 設置為一個不是 10 的整數倍的數值與將它設置為下一個 10 的整數倍作用相同。

vacuum_cost_page_hit (integer)：清理一個在共用緩存裡找到的緩衝區的開銷。它代表鎖住緩衝集區，查找共用的散清單以及掃描頁面的內容的開銷。缺省值是 1。

vacuum_cost_page_miss (integer)：清理一個要從磁片上讀取的緩衝區的估計開銷。這個行為代表鎖住緩衝集區，查找共用散清單，從磁片讀取需要的資料塊以及掃描它的內容的開銷。 缺省值是 10。

    vacuum_cost_page_dirty (integer)：如果清理修改一個原先是乾淨的塊的預計開銷。它需要一個把髒的磁片塊再次沖刷到磁片上的額外開銷。缺省值是 20。

vacuum_cost_limit (integer)：導致清理進程休眠的積累開銷。缺省是 200。

注意: 有些操作會持有關鍵的鎖，並且應該儘快結束。 在這樣的操作過程中，基於開銷的清理延遲不會發生作用。為了避免在這種情況下的長延時，實際的延遲是這樣計算的： vacuum_cost_delay * accumulated_balance / vacuum_cost_limit 與 vacuum_cost_delay * 4 之間的最大值。

後端寫進程

從 PostgreSQL 8.0 開始，就有一個獨立的伺服器進程，叫做後端寫進程， 它唯一的功能就是發出寫"髒"共用緩衝區的命令。 這麼做的目的是讓持有使用者查詢的伺服器進程應該很少或者幾乎不等待寫動作的發生，因為後端寫進程會做這件事情。這樣的安排同樣也減少了檢查點造成的性能下降。 後端寫進程將持續的把髒頁面刷新到磁片上，所以再檢查點到來的時候，只有幾個頁面需要刷新到磁片上。但是這樣還是增加了 I/O 的總淨負荷，因為以前的檢查點間隔裡，一個重複弄髒的頁面可能只會沖刷一次， 而同一個間隔裡，後端寫進程可能會寫好幾次。在大多數情況下，連續的低負荷要比週期性的尖峰負荷好，但是在本節討論的參數可以用於為本地需要調節其行為。

bgwriter_delay (integer)：聲明後端寫進程活躍回合之間的延遲。在每個回合裡，寫進程都會為一些髒的緩衝區發出寫操作（可以用下面的參數控制）。選取的緩衝區總是那些在當前的髒緩衝區裡當前最少使用的。 然後它就休眠 bgwriter_delay 毫秒，然後重複動作。缺省值是 200。 請注意在許多系統上，休眠延時的有效解析度是 10 毫秒；因此，設置 bgwriter_delay 為一個不是 10 的倍數的數值與把它設置為下一個 10 的倍數是一樣的效果。 這個選項只能在伺服器啟動的時候或者 postgresql.conf 檔裡設置。

bgwriter_percent (integer)：在每個回合裡，當前的髒緩衝區中不超過這個百分比的量將被寫到磁片上 （把小數圓整為下一個整數緩衝區的數值）。 這個選項只能在伺服器啟動的時候或者 postgresql.conf 檔裡設置。

bgwriter_maxpages (integer)：在每個回合裡，不超過這個數值的髒緩衝區寫入。缺省值是 100。 這個選項只能在伺服器啟動的時候或者 postgresql.conf 檔裡設置。

小的 bgwriter_percent 和 bgwriter_maxpages 減少後端寫進程導致的額外 I/O 負荷， 但是會導致在檢查點的時候的更多工作。要降低檢查點時的峰值負荷，增加這些值。要想完全關閉後臺寫進程，可以把 bgwriter_percent 和/或 bgwriter_maxpages 設置為零。

有三個主要方面可以提升PostgreSQL的潛能。

查詢方式的變化 
這主要涉及修改查詢方式以獲取更好的性能: 
創建索引，包括運算式和部分索引； 
使用COPY語句代替多個Insert語句； 
將多個SQL語句組成一個事務以減少提交事務的開銷； 
從一個索引中提取多條記錄時使用CLUSTER； 
從一個查詢結果中取出部分記錄時使用LIMIT； 
使用預編譯式查詢（Prepared Query)； 
使用ANALYZE以保持精確的優化統計； 
定期使用 VACUUM 或 pg_autovacuum 
進行大量資料更改時先刪除索引（然後重建索引）