淘寶內部分享：MySQL & MariaDB效能優化

shilei1發表於2016-04-12
MySql優化

摘要：MySQL是目前使用最多的開源資料庫，但是MySQL資料庫的預設設定效能非常的差，必須進行不斷的優化，而優化是一個複雜的任務，本文描述淘寶資料庫團隊針對MySQL相關的資料庫優化方案。

編者按：MySQL是目前使用最多的開源資料庫，但是MySQL資料庫的預設設定效能非常的差，必須進行不斷的優化，而優化是一個複雜的任務，本文描述淘寶資料庫團隊針對MySQL資料庫Metadata Lock子系統的優化，hash_scan 演算法的實現解析的效能優化，TokuDB·版本優化，以及MariaDB·的效能優化。本文來自淘寶團隊內部經驗分享。

往期文章：淘寶內部分享：怎麼跳出MySQL的10個大坑


MySQL·　5.7優化·Metadata Lock子系統的優化

背景

引入MDL鎖的目的，最初是為了解決著名的bug#989，在MySQL 5.1及之前的版本，事務執行過程中並不維護涉及到的所有表的Metatdata 鎖，極易出現複製中斷，例如如下執行序列：

Session 1: BEGIN;
Session 1: INSERT INTO t1 VALUES (1);
Session 2: Drop table t1; --------SQL寫入BINLOG
Session 1: COMMIT; -----事務寫入BINLOG

在備庫重放 binlog時，會先執行DROP TABLE，再INSERT資料，從而導致複製中斷。



在MySQL 5.5版本里，引入了MDL, 在事務過程中涉及到的所有表的MDL鎖，直到事務結束才釋放。這意味著上述序列的DROP TABLE 操作將被Session 1阻塞住直到其提交。

不過用過5.5的人都知道，MDL實在是個讓人討厭的東西，相信不少人肯定遇到過在使用mysqldump做邏輯備份時，由於需要執行FLUSH TABLES WITH READ LOCK (以下用FTWRL縮寫代替)來獲取全域性GLOBAL的MDL鎖，因此經常可以看到“wait for global read lock”之類的資訊。如果備庫存在大查詢，或者複製執行緒正在執行比較漫長的DDL，並且FTWRL被block住，那麼隨後的QUERY都會被block住，導致業務不可用引發故障。

為了解決這個問題，Facebook為MySQL增加新的介面替換掉FTWRL 只建立一個read view ，並返回與read view一致的binlog位點；另外Percona Server也實現了一種類似的辦法來繞過FTWRL，具體點選文件連線以及percona的部落格，不展開闡述。

MDL解決了bug#989，卻引入了一個新的熱點，所有的MDL鎖物件被維護在一個hash物件中；對於熱點，最正常的想法當然是對其進行分割槽來分散熱點，不過這也是Facebook的大神Mark Callaghan在report了bug#66473後才加入的，當時Mark觀察到MDL_map::mutex的鎖競爭非常高，進而推動官方改變。因此在MySQL 5.6.8及之後的版本中，引入了新引數metadata_locks_hash_instances來控制對mdl hash的分割槽數(Rev:4350)；

不過故事還沒結束，後面的測試又發現雜湊函式有問題，somedb. someprefix1 … .somedb .someprefix8 的hash key值相同，都被hash到同一個桶下面了，相當於hash分割槽沒生效。這屬於hash演算法的問題，喜歡考古的同學可以閱讀下bug#66473後面Dmitry Lenev的分析。

Mark進一步的測試發現Innodb的hash計算演算法比my_hash_sort_bin要更高效， Oracle的開發人員重開了個bug#68487來跟蹤該問題，並在MySQL5.6.15對hash key計算函式進行優化，包括fix 上面說的hash計算問題(Rev:5459)，使用MurmurHash3演算法來計算mdl key的hash值。

MySQL 5.7 對MDL鎖的優化

在MySQL 5.7裡對MDL子系統做了更為徹底的優化。主要從以下幾點出發：

