MySQL Undo

Databases

MySQL

MySQL Configuration

Calculating Rollback Time

DELIMITER $$
CREATE FUNCTION RollbackTimeCalc(processID INT, timeInterval INT)RETURNS VARCHAR(225)DETERMINISTICBEGIN   DECLARE RollbackModifiedBeforeInterval INT;   DECLARE RollbackModifiedAfterInterval INT;  DECLARE RollbackPendingRows INT;   DECLARE Result varchar(20);            SELECT trx_rows_modified INTO RollbackModifiedBeforeInterval        FROM information_schema.innodb_trx       WHERE trx_mysql_thread_id = processID         AND trx_state = 'ROLLING BACK';
      DO SLEEP(timeInterval);
      SELECT trx_rows_modified INTO RollbackModifiedAfterInterval        FROM information_schema.innodb_trx       WHERE trx_mysql_thread_id = processID         AND trx_state = 'ROLLING BACK';        SET Result=SEC_TO_TIME(ROUND((RollbackModifiedAfterInterval*timeInterval)/(RollbackModifiedBeforeInterval-RollbackModifiedAfterInterval)));        SELECT trx_rows_modified INTO RollbackPendingRows        FROM information_schema.innodb_trx       WHERE trx_mysql_thread_id = processID         AND trx_state = 'ROLLING BACK';        RETURN(CONCAT('Estimation Time of Rollback : ', Result, ' Pending rows to rollback ', RollbackPendingRows));END$$DELIMITER ;Based on script from: https://www.mydbops.com/blog/how-to-estimate-time-for-rollback-in-a-cancelled-transaction-mysql

SELECT RollbackTimeCalc(99,5);

Where 99 is the Process ID and 5 is the sample interval

innodb_rollback_segments

Bibliography

https://dev.mysql.com/doc/refman/5.6/en//innodb-undo-logs.htmlhttps://www.mydbops.com/blog/how-to-estimate-time-for-rollback-in-a-cancelled-transaction-mysql

Page updated

Google Sites

Report abuse