關于pt-archiver和自增主鍵的那些事

更新時間：2022年04月25日 12:55:49 作者：yangyidba

mysql是我們經(jīng)常會用到的一個數(shù)據(jù)庫,mysql數(shù)據(jù)庫中有一個主鍵生成規(guī)則,就是自增,這篇文章主要給大家介紹了關于pt-archiver和自增主鍵的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

前言

pt-archiver 是一款常見的表清理或者歸檔工具。

MySQL 中刪除大表之前可以使用 pt-archiver 批量刪除所有記錄。這樣助于避免在某些情況下您的服務器可能會意外的情況，比如磁盤 IO 滿導致數(shù)據(jù)庫hang或者影響正常 SQL 慢查。

筆者最近遇到一個案例，有客戶反饋 "使用 pt-archiver 刪除數(shù)據(jù)時，最后一行數(shù)據(jù)未被刪除。這個是不是bug?"

分析

在解決客戶的問題之前，我們需要解釋為什么在刪除大表之前使用 pt-archiver 當我們在 MySQL 中刪除一個表時, MySQL 系統(tǒng)會做如下動作:

刪除表數(shù)據(jù)/索引 (ibd) 和定義 (frm) 文件。
刪除觸發(fā)器。
通過刪除要刪除的表來更新表定義緩存。
掃描 InnoDB 緩沖池以查找關聯(lián)頁面以使其無效。--內(nèi)存到的表會遇到系統(tǒng)hang。

需要注意的是，DROP 是一個 DDL 語句，它需要持有元數(shù)據(jù)鎖 (MDL) 才能完成，這樣會導致所有其他線程必須等待DDL完成，清除表相關的大量數(shù)據(jù)頁會對緩沖池產(chǎn)生額外的壓力。

最后，table_definition_cache 操作需要 LOCK_open mutex 來清理，這會導致所有其他線程等待直到刪除完成。

為了降低此操作的嚴重性，我們可以使用 pt-archiver 通過批量的形式刪除大量數(shù)據(jù)，從而顯著降低表大小。一旦我們從大表中刪除了記錄，DROP 操作就會快速進行而不會對系統(tǒng)性能產(chǎn)生影響。

社區(qū)成員注意到此行為，在 pt-archiver 完成后，該表仍有一行待處理。

#?Created?table
mysql>?CREATE?TABLE?`tt1`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`a`?char(5)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB

#?Poured?random?test?data?into?it
mysql>?call?populate('test','att1',10000,'N');

