POSTGRESQL 是一個對象關(guān)系型數(shù)據(jù)庫，由來自全球一組網(wǎng)絡(luò)開發(fā)者開發(fā)。它是一個可代替如Oracle、Informix商業(yè)數(shù)據(jù)庫的開源版本。

POSTGRESQL 最初由加州大學(xué)伯克利分校開發(fā)。1996年，一個小組開始在互聯(lián)網(wǎng)上開發(fā)該數(shù)據(jù)庫。他們使用email分享想法，用文件服務(wù)器分享代碼。POSTGRESQL現(xiàn)在在功能方面、性能方面以及可靠性上可與商業(yè)數(shù)據(jù)庫比肩。它支持事務(wù)、視圖、存儲過程和參考完整性約束。它也支持大量的編程接口，包括ODBC、Java(JDBC)、TCL/TK、PHP、Perl以及Python。得益于互聯(lián)網(wǎng)開發(fā)者人才庫，POSTGRESQL 還有廣闊的增長空間。

性能概念

數(shù)據(jù)庫性能優(yōu)化有兩個方面。一方面是提高數(shù)據(jù)庫對電腦CPU,內(nèi)存和硬盤的使用。另一方面是最優(yōu)化傳遞到數(shù)據(jù)庫的查詢。這篇文章討論的是在硬件方面優(yōu)化數(shù)據(jù)庫性能。通過使用例如：CREATE INDEX,VACUUM,VACUUM FULL,ANALYZE,CLUSTER和EXPLAIN這些數(shù)據(jù)庫SQL命令，插敘查詢的最優(yōu)化已經(jīng)完成了。這些在我寫的《PostgreSQL:Introduction and Concepts》（http://momjian.us/main/writings/pgsql/aw_pgsql_book/）這本書中已經(jīng)討論過了。

為了理解硬件性能的問題，就必須理解在電腦的內(nèi)部發(fā)生了什么。簡單的說，一臺電腦可以被視為一個被存儲器包圍的中央處理單元（CPU)。在和CPU同一小片上的是不同的寄存器，它們保存了中間運算結(jié)果和各種指針以及計數(shù)器。包圍這些的是CPU cache，其中有最新的訪問信息。越過CPU cache是大量的隨機(jī)存取存儲器（RAM），它保存了正在運行的程序以及數(shù)據(jù)。在RAM的外圍就是硬盤了，它保存了更加多的信息。硬盤是唯一可以永久存儲信息的區(qū)域。，所以電腦關(guān)機(jī)后，所有被保存下來的信息都在這里。歸納起來，這些是包圍CPU的存儲區(qū)域：

\includegraphics[height=0.25\textheight]{caches}

存儲區(qū)域 容量

CPU寄存器 幾字節(jié)

CPU高速緩存 幾千字節(jié)

RAM 幾兆字節(jié)

硬盤 幾千兆字節(jié)

你可以看到儲存大小隨著離CPU距離的增加而增加。理論上，大容量的永久存儲可以被安置在CPU的旁邊，但是這將變的很慢而且很昂貴。實際當(dāng)中，最常用的信息被放在CPU的旁邊，而不怎么用的信息就放得離CPU遠(yuǎn)遠(yuǎn)的。在CPU需要的時候再拿給CPU。

縮短數(shù)據(jù)與 CPU 的距離

數(shù)據(jù)在各種存儲區(qū)域的轉(zhuǎn)移是自動執(zhí)行的。編譯器決定哪些數(shù)據(jù)存在寄存器里頭。CPU 決定哪些數(shù)據(jù)存在緩存里面。 操作系統(tǒng)負(fù)責(zé)內(nèi)存和硬盤之間的數(shù)據(jù)交換。

數(shù)據(jù)庫管理員對 CPU 的寄存器和緩存無能為力。要提高數(shù)據(jù)庫的性能，只能通過增加內(nèi)存中的有用數(shù)據(jù)量， 從而減少磁盤訪問來獲得。

看似簡單， 其實不然， 內(nèi)存中的數(shù)據(jù)包含很多東西：

正在執(zhí)行中的程序

程序的數(shù)據(jù)和堆棧

POSTGRESQL 共享緩存

內(nèi)核磁盤緩存

內(nèi)核

理想的性能調(diào)整， 既要增加內(nèi)存中的數(shù)據(jù)庫數(shù)據(jù)占有量，又不能對系統(tǒng)造成負(fù)面影響。

POSTGRESQL 共享緩存

POSTGRESQL 沒有直接訪問磁盤，而是訪問 POSTGRESQL 的緩存。然后再由 POSTGRESQL 的后臺程序讀寫這些數(shù)據(jù)塊， 最后寫到磁盤上。

后臺首先在表中，查找緩存是否已經(jīng)存在這些數(shù)據(jù)。 有， 就繼續(xù)處理。沒有， 則由操作系統(tǒng)從內(nèi)核磁盤緩存， 或者直接從磁盤加載這些數(shù)據(jù)。無論哪一種，代價都很高。

