pt-archiver是一個十分高效的表數據歸檔工具，歸檔數據可以分批進行事務處理，減少性能消耗；

如果實例開啟了GTID，因為GTID不支持CTAS創建表的語法，可以使用pt-archiver處理；

對于跨實例或者跨服務器的表數據歸檔，pt-archiver可以運行在目標端服務器，因為生成的臨時文件是在工具執行所在的服務器。

對于大表的過期數據的批量刪除也可以通過pt-archiver指定選項--purge進行處理。

QQ截圖20200701150725.png

需要清理的表的信息

mysql> select count(*) from fwk_biz_log;

+----------+

| count(*) |

+----------+

| 4347412 |

+----------+

1 row in set (1.05 sec)

mysql> select count(*) from fwk_biz_log where T_LOG_DATE BETWEEN '2018-08-01' AND '2018-09-01';

+----------+

| count(*) |

+----------+

| 11092 |

+----------+

1 row in set (0.01 sec)

刪除數據

將2019-09-01之前的日志刪除，查看數據庫磁盤使用狀態

通過 --drt-run 打印查詢需要清除數據的執行語句，確認執行內容

[root@VM_100_3_centos ~]# pt-archiver --source h=10.41.98.234,D=ipcis_cloud,t=fwk_biz_log --user=user --password=pass --purge --where "T_LOG_DATE BETWEEN '2019-09-01' AND '2019-10-01'" --dry-run

SELECT /*!40001 SQL_NO_CACHE */ `n_log_id`,`t_log_date`,`c_log_file`,`c_log_line`,`c_log_thread`,`c_log_level`,`c_log_message`,`c_log_messagea`,`c_log_messageb`,`c_log_messagec` FROM `ipcis_cloud`.`fwk_biz_log` FORCE INDEX(`PRIMARY`) WHERE (T_LOG_DATE BETWEEN '2019-09-01' AND '2019-10-01') AND (`n_log_id` < '29476920') ORDER BY `n_log_id` LIMIT 1

SELECT /*!40001 SQL_NO_CACHE */ `n_log_id`,`t_log_date`,`c_log_file`,`c_log_line`,`c_log_thread`,`c_log_level`,`c_log_message`,`c_log_messagea`,`c_log_messageb`,`c_log_messagec` FROM `ipcis_cloud`.`fwk_biz_log` FORCE INDEX(`PRIMARY`) WHERE (T_LOG_DATE BETWEEN '2019-09-01' AND '2019-10-01') AND (`n_log_id` < '29476920') AND ((`n_log_id` >= ?)) ORDER BY `n_log_id` LIMIT 1

DELETE FROM `ipcis_cloud`.`fwk_biz_log` WHERE (`n_log_id` = ?)

執行刪除

刪除 2019-09-01 之前的數據

[root@VM_100_3_centos ~]# pt-archiver --source h=10.41.98.234,D=ipcis_cloud,t=fwk_biz_log --user=user --password=pass --purge --where "T_LOG_DATE

[root@VM_100_3_centos ~]#

mysql> select * from fwk_biz_log WHERE T_LOG_DATE

Empty set (0.00 sec)

mysql> select count(*) from fwk_biz_log ;

+----------+

| count(*) |

+----------+

| 4238760 |

+----------+

1 row in set (0.93 sec)

刪除 2019-09-01 跟 2019-10-01 之間的數據

[root@VM_100_3_centos ~]# pt-archiver --source h=10.41.98.234,D=ipcis_cloud,t=fwk_biz_log --user=user --password=pass --purge --where "T_LOG_DATE BETWEEN '2019-09-01' AND '2019-10-01'"

[root@VM_100_3_centos ~]#

mysql> select * from fwk_biz_log WHERE T_LOG_DATE

Empty set (0.00 sec)

mysql>

mysql>

mysql> select count(*) from fwk_biz_log ;

+----------+

| count(*) |

+----------+

| 4234534 |

+----------+

1 row in set (1.11 sec)

在騰訊云上驗證

圖片.png

碎片空間增加了，沒有解決碎片的問題。

歸檔數據

將2019-11月的日志歸檔到 fwk_biz_log_201911表中

創建歸檔表

歸檔數據

[root@VM_100_3_centos ~]# pt-archiver --source h=10.41.98.234,D=ipcis_cloud,t=fwk_biz_log --dest h=10.41.98.234,D=ipcis_cloud,t=fwk_biz_log_201912 --user=user --password=pass --where "T_LOG_DATE BETWEEN '2019-12-01' AND '2020-01-01'" --limit 1000 --commit-each

查看歸檔結果

mysql> select * from fwk_biz_log_201912 limit 1;

