一次优化log_buffer_space的经历

最新推荐文章于 2025-02-22 15:39:11 发布
转载 最新推荐文章于 2025-02-22 15:39:11 发布 · 204 阅读 · 0

本文探讨了在向数据库表中批量插入数据时如何优化提交策略以避免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/

cssg91583
关注
oracle redo log优化,实例优化——redo_log_buffer
weixin_32567865的博客
04-14 647
用户修改一条数据时，redo的一系列的行为1，用户发出一条SQL语句，Oracle为该事务分配了唯一的事务号2，服务器进程负责将需要的数据、索引和undo数据读入内存，并将要更新的行加锁3，服务器进程获得重做拷贝闩锁(闩锁实现对redo_buffer的串行使用)，该闩锁是服务器进程访问redo_log_buffer的第一步。此时如果，没有其他的闩锁使用，则别的服务器进程无法使用redo_log_b...
innodb_buffer_pool_size过小+同步任务目标表过大，导致MySQL无法处理正常请求
自由人的博客
08-16 830
innodb_buffer_pool_size过小+同步任务目标表过大，导致MySQL无法处理正常请求
1_log_buffer上的优化思路
wolihaito的博客
02-20 424
LGWR 进程将log buffer 中的日志条目写往在线日志的条件有如下3个： 1.事务提交时 2.log buffer 中日志条目超过1M时 3.每隔3秒是 虽然触发lgwr 进程写日志的条件很多，但在日志生成量很大的系统中（比如每秒生成10M以上），建议设置比较大的 logbuffer （如 50m）,从而防止log buffer 空间不足。 oracle 写日志特性： 1.由于lgwr 进...
log buffer及日志管理深入分析及性能调整（一）
DBA实施者
01-05 9827
1. log buffer的概念 1.1 log buffer概述 数据库在运行过程中，不可避免的要遇到各种能够导致数据块库损坏的情况。比如突然断电、oracle或者操作系统的程序bug导致数据库内部逻辑结构损坏、磁盘介质损坏等，都有可能造成数据库崩溃，从而导致数据丢失的现象发生。 为了避免，或者说为了修复这些状况所导致的数据丢失现象，oracle引入了日志缓冲区和日志文件的概念。所谓日志，
log buffer —— log buffer space
事后诸葛亮的程序人生（微信：zq9017197）
11-03 6019
欲向重做缓冲区上写入重做记录的进程，为了确保拥有重做缓冲区内必要的空间，需要获得redo allocation锁存器。已获得redo allocation锁存器的状态下，在想要得到重做缓冲区时，若没有适当的剩余空间，则需要等到直到获得空间为止。这时，根据情况等待两种事件。如果当前正在使用的重做日志文件已满，因此无法获得剩余空间，LGWR就会执行日志文件切换，服务器进程则等待log file swi
等待事件 log buffer space（日志缓冲空间）
小楼一夜听春雨，深巷明朝卖杏花
07-02 6169
 log Buffer Space-日志缓冲空间 当数据库产生日志的速度比 LGWR 的写出速度快，或者是当日志切换（ log switch）太慢时，就会发生这种等待。这个等待出现时，通常表明 redo log buffer 过小， 为解决这个问题，可以考虑增大日志文件的大小，或者增加日志缓冲区的大小。 另外一个可能的原因是磁盘 I/O 存在瓶颈，可以考虑使用写入速度更快的磁盘。在允许的条件下...
