編者按：淘寶自從2010開始規(guī)模使用MySQL，替換了之前商品、交易、用戶等原基于IOE方案的核心數(shù)據(jù)庫(kù)，目前已部署數(shù)千臺(tái)規(guī)模。同時(shí)和Oracle, Percona, Mariadb等上游廠商有良好合作，共向上游提交20多個(gè)Patch。目前淘寶核心系統(tǒng)研發(fā)部數(shù)據(jù)庫(kù)組，根據(jù)淘寶的業(yè)務(wù)需求，改進(jìn)數(shù)據(jù)庫(kù)和提升性能，提供高性能、可擴(kuò)展的、穩(wěn)定可靠的數(shù)據(jù)庫(kù)(存儲(chǔ))解決方案。 目前有以下幾個(gè)方向：?jiǎn)螜C(jī)，提升單機(jī)數(shù)據(jù)庫(kù)的性能，增加我們所需特性；集群，提供性能擴(kuò)展，可靠性，可能涉及分布式事務(wù)處理；IO存儲(chǔ)體系，跟蹤IO設(shè)備變化潮流， 研究軟硬件結(jié)合，輸出高性能存儲(chǔ)解決方案。本文是來自淘寶內(nèi)部數(shù)據(jù)庫(kù)內(nèi)容分享。

MySQL · 性能優(yōu)化· Group Commit優(yōu)化

背景

關(guān)于Group Commit網(wǎng)上的資料其實(shí)已經(jīng)足夠多了，我這里只簡(jiǎn)單的介紹一下。

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

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

很快Mariadb就提出了一個(gè)Binlog Group Commit方案，即在準(zhǔn)備寫入Binlog時(shí)，維持一個(gè)隊(duì)列，最早進(jìn)入隊(duì)列的是leader，后來的是follower，leader為搜集到的隊(duì)列中的線程依次寫B(tài)inlog文件, 并commit事務(wù)。Percona 的Group Commit實(shí)現(xiàn)也是Port自Mariadb。不過仍在使用Percona Server5.5的朋友需要注意，該Group Commit實(shí)現(xiàn)可能破壞掉Semisync的行為，感興趣的點(diǎn)擊  bug#1254571

Oracle MySQL 在5.6版本開始也支持Binlog Group Commit，使用了和Mariadb類似的思路，但將Group Commit的過程拆分成了三個(gè)階段：flush stage 將各個(gè)線程的binlog從cache寫到文件中; sync stage 對(duì)binlog做fsync操作（如果需要的話）；commit stage 為各個(gè)線程做引擎層的事務(wù)commit。每個(gè)stage同時(shí)只有一個(gè)線程在操作。

Tips：當(dāng)引入Group Commit后，sync_binlog的含義就變了，假定設(shè)為1000，表示的不是1000個(gè)事務(wù)后做一次fsync，而是1000個(gè)事務(wù)組。

Oracle MySQL的實(shí)現(xiàn)的優(yōu)勢(shì)在于三個(gè)階段可以并發(fā)執(zhí)行，從而提升效率。

XA Recover

在Binlog打開的情況下，MySQL默認(rèn)使用MySQL_BIN_LOG來做XA協(xié)調(diào)者，大致流程為：

1.掃描最后一個(gè)Binlog文件，提取其中的xid； 
2.InnoDB維持了狀態(tài)為Prepare的事務(wù)鏈表，將這些事務(wù)的xid和Binlog中記錄的xid做比較，如果在Binlog中存在，則提交，否則回滾事務(wù)。

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

Tips：MySQL為何只需要掃描最后一個(gè)Binlog文件呢 ？ 原因是每次在rotate到新的Binlog文件時(shí)，總是保證沒有正在提交的事務(wù)，然后fsync一次InnoDB的redo log。這樣就可以保證老的Binlog文件中的事務(wù)在InnoDB總是提交的。

問題

其實(shí)問題很簡(jiǎn)單：每個(gè)事務(wù)都要保證其Prepare的事務(wù)被write/fsync到redo log文件。盡管某個(gè)事務(wù)可能會(huì)幫助其他事務(wù)完成redo 寫入，但這種行為是隨機(jī)的，并且依然會(huì)產(chǎn)生明顯的log_sys->mutex開銷。

優(yōu)化

從XA恢復(fù)的邏輯我們可以知道，只要保證InnoDB Prepare的redo日志在寫B(tài)inlog前完成write/sync即可。因此我們對(duì)Group Commit的第一個(gè)stage的邏輯做了些許修改，大概描述如下：

 Step1. InnoDB Prepare，記錄當(dāng)前的LSN到thd中； 
 Step2. 進(jìn)入Group Commit的flush stage；Leader搜集隊(duì)列，同時(shí)算出隊(duì)列中最大的LSN。
 Step3. 將InnoDB的redo log write/fsync到指定的LSN 
 Step4. 寫B(tài)inlog并進(jìn)行隨后的工作(sync Binlog, InnoDB commit , etc)

通過延遲寫redo log的方式，顯式的為redo log做了一次組寫入，并減少了log_sys->mutex的競(jìng)爭(zhēng)。

目前官方MySQL已經(jīng)根據(jù)我們r(jià)eport的bug#73202鎖提供的思路，對(duì)5.7.6的代碼進(jìn)行了優(yōu)化，對(duì)應(yīng)的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.

性能數(shù)據(jù)

簡(jiǎn)單測(cè)試了下，使用sysbench, update_non_index.lua, 100張表，每張10w行記錄，innodb_flush_log_at_trx_commit=2, sync_binlog=1000，關(guān)閉Gtid

 并發(fā)線程        原生                  修改后
 32             25600                27000
 64             30000                35000
 128            33000                39000
 256            29800                38000

MySQL · 新增特性· DDL fast fail

背景