第一，儘管對MDL HASH進行了分割槽，但由於是以表名+庫名的方式作為key值進行分割槽，如果查詢或者DML都集中在同一張表上，就會hash到相同的分割槽，引起明顯的MDL HASH上的鎖競爭。

針對這一點，引入了LOCK-FREE的HASH來儲存MDL_lock，LF_HASH無鎖演算法基於論文"Split-Ordered Lists: Lock-Free Extensible Hash Tables"，實現還比較複雜。 注：實際上LF_HASH很早就被應用於Performance Schema，算是比較成熟的程式碼模組。由於引入了LF_HASH，MDL HASH分割槽特性自然直接被廢除了 。對應WL#7305， PATCH(Rev:7249)

第二，從廣泛使用的實際場景來看，DML/SELECT相比DDL等高階別MDL鎖型別，是更為普遍的，因此可以針對性的降低DML和SELECT操作的MDL開銷。

為了實現對DML/SELECT的快速加鎖，使用了類似LOCK-WORD的加鎖方式，稱之為FAST-PATH，如果FAST-PATH加鎖失敗，則走SLOW-PATH來進行加鎖。

每個MDL鎖物件（MDL_lock）都維持了一個long long型別的狀態值來標示當前的加鎖狀態，變數名為MDL_lock::m_fast_path_state 舉個簡單的例子：（初始在sbtest1表上對應MDL_lock::m_fast_path_state值為0）

Session 1: BEGIN;
Session 1: SELECT * FROM sbtest1 WHERE id =1; //m_fast_path_state = 1048576, MDL ticket 不加MDL_lock::m_granted佇列
Session 2: BEGIN;
Session 2: SELECT * FROM sbtest1 WHERE id =2; //m_fast_path_state=1048576+1048576=2097152，同上，走FAST PATH
Session 3: ALTER TABLE sbtest1 ENGINE = INNODB; //DDL請求加的MDL_SHARED_UPGRADABLE型別鎖被視為unobtrusive lock，可以認為這個是比上述SQL的MDL鎖級別更高的鎖，並且不相容，因此被強制走slow path。而slow path是需要加MDL_lock::m_rwlock的寫鎖。m_fast_path_state = m_fast_path_state | MDL_lock::HAS_SLOW_PATH | MDL_lock::HAS_OBTRUSIVE
注:DDL還會獲得庫級別的意向排他MDL鎖或者表級別的共享可升級鎖，但為了表述方便，這裡直接忽略了，只考慮涉及的同一個MDL_lock鎖物件。
Session 4: SELECT * FROM sbtest1 WHERE id =3; // 檢查m_fast_path_state &HAS_OBTRUSIVE，如果DDL還沒跑完，就會走slow path。

從上面的描述可以看出，MDL子系統顯式的對鎖型別進行了區分（OBTRUSIVE or UNOBTRUSIVE），儲存在陣列矩陣m_unobtrusive_lock_increment。 因此對於相容型別的MDL鎖型別，例如DML/SELECT，加鎖操作幾乎沒有任何讀寫鎖或MUTEX開銷。對應WL#7304, WL#7306 ， PATCH（Rev:7067,Rev:7129）(Rev:7586)

第三，由於引入了MDL鎖，實際上早期版本用於控制Server和引擎層表級併發的THR_LOCK 對於Innodb而言已經有些冗餘了，因此Innodb表完全可以忽略這部分的開銷。

不過在已有的邏輯中，Innodb依然依賴THR_LOCK來實現LOCK TABLE tbname READ，因此增加了新的MDL鎖型別來代替這種實現。實際上程式碼的大部分修改都是為了處理新的MDL型別，Innodb的改動只有幾行程式碼。對應WL#6671，PATCH(Rev:8232)

第四，Server層的使用者鎖（通過GET_LOCK函式獲取）使用MDL來重新實現。

使用者可以通過GET_LOCK()來同時獲取多個使用者鎖，同時由於使用MDL來實現，可以藉助MDL子系統實現死鎖的檢測。注意由於該變化，導致使用者鎖的命名必須小於64位元組，這是受MDL子系統的限制導致。對應WL#1159, PATCH(Rev:8356)



MySQL·效能優化·hash_scan 演算法的實現解析