POSTGRESQL 默認(rèn)分配 1000 個緩存。每個緩存有 8k 字節(jié)。增加緩存的數(shù)量，能增加后臺訪問緩存的頻率，減少代價較高的系統(tǒng)請求。緩存的數(shù)量，可以通過 postmaster 命令行的參數(shù)， 或者配置文件 postgresql.conf 中的 shared_buffers 的值來設(shè)置。

多大才算太大?

你可能在想， “那我把所有的內(nèi)存都分配給 POSTGRESQL 的緩沖區(qū)好了”。 如果你這么做， 那系統(tǒng)內(nèi)核以及其他程序就沒有內(nèi)存可用了。理想的 POSTGRESQL 共享緩沖區(qū)大小，是在沒有對系統(tǒng)產(chǎn)生不利影響的情況下， 越大越好。

要理解什么是不利影響，首先要明白 UNIX 是如何管理內(nèi)存的。要是內(nèi)存容量足夠大，能容下所有的程序和數(shù)據(jù)。 那我們也就用不著管理內(nèi)存了。問題是， 內(nèi)存的容量有限，所以， 需要內(nèi)核將內(nèi)存中的數(shù)據(jù)分頁， 存入磁盤，這就是傳說的的數(shù)據(jù)交換。原理是， 將當(dāng)前用不上的數(shù)據(jù)移到磁盤中。這個操作叫做交換區(qū)頁面移入（swap pageout）。頁面移入交換區(qū)不難，只要在程序非活躍期執(zhí)行就可以。問題在于， 頁面重新從交換區(qū)移出來的時候。 也就是， 移到交換區(qū)的舊頁面， 又重新移回內(nèi)存。這個操叫交換區(qū)移出（ swap pagein）。說它是個問題， 是因為， 當(dāng)頁面移入內(nèi)存的時候， 程序需要終止執(zhí)行， 直到移入操作完成。

系統(tǒng)的頁面移入活躍情況， 可以通過像 vmstatand sar 這種系統(tǒng)分析工具來查看， 是否有足夠的內(nèi)存， 維持系統(tǒng)的正常運作。不要把交換區(qū)頁面移出，跟常規(guī)的頁面移出搞混了。常規(guī)的頁面移出， 將頁面數(shù)據(jù)從文件系統(tǒng)中讀出來，當(dāng)作是系統(tǒng)操作的一部分。如果你看不出， 是否有交換區(qū)頁面移出操作。但是交換區(qū)頁面移入的操作非?；钴S， 這也說明，有大量的頁面移出的操作正在進(jìn)行。

高速緩存（cache)容量的影響

或許你會想為什么高速緩存的大小如此重要。首先，試想一下PostgreSQL共享緩存大到可以放下整張表。重復(fù)連續(xù)掃描這張表就不需要硬盤的參與，因為數(shù)據(jù)已經(jīng)在cache里了。現(xiàn)在假設(shè)cache比表小一個單元。一次連續(xù)的掃描將會把所有單元載入cache直到最后一個單元。當(dāng)需要最后一個單元時，最初的單元被移除。當(dāng)另一次連續(xù)掃描開始的時候，最初的單元已經(jīng)不再cache里了，為了載入它，最開始的單元會被移除，也就是第一次掃描時的第二個單元會被移除。這將持續(xù)進(jìn)行到單元結(jié)束。這個例子很極端，但是你可以看到減少一個單元就將會把cache的效率從100%變?yōu)?%。這表明找到合適的cache容量會戲劇性的改變性能。

合適容量的共享緩存

理論上，POSTGERSQL共享緩存將是：

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

它應(yīng)該足夠小來避免 swap pagein 的發(fā)生。

記住數(shù)據(jù)庫管理器運行時分配所有的共享存儲。這一區(qū)域即使在沒有訪問數(shù)據(jù)庫的請求時也保持一樣大小。一些操作系統(tǒng)pageout未指定的共享存儲，而另一些LOCK共享存儲到RAM中。LOCK貢獻(xiàn)存儲更好一點。P OSTGERSQL的管理員指導(dǎo)手冊里有關(guān)于不同操作系統(tǒng)核心配置的信息， http://developer.postgresql.org/docs/postgres/kernel-resources.html。

批量排序的內(nèi)存規(guī)模

另一項能調(diào)節(jié)性能的參數(shù)是， 用做批量排序的內(nèi)存容量。當(dāng)對大量數(shù)據(jù)排序時， POSTGRESQL 會將他們拆分成許多小的數(shù)據(jù)塊進(jìn)行排序。然后將中間結(jié)果存在臨時文件里面。這些文件最終被合并，重新排序，直到所有的數(shù)據(jù)行的排序完畢。增加批量處理的內(nèi)存規(guī)模， 能減少臨時文件的數(shù)量。從而提高排序速度。不過， 如果批量處理的規(guī)模設(shè)置太大， 會導(dǎo)致交換區(qū)的分頁操作變得更頻繁。這種情況下，使用大量臨時文件的小規(guī)模批量排序速度比較快。所以， 由交換區(qū)分頁活躍程度， 決定內(nèi)存是不是被過量分配。記住， 這個參數(shù)是給后臺執(zhí)行排序用的。如： ORDER BY， CREATE INDEX，或者數(shù)據(jù)合并。有幾個并行排序任務(wù)， 就需要幾倍這樣的內(nèi)存容量。