項(xiàng)目的快速迭代開發(fā)和在線業(yè)務(wù)需要保持持續(xù)可用的要求，導(dǎo)致MySQL的ddl變成了DBA很頭疼的事情，而且經(jīng)常導(dǎo)致故障發(fā)生。本篇介紹RDS分支上做的一個(gè)功能改進(jìn)，DDL fast fail。主要解決：DDL操作因?yàn)闊o法獲取MDL排它鎖，進(jìn)入等待隊(duì)列的時(shí)候，阻塞了應(yīng)用所有的讀寫請(qǐng)求問題。

MDL鎖機(jī)制介紹

首先介紹一下MDL(METADATA LOCK)鎖機(jī)制，MySQL為了保證表結(jié)構(gòu)的完整性和一致性，對(duì)表的所有訪問都需要獲得相應(yīng)級(jí)別的MDL鎖，比如以下場(chǎng)景：

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對(duì)t1表做查詢，首先需要獲取t1表的MDL_SHARED_READ級(jí)別MDL鎖。鎖一直持續(xù)到commit結(jié)束，然后釋放。
• session 2對(duì)t1表做DDL，需要獲取t1表的MDL_EXCLUSIVE級(jí)別MDL鎖，因?yàn)镸DL_SHARED_READ與MDL_EXCLUSIVE不相容，所以session 2被session 1阻塞，然后進(jìn)入等待隊(duì)列。
• session 3對(duì)t1表做查詢，因?yàn)榈却?duì)列中有MDL_EXCLUSIVE級(jí)別MDL鎖請(qǐng)求，所以session3也被阻塞，進(jìn)入等待隊(duì)列。

這種場(chǎng)景就是目前因?yàn)镸DL鎖導(dǎo)致的很經(jīng)典的阻塞問題，如果session1長(zhǎng)時(shí)間未提交，或者查詢持續(xù)過長(zhǎng)時(shí)間，那么后續(xù)對(duì)t1表的所有讀寫操作，都被阻塞。 對(duì)于在線的業(yè)務(wù)來說，很容易導(dǎo)致業(yè)務(wù)中斷。

aliyun RDS分支改進(jìn)

DDL fast fail并沒有解決真正DDL過程中的阻塞問題，但避免了因?yàn)镈DL操作沒有獲取鎖，進(jìn)而導(dǎo)致業(yè)務(wù)其他查詢/更新語句阻塞的問題。

其實(shí)現(xiàn)方式如下：

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

其處理邏輯如下：

首先嘗試獲取t1表的MDL_EXCLUSIVE級(jí)別的MDL鎖：

• 當(dāng)語句指定的是no_wait，如果獲取失敗，客戶端將得到報(bào)錯(cuò)信息：ERROR : Lock wait timeout exceeded; try restarting transaction。
• 當(dāng)語句指定的是wait 1，如果獲取失敗，最多等待1s，然后得到報(bào)錯(cuò)信息：ERROR : Lock wait timeout exceeded; try restarting transaction。

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

與Oracle的比較

在Oracle 10g的時(shí)候，DDL操作經(jīng)常會(huì)遇到這樣的錯(cuò)誤信息：

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

其實(shí)DDL獲取排他鎖的設(shè)計(jì)，需要考慮的就是兩個(gè)問題：

1. 雪崩，如果你采用排隊(duì)阻塞的機(jī)制，那么DDL如果長(zhǎng)時(shí)間無法獲取鎖，就會(huì)導(dǎo)致應(yīng)用的雪崩效應(yīng)，對(duì)于高并發(fā)的業(yè)務(wù)，也是災(zāi)難。
2. 餓死，如果你采用強(qiáng)制式的機(jī)制，那么要防止DDL一直無法獲取鎖的情況，在業(yè)務(wù)高峰期，可能DDL永遠(yuǎn)無法成功。

在Oracle 11g的時(shí)候，引入了DDL_LOCK_TIMEOUT參數(shù)，如果你設(shè)置了這個(gè)參數(shù)，那么DDL操作將使用排隊(duì)阻塞模式，可以在session和global級(jí)別設(shè)置， 給了用戶更多選擇。

MySQL · 性能優(yōu)化· 啟用GTID場(chǎng)景的性能問題及優(yōu)化

背景

MySQL從5.6版本開始支持GTID特性，也就是所謂全局事務(wù)ID，在整個(gè)復(fù)制拓?fù)浣Y(jié)構(gòu)內(nèi)，每個(gè)事務(wù)擁有自己全局唯一標(biāo)識(shí)。GTID包含兩個(gè)部分，一部分是實(shí)例的UUID，另一部分是實(shí)例內(nèi)遞增的整數(shù)。

GTID的分配包含兩種方式，一種是自動(dòng)分配，另外一種是顯式設(shè)置session.gtid_next，下面簡(jiǎn)單介紹下這兩種方式：

自動(dòng)分配

如果沒有設(shè)置session級(jí)別的變量gtid_next，所有事務(wù)都走自動(dòng)分配邏輯。分配GTID發(fā)生在GROUP COMMIT的第一個(gè)階段，也就是flush stage，大概可以描述為：

•  Step 1：事務(wù)過程中，碰到第一條DML語句需要記錄Binlog時(shí)，分配一段Gtid事件的cache，但不分配實(shí)際的GTID
•  Step 2：事務(wù)完成后，進(jìn)入commit階段，分配一個(gè)GTID并寫入Step1預(yù)留的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后，各個(gè)會(huì)話將其使用的GTID從gtid_owned中移除。

顯式設(shè)置

用戶通過設(shè)置session級(jí)別變量gtid_next可以顯式指定一個(gè)GTID，流程如下：

•  Step 1：設(shè)置變量gtid_next，指定的GTID被加入到gtid_owned集合中。
•  Step 2：執(zhí)行任意事務(wù)SQL，在將binlog從線程cache刷到binlog文件后，將GTID加入到gtid_executed集合中。
•  Step 3：在完成事務(wù)COMMIT后，從gtid_owned中移除。

備庫(kù)SQL線程使用的就是第二種方式，因?yàn)閭鋷?kù)在apply主庫(kù)的日志時(shí)，要保證GTID是一致的，SQL線程讀取到GTID事件后，就根據(jù)其中記錄的GTID來設(shè)置其gtid_next變量。

