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

淘寶丁奇發表於2015-01-17
MySql

編者按：淘寶自從2010開始規模使用MySQL，替換了之前商品、交易、使用者等原基於IOE方案的核心資料庫，目前已部署數千臺規模。同時和Oracle, Percona, Mariadb等上游廠商有良好合作，共向上遊提交20多個Patch。目前淘寶核心系統研發部資料庫組，根據淘寶的業務需求，改進資料庫和提升效能，提供高效能、可擴充套件的、穩定可靠的資料庫(儲存)解決方案。 目前有以下幾個方向：單機，提升單機資料庫的效能，增加我們所需特性；叢集，提供效能擴充套件，可靠性，可能涉及分散式事務處理；IO儲存體系，跟蹤IO裝置變化潮流， 研究軟硬體結合，輸出高效能儲存解決方案。本文是來自淘寶內部資料庫內容分享。

MySQL · 效能優化· Group Commit優化

背景

關於Group Commit網上的資料其實已經足夠多了，我這裡只簡單的介紹一下。

眾所周知，在MySQL5.6之前的版本，由於引入了Binlog/InnoDB的XA，Binlog的寫入和InnoDB commit完全序列化執行，大概的執行序列如下：

 InnoDB prepare  （持有prepare_commit_mutex）；
 write/sync Binlog；
 InnoDB commit (寫入COMMIT標記後釋放prepare_commit_mutex)。

當sync_binlog=1時，很明顯上述的第二步會成為瓶頸，而且還是持有全域性大鎖，這也是為什麼效能會急劇下降。

很快Mariadb就提出了一個Binlog Group Commit方案，即在準備寫入Binlog時，維持一個佇列，最早進入佇列的是leader，後來的是follower，leader為蒐集到的佇列中的執行緒依次寫Binlog檔案, 並commit事務。Percona 的Group Commit實現也是Port自Mariadb。不過仍在使用Percona Server5.5的朋友需要注意，該Group Commit實現可能破壞掉Semisync的行為，感興趣的點選  bug#1254571

Oracle MySQL 在5.6版本開始也支援Binlog Group Commit，使用了和Mariadb類似的思路，但將Group Commit的過程拆分成了三個階段：flush stage 將各個執行緒的binlog從cache寫到檔案中; sync stage 對binlog做fsync操作（如果需要的話）；commit stage 為各個執行緒做引擎層的事務commit。每個stage同時只有一個執行緒在操作。

Tips：當引入Group Commit後，sync_binlog的含義就變了，假定設為1000，表示的不是1000個事務後做一次fsync，而是1000個事務組。

Oracle MySQL的實現的優勢在於三個階段可以併發執行，從而提升效率。

XA Recover

在Binlog開啟的情況下，MySQL預設使用MySQL_BIN_LOG來做XA協調者，大致流程為：

1.掃描最後一個Binlog檔案，提取其中的xid；
2.InnoDB維持了狀態為Prepare的事務連結串列，將這些事務的xid和Binlog中記錄的xid做比較，如果在Binlog中存在，則提交，否則回滾事務。

通過這種方式，可以讓InnoDB和Binlog中的事務狀態保持一致。顯然只要事務在InnoDB層完成了Prepare，並且寫入了Binlog，就可以從崩潰中恢復事務，這意味著我們無需在InnoDB commit時顯式的write/fsync redo log。

Tips：MySQL為何只需要掃描最後一個Binlog檔案呢 ？ 原因是每次在rotate到新的Binlog檔案時，總是保證沒有正在提交的事務，然後fsync一次InnoDB的redo log。這樣就可以保證老的Binlog檔案中的事務在InnoDB總是提交的。

問題

其實問題很簡單：每個事務都要保證其Prepare的事務被write/fsync到redo log檔案。儘管某個事務可能會幫助其他事務完成redo 寫入，但這種行為是隨機的，並且依然會產生明顯的log_sys->mutex開銷。

優化

從XA恢復的邏輯我們可以知道，只要保證InnoDB Prepare的redo日誌在寫Binlog前完成write/sync即可。因此我們對Group Commit的第一個stage的邏輯做了些許修改，大概描述如下：

 Step1. InnoDB Prepare，記錄當前的LSN到thd中；
Step2. 進入Group Commit的flush stage；Leader蒐集佇列，同時算出佇列中最大的LSN。
Step3. 將InnoDB的redo log write/fsync到指定的LSN
Step4. 寫Binlog並進行隨後的工作(sync Binlog, InnoDB commit , etc)

通過延遲寫redo log的方式，顯式的為redo log做了一次組寫入，並減少了log_sys->mutex的競爭。

目前官方MySQL已經根據我們report的bug#73202鎖提供的思路，對5.7.6的程式碼進行了優化，對應的Release Note如下：

When using InnoDB with binary logging enabled, concurrent transactions written in the InnoDB redo log are now grouped together before synchronizing to disk when innodb_flush_log_at_trx_commit is set to 1, which reduces the amount of synchronization operations. This can lead to improved performance.

效能資料

簡單測試了下，使用sysbench, update_non_index.lua, 100張表，每張10w行記錄，innodb_flush_log_at_trx_commit=2, sync_binlog=1000，關閉Gtid

 併發執行緒        原生                  修改後
 32             25600                27000
 64             30000                35000
 128            33000                39000
 256            29800                38000

MySQL · 新增特性· DDL fast fail

背景

