MySQL 8.0 三大日志的Buffer和刷盘策略默认值

一、MySQL 8.0 三大日志的Buffer和刷盘策略默认值

1. Redo Log

# 缓冲区和刷盘策略
innodb_log_buffer_size = 16M          # Redo Log Buffer大小，默认16MB
innodb_flush_log_at_trx_commit = 1    # 刷盘策略，默认1（最安全）

刷盘策略解释：

0: 每秒刷盘一次（性能最好，可能丢失约1秒数据）
1: 每次事务提交都刷盘（最安全，默认值）
2: 写入OS缓存，不保证立即刷盘

2. Undo Log

# Undo Log配置（MySQL 8.0有重大变化）
innodb_undo_tablespaces = 2          # 默认2个Undo表空间
innodb_max_undo_log_size = 1G        # 每个undo表空间最大1GB
innodb_undo_log_truncate = ON        # 启用自动清理

# Undo Log没有直接的Buffer参数，它使用Buffer Pool
# 刷盘策略跟随数据页刷盘（受innodb_flush_method影响）

3. Binlog

# Buffer和刷盘策略
binlog_cache_size = 32K             # 每个线程的binlog缓存大小
sync_binlog = 1                     # 刷盘策略，默认1（每次提交刷盘）
binlog_format = ROW                 # 默认格式：ROW

sync_binlog解释：

0: 依赖OS刷盘（性能最好）
1: 每次事务提交刷盘（最安全，默认值）
N: 每N次事务提交刷盘

二、一次UPDATE操作中三大日志的写入顺序

下面是一个UPDATE语句在MySQL内部的完整日志写入流程：

-- 示例：更新用户余额
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 假设原balance=500，新balance=400

完整写入流程图：

┌───────────────────────────────────────────────────────┐
│                    UPDATE语句开始                      │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  1. 从磁盘加载数据页到Buffer Pool（如果不存在）       │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  2. 生成Undo Log（记录旧值）                          │
│     - 写入Undo Buffer（Buffer Pool中的Undo页）       │
│     - 同时生成对应的Redo Log记录                      │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  3. 在Buffer Pool中更新数据（内存修改）               │
│     - 数据页标记为"脏页"                              │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  4. 生成Redo Log（记录数据页变化）                    │
│     - 写入Redo Log Buffer                             │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│                两阶段提交开始                          │
├───────────────────────────────────────────────────────┤
│  ┌─────────────────────────────────────────────────┐ │
│  │ 5. Redo Log Prepare                             │ │
│  │    - 将Redo Log标记为PREPARE状态                │ │
│  │    - 根据innodb_flush_log_at_trx_commit刷盘     │ │
│  └─────────────────────────────────────────────────┘ │
│                            ↓                         │
│  ┌─────────────────────────────────────────────────┐ │
│  │ 6. Binlog写入                                   │ │
│  │    - 写入Binlog Cache                           │ │
│  │    - 根据sync_binlog刷盘到磁盘                  │ │
│  └─────────────────────────────────────────────────┘ │
│                            ↓                         │
│  ┌─────────────────────────────────────────────────┐ │
│  │ 7. Redo Log Commit                              │ │
│  │    - 将Redo Log标记为COMMIT状态                 │ │
│  │    - 根据innodb_flush_log_at_trx_commit再次刷盘 │ │
│  └─────────────────────────────────────────────────┘ │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  8. 事务完成，返回客户端成功                          │
└───────────────────────────────────────────────────────┘
                              ↓
┌───────────────────────────────────────────────────────┐
│  9. 后台异步操作                                      │
│     - 脏页刷盘到数据文件（Checkpoint）               │
│     - Undo Log清理（Purge线程）                      │
└───────────────────────────────────────────────────────┘

三、关键时序和两阶段提交

核心：两阶段提交（2PC）保证Redo Log和Binlog一致性