問題

由于在實(shí)例內(nèi)，GTID需要保證唯一性，因此不管是操作gtid_executed集合和gtid_owned集合，還是分配GTID，都需要加上一個(gè)大鎖。我們的優(yōu)化主要集中在第一種GTID分配方式。

對(duì)于GTID的分配，由于處于Group Commit的第一個(gè)階段，由該階段的leader線程為其follower線程分配GTID及刷Binlog，因此不會(huì)產(chǎn)生競(jìng)爭(zhēng)。

而在Step 5，各個(gè)線程在完成事務(wù)提交后，各自去從gtid_owned集合中刪除其使用的gtid。這時(shí)候每個(gè)線程都需要獲取互斥鎖，很顯然，并發(fā)越高，這種競(jìng)爭(zhēng)就越明顯，我們很容易從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

這同時(shí)也會(huì)影響到GTID的分配階段，導(dǎo)致TPS在高并發(fā)場(chǎng)景下的急劇下降。

解決

實(shí)際上對(duì)于自動(dòng)分配GTID的場(chǎng)景，并沒有必要維護(hù)gtid_owned集合。我們的修改也非常簡(jiǎn)單，在自動(dòng)分配一個(gè)GTID后，直接加入到gtid_executed集合中，避免維護(hù)gtid_owned，這樣事務(wù)提交時(shí)就無需去清理gtid_owned集合了，從而可以完全避免鎖競(jìng)爭(zhēng)。

當(dāng)然為了保證一致性，如果分配GTID后，寫入Binlog文件失敗，也需要從gtid_executed集合中刪除。不過這種場(chǎng)景非常罕見。

性能數(shù)據(jù)

使用sysbench，100張表，每張10w行記錄，update_non_index.lua，純內(nèi)存操作，innodb_flush_log_at_trx_commit = 2，sync_binlog = 1000

 并發(fā)線程       原生               修改后
 32           24500              25000
 64           27900              29000
 128          30800              31500
 256          29700              32000
 512          29300              31700
 1024         27000              31000

從測(cè)試結(jié)果可以看到，優(yōu)化前隨著并發(fā)上升，性能出現(xiàn)下降，而優(yōu)化后則能保持TPS穩(wěn)定。

MySQL · 捉蟲動(dòng)態(tài)· InnoDB自增列重復(fù)值問題

問題重現(xiàn)

先從問題入手，重現(xiàn)下這個(gè) 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 |
+----+------+

這里我們關(guān)閉MySQL，再啟動(dòng)MySQL,然后再插入一條數(shù)據(jù)

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

我們看到插入了（2,2），而如果我沒有重啟，插入同樣數(shù)據(jù)我們得到的應(yīng)該是（4,2)。 上面的測(cè)試反映了MySQLd重啟后，InnoDB存儲(chǔ)引擎的表自增id可能出現(xiàn)重復(fù)利用的情況。

自增id重復(fù)利用在某些場(chǎng)景下會(huì)出現(xiàn)問題。依然用上面的例子，假設(shè)t1有個(gè)歷史表t1_history用來存t1表的歷史數(shù)據(jù)，那么MySQLd重啟前,ti_history中可能已經(jīng)有了（2,2）這條數(shù)據(jù)，而重啟后我們又插入了（2，2），當(dāng)新插入的(2,2)遷移到歷史表時(shí)，會(huì)違反主鍵約束。

原因分析

InnoDB 自增列出現(xiàn)重復(fù)值的原因：

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)

建表時(shí)可以指定 AUTO_INCREMENT值，不指定時(shí)默認(rèn)為1，這個(gè)值表示當(dāng)前自增列的起始值大小，如果新插入的數(shù)據(jù)沒有指定自增列的值，那么自增列的值即為這個(gè)起始值。對(duì)于InnoDB表，這個(gè)值沒有持久到文件中。而是存在內(nèi)存中（dict_table_struct.autoinc）。那么又問，既然這個(gè)值沒有持久下來，為什么我們每次插入新的值后， show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實(shí)show create table t1是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

知道了AUTO_INCREMENT是實(shí)時(shí)存儲(chǔ)內(nèi)存中的。那么，MySQLd 重啟后，從哪里得到AUTO_INCREMENT呢? 內(nèi)存值肯定是丟失了。實(shí)際上MySQL采用執(zhí)行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT。而這種方法就是造成自增id重復(fù)的原因。

MyISAM自增值

MyISAM也有這個(gè)問題嗎？MyISAM是沒有這個(gè)問題的。myisam會(huì)將這個(gè)值實(shí)時(shí)存儲(chǔ)在.MYI文件中（mi_state_info_write）。MySQLd重起后會(huì)從.MYI中讀取AUTO_INCREMENT值（mi_state_info_read)。因此，MyISAM表重啟是不會(huì)出現(xiàn)自增id重復(fù)的問題。

問題修復(fù)

MyISAM選擇將AUTO_INCREMENT實(shí)時(shí)存儲(chǔ)在.MYI文件頭部中。實(shí)際上.MYI頭部還會(huì)實(shí)時(shí)存其他信息，也就是說寫AUTO_INCREMENT只是個(gè)順帶的操作，其性能損耗可以忽略。InnoDB 表如果要解決這個(gè)問題，有兩種方法。

1）將AUTO_INCREMENT最大值持久到frm文件中。 
2）將 AUTO_INCREMENT最大值持久到聚集索引根頁(yè)trx_id所在的位置。

