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的存儲區 域：

存儲區域 容量

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 這種系統分析工具來查看， 是否有足夠的內存， 維持系統的正常運作。不要把交換區頁面移出，跟常規的頁面移出搞混了。常規的頁面移出， 將頁面數據從文件系統中讀出來，當作是系統操作的一部分。如果你看不出， 是否有交換區頁面移出操作。但是交換區頁面移入的操作非常活躍， 這也說明，有大量的頁面移出的操作正在進行。

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

或許你會想為什么高速緩存的大小如此重要。首先，試想一下PostgreSQL共享緩存大到可以放下整張表。重復連續掃描這張表就不需要硬盤的參 與，因為數據已經在cache里了。現在假設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 數據文件移到其他磁盤. 注意, 別把文件移到同一個磁盤的其他文件系統. 因為同一個磁盤上的所有文件系統共享一個磁頭.

數據庫分布到不同磁盤的方式, 有下列幾種:

• 轉移數據庫, 表, 索引(Databases,_Tables,_Indexes)

• 利用表空間(Tablespace)在不同磁盤上創建對象.

• 轉移預寫日志

• 通過 initdb -X 和 符號鏈接文件(symbolic link) 將 pg_xlog 目錄移到其他磁盤. 跟其他寫操作不同, POSTGRESQL 日志寫操作, 必須在事務結束前, 提交給磁盤. 就算使用緩存, 也無法推遲這些寫操作. 在不同磁盤上保存日志, 可以減少磁頭的移動造成的延時.  ( postgres -F 參數 可以關閉"日志保存(在磁盤上)"這項功能.  但是, 如果遇到操作系統崩潰, 只能通過備份文件恢復.)

其他方法還有, 利用 RAID 磁盤陣列將單個文件系統分布到多個磁盤中. 雖然, 鏡像導致數據庫寫入速度變慢, 但是可以提高讀取的速度.  因為, 數據可以同時從多個磁盤上讀出來. 很多網站都喜歡用 RAID 1+0 或 RAID0+1, 原因是, 它有分段操作提高性能, 鏡像文件保障可靠性. RAID 5 在磁盤數量不少于 6 個的時候, 速度最快. 理論上, RAID5 都有用電池做后備電源的寫緩存, 所以磁盤寫入操作效率很高, 不至于會拖程序的后腿.

磁盤緩沖

現在的磁盤都有讀和寫緩沖。讀緩沖保存最近的讀請求在磁盤的內存里面。 寫緩沖保存最近的寫請求， 知道他們能有效地存到磁盤上。可能你已經看到了， 這里有個問題 -- 要是寫緩沖區里面的數據還沒來得及存到磁盤上， 就斷電了怎么辦？有些磁盤和 RAID 控制器， 都有電池做后備電源， 能將寫緩存里面的數據保持到供電回復位置。不過， 多數磁盤和 RAID 控制器都沒有這個功能， 所以可靠性是個問題。  

好在， 大部分磁盤都可以關閉寫緩存。SCSI 磁盤寫緩存一般都是關閉的。 IDE 磁盤的寫緩存默認是開啟的， 但是可以在操作系統中， 使用命令行 hdparm -W0, sysctl hw.ata.wc = 0, 或 scsicmd 關閉。那是， 有些 IDE 磁盤雖然提示寫緩存已經關閉， 其實還是在用。結果導致磁盤變得不穩定。沒有代價高昂的測試， 是很難看出來磁盤的寫緩存是否真的關閉了。 

由于 PostgreSQL 每次都要調用  fsync() 將預寫日志寫入磁盤，并等到日志寫操作執行完畢，才提交事務。所以， 如果使用寫緩存，用戶會發現， 性能變快了很多。 因此，對于 PostgreSQL 來說，從性能和可靠性這兩方面衡量，最好能使用有電池做后備電源的寫緩存。

SCSI vs. IDE