// 两阶段提交的伪代码实现
public void commitTransaction(Transaction trx) {
    // ---------- Prepare阶段 ----------
    // 1. Redo Log Prepare
    writeRedoLogAndSetState(trx, State.PREPARE);
    
    // 根据innodb_flush_log_at_trx_commit决定是否刷盘
    if (innodb_flush_log_at_trx_commit >= 1) {
        fsyncRedoLog();  // 刷盘
    }
    
    // 2. Binlog写入
    writeBinlog(trx);
    
    // 根据sync_binlog决定是否刷盘
    if (sync_binlog == 1) {
        fsyncBinlog();  // 刷盘
    }
    
    // ---------- Commit阶段 ----------
    // 3. Redo Log Commit
    setRedoLogState(trx, State.COMMIT);
    
    // 再次根据innodb_flush_log_at_trx_commit决定
    if (innodb_flush_log_at_trx_commit == 1) {
        fsyncRedoLog();  // 刷盘
    }
    
    // 4. 清理工作
    releaseLocks(trx);
    markTransactionComplete(trx);
}

崩溃恢复时的处理逻辑：

-- 崩溃恢复检查点
1. 读取Redo Log，找到最后一条记录
2. 检查Redo Log状态：
   - 如果状态是COMMIT：重做这个事务
   - 如果状态是PREPARE：检查Binlog
3. 检查Binlog：
   - 如果Binlog中有这个事务：重做事务（提交）
   - 如果Binlog中没有：回滚事务

四、参数配置对性能和安全的影响

1. 最安全的配置（金融级）

[mysqld]
# Redo Log
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 64M

# Binlog  
sync_binlog = 1
binlog_cache_size = 1M

# 数据持久化
innodb_flush_method = O_DIRECT
innodb_doublewrite = ON

效果： 每次提交都刷盘，零数据丢失，但性能较低（~1000 TPS）

2. 平衡配置（电商级）

[mysqld]
# Redo Log
innodb_flush_log_at_trx_commit = 2  # 写入OS缓存
innodb_log_buffer_size = 32M

# Binlog
sync_binlog = 100  # 每100次提交刷盘
binlog_cache_size = 512K

# 数据持久化
innodb_flush_method = O_DIRECT_NO_FSYNC

效果： 可能丢失少量事务，但性能高（~5000-10000 TPS）

3. 性能优先配置（日志/监控）

[mysqld]
# Redo Log
innodb_flush_log_at_trx_commit = 0  # 每秒刷盘
innodb_log_buffer_size = 16M

# Binlog
sync_binlog = 0  # 依赖OS
binlog_cache_size = 256K

效果： 可能丢失最多1秒数据，性能最高（~20000+ TPS）

五、实际测试验证

1. 查看当前配置

-- 查看Redo Log配置
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';

-- 查看Binlog配置  
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'binlog_cache_size';

-- 查看Undo配置
SHOW VARIABLES LIKE 'innodb_undo%';

2. 监控日志写入

-- 监控Redo Log写入
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME IN (
    'Innodb_log_writes',
    'Innodb_os_log_fsyncs',
    'Innodb_log_write_requests'
);

-- 监控Binlog写入
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';
/*
Binlog_cache_disk_use  # 使用磁盘缓存的次数（过多表示cache_size不够）
Binlog_cache_use       # 使用缓存的次数
*/

-- 查看Undo使用
SELECT 
    SPACE, 
    NAME, 
    FILE_SIZE/1024/1024 as size_mb,
    FREE/1024/1024 as free_mb
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE NAME LIKE '%undo%';

3. 性能压测对比

# 使用sysbench测试不同配置的性能
# 配置1：innodb_flush_log_at_trx_commit=1, sync_binlog=1
sysbench oltp_write_only --mysql-flush-log-at-trx-commit=1 run

# 配置2：innodb_flush_log_at_trx_commit=2, sync_binlog=100  
sysbench oltp_write_only --mysql-flush-log-at-trx-commit=2 run

# 配置3：innodb_flush_log_at_trx_commit=0, sync_binlog=0
sysbench oltp_write_only --mysql-flush-log-at-trx-commit=0 run

六、生产环境建议

1. 通用推荐配置

[mysqld]
# Redo Log（保证主库安全）
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_files_in_group = 4
innodb_log_file_size = 1G

# Binlog（主库安全，从库可调整）
sync_binlog = 1  # 主库设为1，从库可设为0或100
binlog_cache_size = 1M
binlog_format = ROW
binlog_row_image = MINIMAL  # 节省空间

# Undo Log（自动管理）
innodb_undo_tablespaces = 2
innodb_max_undo_log_size = 1G
innodb_undo_log_truncate = ON

2. 云数据库的默认配置

# AWS RDS MySQL 8.0默认：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format = ROW

# Azure Database for MySQL默认：
innodb_flush_log_at_trx_commit = 1  
sync_binlog = 1
binlog_format = ROW

