MySQL超大表刪除資料過程

牛奔發表於2024-03-26
MySql

背景

筆者在公司負責公司的OpenAPI應用，估產生了呼叫審計的需求。對於儲存這些AccessLog，雖然業界有很合適的架構和理論，奈何我司已成本優先，且作為toB的專案，呼叫量並不算特別大，每天也就2G左右的AccessLog產生。業務特徵又導致整個訂單的週期非常長，最少要儲存1年以上的記錄，以備排查問題所用（扯皮甩鍋）。所以使用了大磁碟的MySQL直接儲存。其表結構如下：

CREATE TABLE `access_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `gmt_create` bigint(20) NOT NULL COMMENT '建立時間',
  `trace_id` varchar(50) DEFAULT NULL COMMENT 'traceId',
  `api_name` varchar(50) DEFAULT NULL COMMENT 'api名稱',
  `api_context` longtext COMMENT '呼叫正文',
  `api_result` longtext COMMENT '返回正文',
  `is_success` tinyint(4) DEFAULT NULL COMMENT '是否成功',
  `time_consuming` bigint(20) DEFAULT NULL COMMENT '消耗時間(毫秒)',
  PRIMARY KEY (`id`),
  KEY `idx_trace_id` (`trace_id`),
  KEY `idx_gmt_create` (`gmt_create`,`api_name`),
  KEY `idx_api_name` (`api_name`,`gmt_create`,`is_success`)
) ENGINE=InnoDB COMMENT='流量入口-api記錄'

而隨著業務發展，需要接入的系統也越來越多，甚至有定時任務需要輪詢介面，導致日誌量暴增。達到了日均40G的地步。單表最大資料量在600G

在此期間，使用了各種手段最佳化寫入量。忽略某些超高頻又不影響業務的API。只記錄某些介面錯誤呼叫的日誌等等。

至於為什麼不採用以月為字尾的動態表，涉及到我司DB管控問題。該方案一直無法透過。

問題拖到現在，涉及兩張表：accessLog 600G， errorLog 200G。儲存已經達到了物理機的上限，擴容就需要進行資料庫遷移，最少需要一週時間提前做資料遷移。

要求

現狀：

  1. 兩張超大表：accessLog 600G， errorLog 200G。
  2. 近兩週暴增了400G的佔用
  3. 整個機器的儲存空間已經達到91%。剩餘90G左右空間。

要求：

  1. 線上做到寫入無影響。
  2. 資料庫不能因此當機。

技術方案

因為涉及的表過大，操作必須謹慎，不能產生臨時表，表重建等隱形操作。

流程如下：

  1. 清理errorLog表，只留存3天資料
  2. 檢查實際空間佔用，確定重建表的空間安全
  3. 重建該表，將可用空間提升到200G左右
  4. 歸檔accessLog表
  5. 清理其超高頻的API日誌
  6. 按照日期保留3個月的日誌
  7. 檢查實際空間佔用，確定重建時空間安全。
  8. 說服DBA，同意基於日期的動態表方案。

清理資料

清理資料相對簡單，只需要加上主鍵排序+limit即可

delete from table_name where *** order by id limit 10000;

但是在清理過程中需要注意binlog檔案大小，因為binlog一般配置了按天儲存檔案，可能導致binlog打滿磁碟的情況。

檢視binlog檔案大小

show binary logs;


| Log_name         | File_size|
| mysql-bin.003312 | 15178497 |
| mysql-bin.003313 | 3841846  |
| mysql-bin.003314 | 12789083 |
| mysql-bin.003315 | 9800029  |

檢視正在寫入的Binlog檔案

show master status;  
  
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |  
| mysql-bin.003315 | 10479164 |              |                  | ****              |

執行過程中，需要暫停一段時間執行，方便binlog切換等操作，否則可能導致例項一直繁忙狀態無法生成新的binlog檔案

清理歷史binlog檔案

PURGE MASTER LOGS TO 'mysql-bin.003315';  
  
--- 清理 mysql-bin.003315 之前的檔案，並不會清理當前檔案。

清理完成後可以再次檢視binlog檔案

檢查實際空間大小

因為MySQL的物理刪除本質上也是邏輯刪除，所以空間並不會被釋放，需要檢查實際的空間佔用，保證重建表時，空間在安全範圍內。

SELECT CONCAT(table_schema, '.', table_name)                      AS 'Table Name',
       CONCAT(ROUND(table_rows / 1000000, 4), 'M')                AS 'Number of Rows',
       CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 4), 'G')  AS 'Data Size',
       CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 4), 'G') AS 'Index Size',
       CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 4), 'G')
                                                                  AS 'Total',
       CONCAT(ROUND((data_free) / (1024 * 1024 * 1024), 4), 'G')
                                                                  AS 'Free Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'database_name';

替換掉database_name為資料庫名稱，則可以看到表有效資料的佔用大小，可釋放空間大小等等。

如Total>資料庫剩餘空間，則重建就是安全的。

重建表

重建表使用一下語句：

OPTIMIZE TABLE `table_name`;

該命令會重建表

結果

清理資料過程中

  • 180 IOPS左右浮動
  • CPU在20%左右
  • 磁碟空間無明顯增長

重建過程中

  • 6000左右的 IOPS，完全吃滿了磁碟效能
  • CPU 40%左右浮動
  • 磁碟初始新增20GB，後續斷崖式下降

原文：https://blog.lianglianglee.com/2023/09/12/mysql-big-table-clean-data/

相關文章