專案的快速迭代開發和線上業務需要保持持續可用的要求，導致MySQL的ddl變成了DBA很頭疼的事情，而且經常導致故障發生。本篇介紹RDS分支上做的一個功能改進，DDL fast fail。主要解決：DDL操作因為無法獲取MDL排它鎖，進入等待佇列的時候，阻塞了應用所有的讀寫請求問題。

MDL鎖機制介紹

首先介紹一下MDL(METADATA LOCK)鎖機制，MySQL為了保證表結構的完整性和一致性，對錶的所有訪問都需要獲得相應級別的MDL鎖，比如以下場景：

session 1: start transaction; select * from test.t1;
session 2: alter table test.t1 add extra int;
session 3: select * from test.t1;

  • session 1對t1表做查詢，首先需要獲取t1表的MDL_SHARED_READ級別MDL鎖。鎖一直持續到commit結束，然後釋放。
  • session 2對t1表做DDL，需要獲取t1表的MDL_EXCLUSIVE級別MDL鎖，因為MDL_SHARED_READ與MDL_EXCLUSIVE不相容，所以session 2被session 1阻塞，然後進入等待佇列。
  • session 3對t1表做查詢，因為等待佇列中有MDL_EXCLUSIVE級別MDL鎖請求，所以session3也被阻塞，進入等待佇列。

這種場景就是目前因為MDL鎖導致的很經典的阻塞問題，如果session1長時間未提交，或者查詢持續過長時間，那麼後續對t1表的所有讀寫操作，都被阻塞。 對於線上的業務來說，很容易導致業務中斷。

aliyun RDS分支改進

DDL fast fail並沒有解決真正DDL過程中的阻塞問題，但避免了因為DDL操作沒有獲取鎖，進而導致業務其他查詢/更新語句阻塞的問題。

其實現方式如下：

alter table test.t1 no_wait/wait 1 add extra int;
在ddl語句中，增加了no_wait/wait 1語法支援。

其處理邏輯如下：

首先嚐試獲取t1表的MDL_EXCLUSIVE級別的MDL鎖：

  • 當語句指定的是no_wait，如果獲取失敗，客戶端將得到報錯資訊：ERROR : Lock wait timeout exceeded; try restarting transaction。
  • 當語句指定的是wait 1，如果獲取失敗，最多等待1s，然後得到報錯資訊：ERROR : Lock wait timeout exceeded; try restarting transaction。

另外，除了alter語句以外，還支援rename，truncate，drop，optimize，create index等ddl操作。

與Oracle的比較

在Oracle 10g的時候，DDL操作經常會遇到這樣的錯誤資訊：

ora-00054:resource busy and acquire with nowait specified 即DDL操作無法獲取表上面的排它鎖，而fast fail。

其實DDL獲取排他鎖的設計，需要考慮的就是兩個問題：

  1. 雪崩，如果你採用排隊阻塞的機制，那麼DDL如果長時間無法獲取鎖，就會導致應用的雪崩效應，對於高併發的業務，也是災難。
  2. 餓死，如果你採用強制式的機制，那麼要防止DDL一直無法獲取鎖的情況，在業務高峰期，可能DDL永遠無法成功。

在Oracle 11g的時候，引入了DDL_LOCK_TIMEOUT引數，如果你設定了這個引數，那麼DDL操作將使用排隊阻塞模式，可以在session和global級別設定， 給了使用者更多選擇。

MySQL · 效能優化· 啟用GTID場景的效能問題及優化

背景

MySQL從5.6版本開始支援GTID特性，也就是所謂全域性事務ID，在整個複製拓撲結構內，每個事務擁有自己全域性唯一標識。GTID包含兩個部分，一部分是例項的UUID，另一部分是例項內遞增的整數。

GTID的分配包含兩種方式，一種是自動分配，另外一種是顯式設定session.gtid_next，下面簡單介紹下這兩種方式：

自動分配

如果沒有設定session級別的變數gtid_next，所有事務都走自動分配邏輯。分配GTID發生在GROUP COMMIT的第一個階段，也就是flush stage，大概可以描述為：

  •  Step 1：事務過程中，碰到第一條DML語句需要記錄Binlog時，分配一段Gtid事件的cache，但不分配實際的GTID
  •  Step 2：事務完成後，進入commit階段，分配一個GTID並寫入Step1預留的Gtid事件中，該GTID必須保證不在gtid_owned集合和gtid_executed集合中。 分配的GTID隨後被加入到gtid_owned集合中。
  •  Step 3：將Binlog 從執行緒cache中刷到Binlog檔案中。
  •  Step 4：將GTID加入到gtid_executed集合中。
  •  Step 5：在完成sync stage 和commit stage後，各個會話將其使用的GTID從gtid_owned中移除。

顯式設定

使用者通過設定session級別變數gtid_next可以顯式指定一個GTID，流程如下：

  •  Step 1：設定變數gtid_next，指定的GTID被加入到gtid_owned集合中。
  •  Step 2：執行任意事務SQL，在將binlog從執行緒cache刷到binlog檔案後，將GTID加入到gtid_executed集合中。
  •  Step 3：在完成事務COMMIT後，從gtid_owned中移除。

備庫SQL執行緒使用的就是第二種方式，因為備庫在apply主庫的日誌時，要保證GTID是一致的，SQL執行緒讀取到GTID事件後，就根據其中記錄的GTID來設定其gtid_next變數。

問題

由於在例項內，GTID需要保證唯一性，因此不管是操作gtid_executed集合和gtid_owned集合，還是分配GTID，都需要加上一個大鎖。我們的優化主要集中在第一種GTID分配方式。

