針對原生MySQL Query Cache的不足,阿里雲進行重新設計和全新實現,推出Fast Query Cache,能夠有效提高資料庫查詢效能。
前提條件
- 執行個體版本為MySQL 5.7(核心小版本20200331或以上)。
- 執行個體未開啟資料庫獨享代理。
背景資訊
查詢快取(Query Cache)是為了提高查詢效能而實現的一種緩衝策略,其基本思想是:對於每個合格查詢語句,直接對結果集進行緩衝,當下次查詢命中時,直接從緩衝中取出對應的結果集返回,不需要經歷SQL的分析、最佳化、執行等複雜過程,通過節約CPU資源來達到查詢加速的目標,是一項非常實用的技術。
MySQL原生Query Cache在設計和實現上存在著較多嚴重問題:
- 並發處理較差,在多核情況下,可能並發越高效能降低越嚴重。
- 記憶體管理較差,記憶體利用率低並且回收不及時,造成記憶體浪費。
- 當快取命中率較低時,效能無提升甚至會出現嚴重降低。
由於以上問題,MySQL原生Query Cache沒有得到廣泛應用,在最新版的MySQL 8.0中,取消此功能。阿里雲資料庫團隊對Query Cache進行重新設計和全新實現,解決了以上幾個主要問題:
- 最佳化並發控制
取消全域鎖同步機制,採用無鎖機制,重新設計並發情境下的同步問題,能夠充分利用多核的處理能力,保證高並發情境下的效能。
- 最佳化記憶體管理
取消記憶體預分配機制,採用更加靈活的動態記憶體分配機制,及時回收無效的記憶體,保證記憶體的真實利用率。
- 最佳化緩衝機制
動態檢測緩衝利用率,即時調整緩衝策略,解決命中率偏低或讀寫混合等情境下的效能降低問題。
相比原生Query Cache,Fast Query Cache可以在不同的業務情境中放心開啟,提高查詢效能。
使用Fast Query Cache
您可以在RDS控制台設定參數query_cache_type和query_cache_size使用Fast Query Cache。
參數 | 說明 |
query_cache_type | Fast Query Cache功能開關,取值:
|
query_cache_size | Fast Query Cache使用的記憶體大小,取值範圍:0~10485760000,需要為1024的整數倍。單位:Byte。 |
由於Fast Query Cache功能需要佔用額外的記憶體空間,所以建議使用Fast Query Cache功能時同步修改參數innodb_buffer_pool_size的大小,推薦的修改步驟如下:
- 修改innodb_buffer_pool_size為原先的90%,分出10%的空間給query_cache_size。例如原先為{DBInstanceClassMemory*7/10},需要改為{DBInstanceClassMemory*63/100}。具體操作,請參見調整執行個體Buffer Pool大小。
- 修改參數query_cache_size。具體操作,請參見設定執行個體參數。
- 若能夠評估結果集大小,query_cache_size可以設定為
20% * 結果集大小
。 - 若無法準確評估結果集大小,query_cache_size可以設定為
10% * innodb_buffer_pool_size
。
說明 如果變更執行個體規格,參數query_cache_size的值不會隨執行個體規格變化,請及時修改此參數值。 - 若能夠評估結果集大小,query_cache_size可以設定為
- 修改參數query_cache_type為1,開啟Fast Query Cache功能。具體操作,請參見設定執行個體參數。
效能比較
在相同情境下,分別測試QC-OFF(關閉Query Cache)、MySQL-QC(開啟MySQL原生Query Cache)和Fast-QC(開啟Fast Query Cache)的QPS。
- 測試環境:4核8GB 獨享型執行個體
- 測試載入器:Sysbench
- 資料量:250MB(25張表,每張表40000條記錄)
- 情境1:全部命中(唯讀)
測試情境為Sysbench oltp_point_select,用例中僅包括主鍵上的點查(point select),將Query Cache設為512MB,記憶體大於測試資料量,緩衝可以全部命中,主要關注不同並發下的效能提升效果。
表 1. 全部命中(唯讀)QPS 並發數 QC-OFF MySQL-QC(相比QC-OFF提升) Fast-QC(相比QC-OFF提升) 1 8093 8771(8.38%) 9261(14.43%) 8 62262 65686(5.50%) 75313(20.96%) 16 97083 73027(-24.78%) 139323(43.51%) 32 97337 60567(-37.78%) 200978(106.48%) 64 106283 60216(-43.34%) 221659(108.56%) 128 107781 62844(-41.69%) 231409(114.70%) 256 106694 63832(-40.17%) 222187(108.25%) 512 101733 64866(-36.24%) 203789(100.32%) 1024 89548 62291(-30.44%) 203542(127.30%) 說明 測試結果顯示,在較高並發的情境下,MySQL原生Query Cache並發處理效能出現較大幅度的降低,Fast Query Cache在各個並發情境下無效能降低,最高時能夠提高一倍的QPS。 - 情境2:高命中率(唯讀)
測試情境為Sysbench oltp_read_only,用例中包含返回多條記錄的範圍查詢,將Query Cache設為512MB,記憶體才相對比較充足,命中率可以達到80%以上,這時主要關注不同並發下的效能提升效果。
表 2. 高命中率(唯讀)QPS 並發數 QC-OFF MySQL-QC(相比QC-OFF提升) Fast-QC(相比QC-OFF提升) 1 5099 6467(26.83%) 7022(37.71%) 8 28782 28651(-0.46%) 45017(56.41%) 16 35333 31099(-11.98%) 66770(88.97%) 32 34864 27610(-20.81%) 67623(93.96%) 64 35503 27518(-22.49%) 75981(114.01%) 128 35744 27733(-22.41%) 80396(124.92%) 256 35685 27738(-22.27%) 80925(126.78%) 512 35308 27398(-22.40%) 79323(124.66%) 1024 34044 26861(-22.10%) 75742(122.48%) 說明 測試結果顯示,隨著並發數的增加,MySQL原生Query Cache的效能出現明顯的降低,Fast Query Cache的效能則會不斷提升,最高時能夠提高一倍多的QPS。 - 情境3:低命中率(唯讀)
測試情境為Sysbench oltp_read_only,用例中包含返回多條記錄的範圍查詢,將Query Cache設為16MB,記憶體明顯嚴重不足,快取命中率只有10%左右,記憶體不足時會涉及快取項目的大量淘汰,影響效能,這時主要關注不同並發下的效能降低程度。
表 3. 低命中率(唯讀)QPS 並發數 QC-OFF MySQL-QC(相比QC-OFF提升) Fast-QC(相比QC-OFF提升) 1 5004 4727(-5.54%) 5199(3.90%) 8 28795 22542(-21.72%) 28578(-0.75%) 16 35455 24064(-32.13%) 35682(0.64%) 32 34526 21330(-38.22%) 35871(3.90%) 64 35514 19791(-44.27%) 36051(1.51%) 128 35983 19519(-45.75%) 36253(0.75%) 256 35695 19168(-46.30%) 36337(1.80%) 512 35182 18420(-47.64%) 35972(2.25%) 1024 33915 20168(-40.53%) 34546(1.86%) 說明 測試結果顯示,MySQL原生Query Cache的效能降低明顯,最多出現了接近50%的效能損失,Fast Query Cache最佳化了低命中率情境,幾乎不會帶來任何額外的效能損失。 - 情境4:讀寫混合
測試情境為Sysbench oltp_read_write,每個事務中都有對錶的更新操作,可以認為緩衝基本處於失效狀態,頻繁的更新操作涉及緩衝的主動淘汰,理論上會比較影響效能,這時主要關注不同並發下的效能衰減程度。
表 4. 讀寫混合QPS 並發數 QC-OFF Fast-QC(相比QC-OFF提升) 1 4152 4098(-1.30%) 8 21359 21195(-0.77%) 16 26020 25548(-1.81%) 32 27595 26996(-2.17%) 64 29229 28733(-1.70%) 128 29265 28828(-1.49%) 256 29911 29616(-0.99%) 512 29148 28816(-1.14%) 1024 29204 28824(-1.30%) 說明 測試結果顯示,Fast Query Cache在讀寫混合情境下不會出現過多的效能降低,整體效能影響很小。
實踐指南
在快取資料集大小明確的情況下,例如使用SQL_CACHE關鍵字對指定表開啟Query Cache,可以參照前面的測試進行效能評估。接下來將對Fast Query Cache的使用作一些補充說明。
- 適用情境指南
- Fast Query Cache主要目的是提高讀操作效能,建議在讀多寫少的情境下開啟,或者使用SQL_CACHE關鍵字針對讀多寫少的表開啟。如果寫多讀少,資料的更新非常頻繁,可能會出現很少的效能降低。
- 開啟Fast Query Cache帶來的效能提升和快取命中率直接相關。在全域開啟前建議查看InnoDB Buffer Pool的命中率(命中率 = 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests),如果命中率低於80%,則不建議開啟。您也可以通過TABLE_STATISTICS表查看錶層級的讀寫比,對讀寫比高的表通過SQL_CACHE關鍵字顯式開啟Fast Query Cache。查詢TABLE_STATISTICS表請參見Performance Insight。
- 緩衝使用方式(query_cache_type)
query_cache_type參數支援會話級修改,使用者可以根據真實業務情境進行靈活設定,請參見以下建議:
- 對於更新頻繁、寫多讀少等不適合Query Cache的情境,應將query_cache_type全域設定為0。
- 對於資料量較小、訪問模式比較固定、命中率較高的情境,可以將query_cache_type全域設定為1。
- 對於資料量較大、訪問模式不固定、命中率無法保障的情境,可將query_cache_type設定為2,僅對指定的語句,通過SQL_CACHE關鍵字使用Fast Query Cache。
- 緩衝大小(query_cache_size)設定
query_cache_size和SQL息息相關,如果緩衝中有返回多條記錄的查詢,緩衝可能需要是資料量的數倍。如果SQL中不包含範圍查詢,可以參見以下測試來評估資料量和query_cache_size的關係。
- 測試環境:4核8GB獨享型執行個體(innodb_buffer_pool_size=6GB)
- 測試載入器:Sysbench
- 資料量:10GB(100張表,每張表400000條記錄)
測試情境為Sysbench oltp_point_select、64並發、Special分布(20%熱點)。測試不同query_cache_size大小對於效能的影響。對應上述的資料量,全量結果集的真實大小為2.5GB。
表 5. 不同緩衝QPS query_cache_size(MB) QC-OFF Fast-QC命中率 Fast-QC(相比QC-OFF提升) 64 98236 22% 99440(1.23%) 128 98236 45% 114155(16.21%) 256 98236 72% 140668(43.19%) 512 98236 82% 151260(53.98%) 1024 98236 84% 153866(56.63%) 2048 98236 87% 159597(62.46%) 4096 98236 92% 169412(72.45%) Fast Query Cache在不同query_cache_size的設定下都不會引起效能退化,對於主鍵查詢操作,在不同快取命中率下都有效能提升,達到90%以上時,提升效果比較明顯;對於範圍查詢或帶
Order By
的排序語句,快取命中率低於90%時,也能節約大量的CPU,帶來較大的效能提升。