pt-archiver和自增主鍵的問題怎么解決

今天小編給大家分享一下pt-archiver和自增主鍵的問題怎么解決的相關(guān)知識點，內(nèi)容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

前言

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

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

問題 "使用 pt-archiver  刪除數(shù)據(jù)時，最后一行數(shù)據(jù)未被刪除。這個是不是bug?"

分析

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

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

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

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

為了降低此操作的嚴重性，我們可以使用 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)

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

將表從 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 歸檔行?！?/p>

這意味著，選項 –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')" 了嗎？

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

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

AUTO_INCREMENT 計數(shù)器存儲在內(nèi)存中，當(dāng) 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
...

上面的測試結(jié)果告訴我們: 這里的問題實際上并不在于 pt-archiver，而在于參數(shù)選項。在處理 AUTO_INCREMENT 列時使用 pt-archiver 時，了解使用 &ndash;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)

使用 &ndash;no-delete 選項的復(fù)制操作也是如此。

[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 選項的原理和作用 。在我們得出一切都很好的結(jié)論之前,讓我們多考慮一下選項本身存在的意義。

• 默認情況下，&ndash;no-delete 操作應(yīng)包含 &ndash;no-safe-auto-increment 選項。目前，safe-auto-increment 是默認行為。當(dāng)我們使用 pt-archiver 的 --no-delete 選項時，沒有刪除操作。這意味著 safe-auto-increment 不應(yīng)成為關(guān)注的原因。

• 對于 MySQL 8.0，不需要 safe-auto-increment 選項。因為 MySQL 8.0 開始，自增的值是持久化的，并且在實例重新啟動或崩潰后自增的最大值不變。

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

以上就是“pt-archiver和自增主鍵的問題怎么解決”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注億速云行業(yè)資訊頻道。