對於GTID的分配，由於處於Group Commit的第一個階段，由該階段的leader執行緒為其follower執行緒分配GTID及刷Binlog，因此不會產生競爭。

而在Step 5，各個執行緒在完成事務提交後，各自去從gtid_owned集合中刪除其使用的gtid。這時候每個執行緒都需要獲取互斥鎖，很顯然，併發越高，這種競爭就越明顯，我們很容易從pt-pmp輸出中看到如下類似的trace：

 ha_commit_trans—>MySQL_BIN_LOG::commit—>MySQL_BIN_LOG::ordered_commit—>MySQL_BIN_LOG::finish_commit—>Gtid_state::update_owned_gtids_impl—>lock_sidno

這同時也會影響到GTID的分配階段，導致TPS在高併發場景下的急劇下降。

解決

實際上對於自動分配GTID的場景，並沒有必要維護gtid_owned集合。我們的修改也非常簡單，在自動分配一個GTID後，直接加入到gtid_executed集合中，避免維護gtid_owned，這樣事務提交時就無需去清理gtid_owned集合了，從而可以完全避免鎖競爭。

當然為了保證一致性，如果分配GTID後，寫入Binlog檔案失敗，也需要從gtid_executed集合中刪除。不過這種場景非常罕見。

效能資料

使用sysbench，100張表，每張10w行記錄，update_non_index.lua，純記憶體操作，innodb_flush_log_at_trx_commit = 2，sync_binlog = 1000

 併發執行緒       原生               修改後
 32           24500              25000
 64           27900              29000
 128          30800              31500
 256          29700              32000
 512          29300              31700
 1024         27000              31000

從測試結果可以看到，優化前隨著併發上升，效能出現下降，而優化後則能保持TPS穩定。

MySQL · 捉蟲動態· InnoDB自增列重複值問題

問題重現

先從問題入手，重現下這個 bug

use test;
drop table if exists t1;
create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
insert into t1 values (1,2);
insert into t1 values (null,2);
insert into t1 values (null,2);
select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
+----+------+
delete from t1 where id=2;
delete from t1 where id=3;
select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+

這裡我們關閉MySQL，再啟動MySQL,然後再插入一條資料

insert into t1 values (null,2);
select * FROM T1;
+----+------+
| id | a |
+----+------+
| 1 | 2 |
+----+------+
| 2 | 2 |
+----+------+

我們看到插入了（2,2），而如果我沒有重啟，插入同樣資料我們得到的應該是（4,2)。 上面的測試反映了MySQLd重啟後，InnoDB儲存引擎的表自增id可能出現重複利用的情況。

自增id重複利用在某些場景下會出現問題。依然用上面的例子，假設t1有個歷史表t1_history用來存t1錶的歷史資料，那麼MySQLd重啟前,ti_history中可能已經有了（2,2）這條資料，而重啟後我們又插入了（2，2），當新插入的(2,2)遷移到歷史表時，會違反主鍵約束。

原因分析

InnoDB 自增列出現重複值的原因：

MySQL> show create table t1\G;
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=innodb AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

建表時可以指定 AUTO_INCREMENT值，不指定時預設為1，這個值表示當前自增列的起始值大小，如果新插入的資料沒有指定自增列的值，那麼自增列的值即為這個起始值。對於InnoDB表，這個值沒有持久到檔案中。而是存在記憶體中（dict_table_struct.autoinc）。那麼又問，既然這個值沒有持久下來，為什麼我們每次插入新的值後， show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實show create table t1是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

知道了AUTO_INCREMENT是實時儲存記憶體中的。那麼，MySQLd 重啟後，從哪裡得到AUTO_INCREMENT呢? 記憶體值肯定是丟失了。實際上MySQL採用執行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT。而這種方法就是造成自增id重複的原因。

MyISAM自增值

MyISAM也有這個問題嗎？MyISAM是沒有這個問題的。myisam會將這個值實時儲存在.MYI檔案中（mi_state_info_write）。MySQLd重起後會從.MYI中讀取AUTO_INCREMENT值（mi_state_info_read)。因此，MyISAM表重啟是不會出現自增id重複的問題。

問題修復

MyISAM選擇將AUTO_INCREMENT實時儲存在.MYI檔案頭部中。實際上.MYI頭部還會實時存其他資訊，也就是說寫AUTO_INCREMENT只是個順帶的操作，其效能損耗可以忽略。InnoDB 表如果要解決這個問題，有兩種方法。

1）將AUTO_INCREMENT最大值持久到frm檔案中。
2）將 AUTO_INCREMENT最大值持久到聚集索引根頁trx_id所在的位置。

第一種方法直接寫檔案效能消耗較大，這是一額外的操作，而不是一個順帶的操作。我們採用第二種方案。為什麼選擇儲存在聚集索引根頁頁頭trx_id，頁頭中儲存trx_id,只對二級索引頁和insert buf 頁頭有效（MVCC)。而聚集索引根頁頁頭trx_id這個值是沒有使用的，始終保持初始值0。正好這個位置8個位元組可存放自增值的值。我們每次更新AUTO_INCREMENT值時，同時將這個值修改到聚集索引根頁頁頭trx_id的位置。 這個寫操作跟真正的資料寫操作一樣，遵守write-ahead log原則，只不過這裡只需要redo log ,而不需要undo log。因為我們不需要回滾AUTO_INCREMENT的變化（即回滾後自增列值會保留，即使insert 回滾了，AUTO_INCREMENT值不會回滾）。

