We have some huge tables in MySQL database, We have already archived/deleted data older than 2012 in 2013, Now we need to archive/delete data older than 2013 means we need to archive data for year 2012 without application downtime.
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
So I need to delete 611992998 records from biggets table.
We have One MySQL Master and 4 MySQL Slaves, We need to delete data from all the servers, What I am thinking is i will delete data in chunks from master so that slaves also didn't lag too much.For that i have created a procedure here is the procedure, I have not yet tested it
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 ;
I have used variable @number_of_records_to_delete
in procedure because,I can pass no of records which i target to delete.
Sample call statement
CALL PurgeOlderData('2012-01-01 00:00:00','2012-01-05 00:00:00',100000,'RTestAll');
I can increase the value of @number_of_records_to_delete
accordingly.
Why DELETE with LIMIT 5000 :
I will first test how much time DELETE with LIMIT 5000 takes if it is Ok (Means executes in 1-3 seconds and Slaves are also Ok), I can increase DELETE LIMIT to 10000, If it is also Ok , I can increase some more.
Table Structure of huge table
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
So my questions are
Does the approach i am going to opt will work and is it optimized way to go. Because we don't want any issues in servers (Like slave lag) due to deletions.
What would be the best approach to do it without issues and downtime.
How can i use pt-archiver for doing this.
pt-archiver --source h=localhost,D=<db_name>,t=RTesAll,S=/path/to/socket_file --where "recordDate < current_date - interval 1 year" --limit=50000 --progress=50000 --purge --txn-size=50000 --statistics --bulk-delete --dry-run
. You may also consider adding check-slave-lag etc. YOU SHOULD TEST THE COMMAND BEFORE USING IT IN PRODUCTION! Check this post also.PRIMARY KEY
starts with the condition in theWHERE
clause. More discussion here . Note thatPARTITIONing
on date and usingDROP PARTITION
is even better.