PostgreSQL 硬體效能調優

標籤：size   排列   sdn   技術分享   ext   odbc   .net   分析工具   down   

PostgreSQL 硬體效能調優

翻譯自文章《PostgreSQL Hardware Performance Tuning》

PostgreSQL是一個由世界範圍內開發人員在互連網上開發的對象-關係型資料庫。她是商務資料庫如Oracle和Informix一個開源替代。

PostgreSQL最初由加大伯克利分校開發。在1996年，一個團隊開始在互連網上開發這個資料庫。他們通過郵件來交流思想並且通過檔案伺服器來共用代碼。PostgresSQL現在在專業特性、效能、可靠性上比得上商務資料庫。她具有事務、視圖、預存程序、引用一致性約束。她支援許多編程介面，包括ODBC、Java(JDBC)、TCL/TK、PHP、Perl和Python。得益於一大批有才乾的網路開發人員，PostgreSQL 繼續以驚人的速度提升 。

效能概念

有兩個方面的資料庫效能調優。一個是提高資料庫伺服器使用的CPU、記憶體、磁碟驅動。第二個是最佳化發送到資料庫的查詢。這篇文章討論硬體方面的效能調優。查詢最佳化可以通過SQL命令如 CREATE INDEX、VACUUM 、VACUUM FULL、ANALYZE、CLUSTER 和 EXPLAIN 來完成。這些在《PostgreSQL:Introduction and Concepts》中討論在 http://momjian.us/main/writings/pgsql/aw_pgsql_book/。

為了理解硬體調優問題，理解電腦裡正在發生什麼是非常重要的。簡單來說，電腦可以想象成一個被儲存包圍著的CPU。在和CPU同一晶片上是幾個用來儲存中間結果和不同指標和計數的CPU寄存器。在這些周圍是用來儲存最近訪問資訊的CPU緩衝。在CPU緩衝外面是大量的用來儲存正在執行程式和資料的隨機訪問主存（RAM）。在主存的外面是儲存更多資訊的磁碟驅動。磁碟驅動是唯一的持久儲存區，所以任何要在關機後保持的資訊都必須放在那裡。總之，在CPU的周圍有這些儲存：

儲存地區	度量單位
CPU 寄存器	bytes
CPU 緩衝	kilobytes
RAM	megabytes
磁碟驅動	gigabytes

你可以看到距離CPU越遠儲存越大。理論上，大量的持久儲存可以放在CPU旁邊，但是會特別慢且比較昂貴。在實踐中，經常被使用的資訊儲存在CPU旁邊，不經常訪問的資訊儲存遠離CPU，當使用時被帶到CPU。

保持資訊靠近CPU

在不同儲存區之間移動資訊是自動發生的。編譯器決定哪些資訊儲存在寄存器中。CPU晶片邏輯保證最近使用的資訊儲存在CPU緩衝中。作業系統控制哪些資訊儲存在RAM並在磁碟機間來回穿梭。

CPU寄存器和CPU緩衝不能被資料庫管理員有效調優。有效資料庫調優涉及在RAM中增加有用的資訊，來儘可能的阻止磁碟訪問。

你可能認為這很容易，但是不是。電腦的RAM包含很多東西：

• 正在執行的程式
• 程式資料和棧
• PostgreSQL shared buffer cache
• 核心磁碟緩衝
• 核心

合適的調優包括在RAM中儘可能多的儲存資料庫資訊，同時不影響作業系統的其他地區

PostgreSQL Shared Buffer Cache

PostgreSQL 不直接更改磁碟上的資訊。相反，它請求讀資料到PostgreSQL shared buffer cache。PostgreSQL後台來讀和寫這些塊，並且最終刷到磁碟上。

需要訪問表的後台先在緩衝中尋找需要的塊。如果它們已經存在，可以馬上繼續處理。如果沒有，一個作業系統請求被發送去載入這些塊。這些塊從核心磁碟緩衝或者直接從磁碟載入。這可能是昂貴的操作。

多大算太大？

你可能想，“我把全部的RAM都給PostgreSQL shared buffer cache”。然而，如果你那樣做，會沒有其他的空間分配給核心和其他任何要啟動並執行程式。合適的PostgreSQL shared buffer cache 是不產生其他不良活動的最大空間。

為了理解不良活動，你需要去理解UNIX 作業系統怎麼管理記憶體。如果有足夠的記憶體去裝進所有的程式和資料，只需要很少的記憶體管理。然而，如果所有東西不能裝進記憶體，核心開始強制記憶體刷出到叫swap磁碟地區。它把最近沒有使用的記憶體頁移出。這個操作叫做swap pageout。Pageouts 不是問題因為它們發生在非啟用時間段。糟糕的是當這些頁必須從swap返回時，意為著移到swap的舊頁必須移入RAM。這種叫 swap pagin。這種情況糟糕因為當頁從swap移動時，程式必須暫停直到pagein完成。

Pagein 活動可以被系統分析工具如vmstat和sar展示，並且暗示沒有足夠的記憶體去有效運行。不要混淆swap pageins和ordinary pageins。它包含的頁是作為的正常的作業系統讀取檔案系統的一部分。如果你不能發現swap pageins，許多pageouts 是一個好的暗示，你也進行中swap pageins。

緩衝大小的影響

你可能好奇為什麼緩衝大小如此重要。首先,假設PostgreSQL shared buffer cache 足夠大去裝下整個表。這張表的重複順序掃描不需要訪問磁碟，因為所有的資料已經在緩衝中了。現在假設緩衝比表小一個塊大小。這個表的順序掃描需要載入所有的表資料區塊進入緩衝直到最後一個。當需要塊時，最老的塊被移出，這種情況就是表的第一個塊。當另外一個順序掃描發生時，第一個塊已經不在緩衝中，需要把它重新裝入，這時最老的塊，也就是這個表的第二個塊被移出緩衝。這又會把下一個需要的塊繼續推出去直到這個表的最後。這是一個極端的例子，但是你可以明白減少一個塊可以把緩衝的效率從100%變為0%。它展示了找到正確的緩衝大小可以極大的影響效能。

合適的Shared Buffer Cache大小

理想的，PostgreSQL shared buffer cache 應該是：

• 足夠大能裝入經常訪問的表
• 足夠小能避免swap pageins活動

記住postmaster分配所有的共用記憶體在它啟動的時候。這片地區保持相同的大小即使沒有一個人訪問資料庫。一些作業系統pageout不涉及共用記憶體，一些鎖定主存中的共用記憶體。鎖定共用記憶體是推薦的。PostgreSQL管理員指南有關於各種作業系統核心參數配置資訊。

批量排序記憶體大小

另外一個調優的參數是批量排序使用的記憶體大小。當排序大表或者結果集,PostgresSQL會分成幾部分排序它們，把中間結果放到臨時檔案中。這些檔案合并並且重新排序直到所有的行完成排序。增加批量大小能減少臨時檔案且經常能加快排序。然而，如果批量排序太大，它們會引起pageins，因為在排序時一部分批量排序pageout到swap區。這種情況下使用更小的批量排序和更多的臨時檔案是更快的。所以再次，swap pageins 指示已經分配了太多的記憶體。記住每個後台排序如ORDER BY,CREATE INDEX,或者merge join都使用這個參數。有幾個並行的排序會使用幾倍的這個記憶體大小。

緩衝大小和排序大小

緩衝大小和排序大小都影響記憶體使用量，所以你不可能最大化一個而不影響另一個。記住緩衝大小在postmaster啟動是分配，而排序大小由正在執行的排序操作的數量決定。通常,緩衝大小比排序大小更重要。然而，可預見的使用ORDER BY ,CREATE INDEX或者merge joins的確定查詢可以被大的批量排序大小加速。

同時,很多作業系統限制了多少共用記憶體可以分配。增加這些限制需要作業系統相關的知識去重新編譯或者重新設定核心。更多的資訊可以在PostgreSQL管理員指南中找到。

作為調優的起點，如果只有一些大的會話，用15%的主存作為緩衝大小，2-4%作為排序大小。如果有很多小的會話可以調的更小。你可以嘗試增加他們去看是否有效能提升同時沒有swap發生。如果shared buffer cache太大，你會浪費維護太多緩衝的管理開銷，並且佔用其他進程和額外的核心磁碟緩衝的RAM。

