-- REDO OCCUPANCY PERCENT (DB should be 10.5+)
WITH STS
AS (
    SELECT (
            SELECT VARIABLE_VALUE
            FROM information_schema.GLOBAL_STATUS
            WHERE VARIABLE_NAME = 'INNODB_CHECKPOINT_AGE'
            ) AS INNODB_CHECKPOINT_AGE
        ,(
            SELECT VARIABLE_VALUE
            FROM information_schema.GLOBAL_VARIABLES
            WHERE VARIABLE_NAME = 'INNODB_LOG_FILE_SIZE'
            ) AS INNODB_LOG_FILE_SIZE
        ,(            
            SELECT VARIABLE_VALUE
            FROM information_schema.GLOBAL_VARIABLES
            WHERE VARIABLE_NAME = 'INNODB_MAX_DIRTY_PAGES_PCT_LWM'
            ) AS INNODB_MAX_DIRTY_PAGES_PCT_LWM
        ,(
            SELECT VARIABLE_VALUE
            FROM information_schema.GLOBAL_VARIABLES
            WHERE VARIABLE_NAME = 'INNODB_MAX_DIRTY_PAGES_PCT'
            ) AS INNODB_MAX_DIRTY_PAGES_PCT
    )
SELECT format((INNODB_CHECKPOINT_AGE / INNODB_LOG_FILE_SIZE) * 100, 2) AS REDO_OCCUPANCY_PCT, 
INNODB_LOG_FILE_SIZE/1024/1024 as INNODB_LOG_FILE_MB,
INNODB_MAX_DIRTY_PAGES_PCT_LWM, INNODB_MAX_DIRTY_PAGES_PCT
FROM STS;