#?Purged?data?using?pt-archiver
[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--purge?--where?"1=1"

#?Verifying?count?(expected?0,?got?1)
mysql>?select?count(*)?from?test.tt1;
+----------+
|?count(*)?|
+----------+
|????????1?|
+----------+
1?row?in?set?(0.00?sec)

當我們使用帶有 --no-delete 參數(shù)的 pt-archiver 進行數(shù)據(jù)歸檔時，也會發(fā)生同樣的情況。我們的工具 pt-archiver 似乎沒有將最大值復制到目標表。

將表從?tt1?遷移到?tt2?
[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--dest=h=localhost,D=test,t=tt2?--no-delete?--where?"1=1"

mysql>?select?count(*)?from?tt2;
+----------+
|?count(*)?|
+----------+
|?????5008?|
+----------+
1?row?in?set?(0.00?sec)

mysql>?select?count(*)?from?tt1;
+----------+
|?count(*)?|
+----------+
|?????5009?|
+----------+
1?row?in?set?(0.00?sec)

解析

通讀 pt-archiver 文檔，有一個選項 –[no]safe-auto-increment 描述了用法：“不要使用 max AUTO_INCREMENT 歸檔行。”

這意味著，選項 –safe-auto-increment（默認）添加了一個額外的 WHERE 子句，以防止 pt-archiver 在提升單列 AUTO_INCREMENT 時刪除最新的行，如下面的代碼部分所示：

https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-archiver#L6449
???if?(?$o->get('safe-auto-increment')
?????????&&?$sel_stmt->{index}
?????????&&?scalar(@{$src->{info}->{keys}->{$sel_stmt->{index}}->{cols}})?==?1
?????????&&?$src->{info}->{is_autoinc}->{
????????????$src->{info}->{keys}->{$sel_stmt->{index}}->{cols}->[0]
?????????}
???)?{
??????my?$col?=?$q->quote($sel_stmt->{scols}->[0]);
??????my?($val)?=?$dbh->selectrow_array("SELECT?MAX($col)?FROM?$src->{db_tbl}");
??????$first_sql?.=?"?AND?($col?<?"?.?$q->quote_val($val)?.?")";
???}

讓我們通過空運行輸出看看這兩個命令之間的區(qū)別：

#?With?--no-safe-auto-increment
[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--dest=h=localhost,D=test,t=tt2?--no-delete?--where?"1=1"?<strong>--no-safe-auto-increment</strong>?--dry-run
SELECT?/*!40001?SQL_NO_CACHE?*/?`id`,`a`?FROM?`test`.`tt1`?FORCE?INDEX(`PRIMARY`)?WHERE?(1=1)?ORDER?BY?`id`?LIMIT?1
SELECT?/*!40001?SQL_NO_CACHE?*/?`id`,`a`?FROM?`test`.`tt1`?FORCE?INDEX(`PRIMARY`)?WHERE?(1=1)?AND?((`id`?>??))?ORDER?BY?`id`?LIMIT?1
INSERT?INTO?`test`.`tt2`(`id`,`a`)?VALUES?(?,?)

#?Without?--no-safe-auto-increment?(default)
[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--dest=h=localhost,D=test,t=tt2?--no-delete?--where?"1=1"?--dry-run
SELECT?/*!40001?SQL_NO_CACHE?*/?`id`,`a`?FROM?`test`.`tt1`?FORCE?INDEX(`PRIMARY`)?WHERE?(1=1)?<strong>AND?(`id`?<?'5009')</strong>?ORDER?BY?`id`?LIMIT?1
SELECT?/*!40001?SQL_NO_CACHE?*/?`id`,`a`?FROM?`test`.`tt1`?FORCE?INDEX(`PRIMARY`)?WHERE?(1=1)?<strong>AND?(`id`?<?'5009')</strong>?AND?((`id`?>??))?ORDER?BY?`id`?LIMIT?1
INSERT?INTO?`test`.`tt2`(`id`,`a`)?VALUES?(?,?)

注意到上面的附加子句 "AND ( id< '5009')" 了嗎？

如果服務器重新啟動，–no-safe-auto-increment 的這個選項可以防止重新使用 AUTO_INCREMENT 值。請注意，額外的 WHERE 子句包含自歸檔或清除作業(yè)開始時自增列的最大值。如果在 pt-archiver 運行時插入新行，pt-archiver 將看不到它們。

好吧，現(xiàn)在我們知道了為什么沒有刪除干凈的“原因”，但為什么呢？AUTO_INCREMENT 的安全問題是什么？

AUTO_INCREMENT 計數(shù)器存儲在內(nèi)存中，當 MySQL 8.0之前的版本重新啟動（崩潰或其他）時，計數(shù)器將重置為最大值。如果發(fā)生這種情況并且表正在接受寫入，則 AUTO_INCREMENT 值將更改。

#?deleting?everything?from?table
mysql>?delete?from?tt1;
...
mysql>?show?table?status?like?'tt1'\G
***************************?1.?row?***************************
???????????Name:?tt1
?????????Engine:?InnoDB
...
?Auto_increment:?10019
...

#?Restarting?MySQL
[root@centos_2?~]#?systemctl?restart?mysql

#?Verifying?auto-increment?counter
[root@centos_2?~]#?mysql?test?-e?"show?table?status?like?'tt1'\G"
***************************?1.?row?***************************
???????????Name:?tt1
?????????Engine:?InnoDB
...
?Auto_increment:?1
...

上面的測試結果告訴我們: 這里的問題實際上并不在于 pt-archiver，而在于參數(shù)選項。在處理 AUTO_INCREMENT 列時使用 pt-archiver 時，了解使用 –no-safe-auto-increment 選項很重要。

讓我們用我們的實驗室數(shù)據(jù)來驗證它。

#?Verifying?the?usage?of?–no-safe-auto-increment?option
[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--purge?--where?"1=1"?--no-safe-auto-increment

mysql>?select?count(*)?from?test.tt1;
+----------+
|?count(*)?|
+----------+
|????????0?|
+----------+
1?row?in?set?(0.00?sec)

使用 –no-delete 選項的復制操作也是如此。

[root@centos_2?~]#?pt-archiver?--source=h=localhost,D=test,t=tt1?--dest=h=localhost,D=test,t=tt2?--no-delete?--where?"1=1"?--no-safe-auto-increment

mysql>?select?count(*)?from?tt1;?select?count(*)?from?tt2;
+----------+
|?count(*)?|
+----------+
|?????5009?|
+----------+
1?row?in?set?(0.00?sec)

+----------+
|?count(*)?|
+----------+
|?????5009?|
+----------+
1?row?in?set?(0.00?sec)

通過上面的代碼和實際測試，我們知道了 pt-archiver 的 -[no]safe-auto-increment 選項的原理和作用。在我們得出一切都很好的結論之前,讓我們多考慮一下選項本身存在的意義。

默認情況下，–no-delete 操作應包含 –no-safe-auto-increment 選項。目前，safe-auto-increment 是默認行為。當我們使用 pt-archiver 的 --no-delete 選項時，沒有刪除操作。這意味著 safe-auto-increment 不應成為關注的原因。
對于 MySQL 8.0，不需要 safe-auto-increment 選項。因為 MySQL 8.0 開始，自增的值是持久化的，并且在實例重新啟動或崩潰后自增的最大值不變。參考：MySQL 工作日志 https://dev.mysql.com/worklog/task/?id=6204

而且由于 MySQL 8.0 auto-increment 是通過重做日志持久化的，這使得它們成為pt-archiver 不關心的一個原因。因此，我們根本不需要 safe-auto-increment 選項。

結論

pt-archiver 是歸檔 MySQL 數(shù)據(jù)的好工具，重要的是要了解所有選項以完全控制我們想要使用它實現(xiàn)的目標。
以后需要根據(jù)自增id進行歸檔的場景，pt-archiver 默認最大的id不會進行歸檔，需要添加參數(shù)：--no-safe-auto-increment 才能對最大id進行處理。

到此這篇關于pt-archiver和自增主鍵的文章就介紹到這了,更多相關pt-archiver和自增主鍵內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: