Ad

I Want To Keep Only 5 Days Data And Delete Rest Of The Data If It Is Less Than The Retention Date. Need To Remember Each Day 2000000 Row Generate

- 1 answer

DELIMITER $$
CREATE PROCEDURE sp_delete_data()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE loop_counter INT DEFAULT 0;
  DECLARE retain_days datetime;
  DECLARE delete_days datetime;
  SET loop_counter=(SELECT ROUND(count(*)/100,0) FROM data2 WHERE datetime<(SELECT  DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2));
  SET retain_days=(SELECT DATE_SUB(now(),INTERVAL 5 DAY)); -- 5 days data will keep
  SET delete_days =(SELECT  DATE_ADD(min(datetime),INTERVAL 1 DAY) FROM data2); -- check old data from table data2
        WHILE i <= loop_counter DO
            IF retain_days>delete_days THEN
                DELETE FROM data2 where datetime<delete_days LIMIT 1000;
            END IF;
            SET i = i + 1;
        END WHILE;  
END$$
DELIMITER ;

I want to keep only 5 days data and delete rest of the data if it is less than the retention date. Since each day data generate almost 2000000 rows that is why it is difficult to delete whole data by one shot. That is why I want to delete 100000 data in each loop. Here, loop_counter variable used to find how many loop we should use for this day data. retain_days variable define to find retention date delete_days variable define to find deleted date. based on retain_days and delete_days varoable data will retain and delete. Fnally this procedure will call by event every 1 day.

My code loop is not working as expected. Need expert solution. If is there any performance issue to delete data like this please let me know. Thanks In Advance

Ad

Answer

Just create an event that runs once a day:

CREATE EVENT purge_old_data
    ON SCHEDULE EVERY '1' DAY
    STARTS CURRENT_TIMESTAMP()
    ON COMPLETION PRESERVE
    COMMENT 'Delete rows older than 5 days'
    DO 
BEGIN
  DELETE
    FROM data2
  WHERE `datetime` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 DAY);
END;

You should not use a loop to delete smaller chunks. In SQL databases loops actually make performance worse. If you have to delete 100s of millions of rows in the first run it is really not a problem for MariaDB.

Ad
source: stackoverflow.com
Ad