問題描述

首先，我們執行下面的TestCase：

[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. --source include/master-slave.inc  
  2. --source include/have_binlog_format_row.inc  
  3. connection slave;  
  4. set global slave_rows_search_algorithms='TABLE_SCAN';  
  5. connection master;  
  6. create table t1(id int, name varchar(20);  
  7. insert into t1 values(1,'a');  
  8. insert into t2 values(2, 'b');  
  9. ......  
  10. insert into t3 values(1000, 'xxx');  
  11. delete from t1;  
  12. ---source include/rpl_end.inc  
隨著 t1 資料量的增大，rpl_hash_scan.test 的執行時間會隨著 t1 資料量的增大而快速的增長，因為在執行 'delete from t1;' 對於t1的每一行刪除操作，備庫都要掃描t1,即全表掃描，如果 select count(*) from t1 = N, 則需要掃描Ｎ次 t1 表， 則讀取記錄數為： O(N + (N-1) + (N-2) + .... + 1) = O(N^2)，在 replication 沒有引入 hash_scan，binlog_format=row時，對於無索引表，是通過 table_scan 實現的，如果一個update_rows_log_event/delete_rows_log_event 包含多行修改時，每個修改都要進行全表掃描來實現，其 stack 如下：



[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. #0 Rows_log_event::do_table_scan_and_update  
  2. #1 0x0000000000a3d7f7 in Rows_log_event::do_apply_event   
  3. #2 0x0000000000a28e3a in Log_event::apply_event  
  4. #3 0x0000000000a8365f in apply_event_and_update_pos  
  5. #4 0x0000000000a84764 in exec_relay_log_event   
  6. #5 0x0000000000a89e97 in handle_slave_sql (arg=0x1b3e030)   
  7. #6 0x0000000000e341c3 in pfs_spawn_thread (arg=0x2b7f48004b20)   
  8. #7 0x0000003a00a07851 in start_thread () from /lib64/libpthread.so.0  
  9. #8 0x0000003a006e767d in clone () from /lib64/libc.so.6  
這種情況下，往往會造成備庫延遲，這也是無索引表所帶來的複製延遲問題。



如何解決問題：


  1. RDS 為了解這個問題，會在每個表建立的時候檢查一下表是否包含主建或者唯一建，如果沒有包含，則建立一個隱式主建，此主建對使用者透明，使用者無感，相應的show create, select * 等操作會遮蔽隱式主建，從而可以減少無索引錶帶來的影響;
  2. 官方為了解決這個問題，在5.6.6 及以後版本引入引數 slave_rows_search_algorithms ，用於指示備庫在 apply_binlog_event時使用的演算法，有三種演算法TABLE_SCAN,INDEX_SCAN,HASH_SCAN，其中table_scan與index_scan是已經存在的，本文主要研究HASH_SCAN的實現方式，關於引數slave_rows_search_algorithms的設定。


hash_scan 的實現方法：

簡單的講，在 apply rows_log_event時，會將 log_event 中對行的更新快取在兩個結構中，分別是：m_hash, m_distinct_key_list。 m_hash：主要用來快取更新的行記錄的起始位置，是一個hash表； m_distinct_key_list：如果有索引，則將索引的值push 到m_distinct_key_list，如果表沒有索引，則不使用這個List結構； 其中預掃描整個呼叫過程如下： Log_event::apply_event

[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. Rows_log_event::do_apply_event  
  2.    Rows_log_event::do_hash_scan_and_update   
  3.      Rows_log_event::do_hash_row  (add entry info of changed records)  
  4.        if (m_key_index < MAX_KEY) (index used instead of table scan)  
  5.          Rows_log_event::add_key_to_distinct_keyset ()  
當一個event 中包含多個行的更改時，會首先掃描所有的更改，將結果快取到m_hash中，如果該表有索引，則將索引的值快取至m_distinct_key_list List 中，如果沒有，則不使用這個快取結構，而直接進行全表掃描；


執行 stack 如下：


[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. #0 handler::ha_delete_row   
  2. #1 0x0000000000a4192b in Delete_rows_log_event::do_exec_row   
  3. #2 0x0000000000a3a9c8 in Rows_log_event::do_apply_row  
  4. #3 0x0000000000a3c1f4 in Rows_log_event::do_scan_and_update   
  5. #4 0x0000000000a3c5ef in Rows_log_event::do_hash_scan_and_update   
  6. #5 0x0000000000a3d7f7 in Rows_log_event::do_apply_event   
  7. #6 0x0000000000a28e3a in Log_event::apply_event  
  8. #7 0x0000000000a8365f in apply_event_and_update_pos  
  9. #8 0x0000000000a84764 in exec_relay_log_event   
  10. #9 0x0000000000a89e97 in handle_slave_sql  
  11. #10 0x0000000000e341c3 in pfs_spawn_thread  
  12. #11 0x0000003a00a07851 in start_thread ()   
  13. #12 0x0000003a006e767d in clone ()   

執行過程說明：

Rows_log_event::do_scan_and_update

[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. open_record_scan()  
  2.   do  
  3.     next_record_scan()  
  4.       if (m_key_index > MAX_KEY)  
  5.          ha_rnd_next();  
  6.       else  
  7.          ha_index_read_map(m_key from m_distinct_key_list)         
  8.       entry= m_hash->get()  
  9.       m_hash->del(entry);  
  10.       do_apply_row()  
  11.    while (m_hash->size > 0);  
從執行過程上可以看出，當使用hash_scan時，只會全表掃描一次，雖然會多次遍歷m_hash這個hash表，但是這個掃描是O(1),所以，代價很小，因此可以降低掃描次數，提高執行效率。



hash_scan 的一個 bug

bug詳情：http://bugs.mysql.com/bug.php?id=72788
bug原因：m_distinct_key_list 中的index key 不是唯一的，所以存在著對已經刪除了的記錄重複刪除的問題。
bug修復：http://bazaar.launchpad.net/~mysql/mysql-server/5.7/revision/8494



問題擴充套件：


  • 在沒有索引的情況下，是不是把 hash_scan 開啟就能提高效率，降低延遲呢？不一定，如果每次更新操作只一條記錄，此時仍然需要全表掃描，並且由於entry 的開銷，應該會有後退的情況；
  • 一個event中能包含多少條記錄的更新呢？這個和表結構以及記錄的資料大小有關，一個event 的大小不會超過9000 bytes, 沒有引數可以控制這個size；
  • hash_scan 有沒有限制呢？hash_scan 只會對更新、刪除操作有效，對於binlog_format=statement 產生的 Query_log_event 或者binlog_format=row 時產生的 Write_rows_log_event 不起作用；




TokuDB·版本優化·7.5.0

TokuDB　7.5.0大版本已釋出，是一個里程碑的版本，這裡談幾點優化，以饗儲存引擎愛好者們。

a) shutdown加速

有使用者反饋TokuDB在shutdown的時候，半個小時還沒完事，非常不可接受。在shutdown的時候，TokuDB在幹什麼呢？在做checkpoint，把記憶體中的節點資料序列化並壓縮到磁碟。

那為什麼如此耗時呢？如果tokudb_cache_size開的比較大，記憶體中的節點會非常多，在shutdown的時候，大家都排隊等著被壓縮到磁碟（序列的）。

在7.5.0版本，TokuDB官方針對此問題進行了優化，使多個節點並行壓縮來縮短時間。

BTW: TokuDB在早期設計的時候已保留並行介面，只是一直未開啟。

b) 內節點讀取加速

在記憶體中，TokuDB內節點(internal node)的每個message buffer都有２個重要資料結構：

 1) FIFO結構，儲存{key, value}
 2) OMT結構，儲存{key, FIFO-offset}

由於FIFO不具備快速查詢特性，就利用OMT來做快速查詢(根據key查到value)。這樣，當內節點發生cache miss的時候，索引層需要做：

 1) 從磁碟讀取節點內容到記憶體
 2) 構造FIFO結構
 3) 根據FIFO構造OMT結構(做排序)