延迟有关的常见等待事件
喝醉酒的小白
05-11 940
本文分析了Oracle数据库中五种常见的等待事件及其优化建议：1）log file sync：与事务提交相关，建议合并小事务并优化日志磁盘性能；2）log buffer space：日志缓冲区不足导致，建议增大缓冲区或优化事务量；3）free buffer waits：缓冲区高速缓存不足，建议调整缓存大小或优化查询；4）wait complete waits：DBWR写入延迟，建议提升磁盘性能或调整检查点；5）log file switch：日志切换频繁，建议增大日志组或优化事务。这些问题的核心都与I/O性
MySQL relay_log_recovery源码分析
w1346561235的博客
03-25 1401
此处所列代码为mysql8.0.23版本，其它版本可能略有差异。 relay_log_recovery按照官方文档描述，在server启动的时候，会创建新的relay_log，并将sql_thread指向新的relay_log坐标，并且引导io_thread将从master读取的event写入新的relay log中。从而跳过旧的relay log的apply，进而避免旧的relay log出现问题导致复制出现问题。 但是在MTS下，在出现复制gap的时候，仍然需要在旧的relay log上将gap补齐
mysql之InnoDB Buffer Pool 深度解析与性能优化
qq_43460315的博客
02-22 1727
InnoDB Buffer Pool 是 MySQL InnoDB 存储引擎中至关重要的内存组件，它作为磁盘数据页和 CPU 之间的缓冲层，显著提升了数据访问速度，是 MySQL 性能优化的核心。深入理解 Buffer Pool 的工作原理和调优策略，对于构建高性能 MySQL 应用至关重要。InnoDB Buffer Pool 是 MySQL 性能优化的基石，合理配置和调优 Buffer Pool 可以显著提升数据库性能。
MySQL 日志篇：Redo Log Buffer
一直学，你始终是学生，但是只要创造一招，你便是宗师。
09-09 931
认识 MySQL 的 Redo Log。
等待事件 log buffer space
喝醉酒的小白
07-05 2222
select inst_id,username,event,count(*) from gv$session where wait_class<>'Idle' group by inst_id,username,event order by 4; alter system set commit_logging='BATCH'； 等待事件 log buffer space
Oracle等待事件之四---log相关等待事件
竹林晚风
08-29 1263
一、Log file parallel write 后台进程LGWR负责将log buffer当中的数据写入到REDO文件中，以重用log buffer的数据。如果每个REDO LOG组里面有2个以上的成员，那么LGWR进程将会并行地将REDO信息写入到这些文件中。 如果数据库中出现这个等待事件的瓶颈，主要原因可能是磁盘I/O性能不够或者REDO文件的分布导致了I/O的争用，比如同一个组的RE
Oracle Study之--Oracle等待事件（9）
weixin_34062469的博客
08-08 119
Oracle Study之--Oracle等待事件（9）Log buffer space当log buffer 中没有可用空间来存放新产生的redo log数据时，就会发生log buffer space等待事件。如果数据库中新产生的redo log的数量大于LGWR 写入到磁盘中的redo log 数量，必须等待LGWR 完成写入磁盘的操作，LGWR必须确保redo lo...
与log相关的等待事件及解决方法
冰刀(skate)
10-11 1716
author:skatetime:2009/10/11 一. log buffer的优化 日志块的都是按照顺序往里写，不存在更新日志块的以前的内容，同时每个日志块大小都很小，是操作系统数据块的大小（一般为512k），所以几乎不会有多个进程抢同一个日志块的情况。其争用主要是进程由于找不到可用的日志块而必须等待的情况。而我们知道LGWR负责释放脏的日志块从而提供可用日志块，LGWR
log buffer space等待事件
weixin_33681778的博客
08-27 316
最近，我们有台服务器在delete操作期间发现一直在等待log buffer space，其他节点就没与这个问题。经查，向重做缓冲区上写入重做记录的进程，为了确保拥有重做缓冲区内必要的空间，需要获得redo allocation锁存器。已获得redo allocation锁存器的状态下，在想要得到重做缓冲区时，若没有适当的剩余空间，则需要等到直到获得空间为止。这时，根据情况等待两种事件。如果当前正...
oracle的log buffer,6.log buffer spaces
weixin_39955421的博客
04-05 402
log buffer spaces等待事件原创 2017-08-04 Oracle 宅必备从今天开始讲解awr报告Top 5 Timed Events部分今天讲log buffer space等待事件log buffer spacelog buffer space 这个等待事件一般来说很少发生，一旦等待比较严重 往往说明系统的设置问题如何发生的我们知道Oracle的一些DML操作(insert,u...
Oracle常见等待事件
陈泽钦的博客
12-06 2089
Oracle常见等待事件buffer busy waitsfree buffer waitslibrary cache locklibrary cache pindb file scattered readdirect path readdirect path writelog buffer spacelog file sequential readlog file switch（archiving need）log file switch（checkpoint incomplete）log file sy
log buffer space wait event等待事件
03-30 235
原因：　　1,logfile switch;这时lgwr要把log buffer写入到新的日志文件，而用户进程必须等待这个　　　切换日志事件发生，方可把产生的redo entry写入到log buffer 2,lo...
No buffer space available 一次神奇的维护经历
热门推荐
一起记录GIS学习
08-31 3万+
前段时间在项目维护当中遇见一个问题，方便以后再次遇见类似的问题，可以参考解决问题的思路，记录如下： 问题概述： 维护项目当中，客户这边发来消息，称自己的网站登录不上去了，提示用户名密码错误，我就登录到服务器上查看系统日志和报错情况，如图： 这个异常来源部署的一个jar包，用于读取数据存入mongodb，再看了Tomcat也报错了，错误是：java.net.SocketException。...
[mysqld] server-id=1 port=3306 basedir=/usr/local/mysql datadir= /usr/local/mysql/data character-set-server= utf8 collation-server= utf8_general_ci init_connect="SET NAMES utf8" lower_case_table_names= 1 max_connections= 1000 sql_mode="STRICT_TRANS_TABLES,IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" log_error=/usr/local/mysql/log/error.log log-output=FILE general-log=0 general_log_file=/usr/local/mysql/log/general.log slow-query-log=1 long_query_time=2.5 slow_query_log_file=/usr/local/mysql/log/slow.log log_bin=/usr/local/mysql/log/mysql-bin.log log_bin_trust_function_creators=1 innodb_buffer_pool_size=5120M # 根据系统内存 如何知道binlog的最大存储大小呢
最新发布
03-08
<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、付费专栏及课程。

余额充值