Why does my MySQL queries executed by an Event run poorly after performing well?

Hello I have a decent amount of experience in as a SQL developer in Microsoft SQL but little to no experience as a DBA and I’m just starting to learn MySQL. Basically I have a scheduled stored procedure that runs fine for a number of hours then suddenly stops performing running nearly 30x slower. (not a locking/blocking issue)

I am generating lots of randomized test data on a new server with virtually no activity on it with an EVENT that I set up to run every 10minutes. The event I set up does some very basic logging and executes two stored procedures, one that populates a staging table and the other than populates the final tables (this more closely resembles how the data will get into the system once in production).

Event

• Executes 2 sprocs once every 10min
• logs to a table how long it took to run
• reads the log table and doesn’t execute if last execution hasn’t completed

delimiter $$

CREATE EVENT Score_Stage_Processing_ANDTEST
ON SCHEDULE EVERY 10 minute
STARTS CURRENT_TIMESTAMP 
ON COMPLETION NOT PRESERVE
ENABLE
DO
BEGIN 

    set @ProcName = 'Score_Stage_Processing_ANDTEST';
    set @EndDate = (
        select EndDate 
        from Event_Log el 
        where Name = @ProcName
        order by StartDate desc     
        limit 1);
    set @StartDate = (
        select StartDate 
        from Event_Log el 
        where Name = @ProcName
        order by StartDate desc
        limit 1);
        
    -- Only execute if last execution was successful.
    IF ((@StartDate is not null and @EndDate is not null) or (@StartDate is null and @EndDate is null))
    THEN    
        INSERT INTO Event_Log(Name, StartDate, EndDate)
        VALUES(@ProcName, now(), null);
       
        Set @ID = Last_Insert_ID();
    
        set bulk_insert_buffer_size = 1024*1024*256; -- default 1024*1024*8
        call test_create_scores(1000);
        call Score_Stage_Processing();

        update Event_Log
        set EndDate = now()
        where ID = @ID;
        
    END IF;
  
end $$
delimiter ; 

Stored Procedure 1

