postgresql 優化與維護

postgresql  優化與維護
轉帖 http://ruimemo.wordpress.com/2010/03/31/postgresql-performance-and-maintenance-%ef%bc%88postgres-%e4%bc%98%e5%8c%96%e4%b8%8e%e7%bb%b4%e6%8a%a4/#comment-17

【備註】
作者的說的這些方面 是我們需要在系統部署方面需要了解或者關注的點。

但是對於其中的某些變數的值 不很認同，針對我們的系統需要做相應的修改和除錯

1. 硬體資料庫最重要的就是 I/O了。所以一切從I/O開始。
RAID:  這個基本不用說，資料庫放RAID10上面，只讀的備份資料庫可以放RAID0，反正掛了沒關係。謹記：資料庫是Random Read
注 意的是硬碟外圈要比內圈快，所以跟linux 建立swap的原理一樣，盡力把資料庫的東西放在硬碟的外圈（前端）直接在fdisk的時候就做好。同時有 個省錢方法，因為硬碟的外圍速度遠遠快於內圈的速度，所以選擇一個1 T的SATA硬碟，然後只用外圈的100G，其他的不用，這樣的話，速度要比單買一 個100G的SAS還快。
RAID卡的選擇：
RAID卡一定要帶電池的才可以（BBU）有電源的才能做到東西寫進 CACHE，RAID就返回硬碟寫成功（不用等）
1. Areca
2. LSI (真正的LSI，re-brand不要）
3.  HP P400 以上系列
硬碟選擇：
首選是SAS: 15K RPM 每個SAS大約能提供25MB/s的Random  Write。也就是說在RAID10的設定下，如果需要50MB/s的Random Write就需要４個硬碟
節儉選擇是： SATA  可以多用幾個硬碟（SAS一倍數量）達到在RAID10中接近SAS的速度。就算SATA買SAS一倍的數量，價格仍然比SAS便宜。
也可以買 產品： 例如 Compaq的 MSA 70 (P800 Battery backed RAID control)
CPU：64位
Cache：越大越好 （現在個人電腦都3M的cache了）
CORE：越多 越好 （postgresql畢竟是跑cpu的）建議最少4個core
RAM： 最少4G。通常根據具體需求，用16-64G的RAM
2.  OS （系統）可用系統：
1. Debian Stable
2. CentOS
3. Ubuntu LTS
4. Red Hat
5. SUSE Enterprise
如果準備付費（服務），那麼就是 Canonical, Novell 跟 Redhat這三家選擇而已
如果準備不買任何服務，可以用Debian, CentOS,  Ubuntu LTS
這裡還是覺得系統用Red Hat (不付費就CentOS）畢竟人家是企業級的老大哥，錯不了。
*  現在CentOS也可以買到服務了。
不可用系統： 例如 fedora (redhat QA) ubuntu (non-LTS)
Scheduler:Grub 增加： elevator=deadline

redhat 的圖示可以看出，deadline是資料庫的最佳選擇
檔案系統  （Filesystem）這裡的選擇是：ext2，ext3 跟 ext4。  為什麼只考慮這幾個呢？因為資料庫還是穩定第一，核心開發人員所做的檔案系統，理論上說出問題的情況會少點。

WAL： 放ext2 因為WAL本身自己有Journal了，不需要用ext3 （ext2快很多）
data： ext3 
Block Size：  postgres自己是8k的block size。所以檔案系統也用8k的 block size。這樣才能最佳的提高系統的效能。
ext4：出來時間還 不夠長，不考慮。
分割槽 （Partitioning）Postgres  跟系統 OS 應該在不同分割槽
系統（OS）：系統應該放獨立的RAID1
資料庫 （Postgres Data）：資料庫應該放獨立的RAID10上。 如果RAID是帶電池的，mount 的時候給 data=writeback的選項
獨立的資料庫分割槽，就不許要記錄檔案時間了（都是放資料的）所以mount的時候要給noatime的 選項，這樣可以節約更新時間(timestamp)的I/O了。
WAL日誌（xlogs）： 獨立的RAID1上 （EXT2 系統）日誌是 Sequential write，所以普通的硬碟（SATA）速度就足夠了，沒有必要浪費SAS在log上
Postgresql  日誌（logs）：直接丟給syslog就可以。最好在syslog.conf中設定單獨的檔名. 這裡  例如用local2來做postgresql
local2.*                            -/var/log/postgres/postgres.log
記得log要給Async，這樣才不會等卡在log的I/O上， 同時記得設定logrotate以及建立路徑（path）
ext2 VS ext3 效能測試：
HP DL585
4  Dual Core 8222 processors
64GB RAM
(2) MSA70 direct attached  storage arrays.
25 spindles in each array (RAID 10)
HP P800  Controller
6 Disk in RAID 10 on embedded controller
xlog with  ext3: avg = 87418.44 KB/sec
xlog with ext2: avg = 115375.34 KB/sec
3. Postgres 記憶體 （Memory Usage)Shared  Buffer Cache
Working Memory
Maintenance Memory
Shared BuffersPostgres  啟動時要到的固定記憶體。每個allocation是8k。  Postgres不直接做硬碟讀寫，而是把硬碟中的東西放入Shared Buffers，然後更改Shared Buffers，在flush  到硬碟去。
通常 Shared Buffers設定為記憶體（available memory）的25%-40%左右。
在系統（OS）中，記得設定 kernel.shmmax的值（/etc/sysctl.conf)
kernel.shmmax決定了程式可呼叫的最大共享記憶體數量。簡單的計 算方法是
kernel.shmmax=postgres  shared_buffers + 32 MB
要保留足夠的空間（不然會out of  memory）postgresql除了shared buffer還會用到一些其他的記憶體，例如max_connections, max_locks_pre_transaction
Working Memory 這個是postgres執行作業中 （task）需要的記憶體，例如記憶體內的 hashed （aggregates， hash joins）sort （order by,  distinct  等等）合理的設定，可以保證 postgres在做這些東西的時候可以完全在記憶體內完成，而不需要把資料吐回到硬碟上去作swap。但是設定太大的話，會造 成postgres使用的 記憶體大於實際機器的記憶體，這個時候就會去硬碟swap了。（效能下降）
working memory是per  connection and per sort的設定。所以設定一定要非常小心。舉例來說，如果設定working  memory為32MB，那麼以下例子：
select * from lines, lineitems
where  lines.lineid = lineitems.lineid
and lineid=6
order by baz;
這裡就可 能用到64MB的記憶體。
hashjoin between lines and lineitems (32MB)
order by  baz (32MB)
要注意自己有多少query是用到了order by或者join
如果同時有100個連結，那麼就是 100  connection X 64MB = 6400MB (6G) 記憶體
通常來說，working mem不要給太大，2-4MB足夠
在postgres  8.3之後的版本，working mem可以在query中設定
Query:
begin;
set work_mem to  ’128MB’;
select * from foo order by bar;
insert into foo  values (‘bar’);
reset work_mem;
commit;
Function:
create function return_foo() returns setof  text as
$ select * from foo order by bar; $
SET work_mem to  ’128MB’
LANGUAGE ‘sql’
postgres官方不建議（但是支援）在 postgresql.conf檔案中更改work_mem然後HUP （資料庫應該沒有任何中斷）
利用 explain  analyze可以檢查是否有足夠的work_mem
sort (cost=0.02..0.03 rows=1 width=0)  (actual time=2270.744..22588.341 rows=1000000 loops=1)
Sort Key:  (generate_series(1, 1000000))
Sort Method: external merge Disk:13696kb
->  Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..144.720  rows=1000000 loops=1)
Total runtime: 3009.218 ms
(5 rows)
以上的 query分析顯示，這裡需要從硬碟走13MB的東西。所以這個query應給set work_mem到16MB才能確保效能。
Maintenance Memory （維護記憶體）maintenance_work_mem  決定系統作維護時可以呼叫的記憶體大小。
這個也是同樣可以在query中隨時設定。
這個記憶體只有在VACUUM, CREATE  INDEX 以及 REINDEX  等等系統維護指令的時候才會用到。系統維護是，呼叫硬碟swap會大大降低系統效能。通常maintenance_work_mem超過1G的時候並沒有 什麼實際的效能增加（如果記憶體夠， 設定在1G足以）
Background  Writer (bgwriter)功能：
負責定時寫 shared buffer cache 中的  dirty shared buffers
好處：
a. 減少系統flush shared  buffers到硬碟（已經被bgwriter做了）
b. 在checkpoint中，不會看到I/O的突然性暴增，因為dirty  buffers在背景中已經被flush進硬碟
壞處：
因為一直定時在背後flush  disk，會看到平均硬碟I/O怎加（好過checkpoint時I/O暴增）
設定：
bgwriter_delay：
sleep  between rounds。 default 200（根據機器，資料而調整）
bgwriter_lru_maxpages:
決 定每次bgwriter寫多少資料。如果實際資料大於這裡的設定，那麼剩餘資料將會被postgres的程式（server  process）來完成。server porcess自己寫的資料會造成一定的效能下降。如果想確定所有的資料都由bgwriter來寫，可以設定這裡的值為-1
bgwriter_lru_multiplier:
採 用計算的方式來決定多少資料應該被bgwriter來寫。這裡保持內建的2.0就可以。
計算bgwriter的I/O:
1000  / bgwriter_delay * bgwriter_lru_maxpages * 8192 = 實際I/O
(8192是 postgres的8k block）
例如：
1000/200 * 100 * 8192 = 4096000 = 4000 kb
bgwrater 可以用 pg_stat_bgwriter 來監測。如果想要觀察bgwrater 的執行狀況，記得首先清理舊的stat資訊。
bgwriter如果設定的太大（做太多事情）那麼就會影響到前臺的效能 （server）但是如果由系統（server）來做buffer flush同樣會影響效能。所以這裡的最好設定就是通過觀察  pg_stat_bgwriter 來找到一個最佳的平衡點。
WAL  （write ahead log)postgres中的所有寫動作都是首先寫入WAL，然後才執行的。這樣可以確保資料的準確跟 完整。當中途資料庫崩潰的時候，postgres可以通過WAL恢復到崩潰前的狀況而不會出現資料錯誤等等問題。
WAL  會在兩種情況下被回寫硬碟。
1. commit。  當commit資料的時候，WAL會被強制寫回硬碟（flush）並且所有這個commit之前的東西如果在WAL中，也會一同被flush。
2.  WAL writer程式自己會定時回寫。
FSYNC vs ASYNC
postgres 的 default 是 做  fsync，也就是說postgres會等待資料被寫入硬碟，才會給query返回成功的訊號。如果設定sync=no關閉fsync的 話，postgres不會等待WAL會寫硬碟，就直接返回query成功。通常這個會帶來15-25%的效能提升。
但是缺點就是，如果系統崩潰 （斷電，postgres掛掉）的時候，你將有可能丟失最後那個transcation. 不過這個並不會造成你係統的資料結構問題。（no data  corrupt）如果說在系統出問 題的時候丟失1-2筆資料是可以接受的，那麼25%的效能提升是很可觀的。
WAL設定：
fsync  可以選擇on或者off
wal_sync_method：
linux中是使用fdatasync。其他的。。。不知道，應該是看系統的文 件引數了
full_page_writes:
開啟的時候，在checkpoint之後的第一次對page的更改，postgres會將每 個disk page寫入WAL。這樣可以防止系統當機（斷電）的時候，page剛好只有被寫一半。開啟這個選項可以保證page image的完整性。
關 閉的時候會有一定的效能增加。尤其使用帶電池的RAID卡的時候，危險更低。這個選項屬於底風險換取效能的選項，可以關閉

