PostgreSQL 效能最佳化

PostgreSQL 效能最佳化http://blog.sina.com.cn/s/blog_681cd80d0100md5o.html

 

每次看postgresql的設定，好像每次都忘記。過一段時間又要看。今天終於決定，開始blog吧。
postgresql 最佳化與維護
1. 硬體

資料庫最重要的就是 I/O了。所以一切從I/O開始。
RAID: 這個基本不用說，資料庫放RAID10上面，唯讀備份資料庫可以放RAID0，反正掛了沒關係。謹記：資料庫是Random Read

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 Buffers

Postgres 啟動時要到的固定記憶體。每個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的笑容

維護資料庫是必 須的。基本維護
vacuum

delete資料的時候，資料庫只是記錄這筆資料是‘不要的‘並不是真的刪除資料。所以這個時候就要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不好玩。
ANALYZE

Analyze 會更新統計資料（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
Cluster

Cluster 類似於vacuum full。建議使用cluster而不是vacuum full。cluster跟vacuum full一樣會重寫table，移除所有的dead row。同樣也是要做exclusive lock。
Truncate

Turncat 會刪除一個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設定不同的 statistics

alter 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_cost

planner 作sequential scan時候的cost。default是1，如果記憶體，cache，shared buffer設定正確。那麼這個default的值太低了，可以增加
random_page_cost

planner 作random page fetch的值。default是4.0 如果記憶體，cache，shared buffer設定正確，那麼這個值太高了，可以降低

seq_page_cost跟random_page_cost的值可以設定成一樣的。然後測試效能，可以適當降低random_page_cost的值
cpu_operator_cost

default 是0.0025，測試為，通常設定在0.5比較好
set cpu_operator_cost to 0.5;
explain analyze select ….
cpu_tuple_cost

default 是0.01 測試為，通常設定在0.5比較好
set cpu_tuple_cost to 0.5;
explain analyze select …
effective_cache

應 該跟儘可能的給到系統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 Key

Primary Key 基本因為要做join，跟Natural相比多消耗20%左右的效能。所以儘力primary做在Natural key上。
Btree vs hash

btree 比 hash 快，不管什麼情況，所以不要用hash
gin vs gist