Skip to content

Instantly share code, notes, and snippets.

@heathdutton
Last active December 6, 2021 19:18
Show Gist options
  • Save heathdutton/67a83dedfe3b08f48be855d39c5bb98e to your computer and use it in GitHub Desktop.
Save heathdutton/67a83dedfe3b08f48be855d39c5bb98e to your computer and use it in GitHub Desktop.
MySQL - Automatically recover from a big commit latency bottleneck
-- Note, this will CANCEL commits if they take more than 20s to complete.
-- It is assumed that if you have commit latency greater than 20s that something is terribly wrong,
-- and you are now losing data due to a lack of throughput already.
-- This will help resume opperation at the cost of potentially dropping locking changes.
-- Create a stored proceedure that can recover from a commit latency lock-up
-- Do not run multiple of this at once.
-- When running loop and kill till there are none to kill.
DROP PROCEDURE IF EXISTS `recover_commit_latency`;
DELIMITER ;;
CREATE PROCEDURE `recover_commit_latency`()
BEGIN
DECLARE sql_string MEDIUMTEXT;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET FOREIGN_KEY_CHECKS = 0;
SET @running = (
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE
INFO LIKE "%recover_commit_latency%"
AND INFO NOT LIKE "%INFORMATION_SCHEMA.PROCESSLIST%"
);
IF @running = 0 THEN
SELECT @sql_string := 'recover_commit_latency';
WHILE sql_string != '' DO
SELECT @sql_string := CONCAT('KILL ', ID, ';') -- recover_commit_latency
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE COMMAND != 'Sleep'
AND INFO = 'COMMIT'
AND TIME > 20
ORDER BY TIME DESC
LIMIT 1;
IF @sql_string != '' THEN
PREPARE st FROM @sql_string;
EXECUTE st;
END IF;
END WHILE;
END IF;
END;;
DELIMITER ;
-- Create an event to fire that procedure every second
DROP EVENT IF EXISTS `recover_commit_latency`;
CREATE EVENT `recover_commit_latency`
ON SCHEDULE EVERY 1 SECOND
STARTS CURRENT_TIMESTAMP
ON COMPLETION PRESERVE
DO
CALL recover_commit_latency();
-- Show current events
SHOW EVENTS;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment