从MySQL Master和Slaves中删除巨大的InnoDB表中的数据

2022-10-25 17:19:17

我们在MySQL数据库中有一些巨大的表格,我们已经在2013年存档/删除了早于2012年的数据,现在我们需要存档/删除早于2013年的数据意味着我们需要存档2012年的数据,而无需停止应用程序.

Tables  Size in GB      "TABLE_ROWS"    "TABLE_ROWS BEFORE 2012"
RTesAll 923.65          1982098430       611992998
RTest   32.1            205527090   
RAdT    6.97            25324446    
RAdv    4.37            28260973

所以我需要从biggets表中删除611992998条记录.

我们有一个MySQL Master和4个MySQL Slaves,我们需要从所有服务器中删除数据,我在想的是我将从master中删除数据块,这样奴隶也不会滞后太多.为此我创建了一个这里的程序是程序,我还没有测试过

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;          
END $$
DELIMITER ;

我在程序中使用了变量@number_of_records_to_delete,因为我没有传递我想要删除的记录.

示例呼叫声明

CALL PurgeOlderData('2012-01-01 00:00:00','2012-01-05 00:00:00',100000,'RTestAll');

我可以相应地增加@number_of_records_to_delete的值.

为什么DELETE使用LIMIT 5000：我将首先测试DELETE使用LIMIT 5000需要多长时间,如果它是Ok(Means在1-3秒内执行而Slaves也是Ok),我可以将DELETE LIMIT增加到10000,如果它也是Ok ,我可以增加一些.

表巨大的表结构

CREATE TABLE `RTesAll` (
  `recordDate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `networkId` bigint(11) NOT NULL DEFAULT '0',
  `publisherId` bigint(11) NOT NULL DEFAULT '0',
  `feedId` bigint(11) NOT NULL DEFAULT '0',
  `subPublisherId` bigint(11) NOT NULL DEFAULT '0',
  `subId` varchar(100) NOT NULL DEFAULT '',
  `searches` bigint(20) DEFAULT NULL,
  `matches` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`recordDate`,`networkId`,`publisherId`,`feedId`,`subPublisherId`,`subId`),
  KEY `K_networkId` (`networkId`),
  KEY `K_publisherId` (`publisherId`),
  KEY `K_feedId` (`feedId`),
  KEY `K_subPublisherId` (`subPublisherId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

所以我的问题是

>我将选择的方法是否有效,是否是优化的方式.因为删除所以我们不希望服务器出现任何问题(如奴隶滞后).
>没有问题和停机时间,最好的方法是什么？
>我如何使用pt-archiver来做到这一点.

解决方法:

以下是您应该考虑的一些事项：

考虑因素#1

使用DELETE FROM tbl … LIMIT 5000不是复制安全的,因为要删除的5000行可能不是从属的相同顺序或相同的5000行集.请参阅MySQL Documentation (Replication and LIMIT).如果主服务器上的DELETE被中断,则必须回滚.在那种情况下,调用DELETE FROM tbl … LIMIT 5000的次数可能不太可能相同.在Slave上额外调用DELETE并不重要.通话太少可能会很糟糕.这不应该发生.

建议：确保主服务器和所有从服务器上RTesAll的行数相同.

考虑因素#2

由于您在主服务器上运行存储过程,因此DELETE命令不会在主服务器上开始执行,直到完成主服务器上的DELETE和DELETE命令发布在主服务器的二进制日志中.使用一个Master和4个Slave(5个DB Server),有一些DELETE调用乘以5.

建议#1：你应该添加set sql_log_bin = 0;到存储过程

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;

    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;          
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

防止所有DELETE重复.如果要删除611992998行,则每5000行删除需要执行DELETE … LIMIT 5000 122399次(这是611992998/5000向上舍入到下一个整数).

这将防止必须写入122399次binlogs.

建议#2：同时在主站和从站上运行存储过程.

>这可以防止复制滞后
>这会同时消除所有行
>最好在维护周期或慢读取时间内完成

考虑因素#3

确保在主服务器和从服务器上启用了自动提交.否则,所有删除都将堆积在ibdata1内的撤消日志中,然后作为单个事务或大量回滚一次执行所有删除操作.

试试看！！！

码农公寓

相关文章