POSTGRESQL 是一個對象關系型數據庫，由來自全球一組網絡開發者開發。它是一個可代替如Oracle、Informix商業數據庫的開源版本。

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

性能概念

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

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

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

存儲區域 容量

CPU寄存器 幾字節

CPU高速緩存 幾千字節

RAM 幾兆字節

硬盤 幾千兆字節

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

縮短數據與 CPU 的距離

數據在各種存儲區域的轉移是自動執行的。編譯器決定哪些數據存在寄存器里頭。CPU 決定哪些數據存在緩存里面。 操作系統負責內存和硬盤之間的數據交換。

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

看似簡單， 其實不然， 內存中的數據包含很多東西：

正在執行中的程序

程序的數據和堆棧

POSTGRESQL 共享緩存

內核磁盤緩存

內核

理想的性能調整， 既要增加內存中的數據庫數據占有量，又不能對系統造成負面影響。

POSTGRESQL 共享緩存

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

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

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

多大才算太大?

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

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

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

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

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

合適容量的共享緩存

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

它應該足夠大來應付通常的表訪問操作。

它應該足夠小來避免 swap pagein 的發生。

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

批量排序的內存規模

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

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

緩存規模和排序規模

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

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

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

有價值的服務器參數是effective_cache_size。這個參數被優化器用來估計內核磁盤緩存的大小。在使用統一緩存的內核里，這個值應該設為內核未使用RAM的平均值，因為這樣內核就可以使用未使用的RAM來緩存最近訪問的磁盤頁。在有固定磁盤緩存的內核里，這個值應該設為內核緩存的大小，一般為RAM的10%。

Disk Locality

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

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

多磁盤

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

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