SCSI 盤通常要比 IDE 磁盤貴的多. SCSI 磁盤有個協議, 用于控制器和操作系統之間通信. 而 IDE 磁盤則簡單得多, 一次只能接受一個請求.  SCSI 磁盤的帶標隊列(tagged queueing) 能同時接受多個請求, 并自動排序, 以便提高效率. 這是為什么, 在單用戶, 單文件操作的時候,  SCSI 和 IDE 磁盤在性能上如此相似。不過在多用戶, 多處理器的情況下,  SCSI 性能要比 IDE 好得多。也正是這個原因, SCSI 更適合用在高負載的數據庫服務器上。

其實, SCSI 和 IDE 的差別就只有一種 -- 一個是專為高性能, 高可靠性設計的企業級磁盤；另外一個是優先考慮成本的個人電腦磁盤. 這篇文章, http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf, 詳細的描述了, 廠商是如何以性能, 可靠性和成本為衡量標準, 生產制造磁盤的.  是一篇相當不錯的磁盤選購指南。

文件系統

一些操作系統支出多磁盤文件系統。一些情況下，這將很難看到哪一個文件系統最好。PostgresSQL通常在傳統的Unix文件系統中表現最好，比如很多操作系統支持的BSD UFS/FFS 文件系統。UFS默認的8K塊大小和PostgresSQL的分頁大小一樣。你可以在其上運行日志文件系統或基于日志的文件系統，但是這會增加fsync的先寫日志開銷。稍早的基于SvR3的文件系統變得太破碎化而無法達到很高的性能。

Linux的文件系統實在太多了，因此很難做出選擇。并且沒有一個是十全十美的：ext2不是完全崩潰安全，ext3,XFS和JFSare 是基 于日志的，而Reiser對小文件很完美而且也登載日志。journalling文件系統比ext2慢了一大截，不過它支持崩潰恢復，ext2最好別用。 如果必須要用ext2的話，給它設置下同步。有些人建議ext3系統應該設置data=writebck。

NFS和別的遠程文件系統不推薦用在PostgresSQL上。NFS的文件系統的 語義和本地文件系統的語義不同，而這些差別將導致數據可靠度和奔潰恢復的出現問題。

多中央處理器

POSTRGESQL使用多進程模式，意味著每一個數據庫都有自己的處理單元。因此，所有的多中央處理器操作系統都可以通過可用的CPU來spread多數據庫。然后，如果只有一個數據庫連接，那么它只能使用一個CPU。POSTGRESQL不允許使用多線程來讓一個進程使用多個CPU。

檢查點（checkpoint 事件）

當先寫日志文件填滿后，一個checkpoint事件會強制所有緩沖塊進入硬盤好讓日志文件再使用，Checkpoints也會定時自動執行，通常時間間隔是5分鐘。如果有大量數據庫寫入，那么先寫文件日志將被迅速填滿，導致極度緩慢，因為所有的緩沖塊都涌向了硬盤。

檢查點（checkpoint事件）

checkpoint應該每幾分鐘產生一次。如果一分鐘產生好幾次的話，性能將會變差。為了判斷checkpoint是否過于頻繁，檢查由checkpoint_warning產生的日志信息。如果你的checkpoint每30s內不止一次就會產生這個信息。

減少checkpoint頻率包括增加在data/pg_xlog的先寫日志文件。每一個文件為16M，因此對硬盤的影響是可見的。默認安裝使用了最小數量的日志文件。為了減少checkpoint的頻率，你需要增加這個參數：

• checkpoint_segment=3

默認的值是3，增加這個值直到checkpoint每幾分鐘才產生一次。另一個日志文件：

• LOG：XLogWrite:new log file created-consider increasing WAL_FILES

這條信息表明postgresql.conf中的wal_files參數應該增加。

總結

幸運的是，POSTGERSQL不需要太多的調整。大部分參數會自動 調整以維持最佳性能。管理員也可以控制高速緩存的大小和排序規模的大小來優化可用內存的使用。硬盤存取也可通過驅動延展。其它的參數也可以通過 share/pstgresql/conf.sample設置。你可以復制此文件到data/postgresql.conf來嘗試POSTGRESQL的一些更另類的參數。