第一種方法直接寫文件性能消耗較大，這是一額外的操作，而不是一個(gè)順帶的操作。我們采用第二種方案。為什么選擇存儲(chǔ)在聚集索引根頁(yè)頁(yè)頭trx_id，頁(yè)頭中存儲(chǔ)trx_id,只對(duì)二級(jí)索引頁(yè)和insert buf 頁(yè)頭有效（MVCC)。而聚集索引根頁(yè)頁(yè)頭trx_id這個(gè)值是沒有使用的，始終保持初始值0。正好這個(gè)位置8個(gè)字節(jié)可存放自增值的值。我們每次更新AUTO_INCREMENT值時(shí)，同時(shí)將這個(gè)值修改到聚集索引根頁(yè)頁(yè)頭trx_id的位置。 這個(gè)寫操作跟真正的數(shù)據(jù)寫操作一樣，遵守write-ahead log原則，只不過這里只需要redo log ,而不需要undo log。因?yàn)槲覀儾恍枰貪LAUTO_INCREMENT的變化（即回滾后自增列值會(huì)保留，即使insert 回滾了，AUTO_INCREMENT值不會(huì)回滾）。

因此，AUTO_INCREMENT值存儲(chǔ)在聚集索引根頁(yè)trx_id所在的位置，實(shí)際上是對(duì)內(nèi)存根頁(yè)的修改和多了一條redo log（量很?。?而這個(gè)redo log 的寫入也是異步的，可以說是原有事務(wù)log的一個(gè)順帶操作。因此AUTO_INCREMENT值存儲(chǔ)在聚集索引根頁(yè)這個(gè)性能損耗是極小的。

修復(fù)后的性能對(duì)比，我們新增了全局參數(shù)innodb_autoinc_persistent 取值on/off； on 表示將AUTO_INCREMENT值實(shí)時(shí)存儲(chǔ)在聚集索引根頁(yè)。off則采用原有方式只存儲(chǔ)在內(nèi)存。

./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以下。

改進(jìn)

新增參數(shù)innodb_autoinc_persistent_interval 用于控制持久化AUTO_INCREMENT值的頻率。例如：innodb_autoinc_persistent_interval=100，auto_incrememt_increment=1時(shí)，即每100次insert會(huì)控制持久化一次AUTO_INCREMENT值。每次持久的值為：當(dāng)前值+innodb_autoinc_persistent_interval。

測(cè)試結(jié)論

 innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=1時(shí)性能損耗在%1以下。 
 innodb_autoinc_persistent=ON, innodb_autoinc_persistent_interval=100時(shí)性能損耗可以忽略。

限制

1.  innodb_autoinc_persistent=on， innodb_autoinc_persistent_interval=N>1時(shí)，自增N次后持久化到聚集索引根頁(yè),每次持久的值為當(dāng)前AUTO_INCREMENT+(N-1)*innodb_autoextend_increment。重啟后讀取持久化的AUTO_INCREMENT值會(huì)偏大，造成一些浪費(fèi)但不會(huì)重復(fù)。innodb_autoinc_persistent_interval=1 每次都持久化沒有這個(gè)問題。
2.  如果innodb_autoinc_persistent=on，頻繁設(shè)置auto_increment_increment的可能會(huì)導(dǎo)致持久化到聚集索引根頁(yè)的值不準(zhǔn)確。因?yàn)閕nnodb_autoinc_persistent_interval計(jì)算沒有考慮auto_increment_increment變化的情況，參看dict_table_autoinc_update_if_greater。而設(shè)置auto_increment_increment的情況極少，可以忽略。

注意：如果我們使用需要開啟innodb_autoinc_persistent，應(yīng)該在參數(shù)文件中指定

innodb_autoinc_persistent= on

如果這樣指定set global innodb_autoinc_persistent=on;重啟后將不會(huì)從聚集索引根頁(yè)讀取AUTO_INCREMENT最大值。

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

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

MySQL · 優(yōu)化改進(jìn)· 復(fù)制性能改進(jìn)過程

前言

與oracle 不同，MySQL 的主庫(kù)與備庫(kù)的同步是通過 binlog 實(shí)現(xiàn)的，而redo日志只做為MySQL 實(shí)例的crash recovery使用。MySQL在4.x 的時(shí)候放棄redo 的同步策略而引入 binlog的同步，一個(gè)重要原因是為了兼容其它非事務(wù)存儲(chǔ)引擎，否則主備同步是沒有辦法進(jìn)行的。

redo 日志同步屬于物理同步方法，簡(jiǎn)單直接，將修改的物理部分傳送到備庫(kù)執(zhí)行，主備共用一致的 LSN，只要保證 LSN 相同即可，同一時(shí)刻，只能主庫(kù)或備庫(kù)一方接受寫請(qǐng)求； binlog的同步方法屬于邏輯復(fù)制，分為statement 或 row 模式，其中statement記錄的是SQL語句，Row 模式記錄的是修改之前的記錄與修改之后的記錄，即前鏡像與后鏡像；備庫(kù)通過binlog dump 協(xié)議拉取binlog,然后在備庫(kù)執(zhí)行。如果拉取的binlog是SQL語句，備庫(kù)會(huì)走和主庫(kù)相同的邏輯，如果是row 格式，則會(huì)調(diào)用存儲(chǔ)引擎來執(zhí)行相應(yīng)的修改。

本文簡(jiǎn)單說明5.5到5.7的主備復(fù)制性能改進(jìn)過程。

replication improvement (from 5.5 to 5.7)

(1) 5.5 中，binlog的同步是由兩個(gè)線程執(zhí)行的

io_thread: 根據(jù)binlog dump協(xié)議從主庫(kù)拉取binlog, 并將binlog轉(zhuǎn)存到本地的relaylog；

sql_thread: 讀取relaylog，根據(jù)位點(diǎn)的先后順序執(zhí)行binlog event，進(jìn)而將主庫(kù)的修改同步到備庫(kù)，達(dá)到主備一致的效果； 由于在主庫(kù)的更新是由多個(gè)客戶端執(zhí)行的，所以當(dāng)壓力達(dá)到一定的程度時(shí)，備庫(kù)單線程執(zhí)行主庫(kù)的binlog跟不上主庫(kù)執(zhí)行的速度，進(jìn)而會(huì)產(chǎn)生延遲造成備庫(kù)不可用，這也是分庫(kù)的原因之一，其SQL線程的執(zhí)行堆棧如下：

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 中，引入了多線程模式，在多線程模式下，其線程結(jié)構(gòu)如下

