MySQL InnoDB Redo Log Tuning: Sizing for Production Write Throughput

-- Check current redo log configuration
SHOW VARIABLES LIKE 'innodb_redo%';

-- innodb_redo_log_capacity: total redo log size (MySQL 8.0.30+)
-- Default: 104857600 (100MB) -- FAR TOO SMALL for production

-- Set via my.cnf:
-- innodb_redo_log_capacity = 4294967296  # 4GB

-- Check redo log pressure
SHOW STATUS LIKE 'Innodb_redo_log%';
SHOW STATUS LIKE 'Innodb_checkpoint%';

-- Measure redo log write rate over 60 seconds
SELECT variable_value INTO @lsn_start
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_redo_log_current_lsn';

SELECT SLEEP(60);

SELECT variable_value INTO @lsn_end
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_redo_log_current_lsn';

SELECT (@lsn_end - @lsn_start) / 60 AS redo_bytes_per_second,
       (@lsn_end - @lsn_start) / 60 * 3600 AS redo_bytes_per_hour,
       FORMAT((@lsn_end - @lsn_start) / 60 / 1024 / 1024, 2) AS redo_mb_per_second;

-- Rule of thumb: redo log capacity = 60-90 minutes of peak write throughput
-- If peak is 50 MB/s: capacity = 50 * 60 * 60 = 180GB (use 8-16GB in practice)

# /etc/mysql/mysql.conf.d/innodb.cnf

[mysqld]
# Redo log (MySQL 8.0.30+)
innodb_redo_log_capacity  = 8589934592   # 8GB

# Buffer pool (typically 70-80% of RAM)
innodb_buffer_pool_size   = 48G
innodb_buffer_pool_instances = 8         # 1 per GB of buffer pool

# Checkpoint tuning
innodb_max_dirty_pages_pct      = 75     # default 90; flush earlier
innodb_max_dirty_pages_pct_lwm  = 65     # start flushing at 65%
innodb_io_capacity              = 2000   # IOPS available for background I/O
innodb_io_capacity_max          = 4000   # max IOPS during flush urgency

# Flush method (best for NVMe SSD)
innodb_flush_method = O_DIRECT

-- Checkpoint age as % of redo log capacity
-- Should stay below 75% during normal operations
SELECT
    variable_value AS checkpoint_age_bytes,
    ROUND(variable_value / @@innodb_redo_log_capacity * 100, 1) AS checkpoint_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_checkpoint_age';

-- If checkpoint_pct is consistently above 80%, increase redo log capacity

-- Track buffer pool hit rate
SELECT
    innodb_buffer_pool_read_requests,
    innodb_buffer_pool_reads,
    ROUND((1 - innodb_buffer_pool_reads / NULLIF(innodb_buffer_pool_read_requests, 0)) * 100, 2)
    AS hit_rate_pct
FROM (
    SELECT
        SUM(IF(variable_name = 'Innodb_buffer_pool_read_requests', variable_value, 0)) AS innodb_buffer_pool_read_requests,
        SUM(IF(variable_name = 'Innodb_buffer_pool_reads', variable_value, 0)) AS innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE variable_name IN ('Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads')
) x;