一個有價值的參數是effective_cache_size。調優者使用這個參數去估計核心磁碟緩衝的大小。在不固定緩衝大小的核心中，這個參數可以設定成未使用RAM的平均大小，因為這些核心使用未使用的RAM去緩衝最近訪問的磁碟頁。在其他固定大小磁碟緩衝的核心中，通常應該把你的核心緩衝設定成10%RAM大小。

磁碟局限性

磁碟驅動的物理特性導致了不同於文章中提到的其他儲存地區的效能特點。其他的儲存地區可以以相同速度訪問任何位元組。磁碟驅動有它自己的自旋盤和移動頭，訪問在磁頭附近的資料比距離遠的資料快的多。

移動磁頭去盤面的另外磁柱需要花費一些時間。Unix開發人員知道這個特性。當排序磁碟上的大檔案，他們嘗試把檔案的各個條放在一起。例如，假設一個檔案需要10個磁碟塊。作業系統可能把1-5塊放在一個磁柱上，6-10塊放在另外磁柱上。如果這個檔案需要從頭讀到未，只需要磁頭移動兩次。--一次擷取1-5塊的磁柱，一次擷取6-10塊。然而，如果檔案不是順序讀，如塊1，6，2，7，3，8，4，9，5，10；需要10次磁頭移動。你可以看到，在磁碟上，順序讀比隨機讀快很多。這是為什麼如果一個表的大部分內容需要被讀，PostgreSQL偏向於順序掃描而不是索引掃描。這也凸顯了緩衝的價值。

多磁碟Spindles

磁頭在資料庫活動期間移動相當多。如果產生太多的讀寫請求，驅動會變得飽和，導致效能不佳。（vmstat和sar可以提供每個磁碟活動量的資訊）

一個磁碟飽和的解決方案是移動一些PostgreSQL的資料檔案到其他磁碟。記著，移動檔案到同一磁碟的其他檔案系統沒有協助。所有在同一磁碟的檔案系統使用同一個磁頭。

通過磁碟驅動的資料庫訪問可以通過以下幾個方法加速：
移動資料庫、表、索引
資料表空間允許你在不同的驅動上建立對象。
移動WAL
initdb -X 和符號串連可以用來移動pg_xlog目錄到不同的磁碟驅動。不像其他的寫操作，PostgreSQL 寫日誌必須在事務完成前刷到磁碟。緩衝不能用來延遲這些寫操作。為寫日誌使用單獨的磁碟可以允許磁頭停留在當前日誌的磁柱而沒有移動延遲。（你可以使用postgres -F 參數來阻止日誌刷到磁碟，但是一個作業系統災難需要從備份中恢複）

其他的選項包括使用磁碟陣列，通過多個磁碟驅動加速一個檔案系統。鏡像會降低資料庫寫速度，但是會加速資料庫讀，因為資料可以從每個驅動中獲得。許多網站使用RAID1+0或RAID0+1，因為它同時提供了效能和可靠的優勢。RAID5對於6個或者更多驅動來說是更快的。理論上，RAID 5將有一個由電池支援的快取，因此寫入可以以一種有效方式重新整理到磁碟，而不是在編寫檔案的時候減慢應用程式的速度。

磁碟驅動緩衝

現代磁碟驅動都有讀和寫緩衝。讀緩衝保證最近請求的磁碟塊在磁碟記憶體中可用。寫緩衝儲存寫請求直到它們可以有效儲存到盤面上。你可能意識到這可能產生問題--如果磁碟斷電了，當它持有的寫資料還沒寫到磁碟上怎麼辦？一些磁碟驅動和RAID控制器有後備電池寫緩衝來保證資料安全，且當電源完全恢複時把資料寫到盤面。然而，大部分驅動沒有這種特性，因此它們是不可靠的。

幸運的是，在大多數驅動你可以關閉寫緩衝。SCSI驅動預設關閉寫緩衝。IDE裝置寫緩衝通常是開啟的，但是可以從作業系統命令關閉如hdparm -W0,sysctl hw.ata.wc=0或者scsimd。但是，一些IDE驅動雖然報告寫緩衝關閉了，但是仍在使用它，這是不可靠的。沒有精確的測試很難判斷哪個驅動在撒謊。