io_thread: 同5.5

Coordinator_thread: 負(fù)責(zé)讀取 relay log，將讀取的binlog event以事務(wù)為單位分發(fā)到各個(gè) worker thread 進(jìn)行執(zhí)行，并在必要時(shí)執(zhí)行binlog event（Description_format_log_event， Rotate_log_event 等）。

worker_thread: 執(zhí)行分配到的binlog event，各個(gè)線程之間互不影響；

多線程原理

sql_thread 的分發(fā)原理是依據(jù)當(dāng)前事務(wù)所操作的數(shù)據(jù)庫(kù)名稱來進(jìn)行分發(fā)，如果事務(wù)是跨數(shù)據(jù)庫(kù)行為的，則需要等待已分配的該數(shù)據(jù)庫(kù)的事務(wù)全部執(zhí)行完畢，才會(huì)繼續(xù)分發(fā)，其分配行為的偽碼可以簡(jiǎn)單的描述如下：

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

需要注意的細(xì)節(jié)

• 內(nèi)存的分配與釋放。relay thread 每讀取一個(gè)log_event, 則需要 malloc 一定的內(nèi)存，在work線程執(zhí)行完后，則需要free掉；
• 數(shù)據(jù)庫(kù)名 與 worker 線程的綁定信息在一個(gè)hash表中進(jìn)行維護(hù)，hash表以entry為單位，entry中記錄當(dāng)前entry所代表的數(shù)據(jù)庫(kù)名，有多少個(gè)事務(wù)相關(guān)的已被分發(fā)，執(zhí)行這些事務(wù)的worker thread等信息；
• 維護(hù)一個(gè)綁定信息的array , 在分發(fā)事務(wù)的時(shí)候，更新綁定信息，增加相應(yīng) entry->usage, 在執(zhí)行完一個(gè)事務(wù)的時(shí)候，則需要減少相應(yīng)的entry->usage；
• slave worker 信息的維護(hù)，即每個(gè) worker thread執(zhí)行了哪些事務(wù)，執(zhí)行到的位點(diǎn)是在哪，延遲是如何計(jì)算的，如果執(zhí)行出錯(cuò)，mts_recovery_group 又是如何恢復(fù)的；
• 分配線程是以數(shù)據(jù)庫(kù)名進(jìn)行分發(fā)的，當(dāng)一個(gè)實(shí)例中只有一個(gè)數(shù)據(jù)庫(kù)的時(shí)候，不會(huì)對(duì)性能有提高，相反，由于增加額外的操作，性能還會(huì)有一點(diǎn)回退；
• 臨時(shí)表的處理，臨時(shí)表是和entry綁定在一起的，在執(zhí)行的時(shí)候?qū)ntry的臨時(shí)表掛在執(zhí)行線程thd下面，但沒有固化，如果在臨時(shí)表操作期間，備庫(kù)crash，則重啟后備庫(kù)會(huì)有錯(cuò)誤；

總體上說，5.6 的并行復(fù)制打破了5.5 單線程的復(fù)制的行為，只是在單庫(kù)下用處不大，并且5.6的并行復(fù)制的改動(dòng)引入了一些重量級(jí)的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中，并行復(fù)制的實(shí)現(xiàn)添加了另外一種并行的方式，即主庫(kù)在 ordered_commit中的第二階段的時(shí)候，將同一批commit的 binlog 打上一個(gè)相同的seqno標(biāo)簽，同一時(shí)間戳的事務(wù)在備庫(kù)是可以同時(shí)執(zhí)行的，因此大大簡(jiǎn)化了并行復(fù)制的邏輯，并打破了相同 DB 不能并行執(zhí)行的限制。備庫(kù)在執(zhí)行時(shí)，具有同一seqno的事務(wù)在備庫(kù)可以并行的執(zhí)行，互不干擾，也不需要綁定信息，后一批seqno的事務(wù)需要等待前一批相同seqno的事務(wù)執(zhí)行完后才可以執(zhí)行。

詳細(xì)實(shí)現(xiàn)可參考： http://bazaar.launchpad.net/~MySQL/MySQL-server/5.7/revision/6256 。

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

MySQL · 談古論今· key分區(qū)算法演變分析

本文說明一個(gè)物理升級(jí)導(dǎo)致的 "數(shù)據(jù)丟失"。

現(xiàn)象

在MySQL 5.1下新建key分表，可以正確查詢數(shù)據(jù)。

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啟動(dòng)上面的5.1實(shí)例，發(fā)現(xiàn)(1,1785089517)這行數(shù)據(jù)不能正確查詢出來。

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)

原因分析

跟蹤代碼發(fā)現(xiàn)，5.1 與5.5,5.6 key hash算法是有區(qū)別的。

5.1 對(duì)于非空值的處理算法如下

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 ; pos++)
  {
    nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 
             ((uint)*pos)) + (nr1[0] << 8);
    nr2[0]+=3;
  }
}

通過此算法算出數(shù)據(jù)(1,1785089517)在第3個(gè)分區(qū)

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 ; key++)
  {
    nr1[0]^=(ulong) ((((uint) nr1[0] & 63)+nr2[0]) * 
            ((uint) sort_order[(uint) *key])) + (nr1[0] << 8);
    nr2[0]+=3;
  }
}

通過此算法算出數(shù)據(jù)(1,1785089517)在第5個(gè)分區(qū)，因此，5.5,5.6查詢不能查詢出此行數(shù)據(jù)。

5.1,5.5,5.6對(duì)于空值的算法還是一致的,如下

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

都能正確算出數(shù)據(jù)(2, null)在第3個(gè)分區(qū)。因此，空值可以正確查詢出來。

那么是什么導(dǎo)致非空值的hash算法走了不同路徑呢？在5.1下，計(jì)算字段key hash固定字符集就是my_charset_bin，對(duì)應(yīng)的hash 函數(shù)就是前面的my_hash_sort_simple。而在5.5，5.6下，計(jì)算字段key hash的字符集是隨字段變化的，字段c2類型為int對(duì)應(yīng)my_charset_numeric，與之對(duì)應(yīng)的hash函數(shù)為my_hash_sort_simple。具體可以參考函數(shù)Field::hash

