## Plan Cache背景知識 一條SQL語句輸入到MySQL服務器后，一般要經歷：詞法語法解析(parse)，優化（optimize），生成執行計劃(plan)和執行(execute)的過程。詞法語法分析，優化以及生成執行計劃，這三個階段的主要輸出是SQL語句的執行計劃(plan)，當SQL語句存在多種執行計劃的時候，優化器會從這許多的執行計劃中挑選出一個它認為最優的（通常是占用系統資源最少的，包括CPU以及IO等）作為最終的執行計劃供執行器執行。生成執行計劃的過程會消耗較多的時間，特別是存在許多可選的執行計劃時。如果在一條SQL語句執行的過程中將該語句對應的最終執行計劃進行緩存，當相似的語句再次被輸入服務器時，就可以直接使用已緩存的執行計劃，從而跳過SQL語句生成執行計劃的整個過程，進而可以提高語句的執行速度。 ApsaraDB MySQL 執行計劃緩存目前只支持SELECT操作的語句（以后會支持其他DML操作）。在相似的SQL語句大量重復出現（這里“相似”的SQL語句指的是SQL語句中除了常量有所不同外，其他都必須相同）時，使用執行計劃緩存可以極大的節省語句的執行時間。同時，使用執行計劃緩存會帶來額外的內存開銷，因此建議在內存充裕的情況下使用該功能。 ## Plan Cache的架構  說明： 圖1表示的是ApsaraDB MySQL一條SQL語句輸入MySQL服務器的執行過程。 圖2表示的是當前ApsaraDB MySQL Plan Cache的架構圖。 ## Plan Cache中的數據結構 如圖2所示，Plan Cache包含了如下幾種數據結構： 1.?Execute_plan_cache_manager： 對整個Plan Cache進行管理，負責提供接口供Server其他模塊調用。 2.?Execute_plan_cache_partition（圖示中的PartitionX）： 為了減少“鎖“對整個Plan Cache的增刪改操作引發性能方面的影響，我們將Plan Cache 劃分為多個partition，對于每條符合Plan Cache條件的SQL語句只對其對應的partition進行上“鎖”。 3.?Execute_plan_cache（圖示中的PlanX）：實際用來存儲plan恢復所需的所有信息。 ## Plan Cache相關的系統變量 * rds_enable_exec_plan_cache | 范圍 | GLOBAL | | 類型 | BOOL | | 功能 | a) ON 打開Plan Cache功能。b) OFF 關閉Plan Cache功能，清空Plan Cache(默認)。 | * rds_max_digest_length | 范圍 | GLOBAL | | 類型 | LONG | | 功能 | a) 設置SQL語句中常量替換后的的長度，設置范圍是（128 ~ 1M）。b) 默認值是4K。 c) 如果SQL語句長度大于該值，query的plan不會被緩存。 | * rds_exec_plan_hash_parititions | 范圍 | GLOBAL READONLY | | 類型 | ULONG | | 功能 | Plan Cache manager里面可以有多少個partition。默認值是CACHE_MANAGER_PARTITIONS（8） | * rds_max_exec_plan_caches | 范圍 | GLOBAL READONLY | | 類型 | ULONG | | 功能 | a) Plan Cache里面可以定義多少條緩存的plan記錄數。b) 防止Plan Cache skew發生性能問題。 c) 如果太大失效性能會有影響。d) 默認值是MAX_PLAN_CACHES(1024) | | 備注 | 如果Plan Cache中分配到某個Partition中的記錄數超過了rds_max_exec_plan_caches的平均數， 即rds_max_exec_plan_caches / rds_exec_plan_hash_parititions， Plan Cache將利用LRU對存在的執行計劃記錄進行淘汰。 | * rds_max_plan_cache_mem_size | 范圍 | GLOBAL | | 類型 | ULONG | | 功能 | a) 設置Plan Cache的大小，范圍是（0 ~ +∞）。默認值1M。 | | 備注 | 如果Plan Cache中分配到某個Partition中的記錄所使用的內存超過了rds_max_plan_cache_mem_size的平均數， 即rds_max_plan_cache_mem_size / rds_exec_plan_hash_parititions,? Plan Cache將利用LRU對存在的執行計劃記錄進行淘汰。 | ## Plan Cache目前支持的場景 1. 支持所有存儲引擎。 2. 不支持UPDATE，INSERT，DELETE以及DDL。 3. 不支持UNION，INTERSECT， MINUS。 4. 不支持Explain。 5. Const plan不支持。 6. 系統表查詢不支持。 7. 支持所有SELECT 語句，除下面列出的特殊情況 * 多表連接不支持。 * 包含SUBQUERY或者VIEW的不支持。 * 不支持SP或者UDF。 ## Plan Cache中記錄的失效 ### 自動失效 1. Plan Cache中對于依賴于某個表的所有執行計劃記錄，如果表結構發生了修改或者表被DROP掉，相關的記錄都將被失效； 2. 如果關閉Plan Cache功能，即設置rds_enable_exec_plan_cache為off，所有的記錄都將被失效； ### 主動失效 1. 可以使用ALTER TABLE table_name DROP cached plan語句將于該表相關的執行計劃記錄失效掉； ## Plan Cache相關的HINT 1. no_plan_cache：使用該hint，可以使當前SQL語句忽略使用Plan Cache中緩存的執行計劃，同時Plan Cache也不會緩存當前SQL語句的執行計劃。 2. force_update_plan_cache：使用該hint，如果Plan Cache中存在當前SQL語句相關的執行計劃記錄，Plan Cache將強制更新該條SQL對應的執行計劃緩沖記錄。 由于當前的Plan Cache沒有考慮統計信息變化以及調優過程中使用的強制變更執行計劃的選項，所以如果使用Plan Cache中緩沖的執行計劃效率比較低，可以通過使用HINT來更新Plan Cache中的記錄。 ## Plan Cache 的監控 * 直接查看執行計劃緩存。 ~~~ mysql> use test; Database changed mysql> create table tt(a int, b int); Query OK, 0 rows affected (0.19 sec) mysql> insert into tt values(1,2),(3,4); Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tt; +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.EXEC_CACHE_STATUS; +------------+---------------------+------------+------+-------------+-----------+--------------------------+ | SQL_PRINT | SQL_DIGEST | TABLE_NAME | KEYS | USED_MEMORY | HIT_COUNT | EXTENDED | +------------+---------------------+------------+------+-------------+-----------+--------------------------+ | 4095414401 | SELECT * FROM `tt` | test.tt | | 144 | 0 | ALL, partition number: 1 | +------------+---------------------+------------+------+-------------+-----------+--------------------------+ 1 row in set (0.00 sec) ~~~ * 使用Optimizer_trace來查看當前的SQL語句是否使用了Plan Cache。 ~~~ mysql> SET optimizer_trace="enabled=on"; Query OK, 0 rows affected (0.00 sec) mysql> select * from tt; +------+------+ | a | b | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; ... { "plan_cache": [ { "table": "`tt`", "rows": 0, "cost": 0, "use_cached_plan": "yes", "scan type": "ALL" } ] }, ... ~~~ * 查看當前的Plan Cache的執行狀態。 ~~~ mysql> show status like '%Execute_plan%'; +--------------------------------------+-------+ | Variable_name | Value | +--------------------------------------+-------+ | Execute_plan_cache_hits | 0 | | Execute_plan_cache_misses | 0 | | Execute_plan_cache_records | 0 | | Execute_plan_cache_total_hits | 1 | | Execute_plan_cache_total_used_memory | 176 | | Execute_plan_cache_used_memory | 0 | | Execute_plan_total_cache_records | 1 | +--------------------------------------+-------+ 7 rows in set (0.01 sec) ~~~ 說明： | Execute_plan_cache_hits | 顯示當前session，執行的SQL語句命中Plan Cache的條數。 | | Execute_plan_cache_misses | 顯示當前session，執行的SQL語句未命中Plan Cache的條數。 | | Execute_plan_cache_records | 當前Plan Cache中存在的與當前session相關的執行計劃緩存記錄數。 | | Execute_plan_cache_total_hits | 顯示所有session，執行的SQL語句命中Plan Cache的條數。 | | Execute_plan_cache_total_used_memory | 整個Plan Cache所使用的內存大小。 | | Execute_plan_cache_used_memory | 當前session相關的Plan Cache所使用的內存大小。 | | Execute_plan_total_cache_records | 整個Plan Cache緩存的執行計劃記錄數。 | ## Plan Cache的測試性能 測試環境： HW：64bits，32 cores- 64 processors，2.5G HZ；內存：500G； SSD。 OS: Centos 測試工具：修改后的Sysbench 測試workload：2，4，8，16…1024個sessions。 測試包括RO，RW。 數據集： 100,000行記錄。 測試效果：   圖3是打開和關閉Plan Cache，對于read only sysbench的測試性能圖，可以看到在Plan Cache大小不同對于性能的提升也有差異。如果Plan Cache分配內存太小，會頻繁進行LRU淘汰，導致性能受到影響。但如果Plan Cache分配的內存足夠大，我們可以看到理想狀態下，Plan Cache可以提升5X左右。 圖4是打開和關閉Plan Cache，對于read/write sysbench的測試性能圖，可以看到打開Plan Cache對于write方面的性能幾乎沒有影響。 ## Plan Cache 的并發控制 為了能夠盡量的減少加鎖對并發查詢性能的影響，我們的設計盡可能的減少對鎖的依賴。因此我們對于plan_cache_manager這樣的全局管理對象采取了lock free，并未引入任何RW Lock來控制并發。而是對plan_cache_manager下面的每一個partition使用了RW Lock。 通過將查詢分散到多個partition中有效的減少了讀寫Plan Cache的加鎖時間。對于寫入某個partition中的執行計劃我們首先需要對partition加W-Lock；而對于從Plan Cache中的某個partition獲取執行計劃記錄，我們會使用R-lock來控制并發。 ## 總結 如果當前用戶頻繁使用相似的query進行查詢，Plan Cache可以有效的減少query的優化時間，進而提升query的執行性能。目前Plan Cache是我們開發的第一個版本，只是針對我們線上的場景進行優化，很多場景還不支持。希望在未來的時間里可以完善Plan Cache，提供更多的場景支持。