SQL Server中的Buffer Pool Extension與Hybrid Buffer Pool

by adonisy 27. 七月 2020 12:34

作 者:楊先民
審 稿:張智凱

本篇文章來介紹一下 SQL Server從 2014之後的新功能 Buffer pool extension以及 SQL Server 2019之後的另一個新功能

Hybrid Buffer Pool。

Buffer Pool Extension

之前的文章有提過(呃,如果有興趣可以看看之前的雜誌文章),Buffer pool是 SQL Server在將頁面寫入磁碟之前以及從磁碟讀取頁面之後用於緩存頁面的記憶體區域。

Buffer pool中的高速暫存中存在兩種不同類型的頁面:乾淨頁面和dirty的頁面。乾淨頁面是未進行任何修改的頁面。乾淨頁通常存在於暫存當中,因為它們已被讀取操作(如SELECT語句)訪問。

一旦進入暫存,它們就可以支持所有的句法。例如,DML語句可以訪問乾淨頁,對其進行修改,然後更新其設定成為 dirty的分頁。

dirty的分頁是已被 INSERT,UPDATE和DELETE等語句修改的頁面。這些頁面需要將其關聯的日誌記錄寫入磁碟,隨後,dirty的頁面本身將被刷新到磁碟中,然後再視為乾淨的頁面。

首先寫入日誌記錄的過程稱為WAL(預寫日誌記錄),這是SQL Server如何確保即使在系統故障的情況下也不會丟失任何已提交數據的過程。

dirty的分頁始終保留在暫存當中,直到將它們刷新到磁碟上為止。另一方面,乾淨的頁面會盡可能長時間地保留在高速暫存中,但是當需要空間來暫存新的頁面時,會將它們清除。

SQL Server根據最近最少使用的策略將少用的資訊逐出頁面。這意味著如果緩衝區高速緩存的大小不正確,則讀取密集型工作負載可能會迅速開始遭受記憶體的壓力。

這裡的問題是,與實體儲存裝置相比,RAM比較昂貴,並且可能無法繼續拋出越來越多的記憶體來解決該問題。為了解決這個問題,Microsoft在SQL Server 2014中引入了一種稱為

Buffer Pool Extension(緩衝集區延伸模組)的技術。

Buffer Pool Extension(以後簡稱 BPE)被設計為與非常快的SSD配合使用,該SSD通常位於本機,而不是位於SAN storage上。簡單來說,儲存需要盡快進行。

BPE將成為僅用於乾淨頁面的輔助暫存。從暫存中清除乾淨頁後,它們將被移到 BPE 中,在那裡可以比通過返回主IO子系統更快地檢索它們。

這是一個非常有用的功能,但不是靈丹妙藥。首先,重要的是要記住,BPE 將永遠無法提供與沒有擴展的正確大小的緩衝區高速暫存相同的效能提升。其次,使用緩衝池擴展將獲得的性能提升

是特定於工作負載的。

例如,讀比較密集型的 OLTP工作負載可能會從 BPE 中受益匪淺,而寫比較密集型工作負載則幾乎沒有好處。這是因為無法將 dirty的分頁刷新到擴展區。大型資料倉儲也不大可能受益於

BPE。這是因為表可能很大,以至於在此工作負載方案中常見的全表掃描很可能會佔用高速暫存和擴展的大部分。

這意味著它將清除擴展中的其他資料,並且不太可能使後續查詢受益。

明智的做法是使用彈性的SSD,例如RAID 10。這是因為如果 SDD 有問題,則服務器將立即看到效能下降。萬一擴展存儲的SSD驅動器發生故障,SQL Server將自動禁用擴展。可以手動重新啟用,也可以自動嘗試重新啟用。

我建議你可以將擴展名的大小設置為「最大服務器記憶體」設置的四到八倍,以獲得最佳性能。擴展名的最大可能大小是「最大服務器記憶體」設置的32倍。

可以使用下面的語法來啟用 BPE。該語法假定您要使用的SSD驅動器已映射為S:\。它還假定我們將32GB設置為「最大服務器記憶體」設置,因此我們將擴展名配置為128GB,

是大小的四倍。

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION

ON (FILENAME = 'S:\SSDCache.BPE', SIZE = 128 GB )

 

如果你要將 BPE 關掉的話,則可以使用下面的語法:

ALTER SERVER CONFIGURATION

SET BUFFER POOL EXTENSION OFF