那么問題又來了，5.5后為什么算法會(huì)變化呢？原因在于官方關(guān)于字符集策略的調(diào)整，詳見WL#2649 。

兼容處理

前面講到，由于hash 算法變化，用5.5，5.6啟動(dòng)5.1的實(shí)例，導(dǎo)致不能正確查詢數(shù)據(jù)。那么5.1升級(jí)5.5,5.6就必須兼容這個(gè)問題.MySQL 5.5.31以后，提供了專門的語法 ALTER TABLE ... PARTITION BY ALGORITHM=1 [LINEAR] KEY ...  用于兼容此問題。對(duì)于上面的例子，用5.5或5.6啟動(dòng)5.1的實(shí)例后執(zhí)行

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)

數(shù)據(jù)可以正確查詢出來了。

而實(shí)際上5.5,5.6的MySQL_upgrade升級(jí)程序已經(jīng)提供了兼容方法。MySQL_upgrade 執(zhí)行check table xxx for upgrade 會(huì)檢查key分區(qū)表是否用了老的算法。如果使用了老的算法，會(huì)返回

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)

檢查到錯(cuò)誤信息后會(huì)自動(dòng)執(zhí)行以下語句進(jìn)行兼容。

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

MySQL · 捉蟲動(dòng)態(tài)· MySQL client crash一例

背景

客戶使用MySQLdump導(dǎo)出一張表，然后使用MySQL -e 'source test.dmp'的過程中client進(jìn)程crash，爆出內(nèi)存的segment fault錯(cuò)誤，導(dǎo)致無法導(dǎo)入數(shù)據(jù)。

問題定位

test.dmp文件大概50G左右，查看了一下文件的前幾行內(nèi)容，發(fā)現(xiàn)：

 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 */;

問題定位到第一行出現(xiàn)了不正常warning的信息，是由于客戶使用MySQLdump命令的時(shí)候，重定向了stderr。即:

MySQLdump ...>/test.dmp 2>&1

導(dǎo)致error或者warning信息都重定向到了test.dmp, 最終導(dǎo)致失敗。

問題引申

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

問題1. 不正常的sql，執(zhí)行失敗，報(bào)錯(cuò)出來就可以了，為什么會(huì)導(dǎo)致crash？

MySQL.cc::add_line函數(shù)中，在讀第一行的時(shí)候，讀取到了don't,發(fā)現(xiàn)有一個(gè)單引號(hào)，所以程序死命的去找匹配的另外一個(gè)單引號(hào)，導(dǎo)致不斷的讀取文件，分配內(nèi)存，直到crash。 
假設(shè)沒有這個(gè)單引號(hào)，MySQL讀到第六行，發(fā)現(xiàn);號(hào)，就會(huì)執(zhí)行sql，并正常的報(bào)錯(cuò)退出。

問題2. 那代碼中對(duì)于大小的邊界到底是多少？比如insert語句支持batch insert時(shí)，語句的長(zhǎng)度多少，又比如遇到clob字段呢？

• 首先clob字段的長(zhǎng)度限制。clob家族類型的column長(zhǎng)度受限于max_allowed_packet的大小，MySQL 5.5中，對(duì)于max_allowd_packet的大小限制在(1024, 1024*1024*1024)之間。
• MySQLdump導(dǎo)出insert語句的時(shí)候，如何分割insert語句？MySQLdump時(shí)候支持insert t1 value(),(),();這樣的batch insert語句。 MySQLdump其實(shí)是根據(jù)opt_net_buffer_length來進(jìn)行分割，當(dāng)一個(gè)insert語句超過這個(gè)大小，就強(qiáng)制分割到下一個(gè)insert語句中，這樣更多的是在做網(wǎng)絡(luò)層的優(yōu)化。又如果遇到大的clob字段怎么辦？ 如果一行就超過了opt_net_buffer_length，那就強(qiáng)制每一行都分割。
• MySQL client端讀取dump文件的時(shí)候, 到底能分配多大的內(nèi)存？MySQL.cc中定義了:#define MAX_BATCH_BUFFER_SIZE (1024L * 1024L * 1024L)。 也就是MySQL在執(zhí)行語句的時(shí)候，最多只能分配1G大小的緩存。

所以，正常情況下，max_allowed_packet現(xiàn)在的最大字段長(zhǎng)度和MAX_BATCH_BUFFER_SIZE限制的最大insert語句，是匹配的。

RDS問題修復(fù)原則

從問題的定位上來看，這一例crash屬于客戶錯(cuò)誤使用MySQLdump導(dǎo)致的問題，Aliyun RDS分支對(duì)內(nèi)存導(dǎo)致的crash問題，都會(huì)定位并反饋給用戶。 但此例不做修復(fù)，而是引導(dǎo)用戶正確的使用MySQLdump工具。

MySQL · 捉蟲動(dòng)態(tài)· 設(shè)置 gtid_purged 破壞AUTO_POSITION復(fù)制協(xié)議

bug描述

Oracle 最新發(fā)布的版本 5.6.22 中有這樣一個(gè)關(guān)于GTID的bugfix，在主備場(chǎng)景下，如果我們?cè)谥鲙?kù)上 SET GLOBAL GTID_PURGED = "some_gtid_set"，并且 some_gtid_set 中包含了備庫(kù)還沒復(fù)制的事務(wù)，這個(gè)時(shí)候如果備庫(kù)接上主庫(kù)的話，預(yù)期結(jié)果是主庫(kù)返回錯(cuò)誤，IO線程掛掉的，但是實(shí)際上，在這種場(chǎng)景下主庫(kù)并不報(bào)錯(cuò)，只是默默的把自己 binlog 中包含的gtid事務(wù)發(fā)給備庫(kù)。這個(gè)bug的造成的結(jié)果是看起來復(fù)制正常，沒有錯(cuò)誤，但實(shí)際上備庫(kù)已經(jīng)丟事務(wù)了，主備很可能就不一致了。

