一次优化log_buffer_space的经历

本文探讨了在向数据库表中批量插入数据时如何优化提交策略以避免Log Buffer Space等待事件的发生。通过实验发现,适当调整批量提交的数量可以在确保性能的同时避免日志缓冲区溢出。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

当向一张表中插入大量数据时,EM中发现有log_buffer_space等待事件。该等待事件是由于重做量超出日志缓冲区而产生的。

下面介绍实验环境:

1. 日志缓冲区默认大小6M。
2. 表的行宽:NUMBER(9)*1+NUMBER(1)*76=85
3. 计划一次插入1百万行。

lgwr什么时候开始空工作的,它要有触发他的动作
  A. 重做日志缓冲区的已使用的空间达到三分之一时
  B. 当dbwn进程向磁盘写入已修改的缓冲区的时候
  C. 每隔3秒钟
  D. 用户提交事务处理时的一条提交记录(经常commit会及时刷新重做日志缓冲区空间)

如果我们每50,000行提交一次,那么50,000*85>6M的1/3,因此根据上面的A,将触发LGWR把log_buffer写入redo log,这就是LOG BUFFER SPACE产生的原因!

知道了这点,我们调整为每20,000行提交一次,这样提交前将不会超过日志缓冲区的1/3。

比较一下,单次提交量和时间的区别:

25,000 00:04:37.62
50,000 00:04:50.34
10,000 00:04:40.33
20,000 00:04:35.52

结论,20,000最佳!

注意到:不是越小越好,比如10,000就比20,000花更多时间,批量提交应尽可能越多越好,但不能超过LOG BUFFER的1/3警戒线。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22621861/viewspace-1274302/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/22621861/viewspace-1274302/

<think>好的,我现在需要帮助用户解决关于MySQL配置文件中binlog最大存储大小的问题。用户提供的配置文件中已经设置了一些参数,现在他想知道如何确定binlog的最大存储大小。首先,我需要回顾一下binlog相关的配置参数。 用户可能对binlog不太熟悉,所以需要先解释清楚相关参数。binlog的最大存储大小主要由两个参数控制:max_binlog_size和expire_logs_days。max_binlog_size控制单个文件的大小,而expire_logs_days控制日志保留的时间。但用户可能还不太清楚如何计算总体存储量,需要给出具体的公式。 接下来,用户的问题是如何确定这些参数的值,特别是根据系统内存。可能需要提到,binlog的大小不仅受配置参数影响,还和数据库的写入量有关。高写入量的系统会产生更多的binlog,因此需要更大的存储空间。 另外,用户提供的配置中有innodb_buffer_pool_size设置为5120M,这里可能需要建议将binlog的存储位置放在单独的磁盘,避免影响性能。同时,需要提醒用户定期清理旧的binlog文件,使用PURGE BINARY LOGS命令或者设置expire_logs_days自动清理。 还需要检查用户当前的binlog配置,指导他如何查看当前的文件大小和数量,使用SHOW BINARY LOGS命令。此外,提醒用户考虑备份策略,确保binlog的保留时间足够用于恢复数据。 用户可能没有意识到binlog的存储路径设置,需要确认log_bin的路径是否正确,是否有足够的磁盘空间。另外,注意到用户配置中log_bin_trust_function_creators=1,这可能与存储过程有关,但和binlog大小无关,暂时不需要处理。 最后,需要总结关键点,确保用户能够理解并操作。可能还需要给出一个计算示例,帮助用户具体估算所需的存储空间。同时,强调监控和定期调整的重要性,避免磁盘空间被占满导致服务中断。</think>以下是对MySQL配置参数`[mysqld]`段落的详细解析,并针对**binlog最大存储空间计算**给出解决方案: --- ### 一、当前配置核心参数解读 ```ini [mysqld] # 基础配置 server-id=1 # 集群中唯一标识(主从复制必需) port=3306 # 监听端口 basedir=/usr/local/mysql # MySQL安装目录 datadir=/usr/local/mysql/data # 数据存储目录 character-set-server=utf8 # 默认字符集 collation-server=utf8_general_ci # 排序规则 lower_case_table_names=1 # 表名大小写不敏感(Windows兼容模式) # 性能配置 max_connections=1000 # 最大并发连接数 innodb_buffer_pool_size=5120M # InnoDB缓冲池大小(建议占物理内存70%-80%) # 日志配置 log-error=/usr/local/mysql/log/error.log # 错误日志路径 slow-query-log=1 # 启用慢查询日志 long_query_time=2.5 # 慢查询阈值(秒) log_bin=/usr/local/mysql/log/mysql-bin.log # binlog路径 ``` --- ### 二、binlog存储空间计算指南 #### 1️⃣ 核心控制参数 ```ini # 单个binlog文件最大尺寸(默认1GB) max_binlog_size = 1073741824 # binlog过期时间(默认0=永不过期) expire_logs_days = 30 ``` #### 2️⃣ 存储量计算公式 ``` 总存储空间 ≈ (单文件大小 × 每小时生成文件数) × 24小时 × 过期天数 ``` * **示例计算**: ```bash 假设: - 单文件 1GB - 每小时生成 0.5 个文件(即每2小时写满1个文件) - 过期时间30天 总空间 ≈ (1GB × 0.5) × 24 × 30 = 360GB ``` #### 3️⃣ 实践建议 1. **查看当前使用量**: ```sql SHOW BINARY LOGS; -- 显示所有binlog文件及大小 ``` 2. **动态调整参数**: ```sql SET GLOBAL max_binlog_size = 2147483648; -- 临时设置为2GB(需写入配置文件永久生效) ``` 3. **空间监控命令**: ```bash # 查看binlog目录磁盘使用 df -h /usr/local/mysql/log # 统计binlog总大小 du -sh /usr/local/mysql/log/mysql-bin.* ``` --- ### 三、配置优化建议 1. **存储隔离**:将`log_bin`路径单独挂载SSD硬盘(与数据目录分离) 2. **自动清理**:建议设置`expire_logs_days=15`(根据业务需求调整) 3. **监控警报**:配置Zabbix/Prometheus监控binlog目录磁盘使用率 4. **备份策略**:使用`mysqlbinlog`工具定期备份binlog到异地存储 --- ### 四、常见问题排查 **Q:binlog增长过快怎么办?** - 检查是否有大量未提交事务(`SHOW ENGINE INNODB STATUS`) - 减少DDL操作频率 - 启用`binlog_row_image=MINIMAL`(MySQL 5.6+) **Q:如何安全清理binlog?** ```sql -- 删除指定时间前的日志 PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00'; -- 删除指定文件前的日志 PURGE BINARY LOGS TO 'mysql-bin.000010'; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值