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》這本書中已經(jīng)討論過了。

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

　　存儲區(qū)域 容量

CPU寄存器 幾字節(jié)

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

RAM 幾兆字節(jié)

硬盤 幾千兆字節(jié)

你可以看到儲存大小隨著離CPU距離的增加而增加。理論上，大容量的永久存儲可以被安置在CPU的旁邊，但是這將變的很慢而且很昂貴。實(shí)際當(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ù)量， 從而減少磁盤訪問來獲得。

看似簡單， 其實(shí)不然， 內(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）。說它是個問題， 是因?yàn)椋?當(dāng)頁面移入內(nèi)存的時候， 程序需要終止執(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ū)頁面移入的操作非常活躍， 這也說明，有大量的頁面移出的操作正在進(jìn)行。

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

或許你會想為什么高速緩存的大小如此重要。首先，試想一下PostgreSQL共享緩存大到可以放下整張表。重復(fù)連續(xù)掃描這張表就不需要硬盤的參與，因?yàn)閿?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容量會戲劇性的改變性能。

合適容量的共享緩存

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

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

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

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

內(nèi)存排序隊列大小

另一個優(yōu)化參數(shù)是排序隊列的內(nèi)存總量。當(dāng)對大表或一個記錄集排序時，PostgerSQL會將他們分塊排序，將中間結(jié)果存儲在臨時文件里。這些文件將在所有隊列處理完畢后合并使用。增加每一隊列的大小就會產(chǎn)生更少的臨時文件同時加快處理速度。然后，如果隊列過大，部分內(nèi)存隊列就會在處理過程中pageout到虛擬內(nèi)存從而導(dǎo)致pagein的發(fā)生。因此，用小點(diǎn)的隊列產(chǎn)生更多的臨時文件會快很多，但又一次，當(dāng)太多內(nèi)存被分配時，虛擬內(nèi)存pagein產(chǎn)生。記住這個參數(shù)是后端使用在處理批次上的，而不是 ORDER BY, CREATE INDEX或合并。多少同時的排序就會用多少倍這么多的內(nèi)存。

這個值可以通過數(shù)據(jù)庫管理器命令行標(biāo)志改變或通過改變在postgresql.conf中sort_mem的值來改變。

緩存規(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)熟練才行。更多信息， 參考 PostgerSQL管理員操作手冊。

在調(diào)整的開始，使用15%的RAM作為緩存大小，如果有幾個大的事物就用2-4%的內(nèi)存做排序大小，如果你有很多小事物的話就使用更小的內(nèi)存。你可以嘗試提高它來看看性能是否提升，swapping交換是否發(fā)生。如果共享緩存過大，你就花費(fèi)太多時間來維護(hù)大量的緩存，而且它會浪費(fèi)掉本可以被其他進(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的平均值，因?yàn)檫@樣內(nèi)核就可以使用未使用的RAM來緩存最近訪問的磁盤頁。在有固定磁盤緩存的內(nèi)核里，這個值應(yīng)該設(shè)為內(nèi)核緩存的大小，一般為RAM的10%。

Disk Locality

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

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

多磁盤

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

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

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

轉(zhuǎn)移數(shù)據(jù)庫, 表, 索引(Databases,_Tables,_Indexes)

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

轉(zhuǎn)移預(yù)寫日志

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

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

磁盤緩沖

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

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

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

SCSI vs. IDE

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

其實(shí), SCSI 和 IDE 的差別就只有一種 -- 一個是專為高性能, 高可靠性設(shè)計的企業(yè)級磁盤；另外一個是優(yōu)先考慮成本的個人電腦磁盤。這篇文章詳細(xì)的描述了，廠商是如何以性能, 可靠性和成本為衡量標(biāo)準(zhǔn), 生產(chǎn)制造磁盤的. 是一篇相當(dāng)不錯的磁盤選購指南。

文件系統(tǒng)

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

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

NFS和別的遠(yuǎn)程文件系統(tǒng)不推薦用在PostgresSQL上。NFS的文件系統(tǒng)的 語義和本地文件系統(tǒng)的語義不同，而這些差別將導(dǎo)致數(shù)據(jù)可靠度和奔潰恢復(fù)的出現(xiàn)問題。

多中央處理器

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

檢查點(diǎn)（checkpoint 事件）

當(dāng)先寫日志文件填滿后，一個checkpoint事件會強(qiáng)制所有緩沖塊進(jìn)入硬盤好讓日志文件再使用，Checkpoints也會定時自動執(zhí)行，通常時間間隔是5分鐘。如果有大量數(shù)據(jù)庫寫入，那么先寫文件日志將被迅速填滿，導(dǎo)致極度緩慢，因?yàn)樗械木彌_塊都涌向了硬盤。

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

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

checkpoint_segment=3

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

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

這條信息表明postgresql.conf中的wal_files參數(shù)應(yīng)該增加。

總結(jié)

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