`
- 浏览:
442424 次
- 性别:
- 来自:
北京
-
MySQL 研究innodb_max_purge_lag分享
现象:
线上数据库每个表分配一个ibdata,但是总的ibdata文件很大,超过10G,用相关工具查看,大部分空间都是undo_log
分析了db33的ibdata1的记过
Total number of page: 2398464: 2.4M的page * 16K = 38G
Insert Buffer Free List: 2659
B-tree Node: 5720
Freshly Allocated Page: 12725
Undo Log Page: 2352027
File Segment inode: 25333
可以看到绝大部分的空间,都是被undo log占用了....
分析:
undo log 过大 看来是Mysql的一个“problematic feature” 从03年到现在一直都有人抱怨这个问题。有补丁(需要自己修改源码编译),但是不知道是否可靠...
http://bugs.mysql.com/bug.php?id=57611
http://bugs.mysql.com/bug.php?id=1341
出现的原因的一个可能原因:purge 线程赶不上速度,没有即使回收不用的undo page。可以增加一些
这里面http://bugs.mysql.com/bug.php?id=45173 有人提出一些优化参考的方案:
增大innodb_io_capacity (mysq 5.0不支持 5.1才有)
设置独立的purge thread mysql 5.0不支持,5.5才有
调节 innodb_max_purge_lag 这个值表明,当purge赶不上写操作的时候,写操作delay的时间指标,我们是0,表示不等待,有可能大并发写,purge落后
查看了一下文档,结论:不推荐用innodb_max_purge_lag来实现undo log扩充的问题。
主要原因如下:
1,innodb_max_purge_lag调节参数不好设定,调整不好会强烈影响到正常insert,update的时效,得不偿失
2,innodb_max_purge_lag,有人实验证明,该参数调节影响不是很大,对delete,insert没有作用,副作用大,强烈不推荐 http://mysqldump.azundris.com/archives/81-DELETE,-innodb_max_purge_lag-and-a-case-for-PARTITIONS.html
undo log较大的原因是:
1,Mysql 每10S操作一次purge
2,每次purge mysql做多回收20 page 的undo log
如果10S之内删除,update的数据操作20 page,也就是320K的东西,就会出现purge 回收不及时的情况,就会出现undo log过大。
对应的 queue表,是删除过一次,平均queue数据长度为292 因此只要1S删除queue表超过100行,就会出现上述情况。因此只要线上大规模 delete 数据就会出现删除不干净的情况。
解决方案:
1,delete操作,脚本控制,不要一口气删除感觉,要sleep,控制在1S删除100条的速度 这个已经证明是一个非常好的方案。删除短信数据的时候,如果速度过快,ibdata显著增加,如果控速适当,该文件是根本不会增加的
2, 如果是全表删除,推荐truncate,ddl不会写undo
3, 如果是delete + where 删除的需求,也可以考虑建立新表,导入部分旧表数据+truncate 旧表的方式,
4, DAO层面支持分表操作,彻底去掉删除大表数据这种事情
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
innodb_data_file_path用来指定innodb tablespace文件,如果我们不在My.cnf文件中指定innodb_data_home_dir和innodb_data_file_path那么默认会在datadir目录下创建ibdata1 作为innodb tablespace。 说明 在测试环境...
py_innodb_page_info工具为《INNODB存储引擎》作者姜承尧写的。 该工具用来分析表空间中的各页得类型和信息,用python编写。 网上多是python2版本的,这里给出python3版本的。
mysql 5.6 新特性 innodb
内容包括: 1.MySQL的Innodb引擎配置 1.如何配置MySQL服务器的最大连接数量 2.如何配置innodb_open_files,table_open_cache,innodb_file_io_threads和innodb_buffer_pool_size,innodb_log_file_size
MySQL分析 innodb
使用py_innodb_page_info 查看分析各种log以及data file,深入研究mysql的存储引擎底层原理与实现。 mysql innodb undo redo
《mysql技术内幕-InnoDB存储引擎》中使用的页分析工具类py_innodb_page_info
使用py_innodb_page_info 查看分析各种log以及data file,深入研究mysql的存储引擎底层原理与实现
主要介绍了使用innodb_force_recovery解决MySQL崩溃无法重启问题,这只一个成功案例,并不是万能的解决方法,需要酌情考虑,需要的朋友可以参考下
如果你的服务器的CPU或者IO使用接受饱和,特别是偶尔出现峰值,这时候系统想在超载时能正常处理查询,那么强烈建议关注innodb_thread_concurrency
py_innodb_page.tar.gz
mysql 存储引擎 innodb 日志结构 非常好用,欢迎大家下载!
4> innodb_data_home_dir = /usr/local/mysql/var/存放innodb表引擎表空间的地方 5> innodb_data_file_path = ibdata1:10M:autoextend表空间的名字以8M增长,可以将其大小修改:例如50M 6> innodb_log_group_home_...
MySQL基于MGR的原生高可用解决方法,MySQL InnoDB Cluster【MIC】
mysql> show create table test_autoinc_lock\G *************************** 1. row *************************** Table: test_autoinc_lock Create Table: CREATE TABLE `test_autoinc_lock` ( `id` int(11) NOT...
MySQL Innodb 参数详解与优化实践
innodb_autoinc_lock_mode这个参数控制着在向有auto_increment 列的表插入数据时,相关锁的行为; 通过对它的设置可以达到性能与安全(主从的数据一致性)的平衡 【0】我们先对insert做一下分类 首先insert大致上可以...
浅析在线调整 innodb_buffer_pool_size 作者:zhou mysql版本:5.7 先介绍一下 buffer pool: 在innodb存储引擎中数据访问以page为单位,page也是innodb管理数据库的最小磁盘单位,每个page的默认大小为16KB(可以通过...