Hybird Buffer Pool

SQL Server 2019引入了混合緩衝集區(Hybrid Buffer Pool),提供了對PMEM(持續性記憶體)的支持。 PMEM也稱為SCM(存儲類記憶體)。 PMEM設備駐留在記憶體總線上,

並且是固態且可字節尋址的。

這使其比 USB Flash更快,並且比DRAM便宜。關閉服務器電源後,儲存在PMEM設備上的資料仍然存在。

在Windows中格式化PMEM設備(Windows Server 2016和更高版本開始支援 PMEM設備)以與混合緩衝集區配合使用時,應啟用 DirectAccess 並在 Windows Server 2019

上使用2MB的分配單元大小或最大可用大小,在其他版本的Windows Server上。創建驅動器後,可以將SQL Server event log儲存在設備上。

然後,當從緩衝集區讀取乾淨的頁面時,SQL Server將使用內存映射的IO(也稱為啟發)。這減輕了在訪問頁面之前將頁面複製到DRAM的需要,因此減少了IO延遲。

啟發只能用於乾淨的頁面。如果頁面變髒(dirty的分頁),則將其寫入DRAM,最後將其刷新回PMEM設備。

要在 Windows操作系統上運行的 SQL Server中啟用 PMEM支持,只需在 SQL Server服務上啟用跟踪標誌809。

對於在 Linux上運行的SQL Server,必須在SQL Server服務上啟用跟踪標誌3979。 (有關如何啟用跟踪標誌的資訊,請查一下 help。)

而在 SQL Server的 Help中,對 Hybird Buffer Pool的說明,有一張圖可以參考


 

 

 

 


 

 

 

 

而在 Help 上還寫到:

Windows 和 Linux 都提供混合式緩衝集區功能。 PMEM 裝置必須以支援 DAX (DirectAccess) 的檔案系統格式化。 XFS、EXT4 和 NTFS 檔案系統都支援 DAX。

連結、還原或建立新的資料庫時,SQL Server 會自動偵測資料檔案是否位於正確格式化的 PMEM 裝置,並在啟動時執行資料庫檔案的記憶體對應。

至於開啟方式,則是利用下面的語法:

ALTER SERVER CONFIGURATION

SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = ON;

根據預設,混合式緩衝集區在執行個體範圍上停用。 請注意,為了讓設定變更生效,必須重新啟動 SQL Server 執行個體。 需要重新啟動以方便配置足夠的雜湊頁,納入伺服器的總 PMEM 容量。

下例針對特定資料庫啟用混合式緩衝集區:

ALTER DATABASE <資料庫名稱>

SET MEMORY_OPTIMIZED = ON;

下列範例會停用執行個體層級的混合式緩衝集區:

ALTER SERVER CONFIGURATION

SET MEMORY_OPTIMIZED HYBRID_BUFFER_POOL = OFF;

下例針對特定資料庫停用混合式緩衝集區:

ALTER DATABASE <資料庫名稱>

SET MEMORY_OPTIMIZED = OFF;

根據預設,混合式緩衝集區在資料庫範圍上啟用,所以需要把不需要使用的資料庫設定成 OFF。

下列範例會傳回執行個體的目前混合式緩衝集區設定狀態:

SELECT * FROM sys.server_memory_optimized_hybrid_buffer_pool_configuration;

下列範例列出混合式緩衝集區 (is_memory_optimized_enabled) 的資料庫及資料庫層級設定:

SELECT name, is_memory_optimized_enabled FROM sys.databases;

而在 HELP 也做了以下的建議:

混合式緩衝集區的最佳做法

在 Windows 上格式化 PMEM 裝置時,請使用 NTFS 可提供的最大配置單位大小 (Windows Server 2019 為 2 MB),並確定裝置已針對 DAX (直接存取) 格式化。

在 Windows 中,使用記憶體的鎖定分頁。

檔案大小應該是 2 MB 的倍數 (mod 2 MB 應該等於零)。

如果混合式緩衝集區的伺服器範圍設定為停用,則任何使用者資料庫都不會使用此功能。

如果混合式緩衝集區的伺服器範圍設定為啟用,則您可以使用資料庫範圍設定來停用個別使用者資料庫的功能。

以上就是本期的主題:SQL Server上的 Buffer pool extenstion 以及 Hybird buffer pool。

Tags:

SQL Server資料庫 | 楊先民Adonis Young

不允許評論

NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List