因此，AUTO_INCREMENT值儲存在聚集索引根頁trx_id所在的位置，實際上是對記憶體根頁的修改和多了一條redo log（量很小）,而這個redo log 的寫入也是非同步的，可以說是原有事務log的一個順帶操作。因此AUTO_INCREMENT值儲存在聚集索引根頁這個效能損耗是極小的。

修復後的效能對比，我們新增了全域性引數innodb_autoinc_persistent 取值on/off； on 表示將AUTO_INCREMENT值實時儲存在聚集索引根頁。off則採用原有方式只儲存在記憶體。

./bin/sysbench --test=sysbench/tests/db/insert.lua --MySQL-port=4001 --MySQL-user=root \--MySQL-table-engine=innodb --MySQL-db=sbtest --oltp-table-size=0 --oltp-tables-count=1 \--num-threads=100 --MySQL-socket=/u01/zy/sysbench/build5/run/MySQL.sock  --max-time=7200 --max-requests run
set global innodb_autoinc_persistent=off;
tps: 22199 rt:2.25ms
set global innodb_autoinc_persistent=on;
tps: 22003 rt:2.27ms

可以看出效能損耗在%1以下。

改進

新增引數innodb_autoinc_persistent_interval 用於控制持久化AUTO_INCREMENT值的頻率。例如：innodb_autoinc_persistent_interval=100，auto_incrememt_increment=1時，即每100次insert會控制持久化一次AUTO_INCREMENT值。每次持久的值為：當前值+innodb_autoinc_persistent_interval。

測試結論

 innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=1時效能損耗在%1以下。
innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=100時效能損耗可以忽略。

限制

  1.  innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=N>1時，自增N次後持久化到聚集索引根頁,每次持久的值為當前AUTO_INCREMENT+(N-1)*innodb_autoextend_increment。重啟後讀取持久化的AUTO_INCREMENT值會偏大，造成一些浪費但不會重複。innodb_autoinc_persistent_interval=1 每次都持久化沒有這個問題。
  2.  如果innodb_autoinc_persistent=on，頻繁設定auto_increment_increment的可能會導致持久化到聚集索引根頁的值不準確。因為innodb_autoinc_persistent_interval計算沒有考慮auto_increment_increment變化的情況，參看dict_table_autoinc_update_if_greater。而設定auto_increment_increment的情況極少，可以忽略。

注意：如果我們使用需要開啟innodb_autoinc_persistent，應該在引數檔案中指定

innodb_autoinc_persistent= on

如果這樣指定set global innodb_autoinc_persistent=on;重啟後將不會從聚集索引根頁讀取AUTO_INCREMENT最大值。

疑問：對於InnoDB表，重啟通過select max(id)+1 from t1得到AUTO_INCREMENT值，如果id上有索引那麼這個語句使用索引查詢就很快。那麼，這個可以解釋MySQL 為什麼要求自增列必須包含在索引中的原因。 如果沒有指定索引，則報如下錯誤，

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key 而myisam表竟然也有這個要求，感覺是多餘的。

MySQL · 優化改進· 複製效能改進過程

前言

與oracle 不同，MySQL 的主庫與備庫的同步是通過 binlog 實現的，而redo日誌只做為MySQL 例項的crash recovery使用。MySQL在4.x 的時候放棄redo 的同步策略而引入 binlog的同步，一個重要原因是為了相容其它非事務儲存引擎，否則主備同步是沒有辦法進行的。

redo 日誌同步屬於物理同步方法，簡單直接，將修改的物理部分傳送到備庫執行，主備共用一致的 LSN，只要保證 LSN 相同即可，同一時刻，只能主庫或備庫一方接受寫請求； binlog的同步方法屬於邏輯複製，分為statement 或 row 模式，其中statement記錄的是SQL語句，Row 模式記錄的是修改之前的記錄與修改之後的記錄，即前映象與後映象；備庫通過binlog dump 協議拉取binlog,然後在備庫執行。如果拉取的binlog是SQL語句，備庫會走和主庫相同的邏輯，如果是row 格式，則會呼叫儲存引擎來執行相應的修改。

本文簡單說明5.5到5.7的主備複製效能改進過程。

replication improvement (from 5.5 to 5.7)

(1) 5.5 中，binlog的同步是由兩個執行緒執行的

io_thread: 根據binlog dump協議從主庫拉取binlog, 並將binlog轉存到本地的relaylog；

sql_thread: 讀取relaylog，根據位點的先後順序執行binlog event，進而將主庫的修改同步到備庫，達到主備一致的效果； 由於在主庫的更新是由多個客戶端執行的，所以當壓力達到一定的程度時，備庫單執行緒執行主庫的binlog跟不上主庫執行的速度，進而會產生延遲造成備庫不可用，這也是分庫的原因之一，其SQL執行緒的執行堆疊如下：

sql_thread:
exec_relay_log_event
    apply_event_and_update_pos
         apply_event
             rows_log_event::apply_event
                 storage_engine operation
         update_pos

(2) 5.6 中，引入了多執行緒模式，在多執行緒模式下，其執行緒結構如下

io_thread: 同5.5

Coordinator_thread: 負責讀取 relay log，將讀取的binlog event以事務為單位分發到各個 worker thread 進行執行，並在必要時執行binlog event（Description_format_log_event， Rotate_log_event 等）。

worker_thread: 執行分配到的binlog event，各個執行緒之間互不影響；

多執行緒原理

sql_thread 的分發原理是依據當前事務所操作的資料庫名稱來進行分發，如果事務是跨資料庫行為的，則需要等待已分配的該資料庫的事務全部執行完畢，才會繼續分發，其分配行為的偽碼可以簡單的描述如下：