由於TokuDB內部有不少效能探(ji)針(shu)，他們發現步驟3)是個不小的效能消耗點，因為每次都要把message buffer做下排序構造出OMT，於是在7.5.0版本，把OMT的FIFO-offset(已排序)也持久化到磁碟，這樣排序的損耗就沒了。

c) 順序寫加速

當寫發生的時候，會根據當前的key在pivots裡查詢(二分)當前寫要落入哪個mesage buffer，如果寫是順序(或區域性順序，資料走向為最右邊路徑)的，就可以避免由＂查詢＂帶來的額外開銷。

淘寶內部分享：MySQL & MariaDB效能優化



如何判斷是順序寫呢？TokuDB使用了一種簡單的啟發式方法(heurstic)：seqinsert_score積分式。如果：

1) 當前寫入落入最右節點，對seqinsert_score加一分(原子)
2) 當前寫入落入非最右節點，對seqinsert_score清零(原子)
當seqinsert_score大於100的時候，就可以認為是順序寫，當下次寫操作發生時，首先與最右的節點pivot進行對比判斷，如果確實為順序寫，則會被寫到該節點，省去不少compare開銷。方法簡單而有效。





MariaDB·　效能優化·filesort with small LIMIT optimization

從MySQL 5.6.2/MariaDB 10.0.0版本開始，MySQL/MariaDB針對"ORDER BY ...LIMIT n"語句實現了一種新的優化策略。當n足夠小的時候，優化器會採用一個容積為n的優先佇列來進行排序，而不是排序所有資料然後取出前n條。 這個新演算法可以這麼描述：（假設是ASC排序）

  1. 建立一個只有n個元素的優先佇列（堆），根節點為堆中最大元素
  2. 根據其他條件，依次從表中取出一行資料
  3. 如果當前行的排序關鍵字小於堆頭，則把當前元素替換堆頭，重新Shift保持堆的特性
  4. 再取一條資料重複2步驟，如果沒有下一條資料則執行5
  5. 依次取出堆中的元素（從大到小排序），逆序輸出（從小到大排序），即可得ASC的排序結果

這樣的演算法，時間複雜度為m*log(n)，m為索引過濾後的行數，n為LIMIT的行數。而原始的全排序演算法，時間複雜度為m*log(m)。只要n遠小於m，這個演算法就會很有效。

不過在MySQL 5.6中，除了optimizer_trace，沒有好的方法來看到這個新的執行計劃到底起了多少作用。MariaDB 10.013開始，提供一個系統狀態，可以檢視新執行計劃呼叫的次數：

 Sort_priority_queue_sorts
 描述: 通過優先佇列實現排序的次數。(總排序次數=Sort_range+Sort_scan)
 範圍: Global, Session
 資料型別: numeric
 引入版本: MariaDB 10.0.13
此外，MariaDB還將此資訊打入了Slow Log中。只要指定 log_slow_verbosity=query_plan，就可以在Slow Log中看到這樣的記錄：



[js] view plaincopy在CODE上檢視程式碼片派生到我的程式碼片
  1. # Time: 140714 18:30:39  
  2.  # User@Host: root[root] @ localhost []  
  3.  # Thread_id: 3  Schema: test  QC_hit: No  
  4.  # Query_time: 0.053857  Lock_time: 0.000188  Rows_sent: 11  Rows_examined: 100011  
  5.  # Full_scan: Yes  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  
  6.  # Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: Yes  
  7.  SET timestamp=1405348239;SET timestamp=1405348239;  
  8.  select * from t1 where col1 between 10 and 20 order by col2 limit 100;  
"Priority_queue: Yes" 就表示這個Query利用了優先佇列的執行計劃(pt-query-digest 目前已經可以解析 Priority_queue 這個列)。更多精彩內容，敬請期待！

本文轉載自MySQL.taobao.org ，感謝淘寶資料庫專案組丁奇、鳴嵩、彭立勳、皓庭、項仲、劍川、武藏、祁奚、褚霸、一工。審校：劉亞瓊

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/196700/viewspace-2079444/，如需轉載，請註明出處，否則將追究法律責任。

相關文章