這個參數(shù)的值， 可以通過 postmaster 命令行參數(shù)， 或者配置文件 postgresql.conf 中的 sort_mem 來設(shè)置。

緩存規(guī)模和排序規(guī)模

緩存規(guī)模和排序規(guī)模都會影響內(nèi)存的使用。你不可能增加一個的規(guī)模， 而不影響另外一個。記住，緩存的規(guī)模是在 postmaster 啟動的時候， 就設(shè)好的。 而排序的規(guī)模擇由排序的數(shù)量決定。一般情況下，緩存規(guī)模要大過排序的規(guī)模。不過， 某些用到 ORDER BY， CREATE INDEX 或數(shù)據(jù)合并的查詢， 可以通過加大排序規(guī)模來提速。

此外， 許多操作系統(tǒng)對共享內(nèi)存的分配有限制。修改這一限制， 就意味著， 要重新編譯或者配置內(nèi)核。也就是說， 你要對操作系統(tǒng)這方面相當(dāng)熟練才行。更多信息， 參考 POSTGRESQL 管理員操作手冊，http://developer.postgresql.org/docs/postgres/kernel-resources.html.

在調(diào)整的開始，使用15%的RAM作為緩存大小，如果有幾個大的事物就用2-4%的內(nèi)存做排序大小，如果你有很多小事物的話就使用更小的內(nèi)存。你可以嘗試提高它來看看性能是否提升，swapping交換是否發(fā)生。如果共享緩存過大，你就花費太多時間來維護(hù)大量的緩存，而且它會浪費掉本可以被其他進(jìn)程使用的RAM，無法作為額外的內(nèi)核磁盤的緩存。

有價值的服務(wù)器參數(shù)是effective_cache_size。這個參數(shù)被優(yōu)化器用來估計內(nèi)核磁盤緩存的大小。在使用統(tǒng)一緩存的內(nèi)核里，這個值應(yīng)該設(shè)為內(nèi)核未使用RAM的平均值，因為這樣內(nèi)核就可以使用未使用的RAM來緩存最近訪問的磁盤頁。在有固定磁盤緩存的內(nèi)核里，這個值應(yīng)該設(shè)為內(nèi)核緩存的大小，一般為RAM的10%。

Disk Locality

磁盤本身的特點， 決定了他的性能跟上面提到的其他存儲方式不同。別的存儲方式， 訪問數(shù)據(jù)中的任何一個字節(jié)， 速度都是一樣的。 而磁盤，由于磁盤片在不斷的轉(zhuǎn)動， 磁頭在不斷的移動，訪問離磁頭當(dāng)前位置近的數(shù)據(jù)， 速度要比離磁頭遠(yuǎn)的數(shù)據(jù)快。

磁頭從一個柱面， 移動到同一個磁盤片的另外一個柱面， 比較耗時間。Unix 內(nèi)核開發(fā)人員當(dāng)然知道這一點。所以在磁盤上存儲大文件的時候，他們盡可能把同一個文件的存儲塊緊挨在一起存放。例如：我們有一個文件， 在磁盤上保存， 需要占10個存儲塊。操作系統(tǒng)會把 1-5 存儲塊放在一個柱面， 而 6-10 存在另外一個柱面。從頭到尾讀取這個文件， 只需要磁頭移動兩次 -- 一次移到存放 1-5 存儲塊的柱面， 另外一次移到存放 6-10 那個柱面。但是， 如果文件的讀取不按存儲塊的順序來，比如 1,6,2,7,3,8,4,9,5,10， 那么讀完整個文件就要移動磁頭十次。 所以， 對于磁盤來說，按順序訪問要比隨機(jī)訪問快的多。這也是為什么， POSTGRESQL 在讀取表中的大量數(shù)據(jù)時， 寧可選擇順序掃描， 也不用索引掃描。 磁盤的這個缺點， 讓我們看到了緩存的價值。

多磁盤

數(shù)據(jù)庫操作期間, 磁頭會頻繁移動. 太多的讀/寫請求, 會導(dǎo)致磁盤隊列飽和, 性能急劇下降. (我們可以通過 Vmstat 和 sar 這兩種工具, 查看磁盤的活動情況 )

其中一個解決磁盤隊列飽和的辦法是, 將部分 POSTGRESQL 數(shù)據(jù)文件移到其他磁盤. 注意, 別把文件移到同一個磁盤的其他文件系統(tǒng). 因為同一個磁盤上的所有文件系統(tǒng)共享一個磁頭.