背景知識(shí)

• binlog GTID事件

binlog 中記錄的和GTID相關(guān)的事件主要有2種，Previous_gtids_log_event 和 Gtid_log_event，前者表示之前的binlog中包含的gtid的集合，后者就是一個(gè)gtid，對(duì)應(yīng)一個(gè)事務(wù)。一個(gè) binlog 文件中只有一個(gè) Previous_gtids_log_event，放在開頭，有多個(gè) Gtid_log_event，如下面所示

Previous_gtids_log_event   // 此 binlog 之前的所有binlog文件包含的gtid集合

Gtid_log_event // 單個(gè)gtid event
Transaction
Gtid_log_event
Transaction
.
.
.
Gtid_log_event
Transaction

• 備庫(kù)發(fā)送GTID集合給主庫(kù)

我們知道備庫(kù)的復(fù)制線程是分IO線程和SQL線程2種的，IO線程通過GTID協(xié)議或者文件位置協(xié)議拉取主庫(kù)的binlog，然后記錄在自己的relay log中；SQL線程通過執(zhí)行realy log中的事件，把其中的操作都自己做一遍，記入本地binlog。在GTID協(xié)議下，備庫(kù)向主庫(kù)發(fā)送拉取請(qǐng)求的時(shí)候，會(huì)告知主庫(kù)自己已經(jīng)有的所有的GTID的集合，Retrieved_Gtid_Set + Executed_Gtid_Set，前者對(duì)應(yīng) realy log 中所有的gtid集合，表示已經(jīng)拉取過的，后者對(duì)應(yīng)binlog中記錄有的，表示已經(jīng)執(zhí)行過的；主庫(kù)在收到這2個(gè)總集合后，會(huì)掃描自己的binlog，找到合適的binlog然后開始發(fā)送。

• 主庫(kù)如何找到要發(fā)送給備庫(kù)的第一個(gè)binlog

主庫(kù)將備庫(kù)發(fā)送過來的總合集記為 slave_gtid_executed，然后調(diào)用 find_first_log_not_in_gtid_set(slave_gtid_executed)，這個(gè)函數(shù)的目的是從最新到最老掃描binlog文件，找到第一個(gè)含有不存在 slave_gtid_executed 這個(gè)集合的gtid的binlog。在這個(gè)掃描過程中并不需要從頭到尾讀binlog中所有的gtid，只需要讀出 Previous_gtids_log_event ，如果Previous_gtids_log_event 不是 slave_gtid_executed的子集，就繼續(xù)向前找binlog，直到找到為止。

這個(gè)查找過程總會(huì)停止的，停止條件如下：

1. 找到了這樣的binlog，其Previous_gtids_log_event 是slave_gtid_executed子集
2. 在往前讀binlog的時(shí)候，發(fā)現(xiàn)沒有binlog文件了（如被purge了），但是還沒找到滿足條件的Previous_gtids_log_event，這個(gè)時(shí)候主庫(kù)報(bào)錯(cuò)
3. 一直往前找，發(fā)現(xiàn)Previous_gtids_log_event 是空集

在條件2下，報(bào)錯(cuò)信息是這樣的

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.

其實(shí)上面的條件3是條件1的特殊情況，這個(gè)bugfix針對(duì)的場(chǎng)景就是條件3這種，但并不是所有的符合條件3的場(chǎng)景都會(huì)觸發(fā)這個(gè)bug，下面就分析下什么情況下才會(huì)觸發(fā)bug。

bug 分析

假設(shè)有這樣的場(chǎng)景，我們要用已經(jīng)有MySQL實(shí)例的備份重新做一對(duì)主備實(shí)例，不管是用 xtrabackup 這種物理備份工具或者M(jìn)ySQLdump這種邏輯備份工具，都會(huì)有2步操作，

1. 導(dǎo)入數(shù)據(jù)
2. SET GLOBAL GTID_PURGED ="xxxx"

步驟2是為了保證GTID的完備性，因?yàn)樾聦?shí)例已經(jīng)導(dǎo)入了數(shù)據(jù)，就需要把生成這些數(shù)據(jù)的事務(wù)對(duì)應(yīng)的GTID集合也設(shè)置進(jìn)來。

正常的操作是主備都要做這2步的，如果我們只在主庫(kù)上做了這2步，備庫(kù)什么也不做，然后就直接用 GTID 協(xié)議把備庫(kù)連上來，按照我們的預(yù)期這個(gè)時(shí)候是應(yīng)該出錯(cuò)的，主備不一致，并且主庫(kù)的binlog中沒東西，應(yīng)該報(bào)之前停止條件2報(bào)的錯(cuò)。但是令人大跌眼鏡的是主庫(kù)不報(bào)錯(cuò)，復(fù)制看起來是完全正常的。

為啥會(huì)這樣呢，SET GLOBAL GTID_PURGED 操作會(huì)調(diào)用 MySQL_bin_log.rotate_and_purge切換到一個(gè)新的binlog，并把這個(gè)GTID_PURGED 集合記入新生成的binlog的Previous_gtids_log_event，假設(shè)原有的binlog為A，新生成的為B，主庫(kù)剛啟動(dòng)，所以A就是主庫(kù)的第一個(gè)binlog，它之前啥也沒有，A的Previous_gtids_log_event就是空集，并且A中也不包含任何GTID事件，否則SET GLOBAL GTID_PURGED是做不了的。按照之前的掃描邏輯，掃到A是肯定會(huì)停下來的，并且不報(bào)錯(cuò)。

bug 修復(fù)

官方的修復(fù)就是在主庫(kù)掃描查找binlog之前，判斷一下 gtid_purged 集合不是不比slave_gtid_executed大，如果是就報(bào)錯(cuò)，錯(cuò)誤信息和條件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 · 捉蟲動(dòng)態(tài)· replicate filter 和 GTID 一起使用的問題

