mysql - Rate Limit MySQL Function to prevent timeout

Question

I have a procedure and a function in MySQL. Together they loop over all of our customer logs and delete all but the most recent 25.

Function:

CREATE  FUNCTION `tidylogs`(save_records INT(2)) RETURNS int
    
BEGIN
  DECLARE b,c,s1, bDone INT;
  DECLARE cur_1 CURSOR FOR SELECT DISTINCT office_id from logs;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone  = 1;
    OPEN cur_1;
    SET s1      = 0;
    SET b      = 0;
    SET bDone  = 0;
    REPEAT
        FETCH cur_1 INTO c;           
        CALL tidyofficelogs(c,save_records,@r);
        SET b=b+@r;
    UNTIL bDone =1 
    END REPEAT;
    RETURN b;
    
END

Procedure:

CREATE PROCEDURE `tidyofficelogs`(IN `incoming_office_id` int, IN `how_many_to_save` int, OUT return_val INT)
BEGIN
    DECLARE a INT;
        DELETE FROM logs    WHERE office_id = incoming_office_id AND created_at < date_add(NOW(),interval -2 minute) AND id NOT in (SELECT id FROM ( SELECT id, office_id, url, DENSE_RANK() OVER (PARTITION BY office_id, url ORDER BY id DESC) AS r FROM logs WHERE office_id = incoming_office_id ) AS t WHERE office_id = incoming_office_id AND t.r <= how_many_to_save );
        SELECT ROW_COUNT() into a;
    SET return_val = a;
END

Then to use it, just execute SELECT tidylogs(25);.

The issue I am having is that it locks the logs table for a long time. Originally I had done it all as a single query for all offices, but thought that it would be better to do it in smaller groups. I could have done it in PHP, but I thought it would be fun to do it as a function / procedure.

With that process running, we will get nearly 1000 open processes, taking hundreds, or over 1000, seconds trying to insert new records into the logs table.

I thought I could add a DO SLEEP(5); which would break it up between offices, but then I look at the processlist and the DO SLEEP(5); never seems to end. I have had it lasting 300+ seconds, and could only stop it by killing the process.

Any tips before I just move this into PHP?

标签： mysql