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

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

性能概念

數(shù)據(jù)庫性能優(yōu)化有兩個(gè)方面。一方面是提高數(shù)據(jù)庫對(duì)電腦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ā)生了什么。簡單的說，一臺(tái)電腦可以被視為一個(gè)被存儲(chǔ)器包圍的中央處理單元（CPU)。在和CPU同一小片上的是不同的寄存器，它們保存了中間運(yùn)算結(jié)果和各種指針以及計(jì)數(shù)器。包圍這些的是CPU cache，其中有最新的訪問信息。越過CPU cache是大量的隨機(jī)存取存儲(chǔ)器（RAM），它保存了正在運(yùn)行的程序以及數(shù)據(jù)。在RAM的外圍就是硬盤了，它保存了更加多的信息。硬盤是唯一可以永久存儲(chǔ)信息的區(qū)域。，所以電腦關(guān)機(jī)后，所有被保存下來的信息都在這里。歸納起來，這些是包圍CPU的存儲(chǔ)區(qū)域：

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

存儲(chǔ)區(qū)域 容量

CPU寄存器 幾字節(jié)

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

RAM 幾兆字節(jié)

硬盤 幾千兆字節(jié)

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

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

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

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

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

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

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

POSTGRESQL 共享緩存

內(nèi)核磁盤緩存

內(nèi)核

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

POSTGRESQL 共享緩存

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

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

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

多大才算太大?

你可能在想， “那我把所有的內(nèi)存都分配給 POSTGRESQL 的緩沖區(qū)好了”。 如果你這么做， 那系統(tǒng)內(nèi)核以及其他程序就沒有內(nèi)存可用了。理想的 POSTGRESQL 共享緩沖區(qū)大小，是在沒有對(duì)系統(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ù)移到磁盤中。這個(gè)操作叫做交換區(qū)頁面移入（swap pageout）。頁面移入交換區(qū)不難，只要在程序非活躍期執(zhí)行就可以。問題在于， 頁面重新從交換區(qū)移出來的時(shí)候。 也就是， 移到交換區(qū)的舊頁面， 又重新移回內(nèi)存。這個(gè)操叫交換區(qū)移出（ swap pagein）。說它是個(gè)問題， 是因?yàn)椋?當(dāng)頁面移入內(nèi)存的時(shí)候， 程序需要終止執(zhí)行， 直到移入操作完成。

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

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

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

合適容量的共享緩存

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

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

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

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

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

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

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

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

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

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

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

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

Disk Locality

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

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

多磁盤

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

其中一個(gè)解決磁盤隊(duì)列飽和的辦法是, 將部分 POSTGRESQL 數(shù)據(jù)文件移到其他磁盤. 注意, 別把文件移到同一個(gè)磁盤的其他文件系統(tǒng). 因?yàn)橥粋€(gè)磁盤上的所有文件系統(tǒng)共享一個(gè)磁頭.