問題描述

當(dāng)單個(gè) MySQL 實(shí)例的數(shù)據(jù)增長(zhǎng)到很多的時(shí)候，就會(huì)考慮通過庫(kù)或者表級(jí)別的拆分，把當(dāng)前實(shí)例的數(shù)據(jù)分散到多個(gè)實(shí)例上去，假設(shè)原實(shí)例為A，想把其中的5個(gè)庫(kù)（db1/db2/db3/db4/db5）拆分到5個(gè)實(shí)例（B1/B2/B3/B4/B5）上去。

拆分過程一般會(huì)這樣做，先把A的相應(yīng)庫(kù)的數(shù)據(jù)導(dǎo)出，然后導(dǎo)入到對(duì)應(yīng)的B實(shí)例上，但是在這個(gè)導(dǎo)出導(dǎo)入過程中，A庫(kù)的數(shù)據(jù)還是在持續(xù)更新的，所以還需在導(dǎo)入完后，在所有的B實(shí)例和A實(shí)例間建立復(fù)制關(guān)系，拉取缺失的數(shù)據(jù)，在業(yè)務(wù)不繁忙的時(shí)候?qū)I(yè)務(wù)切換到各個(gè)B實(shí)例。

在復(fù)制搭建時(shí)，每個(gè)B實(shí)例只需要復(fù)制A實(shí)例上的一個(gè)庫(kù)，所以只需要重放對(duì)應(yīng)庫(kù)的binlog即可，這個(gè)通過 replicate-do-db 來設(shè)置過濾條件。如果我們用備庫(kù)上執(zhí)行 show slave status\G 會(huì)看到Executed_Gtid_Set是斷斷續(xù)續(xù)的，間斷非常多，導(dǎo)致這一列很長(zhǎng)很長(zhǎng)，看到的直接效果就是被刷屏了。

為啥會(huì)這樣呢，因?yàn)樵O(shè)了replicate-do-db，就只會(huì)執(zhí)行對(duì)應(yīng)db對(duì)應(yīng)的event，其它db的都不執(zhí)行。主庫(kù)的執(zhí)行是不分db的，對(duì)各個(gè)db的操作互相間隔，記錄在binlog中，所以備庫(kù)做了過濾后，就出現(xiàn)這種斷斷的現(xiàn)象。

除了這個(gè)看著不舒服外，還會(huì)導(dǎo)致其它問題么？

假設(shè)我們拿B1實(shí)例的備份做了一個(gè)新實(shí)例，然后接到A上，如果主庫(kù)A又定期purge了老的binlog，那么新實(shí)例的IO線程就會(huì)出錯(cuò)，因?yàn)樾枰腷inlog在主庫(kù)上找不到了；即使主庫(kù)沒有purge 老的binlog，新實(shí)例還要把主庫(kù)的binlog都從頭重新拉過來，然后執(zhí)行的時(shí)候又都過濾掉，不如不拉取。

有沒有好的辦法解決這個(gè)問題呢？SQL線程在執(zhí)行的時(shí)候，發(fā)現(xiàn)是該被過濾掉的event，在不執(zhí)行的同時(shí)，記一個(gè)空事務(wù)就好了，把原事務(wù)對(duì)應(yīng)的GTID位置占住，記入binlog，這樣備庫(kù)的Executed_Gtid_Set就是連續(xù)的了。

bug 修復(fù)

對(duì)這個(gè)問題，官方有一個(gè)相應(yīng)的bugfix，參見 revno:  5860 ，有了這個(gè)patch后，備庫(kù)B1的 SQL 線程在遇到和 db2-db5 相關(guān)的SQL語句時(shí)，在binlog中把對(duì)應(yīng)的GTID記下，同時(shí)對(duì)應(yīng)記一個(gè)空事務(wù)。

這個(gè) patch 只是針對(duì)Query_log_event，即 statement 格式的 binlog event，那么row格式的呢？ row格式原來就已經(jīng)是這種行為，通過check_table_map 函數(shù)來過濾庫(kù)或者表，然后生成一個(gè)空事務(wù)。

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

TokuDB·特性分析· Optimize Table

來自一個(gè)TokuDB用戶的“投訴”:

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

現(xiàn)象大概是:

用戶有一個(gè)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;

轉(zhuǎn)成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

執(zhí)行"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

再次執(zhí)行"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

繼續(xù)執(zhí)行:

 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

基本穩(wěn)定在這個(gè)大小。

主索引從47M-->63M-->79M，執(zhí)行"OPTIMIZE TABLE"后為什么會(huì)越來越大？

這得從TokuDB的索引文件分配方式說起，當(dāng)內(nèi)存中的臟頁(yè)需要寫到磁盤時(shí)，TokuDB優(yōu)先在文件末尾分配空間并寫入，而不是“覆寫”原塊，原來的塊暫時(shí)成了“碎片”。

這樣問題就來了，索引文件豈不是越來越大？No, TokuDB會(huì)把這些“碎片”在checkpoint時(shí)加入到回收列表，以供后面的寫操作使用，看似79M的文件其實(shí)還可以裝不少數(shù)據(jù)呢！

嗯，這個(gè)現(xiàn)象解釋通了，但還有2個(gè)問題:

1. 在執(zhí)行這個(gè)語句的時(shí)候，TokuDB到底在做什么呢？ 在做toku_ft_flush_some_child，把內(nèi)節(jié)點(diǎn)的緩沖區(qū)(message buffer)數(shù)據(jù)刷到最底層的葉節(jié)點(diǎn)。
2. 在TokuDB里，OPTIMIZE TABLE有用嗎？ 作用非常小，不建議使用，TokuDB是一個(gè)"No Fragmentation"的引擎。

本文轉(zhuǎn)載自MySQL.taobao.org ，感謝淘寶數(shù)據(jù)庫(kù)項(xiàng)目組丁奇、鳴嵩、彭立勛、皓庭、項(xiàng)仲、劍川、武藏、祁奚、褚霸、一工。審校：劉亞瓊