2011年12月6日 星期二

轉:postgresql 優化與維護


1. 硬體
資料庫最重要的就是 I/O了。所以一切從I/O開始。
RAID: 這個基本不用說,資料庫放RAID10上面,唯讀的備份資料庫可以放RAID0,反正掛了沒關係。謹記:資料庫是Random Read
注意的是硬碟外圈要比內圈快,所以跟linux 建立swap的原理一樣,盡力把資料庫的東西放在硬碟的外圈(前端)直接在fdisk的時候就做好。同時有個省錢方法,因為硬碟的週邊速度遠遠快於內圈的速度,所以選擇一個1 TSATA硬碟,然後只用外圈的100G,其他的不用,這樣的話,速度要比單買一個100GSAS還快。
RAID卡的選擇:
RAID卡一定要帶電池的才可以BBU)有電源的才能做到東西寫進 CACHERAID就返回硬碟寫成功(不用等)
1. Areca
2. LSI (真正的LSIre-brand不要)
3. HP P400 以上系列
硬碟選擇:
首選是SAS: 15K RPM 每個SAS大約能提供25MB/sRandom Write。也就是說在RAID10的設定下,如果需要50MB/sRandom Write就需要4個硬碟
節儉選擇是: SATA 可以多用幾個硬碟(SAS一倍數量)達到在RAID10中接近SAS的速度。就算SATASAS一倍的數量,價格仍然比SAS便宜。
也可以買 產品: 例如 Compaq MSA 70 (P800 Battery backed RAID control)
CPU64
Cache:越大越好 (現在個人電腦都3Mcache了)
CORE:越多 越好 (postgresql畢竟是跑cpu的)建議最少4core
RAM: 最少4G。通常根據具體需求,用16-64GRAM
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
http://ruimemo.files.wordpress.com/2010/03/scheduler.jpg?w=300
redhat 的圖示可以看出,deadline是資料庫的最佳選擇
檔案系統 (Filesystem
這裡的選擇是:ext2ext3 ext4。 為什麼只考慮這幾個呢?因為資料庫還是穩定第一,內核開發人員所做的檔案系統,理論上說出問題的情況會少點。

WAL: 放ext2 因為WAL本身自己有Journal了,不需要用ext3 ext2快很多)
data ext3
Block Size postgres自己是8kblock 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)速度就足夠了,沒有必要浪費SASlog
Postgresql 日誌(logs):直接丟給syslog就可以。最好在syslog.conf中設定單獨的檔案名. 這裡 例如用local2來做postgresql
local2.* -/var/log/postgres/postgres.log
記得log要給Async,這樣才不會等卡在logI/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 啟動時要到的固定記憶體。每個allocation8k 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 memorypostgresql除了shared buffer還會用到一些其他的記憶體,例如max_connections, max_locks_pre_transaction
Working Memory
這個是postgres運行作業中 (task)需要的記憶體,例如記憶體內的hashed aggregates hash joinssort order by, distinct 等等)合理的設定,可以保證postgres在做這些東西的時候可以完全在記憶體內完成,而不需要把資料吐回到硬碟上去作swap。但是設定太大的話,會造 成postgres使用的記憶體大於實際機器的記憶體,這個時候就會去硬碟swap了。(效能下降)
working memoryper connection and per sort的設定。所以設定一定要非常小心。舉例來說,如果設定working memory32MB,那麼以下例子:
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_mem16MB才能確保性能。
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怎加(好過checkpointI/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就可以。
計算bgwriterI/O:
1000 / bgwriter_delay * bgwriter_lru_maxpages * 8192 = 實際I/O
(8192 postgres8k 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, checkpointswal到一個最佳平衡狀態。當然這個是理想中的完美,想真的做到。。。繼續想吧。呵呵
4. 維護 – 保持postgres的笑容
維護資料庫是必 須的。基本維護
vacuum
delete資料的 時候,資料庫只是記錄這筆資料是‘不要的‘並不是真的刪除資料。所以這個時候就要vacuum了,vacuum會把標記為‘不要‘的資料清除掉。這裡要注 意的是,vacuum不會清理index。當資料更改超過75%的時候,需要重新建立indexpostgres 8.4 index可以用cluster重建速度快很多。在postgres 9.x中,vacuum=cluster,沒有任何區別了(保留cluster只是為了相容舊版指令)
Full Vacuum
這個會做exclusive lockvacuumfull vacuum的區別是vacuum會把標誌為‘不要‘的空間標誌成可以再次使用(回收)而 full vacuum則會把這個空間刪除(返還給系統OS)所以vacuum之後你的postgres在硬碟上看到的佔用空間不會減少,但是full vacuum會減小硬碟佔用空間。不建議使用full vacuum,第一沒必要,第二exclusive lock不好玩。
ANALYZE
Analyze 會更新統計資訊(statistics)所有的query的最佳方案,以及sql prepared statement都是靠這統計資訊而決定的。所以當資料庫中的一定量資料變動後(例如超過10%),要作analyze,嚴格的說,這個是應該常做的東 西,屬於資料庫正常維護的一部分。另外一個很重要的就是,如果是 upload資料(restore那種)做完之後要記得作analyzerestore自動不給你作的)
當 建立新的table的時候,或者給table增加index,或者對tablereindex,或者restore資料進資料庫,需要手動跑 analyze才可以。analyze直接影響default_statistics_target資料。
Autovacuum
根 據postgres的官方資料,autovacuum8.3之後才變得比較真的實用(8.1推出的)因為在8.3之前,autovacuum一次只能同 時做一個資料庫中的一個table 8.3之後的版本,可以作多資料庫多table
設定
log_autovacuum_min_duration:
-1 為關閉。0log全部。>0就是說超過這個時間的就log下來。例如設定為30,那麼所有超過30ms的都會被日誌記錄。
autovacuum_max_workers:
同 時啟用的autovacuum進程。通常不要設定太高,3個就可以。
autovacuum_naptime:
檢查資料庫的時 間,default1分鐘,不用改動
autovacuum_vacuum_threshold:
最低n行記錄才會引發 autovacuum。也就是資料改變說低於這個值,autovacuum不會運行。default50
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 fullclustervacuum full一樣會重寫table,移除所有的dead row。同樣也是要做exclusive lock
Truncate
Turncat 會刪除一個table中的所有資料, 並且不會造成任何的dead rowdelete則會造成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
跑 個queryexpain 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時候的costdefault1,如果記憶體,cacheshared buffer設定正確。那麼這個default的值太低了,可以增加
random_page_cost
planner random page fetch的值。default4.0 如果記憶體,cacheshared buffer設定正確,那麼這個值太高了,可以降低
seq_page_costrandom_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
full text的時候,用gin不要用 gist

沒有留言: