MySQL · 8.0版本更新 · 效能最佳化篇

標籤：

摘要： 本文主要總結下MySQL在8.0版本和效能相關的一些改動，隨著新的小版本的發布，本文將不斷進行更新，直到正式GA。 已更新版本MySQL 8.0.0MySQL 8.0.0 WL#9387: InnoDB: Group purging of rows by table ID 這個問題最早是faceb...

 

本文主要總結下MySQL在8.0版本和效能相關的一些改動，隨著新的小版本的發布，本文將不斷進行更新，直到正式GA。

 

 

MySQL 8.0.0

 

WL#9387: InnoDB: Group purging of rows by table ID

 

這個問題最早是facebook的工程師Domas報的一個bug，InnoDB使用多線程來進行Undo Purge操作，但分配undo的策略不太合理，直接輪詢分配。這意味著如果從一張表上刪除大量資料，這N個purge線程可能產生大量的索引鎖衝突(例如索引頁合并及重組織)

 

在WL#9387中，在parse undo log時，通過table_id進行分組儲存，在分發時確保同一個table id的記錄被分配給同一個線程。(參考函數 trx_purge_attach_undo_recs)

 

當然這也意味著合理的不會產生衝突的單表操作, 無法利用到多線程purge了，也算是一個弊端。

 

WL#8423: InnoDB: Remove the buffer pool mutex

 

這個算是眾望所歸的改進了，由Percona貢獻的補丁(bug#75534)，主要是對InnoDB的buffer pool mutex這個大鎖進行了拆分，降低鎖衝突：

 

 

分配空閑block(buf_LRU_get_free_block)：

 

• 從free list擷取: buf_pool_t::free_list_mutex

• 從unzip_lru/lru上驅逐一個空閑page，需要buf_pool_t::LRU_list_mutex

 

批量掃描LRU（buf_do_LRU_batch）: buf_pool_t::LRU_list_mutex

 

批量掃描FLUSH_LIST（buf_do_flush_list_batch）: buf_pool_t::flush_list_mutex

 

髒頁加入到flush_list(buf_flush_insert_into_flush_list): buf_pool_t::flush_list_mutex

 

髒頁寫回磁碟後，從flush list上移除(buf_flush_write_complete): buf_pool_t::flush_state_mutex/flush_list_mutex

 

從LRU上驅逐Page(buf_LRU_free_page):buf_pool_t::LRU_list_mutex， 及buf_pool_t::free_list_mutex(buf_LRU_block_free_non_file_page)

 

buf_flush_LRU_list_batch 使用mutex_enter_nowait 來擷取block鎖，如果擷取失敗，說明正被其他session佔用，忽略該block.

 

有些變數的修改從通過buf_pool_t::mutex保護，修改成通過memory barrior來保護(os_rmb or os_wmb), 例如下面幾個函數中均有體現：

 

btr_search_enable()

buf_resize_thread()

buf_get_withdraw_depth()

buf_LRU_get_free_block()

 

通過對鎖的拆分，降低了全域大鎖的競爭，提升了buffer pool的擴充性，這個特性其實在Percona Server中很多年前就有了， 但直到MySQL8.0版本才合并進來。

 

WL#7170: InnoDB buffer estimates for tables and indexes

 

主要是用於為最佳化器提供更準確的資訊，即資料是存在與磁碟還是記憶體中， 這樣最佳化器可以更準確的做出代價計算。

 

增加一個全域對象（buf_stat_per_index_t）來管理所有的索引頁計數

 

為了避免引入新的全域鎖開銷，實現並使用一個lock-free的hash結構("include/ut0lock_free_hash.h)來儲存索引資訊，key值為索引id。（目前索引id具有唯一性，但不排除未來可能發生改變）。

 

增加計數：

 

1. Page剛從磁碟讀入記憶體 (buf_page_io_complete --> buf_page_monitor)

2. 建立一個新的page (btr_page_create)

 

遞減計數：Page從LRU上釋放時進行遞減(buf_LRU_block_remove_hashed)

 

增加新的information_schema.innodb_cached_indexs 列印每個索引在記憶體中的page個數，其結構如下：

 

mysql> show create table INNODB_CACHED_INDEXES\G

*************************** 1. row ***************************

       Table: INNODB_CACHED_INDEXES

Create Table: CREATE TEMPORARY TABLE `INNODB_CACHED_INDEXES` (

  `SPACE_ID` int(11) unsigned NOT NULL DEFAULT ‘0‘,

  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT ‘0‘,

  `N_CACHED_PAGES` bigint(21) unsigned NOT NULL DEFAULT ‘0‘

) ENGINE=MEMORY DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

 

### 和表名/索引名關聯

 

SELECT

    tables.name AS table_name,

    indexes.name AS index_name,

    cached.n_cached_pages AS n_cached_pages

FROM

    information_schema.innodb_cached_indexes AS cached,

    information_schema.innodb_sys_indexes AS indexes,

    information_schema.innodb_sys_tables AS tables

WHERE

    cached.index_id = indexes.index_id

        AND

    indexes.table_id = tables.table_id;

 

相關worklog: WL#7168: API for estimates for how much of table and index data that is in memory buffer

 

WL#9383: INNODB: ADD AN OPTION TO TURN OFF/ON DEADLOCK CHECKER

 

增加選項，可以動態關閉死結檢測，這對諸如熱點更新這樣的情境效果顯著，之前已專門寫了篇部落格，感興趣的自取。

 

Bug#77094

 

這個最佳化來自alisql的貢獻，主要是最佳化了InnoDB Redo的擴充性問題，通過雙buffer機制，允許在寫日誌到磁碟的同時，也允許進行mtr commit，具體參閱我寫的這篇月報。

 

WL#7093: Optimizer provides InnoDB with a bigger buffer

 

為了減少對Btree的鎖佔用，InnoDB在讀取資料時實際上是有一個小的緩衝buffer。對於連續記錄掃描，InnoDB在滿足比較嚴格的條件時採用row cache的方式連續讀取8條記錄（並將記錄格式轉換成MySQL Format），儲存線上程私人的row_prebuilt_t::fetch_cache中；這樣一次尋路就可以擷取多條記錄，在server層處理完一條記錄後，可以直接從cache中取資料而無需再次尋路，直到cache中資料取完，再進行下一輪。

 

在WL#7093中引入了新的介面，由於最佳化器可以估算可能讀取的行數，因此可以提供給儲存引擎一個更合適大小的row buffer來儲存需要的資料。大批量的連續資料掃描的效能將受益於更大的record buffer。

 

Record buffer由最佳化器來自動決定是否開啟，增加新的類Record_buffer進行管理, Record buffer的大小最大不超過128KB， 目前是hard code的，不可以配置。

 

判斷及分配record buffer函數: set_record_buffer， 並通過新的API介面(handler::ha_set_record_buffer)傳到引擎層

 

buffer本身是引擎無關的，在sever層分配，通過handler成員m_record_buffer傳遞到引擎層。

 

增加新的介面，判斷是否支援Record buffer， 目前僅InnoDB支援，需要滿足如下條件 (ref set_record_buffer)：

 

• access type 不是 ref, ref_or_null, index_merge, range, index 或者ALL

• 不是暫存資料表

• 不是loose index scan

• 進入InnoDB引擎層判斷((row_prebuilt_t::can_prefetch_records))

 

       return select_lock_type == LOCK_NONE // 唯讀查詢

                && !m_no_prefetch   // 允許prefetch

                && !templ_contains_blob // 沒有BLOB, TEXT, JSON, GEOMETRY這些大列

                && !templ_contains_fixed_point // 不是空間資料類型DATA_POINT

                && !clust_index_was_generated   // 需要使用者定義的primary key 或者唯一索引（被隱式的用作Pk）

                && !used_in_HANDLER // 不是通過HANDLER訪問的

                && !innodb_api // 不是通過類似innodb memcached訪問的

                && template_type != ROW_MYSQL_DUMMY_TEMPLATE //不是check table

                && !in_fts_query; // 不是全文索引查詢

 

在InnoDB中，當record buffer被配置時，就使用server層提供的record buffer，而不是row_prebuilt_t::fetch_cache

 

官方部落格對此改進的介紹：http://mysqlserverteam.com/mysql-8-0-faster-batch-record-retrieval/

 

WL#9250: Split LOCK_thd_list and LOCK_thd_remove mutexes

 

該Worklog的目的是改進短串連情境下的效能，對thd list的操作可能導致比較高的鎖競爭。

 

解決方案也比較傳統，就是進行分區，將鏈表thd_list劃分成多個數組，目前為8個分區，相應的鎖LOCK_thd_remove和LOCK_thd_list鎖也進行了分區。

轉自：zhaiwx_yinfeng  雲棲社區

MySQL · 8.0版本更新 · 效能最佳化篇