get_slave_worker
  if (contains_partition_info(log_event))
     db_name= get_db_name(log_event);
     entry {db_name, worker_thread, usage} = map_db_to_worker(db_name);
     while (entry->usage > 0)
        wait();
    return worker;
  else if (last_assigned_worker)
    return last_assigned_worker;
  else
    push into buffer_array and deliver them until come across a event that have partition info

需要注意的細節

  • 記憶體的分配與釋放。relay thread 每讀取一個log_event, 則需要 malloc 一定的記憶體，在work執行緒執行完後，則需要free掉；
  • 資料庫名 與 worker 執行緒的繫結資訊在一個hash表中進行維護，hash表以entry為單位，entry中記錄當前entry所代表的資料庫名，有多少個事務相關的已被分發，執行這些事務的worker thread等資訊；
  • 維護一個繫結資訊的array , 在分發事務的時候，更新繫結資訊，增加相應 entry->usage, 在執行完一個事務的時候，則需要減少相應的entry->usage；
  • slave worker 資訊的維護，即每個 worker thread執行了哪些事務，執行到的位點是在哪，延遲是如何計算的，如果執行出錯，mts_recovery_group 又是如何恢復的；
  • 分配執行緒是以資料庫名進行分發的，當一個例項中只有一個資料庫的時候，不會對效能有提高，相反，由於增加額外的操作，效能還會有一點回退；
  • 臨時表的處理，臨時表是和entry繫結在一起的，在執行的時候將entry的臨時表掛在執行執行緒thd下面，但沒有固化，如果在臨時表操作期間，備庫crash，則重啟後備庫會有錯誤；

總體上說，5.6 的並行複製打破了5.5 單執行緒的複製的行為，只是在單庫下用處不大，並且5.6的並行複製的改動引入了一些重量級的bug

  • MySQL slave sql thread memory leak (http://bugs.MySQL.com/bug.php?id=71197)
  • Relay log without xid_log_event may case parallel replication hang (http://bugs.MySQL.com/bug.php?id=72794)
  • Transaction lost when relay_log_info_repository=FILE and crashed (http://bugs.MySQL.com/bug.php?id=73482)

(3) 5.7中，並行複製的實現新增了另外一種並行的方式，即主庫在 ordered_commit中的第二階段的時候，將同一批commit的 binlog 打上一個相同的seqno標籤，同一時間戳的事務在備庫是可以同時執行的，因此大大簡化了並行複製的邏輯，並打破了相同 DB 不能並行執行的限制。備庫在執行時，具有同一seqno的事務在備庫可以並行的執行，互不干擾，也不需要繫結資訊，後一批seqno的事務需要等待前一批相同seqno的事務執行完後才可以執行。

詳細實現可參考： http://bazaar.launchpad.net/~MySQL/MySQL-server/5.7/revision/6256 。

reference： http://geek.rohitkalhans.com/2013/09/enhancedMTS-deepdive.html

MySQL · 談古論今· key分割槽演算法演變分析

本文說明一個物理升級導致的 “資料丟失”。

現象

在MySQL 5.1下新建key分表，可以正確查詢資料。

drop table t1;

create table t1 (c1 int , c2 int) 
PARTITION BY KEY (c2) partitions 5; 
insert into t1  values(1,1785089517),(2,null); 
MySQL> select * from t1 where c2=1785089517;
+------+------------+
| c1   | c2         |
+------+------------+
|    1 | 1785089517 |
+------+------------+
1 row in set (0.00 sec)
MySQL> select * from t1 where c2 is null;
+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)

而直接用MySQL5.5或MySQL5.6啟動上面的5.1例項，發現(1,1785089517)這行資料不能正確查詢出來。

alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2)  partitions 5;
MySQL> select * from t1 where c2 is null;
+------+------+
| c1   | c2   |
+------+------+
|    2 | NULL |
+------+------+
1 row in set (0.00 sec)
MySQL> select * from t1 where c2=1785089517;
Empty set (0.00 sec)

原因分析

跟蹤程式碼發現，5.1 與5.5,5.6 key hash演算法是有區別的。

5.1 對於非空值的處理演算法如下

void my_hash_sort_bin(const CHARSET_INFO *cs __attribute__((unused)),
                     const uchar *key, size_t len,ulong *nr1, ulong *nr2)
{
  const uchar *pos = key; 

  key+= len;

  for (; pos < (uchar*) key&nbsp;; pos++)
  {
    nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 
             ((uint)*pos)) + (nr1[0] << 8);
    nr2[0]+=3;
  }
}

通過此演算法算出資料(1,1785089517)在第3個分割槽

5.5和5.6非空值的處理演算法如下

void my_hash_sort_simple(const CHARSET_INFO *cs,
                         const uchar *key, size_t len,
                         ulong *nr1, ulong *nr2)
{
  register uchar *sort_order=cs->sort_order;
  const uchar *end;

  /* 
    Remove end space. We have to do this to be able to compare
    'A ' and 'A' as identical
  */        
  end= skip_trailing_space(key, len);

  for (; key < (uchar*) end&nbsp;; key++)
  {
    nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 
            ((uint) sort_order[(uint) *key])) + (nr1[0] << 8);
    nr2[0]+=3;
  }
}

通過此演算法算出資料(1,1785089517)在第5個分割槽，因此，5.5,5.6查詢不能查詢出此行資料。

5.1,5.5,5.6對於空值的演算法還是一致的,如下