# 阿里云RDS MySQL 8.0默认：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog_format = ROW

七、常见问题排查

问题1：Redo Log写入瓶颈

-- 症状：TPS上不去，IO等待高
SHOW ENGINE INNODB STATUS\G
-- 查看LOG部分
-- 如果发现"log buffer wait"或"log write wait"，考虑：
-- 1. 增大innodb_log_buffer_size
-- 2. 增加redo log文件大小和数量
-- 3. 使用更快的磁盘

问题2：Binlog缓存不足

-- 症状：Binlog_cache_disk_use值高
SHOW GLOBAL STATUS LIKE 'Binlog_cache%';

-- 解决方案：
SET GLOBAL binlog_cache_size = 1048576;  # 增大到1MB
-- 或者优化事务大小，避免超大事务

问题3：Undo空间膨胀

-- 症状：Undo表空间持续增长
SELECT 
    NAME, 
    SPACE_TYPE,
    FILE_SIZE/1024/1024 as size_mb
FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
WHERE SPACE_TYPE = 'Undo';

-- 解决方案：
-- 1. 检查长事务：SELECT * FROM information_schema.INNODB_TRX;
-- 2. 合理设置事务隔离级别
-- 3. 分批提交大事务

总结

MySQL 8.0默认配置：

innodb_flush_log_at_trx_commit = 1（Redo Log每次提交刷盘）
sync_binlog = 1（Binlog每次提交刷盘）
binlog_format = ROW（行格式）

写入顺序关键点：

Undo Log → Redo Log → 修改数据页 → Binlog
两阶段提交确保Redo Log和Binlog一致性
刷盘策略决定数据安全性和性能平衡

核心原则：

日志先行（Write-Ahead Logging）：先写日志，后改数据
崩溃恢复：Redo重做已提交+未提交修改，Undo回滚未提交修改
数据一致性：通过2PC保证Redo Log和Binlog的一致性

根据业务需求在数据安全性和性能之间找到平衡点，是配置这些参数的关键。

posted @ 2026-01-04 10:25 BingoJ 阅读(2) 评论(0) 收藏举报

刷新页面返回顶部

BingoJ

上可编程*代码，下可颠勺烧小菜；进可排版写文章，退可骑车送外卖。

MySQL 8.0 三大日志的Buffer和刷盘策略默认值

一、MySQL 8.0 三大日志的Buffer和刷盘策略默认值

1. Redo Log

2. Undo Log

3. Binlog

二、一次UPDATE操作中三大日志的写入顺序

完整写入流程图：

三、关键时序和两阶段提交

核心：两阶段提交（2PC）保证Redo Log和Binlog一致性

崩溃恢复时的处理逻辑：

四、参数配置对性能和安全的影响

1. 最安全的配置（金融级）

2. 平衡配置（电商级）

3. 性能优先配置（日志/监控）

五、实际测试验证

1. 查看当前配置

2. 监控日志写入

3. 性能压测对比

六、生产环境建议

1. 通用推荐配置

2. 云数据库的默认配置

七、常见问题排查

问题1：Redo Log写入瓶颈

问题2：Binlog缓存不足

问题3：Undo空间膨胀

总结

公告

BingoJ

上可编程*代码，下可颠勺烧小菜； 进可排版写文章，退可骑车送外卖。

MySQL 8.0 三大日志的Buffer和刷盘策略默认值

一、MySQL 8.0 三大日志的Buffer和刷盘策略默认值

1. Redo Log

2. Undo Log

3. Binlog

二、一次UPDATE操作中三大日志的写入顺序

完整写入流程图：

三、关键时序和两阶段提交

核心：两阶段提交（2PC）保证Redo Log和Binlog一致性

崩溃恢复时的处理逻辑：

四、参数配置对性能和安全的影响

1. 最安全的配置（金融级）

2. 平衡配置（电商级）

3. 性能优先配置（日志/监控）

五、实际测试验证

1. 查看当前配置

2. 监控日志写入

3. 性能压测对比

六、生产环境建议

1. 通用推荐配置

2. 云数据库的默认配置

七、常见问题排查

问题1：Redo Log写入瓶颈

问题2：Binlog缓存不足

问题3：Undo空间膨胀

总结

公告

上可编程*代码，下可颠勺烧小菜；进可排版写文章，退可骑车送外卖。