MySQL · 社區貢獻 · AliSQL那些事兒 · 數據庫內核月報

一直以來我們都在不斷對我們的阿里云MySQL分支做極致的性能優化及功能擴展。我們從社區的分支，如上游版本及Percona Server上學習新的改進和功能，并引入到我們的分支中。同時我們也將我們的一些改進思路反饋到上游，讓整個社區也能享受到我們的成果。本文主要介紹下AliSQL貢獻給上游MySQL5.7版本的一些跟性能相關的優化。注意這里只摘取了幾個比較有意思的優化，在即將開源的AliSQL中，我們將包含更加全面、更加強悍的性能優化補丁以及更豐富的擴展功能，敬請期待 :) ## 優化row模式下的prepare statement性能修復版本： MySQL 5.7.2 這算是一個老問題了，但不是一個通用場景優化。在一次線上業務場景中，我們發現使用Prepare Statement語句進行數據插入時，效率非常低下。我們通過pt-pmp觀察到如下堆棧: ~~~ bmove_upp,String::replace,insert_params_with_log,Prepared_statement::set_parameters,Prepared_statement::execute_loop,mysqld_stmt_execute... ~~~ 相應的perf top輸出： ~~~ 94.14% bmove_upp 0.26% setup_one_conversion_function(THD*, Item_param*, unsigned char) 0.26% mtr_commit ~~~ 通過分析代碼，我們發現當打開Binlog并且當前是數據修改類型的語句時，會去將Prepare Statement中的”?”替換成真正的數據。而在拼湊的過程中又涉及到低效的字符串操作，從而導致性能下降。一個具體的例子是，如果構建一條Prepare語句進行批量的插入操作，那么這里涉及到的SQL構建開銷就會非常龐大。這里拼湊SQL的目的主要是為了記錄binlog。如果使用的是ROW模式，實際上沒必要進行這樣的轉換 (但如果slow log或者general log打開的話，依然需要拼湊SQL)。因此我們加入了對ROW模式的判斷。在優化后，我們以一個比較極端的例子進行測試，即一次INSERT 50,000條記錄，相比之前的數據插入速度提升了好幾倍。當然正常場景下不會有這么大的提升。當然這里的字符串操作太過低效也是一個優化點，使用的是String::replace函數，每次填充參數時，都要重分配buffer并拷貝參數。在[Bug#73056](http://bugs.mysql.com/bug.php?id=73056)，有人提出了改進的意見，即一次分配好內存空間，然后填進去字符串，而不是每次replace。這個問題將在MySQL 8.0被修復，但目前還不得而知怎么修的。 ## 記錄鎖counter優化修復版本： MySQL 5.7.5 當執行類似`SHOW ENGINE INNODB STATUS`這樣的語句時，會打印出每個session持有的事務行鎖的個數。其計算方式是遍歷該事務持有的所有鎖記錄，然后算出鎖的個數。而這個過程是持有全局大鎖lock_sys->mutex的。這意味著如果有高頻次的監控需求，就可能對性能產生影響。修改方式也很簡單，直接為每個事務添加一個計數器(trx_lock_t::n_rec_locks)來維護行鎖的個數。 ## MySQL組提交優化修復版本： MySQL 5.7.6 當Binlog打開時，MySQL使用Group Commit的方式來保證性能。大約遵循如下過程： 1. 事務在引擎層進行InnoDB Prepare，即將對應的undo狀態設置為Prepare，同時對redo日志進行持久化。這一步是無序的，由用戶線程各自發起。 2. 某個事務開始進入binlog commit階段，如果當前flush stage隊列中沒有線程，則自己作為隊列頭leader，否則作為follower。leader在加入隊列到開始真正的寫binlog之間會有一段時差，搜集到的隊列長度取決于并發度。當leader帶著隊列開始寫binlog時，其他會話將允許進入flush stage 隊列，并形成新的組。 3. 隨后進入sync stage及commit stage，多個鏈表可能被組建成更大的鏈表，并進行有序的提交。我們的優化主要是在Prepare階段，為什么在Prepare階段要持久化redo呢？這是由MySQL的XA Recover恢復機制決定的。其內部使用InnoDB和Binlog做XA的方式來實現崩潰恢復安全：當事務處于Prepare狀態時，如果binlog已經寫入文件了，就把該事務提交掉，否則將其回滾。binlog日志和innodb中的undo通過一致的Xid值相互關聯。基于上述事實，只要我們保證在寫binlog之前把Prepare的事務持久化了即可。我們將上述第一步寫日志的動作轉移到group commit的flush stage。這樣做的好處是不僅降低了全局大鎖log_sys->mutex的開銷，也將日志寫進行了顯式組提交，在高并發負載下具有更好的吞吐量。在使用sysbench, update-non-index，純內存更新的測試場景下，我們最多獲得了將近30%的性能提升。具體取決于并發度，并發越高，提升越大。早前的另外一篇[月報](http://mysql.taobao.org/index.php?title=MySQL%E5%86%85%E6%A0%B8%E6%9C%88%E6%8A%A5_2015.01#MySQL_.C2.B7_.E6.80.A7.E8.83.BD.E4.BC.98.E5.8C.96.C2.B7_Group_Commit.E4.BC.98.E5.8C.96)也對該改進做了描述，感興趣的自取。 ## InnoDB Change Buffer優化修復版本： MySQL 5.7.6 這是一個比較漫長的故事，故事的起因從2013年的[bug#70768](https://bugs.mysql.com/bug.php?id=70768)開始，早期有一個全局的latch數組，數組大小為64。這意味著不同的表有可能使用到同一個Latch來保護統計信息的更新，從而導致比較嚴重的鎖沖突問題。為了解決沖突，為每個table對象創建了一個stats_latch。然而這里引入了有個問題，InnoDB為了輔助進行記錄的解析，常常會創建一個線程私有的dummy table/dummy index。對于這樣的表和索引，為其創建latch是沒有必要的。那么這里為什么會產生的嚴重的性能問題呢([bug#71708](https://bugs.mysql.com/bug.php?id=71708))? 主要有兩點原因： 1. 當內存遠小于數據集，并且二級索引被頻繁更新時，change buffer會被觸發并被高頻度使用，相應的dummy table/index也會被頻繁創建和銷毀。 2. dummy table/index的創建和銷毀本身并沒有太大的開銷，但掛在其上的stats_latch（以及其他相關mutex）需要去持有全局鎖rw_lock_list_mutex來管理rw-lock/mutex隊列。這種鎖沖突在極端場景下會導致change buffer的性能極端低下，甚至還不如關閉change buffer的性能。為了修復[bug#71708](https://bugs.mysql.com/bug.php?id=71708)，上游對stats_latch引入了延遲創建的功能，即只在第一次使用到latch時才去創建下，從而繞過了該問題。你以為故事已經結束了嗎？并沒有！！官方的fix并沒有考慮到還有表對象上的autoinc_mutex以及index->zip_pad.mutex。這種互斥鎖的創建/銷毀對于dummy table及dummy index而言都是沒有意義的。我們提交了[Bug#73361](https://bugs.mysql.com/bug.php?id=73361)來描述這個問題。官方對此進行了修復，對表對象上的mutex也采用延遲創建的方法，這才徹底修復了該bug。 ## 提升日志并發寫性能修復版本：MySQL 5.7.13 InnoDB的日志模塊大體可以描述如下： 1. 當某些操作需要日志保護時，InnoDB通過mini transaction（簡稱mtr），首先將日志寫到本地私有cache中 2. 當操作結束時，提交mtr，將本地cache中的日志寫到全局log buffer中 3. log buffer中的日志的寫文件操作主要通過如下幾種情況觸發：后臺線程定期運行; 事務提交; 刷臟頁拷貝日志到log buffer和將log buffer數據寫到磁盤都需要全局大鎖的保護，這意味著，在寫日志時，我們無法向buffer中拷貝，從而影響了整體的吞吐量。為了解決這個問題，我們引入了雙buffer方案，假定名為buf1和buf2，并新增了一個write_mutex。 * 初始化log_sys->buf指向buf1，mtr_commit操作會將日志拷貝到buf1中。 * 當準備將Log buffer中數據寫入到磁盤時，在log_sys->mutex的保護下，將buf1的最后一個block拷貝到buf2開始部位（保證日志的連續性），將log_sys->buf指向buf2，然后釋放log_sys->mutex。這里mutex的加鎖范圍被大大的減少。此時并發線程就可以向buf2中拷貝日志了。 * 將buf1的日志寫盤，文件寫操作通過新的write_mutex保護。通過輪換的使用兩個Buffer，有效的提升了實例的吞吐量，尤其是在innodb_flush_log_at_trx_commit設置為2的時候，我們在純更新場景下測試獲得了接近20%的性能提升。 ## 降低只讀事務的鎖開銷修復版本: MySQL 5.7.14 MySQL5.7對只讀事務場景做了大量的優化，包括移除只讀事務鏈表，server層thr_lock優化等等。但并不意味著只讀事務就沒有優化的余地了。在測試新的只讀事務(非auto-commit，顯式開啟事務)邏輯時，我們發現在高并發下lock_sys->mutex的鎖沖突非常厲害，從performance schema中觀察到： ~~~ mysql> SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME FROM events_waits_summary_global_by_event_name where COUNT_STAR > 0 and EVENT_NAME like 'wait/synch/%' order by SUM_TIMER_WAIT desc limit 10; +------------+-----------------+----------------+---------------------------------------------+ | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT | EVENT_NAME | +------------+-----------------+----------------+---------------------------------------------+ | 17739300 | 172218176088930 | 9707895 | wait/synch/mutex/innodb/lock_mutex | | 35479372 | 77340476989560 | 2179785 | wait/synch/mutex/innodb/trx_sys_mutex | | 35465620 | 27221504947890 | 767340 | wait/synch/mutex/sql/LOCK_table_cache | | 159575929 | 20214954245040 | 126585 | wait/synch/mutex/sql/THD::LOCK_query_plan ~~~ 可以看到lock_sys->mutex的平均等待時間占據最高位。大量會話堵塞在如下堆棧： ~~~ pthread_cond_wait,os_cond_wait(os0sync.cc:214),os_event_wait_low(os0sync.cc:214),sync_array_wait_event(sync0arr.cc:424),mutex_spin_wait(sync0sync.cc:579),mutex_enter_func(sync0sync.ic:220),pfs_mutex_enter_func(sync0sync.ic:220),lock_trx_release_locks(sync0sync.ic:220),trx_commit_in_memory(trx0trx.cc:1381), ... ~~~ 原因是當事務是顯式開啟的事務時，InnoDB總是無條件的去加lock_sys->mutex，并嘗試釋放其持有的事務鎖記錄。而對于只讀事務而言，通常其是不會持有鎖的，也就無需去持有全局大鎖了。修改的方案很簡單，只要事務上不持有事務鎖，就不去加lock_sys->mutex。