因為PostgreSQL每個事務提交時使用fsync()寫WAL日誌到磁碟，並且等待寫完成。當使用寫緩衝時使用者能看到巨大的效能提升。因此，為了效能和可靠性，如果PostgreSQL能使用有後備電池的寫緩衝是一個理想的方案。

SCSI vs IDE

SCSI驅動通常比IDE驅動貴很多。SCSI驅動有一個協議用來在作業系統和控制器間通訊，然而IDE驅動簡單的多且同一時刻只能接受一個請求。有標記隊列的SCSI驅動可以接受多個請求並且重新排列它們提高效率。這是為什麼當單使用者或者單檔案IO時SCSI和IDE驅動有相似的效能特性，但是當多個使用者或者進程請求時SCSI比IDE效能更好。因此，SCSI更適合重負載資料庫伺服器。

實際上，SCSI或IDE是唯一的方法區分兩種主要的驅動：企業驅動，為高效能和高可靠設計。個人電腦驅動，為最小耗費設計。這篇文章http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf,做了優秀的工作來描述在生產基於效能可靠性或者減小耗費的驅動。它是一個優秀的指導在選擇基於這些特性的驅動。

檔案系統

一些作業系統支援多磁碟檔案系統。在這種情況,可能很難知道哪個檔案系統表現最好。PostgreSQL通常在傳統的Unix檔案系統表現最好，像很多作業系統支援的BSD UFS/FFS等。UFS預設的8K塊大小和PostgreSQL的頁大小一樣。你可以運行在日誌和基於日誌的檔案系統，但是這些會在WAL的fsync執行期間引起額外的開銷。老的基於SvR3的檔案系統變的太片段難以有好的效能。

因為有許多檔案系統可以選擇並且沒有一個是最優的，Linux上的檔案系統選擇是特別困難的：ext2不是完全的災難安全的，ext3,XFS和JFS是基於日誌的且Reiser是對小檔案最佳化且記錄日誌。雖然ext2比記錄檔系統塊很多,但是當要求災難恢複時ext2是不可選的。如果必須使用ext2，使用sync enabled掛載。一些人推薦使用ext3時用data=writeback掛載。

不推薦PostgreSQL使用NFS和其他遠程檔案系統。NFS和本地檔案系統沒有一樣的檔案系統特性，這些不一致可能引起資料可靠和災難恢複問題。

多CPU

PostgreSQL使用多進程模型，意為著每個資料庫連接擁有它自己的unix進程。因此,所有多CPU作業系統可以通過可用的CPU來加速多資料庫連接。然而,如果只有一個資料庫連接是活動的,它只能使用一個CPU。PostgreSQL沒有使用多執行緒模式從而允許一個進程使用多個CPU。

檢查點

當WAL檔案充滿時，一個檢查點被執行來強制所有的髒緩衝刷到磁碟，從而使記錄檔可以迴圈。檢查點也會在固定的時間間隔被執行，通常是5分鐘。如果有很多的資料庫寫活動，WAL段會很塊被充滿，導致所有磁碟緩衝被刷到磁碟引起系統過度的緩慢。

檢查點

檢查點應該每幾分鐘發生一次。如果在一分鐘發生幾次，效能會變差。在服務日誌中尋找"checkpoint_warning"訊息來決定你的檢查點是否執行太頻繁。如果30秒內檢查點執行超過一次，會出現這個訊息。

減小檢查點的頻率包括增加在data/pg_xlog中WAL檔案的數量。每個檔案是16 megabyte，所以它影響磁碟使用率。預設的配置使用了最小數量的記錄檔。為了減小檢查點頻率，你需要增加這個參數：

checkpoint_segments=3

預設值是3。增加它直到檢查點幾分鐘出現一次。另外一個可能出現的訊息是:
LOG:XLogWrite:new log file created - consider increasing WAL_FILES
這個訊息暗示postgresql.conf中的wal_files參數需要增加。

結論

幸運的是，PostgreSQL 不需要太多調優。大部分參數自動調整到最佳效能。緩衝大小和排序大小是兩個參數管理員能夠控制去更好的利用可用記憶體。磁碟訪問可以通過多個驅動加速。其他參數可能設定在share/postgresql.conf.sample。你可以copy到data/postgresql.conf去實驗其他PostgreSQL更奇異的參數。

PostgreSQL 硬體效能調優