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是大量的隨機存取存儲器（RAM），它保存了正在運行的程序以及數(shù)據(jù)。在RAM的外圍就是硬盤了，它保存了更加多的信息。硬盤是唯一可以永久存儲信息的區(qū)域。，所以電腦關(guān)機后，所有被保存下來的信息都在這里。歸納起來，這些是包圍CPU的存儲區(qū)域：

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

存儲區(qū)域 容量

CPU寄存器 幾字節(jié)

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

RAM 幾兆字節(jié)

硬盤 幾千兆字節(jié)

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

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

數(shù)據(jù)在各種存儲區(qū)域的轉(zhuǎn)移是自動執(zhí)行的。編譯器決定哪些數(shù)據(jù)存在寄存器里頭。CPU 決定哪些數(shù)據(jù)存在緩存里面。 操作系統(tǒng)負責(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)造成負面影響。

POSTGRESQL 共享緩存

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

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

POSTGRESQL 默認分配 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ù)交換。原理是， 將當前用不上的數(shù)據(jù)移到磁盤中。這個操作叫做交換區(qū)頁面移入（swap pageout）。頁面移入交換區(qū)不難，只要在程序非活躍期執(zhí)行就可以。問題在于， 頁面重新從交換區(qū)移出來的時候。 也就是， 移到交換區(qū)的舊頁面， 又重新移回內(nèi)存。這個操叫交換區(qū)移出（ swap pagein）。說它是個問題， 是因為， 當頁面移入內(nèi)存的時候， 程序需要終止執(zhí)行， 直到移入操作完成。

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

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

或許你會想為什么高速緩存的大小如此重要。首先，試想一下PostgreSQL共享緩存大到可以放下整張表。重復(fù)連續(xù)掃描這張表就不需要硬盤的參與，因為數(shù)據(jù)已經(jīng)在cache里了。現(xiàn)在假設(shè)cache比表小一個單元。一次連續(xù)的掃描將會把所有單元載入cache直到最后一個單元。當需要最后一個單元時，最初的單元被移除。當另一次連續(xù)掃描開始的時候，最初的單元已經(jīng)不再cache里了，為了載入它，最開始的單元會被移除，也就是第一次掃描時的第二個單元會被移除。這將持續(xù)進行到單元結(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貢獻存儲更好一點。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)存容量。當對大量數(shù)據(jù)排序時， POSTGRESQL 會將他們拆分成許多小的數(shù)據(jù)塊進行排序。然后將中間結(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)這方面相當熟練才行。更多信息， 參考 POSTGRESQL 管理員操作手冊，http://developer.postgresql.org/docs/postgres/kernel-resources.html.

在調(diào)整的開始，使用15%的RAM作為緩存大小，如果有幾個大的事物就用2-4%的內(nèi)存做排序大小，如果你有很多小事物的話就使用更小的內(nèi)存。你可以嘗試提高它來看看性能是否提升，swapping交換是否發(fā)生。如果共享緩存過大，你就花費太多時間來維護大量的緩存，而且它會浪費掉本可以被其他進程使用的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)動， 磁頭在不斷的移動，訪問離磁頭當前位置近的數(shù)據(jù)， 速度要比離磁頭遠的數(shù)據(jù)快。

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

多磁盤

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

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