if (field->is_null())
{
  nr1^= (nr1 << 1) | 1;
  continue;
}

都能正確算出資料(2, null)在第3個分割槽。因此，空值可以正確查詢出來。

那麼是什麼導致非空值的hash演算法走了不同路徑呢？在5.1下，計算欄位key hash固定字符集就是my_charset_bin，對應的hash 函式就是前面的my_hash_sort_simple。而在5.5，5.6下，計算欄位key hash的字符集是隨欄位變化的，欄位c2型別為int對應my_charset_numeric，與之對應的hash函式為my_hash_sort_simple。具體可以參考函式Field::hash

那麼問題又來了，5.5後為什麼演算法會變化呢？原因在於官方關於字符集策略的調整，詳見WL#2649 。

相容處理

前面講到，由於hash 演算法變化，用5.5，5.6啟動5.1的例項，導致不能正確查詢資料。那麼5.1升級5.5,5.6就必須相容這個問題.MySQL 5.5.31以後，提供了專門的語法 ALTER TABLE … PARTITION BY ALGORITHM=1 [LINEAR] KEY …  用於相容此問題。對於上面的例子，用5.5或5.6啟動5.1的例項後執行

MySQL> alter table t1 PARTITION BY KEY ALGORITHM = 1 (c2) partitions 5;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
MySQL> select * from t1 where c2=1785089517;
+------+------------+
| c1   | c2         |
+------+------------+
|    1 | 1785089517 |
+------+------------+
1 row in set (0.00 sec)

資料可以正確查詢出來了。

而實際上5.5,5.6的MySQL_upgrade升級程式已經提供了相容方法。MySQL_upgrade 執行check table xxx for upgrade 會檢查key分割槽表是否用了老的演算法。如果使用了老的演算法，會返回

MySQL> CHECK TABLE t1  FOR UPGRADE\G
*************************** 1. row ***************************
   Table: test.t1
      Op: check
Msg_type: error
Msg_text: KEY () partitioning changed, please run:
ALTER TABLE `test`.`t1` PARTITION BY KEY /*!50611 ALGORITHM = 1 */ (c2)
PARTITIONS 5
*************************** 2. row ***************************
   Table: test.t1
      Op: check
Msg_type: status
Msg_text: Operation failed
2 rows in set (0.00 sec)

檢查到錯誤資訊後會自動執行以下語句進行相容。

ALTER TABLE `test`.`t1` PARTITION BY KEY /*!50611 ALGORITHM = 1 */ (c2) PARTITIONS 5。

MySQL · 捉蟲動態· MySQL client crash一例

背景

客戶使用MySQLdump匯出一張表，然後使用MySQL -e ‘source test.dmp’的過程中client程式crash，爆出記憶體的segment fault錯誤，導致無法匯入資料。

問題定位

test.dmp檔案大概50G左右，檢視了一下檔案的前幾行內容，發現：

 A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database If you don't want to restore GTIDs pass set-gtid-purged=OFF. To make a complete dump, pass...
 -- MySQL dump 10.13  Distrib 5.6.16, for Linux (x86_64)
 --
 -- Host: 127.0.0.1    Database: carpath
 -- ------------------------------------------------------
 -- Server version       5.6.16-log
 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

問題定位到第一行出現了不正常warning的資訊，是由於客戶使用MySQLdump命令的時候，重定向了stderr。即:

MySQLdump …>/test.dmp 2>&1

導致error或者warning資訊都重定向到了test.dmp, 最終導致失敗。

問題引申

問題雖然定位到了，但卻有幾個問題沒有弄清楚：

問題1. 不正常的sql，執行失敗，報錯出來就可以了，為什麼會導致crash？

MySQL.cc::add_line函式中，在讀第一行的時候，讀取到了don’t,發現有一個單引號，所以程式死命的去找匹配的另外一個單引號，導致不斷的讀取檔案，分配記憶體，直到crash。
假設沒有這個單引號，MySQL讀到第六行，發現;號，就會執行sql，並正常的報錯退出。

問題2. 那程式碼中對於大小的邊界到底是多少？比如insert語句支援batch insert時，語句的長度多少，又比如遇到clob欄位呢？

  • 首先clob欄位的長度限制。clob家族型別的column長度受限於max_allowed_packet的大小，MySQL 5.5中，對於max_allowd_packet的大小限制在(1024, 1024*1024*1024)之間。
  • MySQLdump匯出insert語句的時候，如何分割insert語句？MySQLdump時候支援insert t1 value(),(),();這樣的batch insert語句。 MySQLdump其實是根據opt_net_buffer_length來進行分割，當一個insert語句超過這個大小，就強制分割到下一個insert語句中，這樣更多的是在做網路層的優化。又如果遇到大的clob欄位怎麼辦？ 如果一行就超過了opt_net_buffer_length，那就強制每一行都分割。
  • MySQL client端讀取dump檔案的時候, 到底能分配多大的記憶體？MySQL.cc中定義了:#define MAX_BATCH_BUFFER_SIZE (1024L * 1024L * 1024L)。 也就是MySQL在執行語句的時候，最多隻能分配1G大小的快取。

所以，正常情況下，max_allowed_packet現在的最大欄位長度和MAX_BATCH_BUFFER_SIZE限制的最大insert語句，是匹配的。

RDS問題修復原則

從問題的定位上來看，這一例crash屬於客戶錯誤使用MySQLdump導致的問題，Aliyun RDS分支對記憶體導致的crash問題，都會定位並反饋給使用者。 但此例不做修復，而是引導使用者正確的使用MySQLdump工具。