+----------+---------------------+---------------------+------------+--------------+-------------+---------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+

| N_LOG_ID | T_LOG_DATE | C_LOG_FILE | C_LOG_LINE | C_LOG_THREAD | C_LOG_LEVEL | C_LOG_MESSAGE | C_LOG_MESSAGEA | C_LOG_MESSAGEB | C_LOG_MESSAGEC |

+----------+---------------------+---------------------+------------+--------------+-------------+---------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+

| 24872794 | 2019-12-01 18:57:41 | PayServiceImpl.java | 458 | svc-#233642 | INFO | 簽約申請 | applySign | {"payType":"2","dataSrc":"66","signature":"3fc5094c38f20aaf6ce9a3d56e9ad27b","bankCode":"ICBC","accountType":"00","accountNo":"6222081812002930000","accountName":"小明","idType":"8","id":"110000199102058366","tel":"18511991009"} | |

+----------+---------------------+---------------------+------------+--------------+-------------+---------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+

1 row in set (0.00 sec)

mysql> select * from fwk_biz_log limit 1;

+----------+---------------------+------------------------+------------+--------------+-------------+------------------------------------------------------+-----------------------+----------------+----------------+

| N_LOG_ID | T_LOG_DATE | C_LOG_FILE | C_LOG_LINE | C_LOG_THREAD | C_LOG_LEVEL | C_LOG_MESSAGE | C_LOG_MESSAGEA | C_LOG_MESSAGEB | C_LOG_MESSAGEC |

+----------+---------------------+------------------------+------------+--------------+-------------+------------------------------------------------------+-----------------------+----------------+----------------+

| 25256968 | 2020-01-01 00:00:01 | PolicyServiceImpl.java | 1994 | svc-#899 | INFO | 更新到期保單狀態Wed Jan 01 00:00:00 CST 2020 | UpdateExpirePlyStatus | ListCPkgNos:[] | |

+----------+---------------------+------------------------+------------+--------------+-------------+------------------------------------------------------+-----------------------+----------------+----------------+

1 row in set (0.00 sec)

mysql> select count(*) from fwk_biz_log_201912;

+----------+

| count(*) |

+----------+

| 6435 |

+----------+

1 row in set (0.00 sec)

--limit

type: int; default: 1

每個片段(statement)查詢和歸檔的行數設置。

限制每個SELECT statements查詢歸檔的行數。默認是 1 。提升Limit限制可以更高效，但是如果你想通過設置很多行來減少歸檔次數需要慎重考慮。這可能會導致其他查詢的問題，取決于存儲引擎，事務隔離級別，以及配置項比如 "--for-update"。

--txn-size

type: int; default: 1

每個事務的行數。

指定每個事務處理的行數。0 關閉transactions altogether。在 pt-archiver 處理這些行后，它提交 "--source" 和 "--dest" (如果有的話)，并且刷新"--file"配置的文件。

這個參數對性能很重要。如果你正在歸檔一個正在運行的服務器，比如大量OLTP任務的服務器，你需要在 transaction size 和 commit overhead 權衡。大的事務可能會導致更多鎖和死鎖，小的事務會導致更頻繁的提交。為了提供建議參數，這里做了個小測試。在個人PC的MySQL實例上，歸檔到文件或其他表。設置值為500，性能約為每千行2秒。 設置為0關閉transactions，開啟autocommit，性能降低到每千行38秒。

如果你不是事務存儲引擎歸檔，或者歸檔到事務存儲引擎，你可以關閉 transactions ，這樣pt-archiver就不會 commit了。

--commit-each

提交每次查詢歸檔 (disables "--txn-size")。

在每個歸檔后、下個查詢前(在sleep前，如果配置 了"--sleep")，提交事務，刷新 "--file" 。

禁用"--txn-size"； 使用 "--limit" 來控制"--commit-each"的事務內容數量。

該配置項常用，作為配置 "--limit" 和 "--txn-size" 同樣值的縮略寫法。 但是更重要的是，它避免了在查詢很多行的時候，事務一直在運行。比如，設想從一個非常大的表的開始位置歸檔舊數據，配置"--limit" 1000 和 "--txn-size" 1000。 在一次歸檔1000行后，pt-archiver查找最近的999行然后歸檔，然后執行下一個SELECT來獲取更多行。這會掃描剩下的表空間，但是沒有獲取更多行。它長時間保持開啟事務，僅用來判斷任務是否結束了。可以使用 "--commit-each" 參數來避免這些問題。

總結

以上是生活随笔為你收集整理的mysql5.6查看归档_MySQL5.6 使用 pt-archiver 分批删除、归档数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。