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》這本書中已經(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ū)域：

　　存儲(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ù)在各種存儲(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的后臺(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)行。

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

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

它應(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)。PostgerSQL的管理員指導(dǎo)手冊(cè)里有關(guān)于不同操作系統(tǒng)核心配置的信息。

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

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

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

在調(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%。

磁盤本身的特點(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ī)訪問快的多。這也是為什么， PostgerSQL在讀取表中的大量數(shù)據(jù)時(shí)， 寧可選擇順序掃描， 也不用索引掃描。 磁盤的這個(gè)缺點(diǎn)， 讓我們看到了緩存的價(jià)值。

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

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

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

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

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

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

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

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

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

好在， 大部分磁盤都可以關(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)定。沒有代價(jià)高昂的測(cè)試， 是很難看出來磁盤的寫緩存是否真的關(guān)閉了。

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

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

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

一些操作系統(tǒng)支出多磁盤文件系統(tǒng)。一些情況下，這將很難看到哪一個(gè)文件系統(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)，但是這會(huì)增加fsync的先寫日志開銷。稍早的基于SvR3的文件系統(tǒng)變得太破碎化而無法達(dá)到很高的性能。

Linux的文件系統(tǒng)實(shí)在太多了，因此很難做出選擇。并且沒有一個(gè)是十全十美的：ext2不是完全崩潰安全，ext3,XFS和JFSare 是基于日志的，而Reiser對(duì)小文件很完美而且也登載日志。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)程模式，意味著每一個(gè)數(shù)據(jù)庫都有自己的處理單元。因此，所有的多中央處理器操作系統(tǒng)都可以通過可用的CPU來spread多數(shù)據(jù)庫。然后，如果只有一個(gè)數(shù)據(jù)庫連接，那么它只能使用一個(gè)CPU。PostgresSQL不允許使用多線程來讓一個(gè)進(jìn)程使用多個(gè)CPU。

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

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

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

checkpoint_segment=3

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

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

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

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