MySQL · 捉蟲動態· 設定 gtid_purged 破壞AUTO_POSITION複製協議

bug描述

Oracle 最新發布的版本 5.6.22 中有這樣一個關於GTID的bugfix，在主備場景下，如果我們在主庫上 SET GLOBAL GTID_PURGED = “some_gtid_set”，並且 some_gtid_set 中包含了備庫還沒複製的事務，這個時候如果備庫接上主庫的話，預期結果是主庫返回錯誤，IO執行緒掛掉的，但是實際上，在這種場景下主庫並不報錯，只是默默的把自己 binlog 中包含的gtid事務發給備庫。這個bug的造成的結果是看起來複制正常，沒有錯誤，但實際上備庫已經丟事務了，主備很可能就不一致了。

背景知識

  • binlog GTID事件

binlog 中記錄的和GTID相關的事件主要有2種，Previous_gtids_log_event 和 Gtid_log_event，前者表示之前的binlog中包含的gtid的集合，後者就是一個gtid，對應一個事務。一個 binlog 檔案中只有一個 Previous_gtids_log_event，放在開頭，有多個 Gtid_log_event，如下面所示

Previous_gtids_log_event   // 此 binlog 之前的所有binlog檔案包含的gtid集合

Gtid_log_event // 單個gtid event
Transaction
Gtid_log_event
Transaction
.
.
.
Gtid_log_event
Transaction
  • 備庫傳送GTID集合給主庫

我們知道備庫的複製執行緒是分IO執行緒和SQL執行緒2種的，IO執行緒通過GTID協議或者檔案位置協議拉取主庫的binlog，然後記錄在自己的relay log中；SQL執行緒通過執行realy log中的事件，把其中的操作都自己做一遍，記入本地binlog。在GTID協議下，備庫向主庫傳送拉取請求的時候，會告知主庫自己已經有的所有的GTID的集合，Retrieved_Gtid_Set + Executed_Gtid_Set，前者對應 realy log 中所有的gtid集合，表示已經拉取過的，後者對應binlog中記錄有的，表示已經執行過的；主庫在收到這2個總集合後，會掃描自己的binlog，找到合適的binlog然後開始傳送。

  • 主庫如何找到要傳送給備庫的第一個binlog

主庫將備庫傳送過來的總合集記為 slave_gtid_executed，然後呼叫 find_first_log_not_in_gtid_set(slave_gtid_executed)，這個函式的目的是從最新到最老掃描binlog檔案，找到第一個含有不存在 slave_gtid_executed 這個集合的gtid的binlog。在這個掃描過程中並不需要從頭到尾讀binlog中所有的gtid，只需要讀出 Previous_gtids_log_event ，如果Previous_gtids_log_event 不是 slave_gtid_executed的子集，就繼續向前找binlog，直到找到為止。

這個查詢過程總會停止的，停止條件如下：

  1. 找到了這樣的binlog，其Previous_gtids_log_event 是slave_gtid_executed子集
  2. 在往前讀binlog的時候，發現沒有binlog檔案了（如被purge了），但是還沒找到滿足條件的Previous_gtids_log_event，這個時候主庫報錯
  3. 一直往前找，發現Previous_gtids_log_event 是空集

在條件2下，報錯資訊是這樣的

Got fatal error 1236 from master when reading data from binary log: ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.

其實上面的條件3是條件1的特殊情況，這個bugfix針對的場景就是條件3這種，但並不是所有的符合條件3的場景都會觸發這個bug，下面就分析下什麼情況下才會觸發bug。

bug 分析

假設有這樣的場景，我們要用已經有MySQL例項的備份重新做一對主備例項，不管是用 xtrabackup 這種物理備份工具或者MySQLdump這種邏輯備份工具，都會有2步操作，

  1. 匯入資料
  2. SET GLOBAL GTID_PURGED =”xxxx”

步驟2是為了保證GTID的完備性，因為新例項已經匯入了資料，就需要把生成這些資料的事務對應的GTID集合也設定進來。

正常的操作是主備都要做這2步的，如果我們只在主庫上做了這2步，備庫什麼也不做，然後就直接用 GTID 協議把備庫連上來，按照我們的預期這個時候是應該出錯的，主備不一致，並且主庫的binlog中沒東西，應該報之前停止條件2報的錯。但是令人大跌眼鏡的是主庫不報錯，複製看起來是完全正常的。

為啥會這樣呢，SET GLOBAL GTID_PURGED 操作會呼叫 MySQL_bin_log.rotate_and_purge切換到一個新的binlog，並把這個GTID_PURGED 集合記入新生成的binlog的Previous_gtids_log_event，假設原有的binlog為A，新生成的為B，主庫剛啟動，所以A就是主庫的第一個binlog，它之前啥也沒有，A的Previous_gtids_log_event就是空集，並且A中也不包含任何GTID事件，否則SET GLOBAL GTID_PURGED是做不了的。按照之前的掃描邏輯，掃到A是肯定會停下來的，並且不報錯。

bug 修復

官方的修復就是在主庫掃描查詢binlog之前，判斷一下 gtid_purged 集合不是不比slave_gtid_executed大，如果是就報錯，錯誤資訊和條件2一樣 Got fatal error 1236 from master when reading data from binary log: ‘The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires。

MySQL · 捉蟲動態· replicate filter 和 GTID 一起使用的問題

問題描述

