`
robbin
  • 浏览: 4822066 次
  • 性别: Icon_minigender_1
  • 来自: 上海
博客专栏
377a9ecd-1ea1-34ac-9530-9daa53bb2a7b
robbin谈管理
浏览量:137102
社区版块
存档分类
最新评论

MySQL InnoDB性能调整的一点实践

阅读更多
因为JavaEye网站的数据库服务器搬家的时候被托管商的工作人员狠狠摔了一下,所以硬盘整个挂掉了,我重新安装数据库服务器的时候,顺手下载了Percona patch过的MySQL5.0版本,使用MySQL自带的heavy innodb配置文件改了改,作为my.cnf启动运行。数据库服务器的物理内存有6GB,其中有4GB可以被MySQL使用,my.cnf相关配置参数如下:

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_flush_method=fdatasync 默认设置


buffer pool越大越好,官方推荐使用物理内存的50%-80%;log_file_size也是越大越好,官方推荐log size加起来要达到buffer pool的25%-100%。使用memlock可以避免MySQL内存进入swap,这些都是默认的推荐配置了,没有什么可以质疑的地方。但是数据库服务器启动以后,运行不太正常。表现出来的现象是:

1、操作系统内存Disk Cache使用了2.7GB
2、操作系统swap空间使用了200MB左右,一直不停进行swap in/swap out
3、CPU的IO Wait偏高,平均在10%以上

这个现象看起来非常怪异和矛盾。IO Wait偏高显然是因为频繁的使用swap进行内存换页引起的,但问题是物理内存非常空闲,操作系统明明有2.7GB空闲物理内存做Disk Cache,怎么不吐出来一点,非要去用swap呢?

想来想去只有一种可能性,就是MySQL存在非常巨大的,频繁的文件读写操作,迫使操作系统不得不分配了2.7GB的Disk Cache,从而造成了物理内存的不足,被迫使用swap。而可能造成巨大文件读写操作的就是buffer pool的flush和log file的flush操作了。因此配置文件做如下修改:

memlock
innodb_buffer_pool_size = 2G
innodb_log_file_size = 64M
innodb_log_files_in_group = 2
innodb_flush_method=O_DIRECT


减少log file size和数量,使用O_DIRECT。重启以后,数据库服务器恢复正常。操作系统Disk Cache下降到900MB,Swap使用了200多MB,但是不再进行swap in/swap out操作,CPU的IO Wait下降到2-3%。

通过这次MySQL InnoDB的调优经历,发现一些和MySQL官方推荐配置不符合的疑惑之处,值得思考和探索:

1、innodb_flush_method究竟应不应该使用O_DIRECT?

所有MySQL调优的建议都说,如果硬件没有预读功能,那么使用O_DIRECT将极大降低InnoDB的性能,因为O_DIRECT跳过了操作系统的文件系统Disk Cache,让MySQL直接读写磁盘了。

但是在我的实践中来看,如果不使用O_DIRECT,操作系统被迫开辟大量的Disk Cache用于innodb的读写缓存,不但没有提高读写性能,反而造成读写性能急剧下降。而且buffer pool的数据缓存和操作系统Disk Cache缓存造成了Double buffer的浪费,显然从我这个实践来看,浪费得非常厉害。

说O_DIRECT造成MySQL直接读写磁盘造成得性能下降问题,我觉得完全是杞人忧天。因为从JavaEye的数据库监测来看,Innodb的buffer pool命中率非常高,有98%以上,真正的磁盘操作是微乎其微的。为了1%的磁盘操作能够得到Disk Cache,而浪费了98%的double buffer内存空间,无论从性能上看,还是从内存资源的消耗来看,都是非常不明智的。

2、innodb_log_file_size究竟应该大一点,还是小一点?

所有MySQL调优建议都说,innodb_log_file_size要越大越好,避免无谓的buffer pool的flush操作。

但是在我的实践中来看,innodb_log_file_size开得太大,会明显增加innodb的log写入操作,而且会造成操作系统需要更多的Disk Cache开销。

因此从我的经验来看,innodb_flush_method=O_DIRECT是必须的,而innodb_log_file_size也不宜太大。
分享到:
评论
9 楼 lection.yu 2012-09-05  
JavaEye这种高并发的论坛,请问各位为何使用InnoDB,而不是MYISAM呢?对于两个引擎的选择如何考虑?
8 楼 oliver_peng 2010-08-05  
使用memlock要注意保留足够的Free memory, 否则Linux内核在没有足够内存给Cache的时候又无法Swap内存会直接将MySQL Kill掉。

7 楼 linkerlin 2010-05-19  
我们也遇到这个问题了。
分析的很透彻啊!
6 楼 myreligion 2010-05-07  
willko 写道
innodb_log_file_size开得太大,会明显增加innodb的log写入操作

====
这个比较没明白意思,持久log的时机取决于innodb_flush_log_at_trx_commit参数,和log大小无关。


对啊,log的大小太大了好像意义不大,文档上说主要和innodb_flush_log_at_trx_commit有关。如果每天备份,又不怕丢数据库,甚至可以把log关掉,也能提高不少性能哪。

对于o_direct,好像是Peter的文档上说FreeBSD适合使用,可以提高很多性能,其他操作系统不适合。

如果内容给4G,innodb_pool_size应该是4G吧?怎么配成了2G?
5 楼 kaki 2010-04-30  
都是相对的,关键要看各种操作的比例。
4 楼 oliver_peng 2009-09-10  
请问有没有统计过访问JavaEye数据库的请求中Select, Insert, Update 和 Delete所占的比例?
3 楼 willko 2009-09-03  
innodb_log_file_size开得太大,会明显增加innodb的log写入操作

====
这个比较没明白意思,持久log的时机取决于innodb_flush_log_at_trx_commit参数,和log大小无关。
2 楼 fire9 2009-09-03  
innodb_log_file_size 大当然有好处,但是一旦宕机,恢复的时间就比较长了。
1 楼 jkfzero 2009-09-03  
广告都发到这里来了,看来猎头的确是JavaEye的主要收入之一呀。

相关推荐

    MySQL 和 InnoDB 性能

    ### MySQL与InnoDB性能分析 #### MySQL架构概览 MySQL是一种关系型数据库管理系统,由瑞典MySQL AB公司开发,目前由Oracle公司维护。MySQL的核心组成部分包括服务器端、存储引擎以及一系列支持服务。 - **服务器...

    MySQL Innodb 参数详解与优化实践

    ### MySQL Innodb 参数详解与优化实践 #### 一、引言 MySQL作为一款广泛使用的开源关系型数据库管理系统,其InnoDB存储引擎因其高可靠性和事务处理能力而备受青睐。为了充分发挥InnoDB的优势并针对特定场景进行性能...

    MySQL技术内幕 InnoDB存储引擎.pptx

    此外,本书还对InnoDB的性能优化进行了详细的分析,提出了很多针对InnoDB性能优化的最佳实践,例如合理设计表结构、选择合适的索引、调整事务隔离级别等。 本书还对MySQL 6版本中InnoDB的改进进行了全面的介绍。与...

    MySQL性能分析与优化调整

    本文将深入探讨MySQL性能分析的关键技术、优化策略以及如何进行调整。 一、性能分析 1. **慢查询日志**:MySQL提供慢查询日志功能,记录执行时间超过设定阈值的SQL语句,这是识别性能瓶颈的起点。 2. **EXPLAIN...

    mysql innodb引擎 知识点总结

    以上只是InnoDB引擎的一些核心特性,实际应用中还有更多高级配置和调优技巧,如索引优化、缓存管理、性能监控等,都需要根据具体场景进行深入理解和实践。在设计和管理大型数据库系统时,了解并掌握这些知识点至关...

    mysql高性能---mysql数据库的性能调优

    4. **内存管理**:调整MySQL的缓冲池大小,如InnoDB Buffer Pool,可以减少磁盘I/O,提高数据读取速度。同时,也要考虑服务器的内存资源,避免过度配置。 5. **并发控制**:通过调整线程池大小、事务隔离级别等参数...

    mysql 高性能优化

    在《MySQL高性能优化》这本书中,作者深入浅出地探讨了如何最大限度地提升MySQL数据库的运行效率,确保系统的高可用性和高性能。 一、索引优化 索引是MySQL数据库性能的关键因素。正确地创建和使用索引可以显著提高...

    MySQL性能调优与架构设

    以上内容涵盖了MySQL性能调优与架构设计的核心要点,实际应用中还需要结合具体业务场景进行深入研究和实践。通过这些知识,你可以有效地提升MySQL数据库的运行效率,保障系统的稳定性和高性能。

    innodb插件mysql

    总结,InnoDB Plugin为MySQL带来了诸多性能优化和新特性,使得MySQL能够更好地应对大规模、高并发的业务场景。然而,正确地安装、配置和使用InnoDB Plugin至关重要,这需要对MySQL的内部机制有深入的理解,并且要...

    MySQL性能调优最佳实践

    本篇文章将探讨一些MySQL性能优化的最佳实践,基于提供的标题和描述,虽然具体细节未直接给出,但我们可以从常见的调优策略进行深入讨论。 1. **索引优化**:索引是提升查询速度的关键。合理地创建主键、唯一键和非...

    MySQL性能诊断与实践.pptx

    MySQL性能诊断与实践是数据库管理中的重要环节,它关乎到系统的稳定性和效率。在面对MySQL性能问题时,我们需要掌握一系列的诊断方法和技术。本讲座主要围绕如何理解和解决MySQL的性能问题,通过介绍诊断方法、使用...

    高性能MySQL

    《高性能MySQL》是一本专为MySQL数据库管理员、开发人员和系统架构师打造的权威指南,其第二版深入探讨了如何优化MySQL的性能,确保数据库系统的高效运行。这本书涵盖了索引策略、查询优化、系统监控以及配置优化等...

    高性能的MySQL中文版

    书中介绍了一系列工具,如MySQL Enterprise Monitor、Percona Toolkit等,以及如何分析和调整性能参数。 8. **分区与并行查询**:大型数据库往往需要分区来提高查询速度。书中会讲述范围分区、哈希分区等方法,并...

    MySQL优化之InnoDB优化

    MySQL中的InnoDB存储引擎在处理大量数据时表现出卓越的性能,尤其在CPU效率方面,它在基于磁盘的关系型数据库引擎中无人能敌。然而,即使InnoDB具有如此高效的性能,优化仍然是必不可少的,因为任何数据库系统都有其...

    MySQL-InnoDB

    MySQL-InnoDB是一个强大的关系型数据库管理系统,其内核支持事务处理、行级锁定以及高度的数据一致性和可用性。...理解并实施这些最佳实践,可以确保MySQL-InnoDB在现代数据中心环境中展现出最佳的性能和稳定性。

    高性能Mysql mysql书籍

    6. **内存管理与缓存**:探讨了MySQL如何管理和使用内存,包括查询缓存、InnoDB缓冲池等,以及如何调整相关参数以提升性能。 7. **存储引擎特性**:深入讲解了InnoDB、MyISAM、Memory、NDB Cluster等存储引擎的工作...

    MySQL性能优化中文手册

    例如,调整innodb_buffer_pool_size可以改善InnoDB存储引擎的缓存性能,而max_connections则影响并发连接的数量。监控和调整这些参数,可以根据系统的实际负载和资源来平衡性能与资源消耗。 最后,定期分析和重建...

Global site tag (gtag.js) - Google Analytics