wal_buffers:
WAL 的儲存大小。default 是 64 kb。 實驗證明， 設定這個值在 256 kb 到 1 MB 之間會提升效能。
wal_writer_delay
WAL 檢查WAL資料（回寫）的間隔時間。值是毫秒（milliseconds)
Checkpoints確保資料回寫硬碟。dirty data page會被 flushed回硬碟。
checkpoint 由以下3中條件激發 （bgwriter如果設定，會幫忙在後臺寫入，所以就不會有checkpoint時候的短期高I/O出現）
1.  到達設定的WAL segments
2. 到達設定的timeout
3. 使用者下達checkpoint指令
如果 checkpoint執行頻率高於checkpint_warning值。postgres會在日誌（log）中記錄出來，通過觀察log，可以來決定 checkpoint_segments的設定。
增 加cehckpoint_segments或者checkpoint_timeout可以有一 定的效能提升。而唯一的壞處就是如果系統掛了，在重啟的時 需要多一點時間來回復（系統啟動回覆期間資料庫是不能用的）鑑於postgres很少掛掉，這個 其實可以設定的很長（1天都可以）
設定：
checkpoint_segments 最多的wal  log數量，到達後會激發checkpoint，通常設定在30就好
checkpoint_timeout  一般設定15-20分鐘，常的可以設定1天也沒關係
checkpoint_completion_target  這個保持不動就好。內建是0.5，意思就是每個checkpoint預計在下個checkpoint完成前的一半時間內完成（聽起來有點繞嘴，呵呵）
checkpoint_warning  如果checkpint速度快於這個時間，在log中記錄。內建是30秒
理論中的完美設定，就是你的backend從來不用回寫硬碟。 東西都是由background來寫入的。這個就要靠調整bgwriter,  checkpoints跟wal到一個最佳平衡狀態。當然這個是理想中的完美，想真的做到。。。繼續想吧。呵呵
4. 維護 – 保持postgres的笑容維護資料庫是必 須的。基本維護
vacuumdelete 資料的 時候，資料庫只是記錄這筆資料是‘不要的‘並不是真的刪除資料。所以這個時候就要vacuum了，vacuum會把標記為‘不要‘的資料清除掉。 這裡要注 意的是，vacuum不會清理index。當資料更改超過75%的時候，需要重新建立index。postgres 8.4  index可以 用cluster重建速度快很多。在postgres  9.x中，vacuum=cluster，沒有任何區別了（保留cluster只是為了相容舊版 指令）
Full Vacuum
這個會做exclusive  lock。vacuum跟full vacuum的區別是vacuum會把 標誌為‘不要‘的空間標誌成可以再次使用（回收）而 full  vacuum則會把這個空間刪除（返還給系統OS）所以vacuum之後你的 postgres在硬碟上看到的佔用空間不會減少，但是full  vacuum會減小硬碟佔用空間。不建議使用full vacuum，第一沒必要，第 二exclusive lock不好玩。
ANALYZEAnalyze 會更新統計資訊（statistics）所有的query的最佳方案，以 及sql prepared  statement都是靠這統計資訊而決定的。所以當資料庫中的一定量資料變動後（例如超過10%），要作 analyze，嚴格的說，這個是應該常做的東 西，屬於資料庫正常維護的一部分。另外一個很重要的就是，如果是 upload資料（restore那種）做完之後要記得作analyze（restore自動不給你作的）
當 建立新的table的時候，或者給table增加index，或者對table作reindex，或者restore資料進資料庫，需要手動跑 analyze才可以。analyze直接影響default_statistics_target資料。
Autovacuum根 據postgres的官方資料，autovacuum在8.3之後才變得比較真的實用（8.1推出的）因為在8.3之前，autovacuum一次只能同 時做一個資料庫中的一個table。 8.3之後的版本，可以作多資料庫多table。
設定
log_autovacuum_min_duration:
-1 為關閉。0是log全部。>0就是說超過這個時間的就log下來。例如設定為30，那麼所有超過30ms的都會被日誌記錄。
autovacuum_max_workers:
同 時啟用的autovacuum程式。通常不要設定太高，3個就可以。
autovacuum_naptime:
檢查資料庫的時 間，default是1分鐘，不用改動
autovacuum_vacuum_threshold:
最低n行記錄才會引發 autovacuum。也就是資料改變說低於這個值，autovacuum不會執行。default是50
autovacuum_analyze_threshold:
運 行analyze的最低值，跟上面的一樣
autovacuum_vacuum_scale_factor:
table中的百分比的計算方 式（超過一定百分比作vacuum)內建是20% (0.2)
autovacuum_analyze_scale_factor：
同上， 不過是analyze的設定
autovacuum_freeze_max_age:
最大XID出發autovacuum
autovacuum_vacuum_cost_delay:
延 遲。。如果系統負荷其他東西，可以讓vacuum慢點，保證其他東西的執行.這裡是通過延遲來限制
autovacuum_vacuum_cost_limit:
同 上，也是作限制的，這裡是通過cost限制limit
ClusterCluster 類似於vacuum full。建議使用cluster而不是vacuum full。cluster跟vacuum  full一樣會重寫table，移除所有的dead row。同樣也是要做exclusive lock。
TruncateTurncat 會刪除一個table中的所有資料， 並且不會造成任何的dead row（delete則會造成dead row）同樣的，turncate也可以用來重建table
begin;
lock  foo in access exclusive mode;
create table bar as select * from foo;
turncate  foo;
insert into foo (select * from bar);
commit;
這樣就重新清理了 foo這個table了。
REINDEX
重 新建立index
5. 其他planner:statistics直接決定planner的結 果。使用planner，那麼要記得確保statistics的準確（analyze）
default_statistics_target:設定analyze分析的值。這個可以在 query中隨時設定更改
set default_statistics_target to 100;
analyze verbose  mytable;
INFO: analyzing “aweber_shoggoth.mytable”
INFO:  “mytable”: scanned 30000 of 1448084  pages, containing 1355449 live rows and 0 dead rows; 30000 rows  in sample, 65426800 estimated total rows
ANALYZE
set  default_statistics_target to 300;
analyze verbose mytable;
INFO:  analyzing “aweber_shoggoth.mytable”
INFO: “mytable”: scanned 90000 of 1448084 pages,  containing 4066431 live  rows and 137 dead rows; 90000 rows in sample,  65428152 estimated total  rows
ANALYZE
Set statistics per column 給不同的column設定不同的  statisticsalter table foo alter column bar set statistics  120
查詢何時需要增加statistics跑 個query作expain analyze
這個就會看到例如：
-> Seq Scan on bar  (cost=0.00-52.00 rows=52 width=2  (actual time=0.007..1.894 rows=3600 loops=1)
這裡的rows應該跟真正的rows數量差不多才 是正確的。
seq_page_costplanner 作sequential scan時候的cost。default是1，如果記憶體，cache，shared  buffer設定正確。那麼這個default的值太低了，可以增加
random_page_costplanner 作random page fetch的值。default是4.0 如果記憶體，cache，shared  buffer設定正確，那麼這個值太高了，可以降低
seq_page_cost跟random_page_cost的值可以設定成一樣 的。然後測試效能，可以適當降低random_page_cost的值
cpu_operator_costdefault 是0.0025，測試為，通常設定在0.5比較好
set cpu_operator_cost to 0.5;
explain  analyze select ….
cpu_tuple_costdefault 是0.01 測試為，通常設定在0.5比較好
set cpu_tuple_cost to 0.5;
explain analyze  select …
effective_cache  
【註釋： 實際上這個引數不想他的名字那樣 對系統效能有多大的提升，                             他是系統記憶體留給磁碟cache 的尺寸，與 pg效能無關】 
應 該跟儘可能的給到系統free能接受的大小（越大越好）
total                        used                  free                   shared             buffer          cached
mem:           xxxx                       yyyyy                  zzz                    aaaa                  bbbb           cccc
設定的計算方法為：
effective_cache=cached X 50% + shared 
這裡的50%可以根據伺服器的繁忙程度 在40%-70%之間調整。
監測方法：
explain analyze ;
set  effective_cache_size=新的值;
explain analyze ;
reset  effective_cache_size;
嘗試出一個最適合的值，就可以改postgresql.conf檔案設定成固定了。
Natural vs Primary KeyPrimary Key  基本因為要做join，跟Natural相比多消耗20%左右的效能。所以盡力primary做在Natural key上。
Btree vs hashbtree 比 hash  快，不管什麼情況，所以不要用hash
gin vs gistfull  text的時候，用gin不要用 gist

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/133735/viewspace-714896/，如需轉載，請註明出處，否則將追究法律責任。