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
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
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
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
analyze table to the event to but that doesn't seem to reset the issue or prevent it from happening.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;
I suspect you are using MyISAM, and that is the root of the problem. Change to InnoDB and lower key_buffer_size to 20M and innodb_buffer_pool_size to 70% of available RAM.
MyISAM comments and questions
MyISAM has problems when lots of deletes on big tables. The tables can get fragmented; even rows can become fragmented.
How much RAM? Keep in mind that the key_buffer is only for MyISAM indexes. (That includes Primary, Unique, and ordinary Index.)
InnoDB comments and questions
What is the value of innodb_buffer_pool_size?
What is the value of autocommit? Is there any transactional stuff around the Event? (BEGIN...COMMIT) Maybe there should be?
For either Engine
If any of the tables continue to grow in size, this may contribute to the sudden slowdown.
Some pairs of SET @.. = ( SELECT ... ) could be turned into a single SELECT .. INTO @this, @that ....
Would the UPDATE/INSERT work with a single INSERT ... ON DUPLICATE KEY UPDATE ...?
If that DELETE is really "clearing out the table", then use TRUNCATE instead.
If Score_Budget has a unique key on the two columns LevelId and StreamId, then you can get rid of the EXISTS clause and change the INSERT to INSERT IGNORE.
Please provide SHOW CREATE TABLE for each table. Indexes may be leading to part of the slowdown.