當單個 MySQL 例項的資料增長到很多的時候，就會考慮通過庫或者表級別的拆分，把當前例項的資料分散到多個例項上去，假設原例項為A，想把其中的5個庫（db1/db2/db3/db4/db5）拆分到5個例項（B1/B2/B3/B4/B5）上去。

拆分過程一般會這樣做，先把A的相應庫的資料匯出，然後匯入到對應的B例項上，但是在這個匯出匯入過程中，A庫的資料還是在持續更新的，所以還需在匯入完後，在所有的B例項和A例項間建立複製關係，拉取缺失的資料，在業務不繁忙的時候將業務切換到各個B例項。

在複製搭建時，每個B例項只需要複製A例項上的一個庫，所以只需要重放對應庫的binlog即可，這個通過 replicate-do-db 來設定過濾條件。如果我們用備庫上執行 show slave status\G 會看到Executed_Gtid_Set是斷斷續續的，間斷非常多，導致這一列很長很長，看到的直接效果就是被刷屏了。

為啥會這樣呢，因為設了replicate-do-db，就只會執行對應db對應的event，其它db的都不執行。主庫的執行是不分db的，對各個db的操作互相間隔，記錄在binlog中，所以備庫做了過濾後，就出現這種斷斷的現象。

除了這個看著不舒服外，還會導致其它問題麼？

假設我們拿B1例項的備份做了一個新例項，然後接到A上，如果主庫A又定期purge了老的binlog，那麼新例項的IO執行緒就會出錯，因為需要的binlog在主庫上找不到了；即使主庫沒有purge 老的binlog，新例項還要把主庫的binlog都從頭重新拉過來，然後執行的時候又都過濾掉，不如不拉取。

有沒有好的辦法解決這個問題呢？SQL執行緒在執行的時候，發現是該被過濾掉的event，在不執行的同時，記一個空事務就好了，把原事務對應的GTID位置佔住，記入binlog，這樣備庫的Executed_Gtid_Set就是連續的了。

bug 修復

對這個問題，官方有一個相應的bugfix，參見 revno:  5860 ，有了這個patch後，備庫B1的 SQL 執行緒在遇到和 db2-db5 相關的SQL語句時，在binlog中把對應的GTID記下，同時對應記一個空事務。

這個 patch 只是針對Query_log_event，即 statement 格式的 binlog event，那麼row格式的呢？ row格式原來就已經是這種行為，通過check_table_map 函式來過濾庫或者表，然後生成一個空事務。

另外這個patch還專門處理了下 CREATE/DROP TEMPORARY TABLE 這2種語句，我們知道row格式下，對臨時表的操作是不會記入binlog的。如果主庫的binlog格式是 statement，備庫用的是 row，CREATE/DROP TEMPORARY TABLE 對應的事務傳到備庫後，就會消失掉，Executed_Gtid_Set集合看起來是不連續的，但是主庫的binlog記的gtid是連續的，這個 patch 讓這種情況下的CREATE/DROP TEMPORARY TABLE在備庫同樣記為一個空事務。

TokuDB·特性分析· Optimize Table

來自一個TokuDB使用者的“投訴”:

https://mariadb.atlassian.net/browse/MDEV-6207

現象大概是:

使用者有一個MyISAM的表test_table:

 CREATE TABLE IF NOT EXISTS `test_table` (
   `id` int(10) unsigned NOT NULL,
   `pub_key` varchar(80) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `pub_key` (`pub_key`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

轉成TokuDB引擎後表大小為92M左右:

 47M     _tester_testdb_sql_61e7_1812_main_ad88a6b_1_19_B_0.tokudb
 45M     _tester_testdb_sql_61e7_1812_key_pub_key_ad88a6b_1_19_B_1.tokudb

執行”OPTIMIZE TABLE test_table”:

 63M     _tester_testdb_sql_61e7_1812_main_ad88a6b_1_19_B_0.tokudb
 61M     _tester_testdb_sql_61e7_1812_key_pub_key_ad88a6b_1_19_B_1.tokudb

再次執行”OPTIMIZE TABLE test_table”:

 79M     _tester_testdb_sql_61e7_1812_main_ad88a6b_1_19_B_0.tokudb
 61M     _tester_testdb_sql_61e7_1812_key_pub_key_ad88a6b_1_19_B_1.tokudb

繼續執行:

 79M     _tester_testdb_sql_61e7_1812_main_ad88a6b_1_19_B_0.tokudb
 61M     _tester_testdb_sql_61e7_1812_key_pub_key_ad88a6b_1_19_B_1.tokudb

 

基本穩定在這個大小。

主索引從47M–>63M–>79M，執行”OPTIMIZE TABLE”後為什麼會越來越大？

這得從TokuDB的索引檔案分配方式說起，當記憶體中的髒頁需要寫到磁碟時，TokuDB優先在檔案末尾分配空間並寫入，而不是“覆寫”原塊，原來的塊暫時成了“碎片”。

這樣問題就來了，索引檔案豈不是越來越大？No, TokuDB會把這些“碎片”在checkpoint時加入到回收列表，以供後面的寫操作使用，看似79M的檔案其實還可以裝不少資料呢！

嗯，這個現象解釋通了，但還有2個問題:

  1. 在執行這個語句的時候，TokuDB到底在做什麼呢？ 在做toku_ft_flush_some_child，把內節點的緩衝區(message buffer)資料刷到最底層的葉節點。
  2. 在TokuDB裡，OPTIMIZE TABLE有用嗎？ 作用非常小，不建議使用，TokuDB是一個”No Fragmentation”的引擎。

相關文章