• generates 70k randomized records and put them into a staging table for processing

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_create_scores`(
    IN in_NumInsertS int
)
sp: BEGIN

    DECLARE i INT DEFAULT 1;    

    set @max = in_NumInsertS;
    
    while i <= @max
    DO
    
    Set @STD = 5000;
    Set @Mean = 20000;
    
    -- 20 random levels Unbreaking New
    insert into stg_Score_Pending (LevelID, SteamID, Score, Stress, isUnbreaking)
    select LevelID 
        , FLOOR(RAND() * (1000000000-100000000) + 100000000) as SteamID -- pretty much always new people
        , floor(((RAND() * 2 - 1) + (RAND() * 2 - 1) + (RAND() * 2 - 1)) * @STD + @Mean) as RandScore
        , FLOOR(RAND() * (9900-6000) + 6000) as Stress -- between 60 and 99
        , 1 as isUnbreaking
    from Level
    where LevelType = 'Campaign'
    order by rand()
    limit 40;
    
    -- 15 random levels breaking new players
    insert into stg_Score_Pending (LevelID, SteamID, Score, Stress, isUnbreaking)
    select LevelID 
        , FLOOR(RAND() * (1000000000-100000000) + 100000000) as SteamID -- pretty much always new people
        , floor(((RAND() * 2 - 1) + (RAND() * 2 - 1) + (RAND() * 2 - 1)) * @STD + @Mean) as RandScore
        , 10000 as Stress -- between 60 and 99
        , 0 as isUnbreaking
    from Level
    where LevelType = 'Campaign'
    order by rand()
    limit 30;
    

    SET i = i + 1;
    end while;

    leave sp;

    
END;

Stored Procedure 2

• Dedupe records from staging as needed
• Insert or update records two 2 different tables (~70k to two different tables)

CREATE DEFINER=`root`@`localhost` PROCEDURE `score_stage_processing`()
BEGIN

    set @BatchSize = 10000;
    set @BatchCount = 200;
    
    set @InitialMax = (select max(ID) from `stg_Score_Pending`);
    set @m = 2147483647;

    -- batches and caps number of updates
    set @MinID = (select min(ID) from `stg_Score_Pending`);
    set @MaxID = @minID + @BatchSize;

    while @BatchCount > 0 and @InitialMax > @MaxID - @BatchSize
    do

        -- Identify Pending Miniumum Stress and Score
            create temporary table if not exists tmp_ScoreBudgetStress
                (primary key tmp_stress_pkey (LevelID, SteamID))
            select ssp.LevelID 
                , ssp.SteamID 
                , case when min(ssp.Score) < ifnull(min(sb.Score),@m) Then min(ssp.Score) else min(sb.Score) end as MinScore
                , case when min(ssp.Stress) < ifnull(min(ss.Score),@m) then min(ssp.Stress) else min(ss.Score) end as MinStress
            from stg_Score_Pending ssp 
                left join Score_Budget sb on sb.LevelID = ssp.LevelID -- This prevents INCREASING the score  
                    and sb.SteamID = ssp.SteamID 
                    and sb.Score < ssp.Score 
                left join Score_Stress ss on ss.LevelID  = ssp.LevelID -- This prevents INCREASING the score
                    and ss.SteamID  = ssp.SteamID 
                    and ss.Score  < sb.Score 
            where ssp.id <= @MaxID 
            group by ssp.LevelID, ssp.SteamID;
        
        
        -- Identify Pending Minimum Unbreaking
            create temporary table if not exists tmp_ScoreUnbreakingBudget
                (primary key tmp_budget_pkey (LevelID, SteamID))
            select ssp.LevelID 
                , ssp.SteamID 
                , case when min(ssp.Score) < ifnull(min(sb.Score),@m) Then min(ssp.Score) else min(sb.Score) end as MinUnbreakingScore
            from stg_Score_Pending ssp 
                left join Score_Budget sb on sb.LevelID = ssp.LevelID -- This prevents INCREASING the score  
                    and sb.SteamID = ssp.SteamID 
                    and sb.Score < ssp.Score 
            where ssp.id <= @MaxID 
                and ssp.isUnbreaking = 1
            group by ssp.LevelID, SteamID;
        
        -- Updates to SCORE BUDGET
        
            update Score_Budget sb 
                inner join tmp_ScoreBudgetStress s on s.LevelID = sb.LevelID -- inner join serves as existance check (update all scores that exists in table already)
                    and s.SteamID = sb.SteamID 
                left join tmp_ScoreUnbreakingBudget u on u.LevelID = sb.LevelID  
                    and u.SteamID = sb.SteamID
            set sb.Score = s.MinScore
                , sb.ScoreUnbreaking = u.MinUnbreakingScore
                , sb.hasNoUnbreaking = case when u.MinUnbreakingScore is null then 1 else 0 end;
         
            insert into Score_Budget (LevelID, SteamID, Score, ScoreUnbreaking, hasNoUnbreaking, SampleKey)
            select s.LevelID
                , s.SteamID
                , s.MinScore
                , u.MinUnbreakingScore
                , case when u.MinUnbreakingScore is null then 1 else 0 end
                , case floor(rand() * 10) 
                     when 0 then 1 -- 10%
                     when 1 then 2 -- 30%
                     when 2 then 2
                     when 3 then 2
                     when 4 then 3 -- 60%
                     when 5 then 3
                     when 6 then 3
                     when 7 then 3
                     when 8 then 3
                     when 9 then 3
                     end as SampleKey
            from tmp_ScoreBudgetStress s
                left join tmp_ScoreUnbreakingBudget u on u.LevelID = s.LevelID  
                    and u.SteamID = s.SteamID
            where not exists (
                select 1
                from Score_Budget sb
                where sb.LevelID  = s.LevelID 
                    and sb.SteamID  = s.SteamID
                );
            
        -- Updates to SCORE STRESS
            update Score_Stress ss 
                inner join tmp_ScoreBudgetStress s on s.LevelID = ss.LevelID -- inner join serves as existance check (update all scores that exists in table already)
                    and s.SteamID = ss.SteamID 
                left join tmp_ScoreUnbreakingBudget u on u.LevelID = ss.LevelID  
                    and u.SteamID = ss.SteamID
            set ss.Score = s.MinStress;
            
            insert into Score_Stress (LevelID, SteamID, Score, SampleKey)
            select s.LevelID
                , s.SteamID
                , s.MinStress
                , case floor(rand() * 10) 
                     when 0 then 1 -- 10%
                     when 1 then 2 -- 30%
                     when 2 then 2
                     when 3 then 2
                     when 4 then 3 -- 60%
                     when 5 then 3
                     when 6 then 3
                     when 7 then 3
                     when 8 then 3
                     when 9 then 3
                     end as SampleKey
            from tmp_ScoreBudgetStress s 
                left join tmp_ScoreUnbreakingBudget u on u.LevelID = s.LevelID  
                    and u.SteamID = s.SteamID
            where not exists (
                select 1
                from Score_Stress ss
                where ss.LevelID  = s.LevelID
                    and ss.SteamID  = s.SteamID
                );
        
        -- Clear Out Staging Table
            
            Delete d From stg_Score_Pending d Where id <= @MaxID;       
            
        -- Drop temporary tables
            drop temporary table if exists tmp_ScoreBudgetStress;
            drop temporary table if exists tmp_ScoreUnbreakingBudget;   
        
        set @MaxID = @MaxID + @BatchSize;
        set @BatchCount = @BatchCount - 1;
    end while;
    
    
END;

Main Problem
The logging table shows the event start and finish quickly then suddenly start taking lots of time. For example my last attempt the event ran successfully in about 30 seconds. Then suddenly the event starts to take 15 minutes each time it executes. (I have special handling to ensure it doesn’t start if it is running)
SS of Custom Event Log Showing fast execution then slow

After the event start to run slowly I have to stop the event not run the job for several hours then try again later. I don’t know exactly what I need to do to fix it right away other than to wait and try again (usually next day)

My Guess
I feel the server is doing one of two things

1. Server gets a bad execution plan. After more and more rows are added table statistics become out of date and MySQL fails to find a good plan. I have attempted adding analyze table to the event to but that doesn’t seem to reset the issue or prevent it from happening.
2. Some memory buffer gets full and I need to wait for it to be flushed. I’ve tried increasing the variable bulk_insert_buffer_size from 8MB to 256MB to no effect. I’ve also added the set command to the event to try to ensure that it remains updated.

Note: There is nothing Locking the tables, this is the only process running on the server and no one is connecting to it other than myself. When I check show full processlist when it is running slow there are no other processes running

I suspect there is some server configuration that I need to change or some sort cache I need to clear in order to prevent the sudden slow down.

So far I have mostly just tried editing a few different variables. I have also tried restarting the server, flushing buffers that I know about, analyzing tables that change a lot.

    set bulk_insert_buffer_size = 1024*1024*256; -- 256mb default 1024*1024*8
    set persist key_buffer_size = 1024*1024*1024; -- 1gb default 1024*1024*16  (recommends 25 to 30 percent of total memory on server)
    set innodb_buffer_pool_size = 1024*1024*1024*13; -- 13gb default 1024*1024*128

Thanks for your help and time!

Edit: DDLs

CREATE TABLE `stg_Score_Pending` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `LevelID` varchar(20) NOT NULL,
  `SteamID` bigint NOT NULL,
  `Score` int NOT NULL,
  `isUnbreaking` bit(1) NOT NULL,
  `Stress` int NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `ix_stg_Score_Pending_LevelID_SteamID` (`LevelID`,`SteamID`)
) ENGINE=InnoDB AUTO_INCREMENT=16948201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `Score_Budget` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LevelID` varchar(20) NOT NULL,
  `SteamID` bigint NOT NULL,
  `Score` int NOT NULL,
  `ScoreUnbreaking` int DEFAULT NULL,
  `hasNoUnbreaking` bit(1) NOT NULL,
  `SampleKey` tinyint NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ux_Score_Budget_LevelID_SteamID` (`LevelID`,`SteamID`),
  KEY `ix_Score_Budget_LevelID_unbreaking` (`LevelID`,`SampleKey`,`hasNoUnbreaking`,`ScoreUnbreaking`),
  KEY `ix_Score_Budget_LevelID_overall` (`LevelID`,`Score`)
) ENGINE=InnoDB AUTO_INCREMENT=14067791 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;

CREATE TABLE `Score_Stress` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LevelID` varchar(20) NOT NULL,
  `SteamID` bigint NOT NULL,
  `Score` int NOT NULL,
  `SampleKey` tinyint NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ux_Score_Stress_LevelID_SteamID` (`LevelID`,`SteamID`),
  KEY `ix_Score_Stress_LevelID_overall` (`LevelID`,`SampleKey`,`Score`)
) ENGINE=InnoDB AUTO_INCREMENT=14067791 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED;