​innodb存储引擎优化

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介:

innodb存储引擎优化
       目前来说:InnoDB是为Mysql处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大 的网站或是应用中Innodb是倍受青睐的。另一方面,在数据库的复制操作中Innodb也是能保证master和slave数据一致有一定的作用。
设置内容:
1. 内存利用方面
    参数:innodb_buffer_pool_size
    默认分配只有8M。如果是一个专用DB服务器,那么他可以占到内存的70%-80%。这个参数不能动态更改,所以分配需 多考虑。分配过大,会使Swap占用过多,致使Mysql的查询特慢。如果你的数据比较小,那么可分配是你的数据大小+10%左右做为这个参数的值。show innodb status,执行此命令,去确认大小。
    例如:数据大小为50M,那么给这个值分配innodb_buffer_pool_size=64M
    参数: innodb_additional_mem_pool 
   系统可以自动调,不用设置太高。
2. 日值控制方面
    参数:innodb_log_file_size
    
这个值分配的大小和数据库的写入速度,事务大小,异常重启后的恢复有很大的关系。设置方法:innodb_log_file_size=256M
    参数:innodb_log_files_in_group
指定你有几个日值组。
分配原则: 一般我们可以用2-3个日值组。默认为两个。
设置方法:innodb_log_files_in_group=3

参数:innodb_log_buffer_size:

作用:事务在内存中的缓冲。分配原则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。
参考:Innodb_os_log_written(show global status 可以拿到)如果这个值增长过快,可以适当的增加innodb_log_buffer_size另外如果你需要处理大理的TEXT,或是BLOB字段,可以考虑增加这个参数的值。

设置方法:innodb_log_buffer_size=3M

参数:innodb_flush_logs_at_trx_commit
作用:控制事务的提交方式分配原则:这个参 数只有3个值,0,1,2请确认一下自已能接受的级别。默认为1,主库请不要更改了。性能更高的可以设置为0或是2,但会丢失一秒钟的事务。说明:这个参 数的设置对Innodb的性能有很大的影响,所以在这里给多说明一下。当这个值为1时:innodb 的事务LOG在每次提交后写入日值文件,并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。当这个值为2时:在每个提交,日志缓冲被写到文件,但不对 日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的是,由于进程调用方面的问题,并不能保证每秒100%的发 生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。当这个值为0时:日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操 作的刷新,但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。
从以上分析,当这个值不为1时,可以取得较好的性能,但遇到异常会有损失,所以需要根据自已的情况去衡量。
设置方法:innodb_flush_logs_at_trx_commit=1

3.文件IO分配,空间占用方面
参数:innodb_file_per_table
作用:使每个Innodb的表,有自已独立的表空间。如删除文件后可以回收那部分空间。分配原则:只有使用不使用。但DB还需要有一个公共的表空间。设置方法:innodb_file_per_table=1
innodb_file_io_threads作用:文件读写IO数,这个参数只在Windows上起作用。在LINUX上只会等于4设置方法:innodb_file_io_threads=4
innodb_open_files作用:限制Innodb能打开的表的数据。分配原则:如果库里的表特别多的情况,请增加这个。这个值默认是300。设置方法:innodb_open_files=800 请适当的增加table_cache 
4. 其它相关参数
参数:innodb_flush_method
作用:Innodb和系统打交道的一个IO模型分配原则,Unix可以设置:fsync() or O_SYNC/O_DSYNC如果系统可以禁止系统的Cache那就把他禁了。Linux可以选择:O_DIRECT 直接写入磁盘,禁止系统Cache了
设置方法:innodb_flush_method=O_DIRECT
参数:innodb_max_dirty_pages_pct 
控制Innodb的脏页在缓冲中在那个百分比之下,值在范围1-100,默认为90.这个参数的另一个用处:当Innodb的内存分配过大,致使 Swap占用严重时,可以适当的减小调整这个值,使达到Swap空间释放出来。建义:这个值最大在90%,最小在15%。太大,缓存中每次更新需要致换数据页太多,太小,放的数据页太小,更新操作太慢。设置方 法:innodb_max_dirty_pages_pct=90动态更改需要有Super权限:set global innodb_max_dirty_pages_pct=50; 



本文转自birdinroom 51CTO博客,原文链接:http://blog.51cto.com/birdinroom/1602603,如需转载请自行联系原作者
相关实践学习
通过日志服务实现云资源OSS的安全审计
本实验介绍如何通过日志服务实现云资源OSS的安全审计。
相关文章
|
存储 SQL 关系型数据库
Mysql系列-4.Mysql存储引擎-InnoDB(下)
Mysql系列-4.Mysql存储引擎-InnoDB
146 0
|
存储 缓存 关系型数据库
MySQL - 存储引擎MyISAM和Innodb
MySQL - 存储引擎MyISAM和Innodb
144 0
|
存储 SQL 关系型数据库
MySQL存储引擎之MyISAM和InnoDB
MySQL存储引擎之MyISAM和InnoDB
121 0
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1677 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
10月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
228 7
|
11月前
|
存储 关系型数据库 MySQL
数据库引擎之InnoDB存储引擎
【10月更文挑战第29天】InnoDB存储引擎以其强大的事务处理能力、高效的索引结构、灵活的锁机制和良好的性能优化特性,成为了MySQL中最受欢迎的存储引擎之一。在实际应用中,根据具体的业务需求和性能要求,合理地使用和优化InnoDB存储引擎,可以有效地提高数据库系统的性能和可靠性。
157 5
|
11月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL的InnoDB存储引擎
InnoDB是MySQL的默认存储引擎,广泛应用于互联网公司。它支持事务、行级锁、外键和高效处理大量数据。InnoDB的主要特性包括解决不可重复读和幻读问题、高并发度、B+树索引等。其存储结构分为逻辑和物理两部分,内存结构类似Oracle的SGA和PGA,线程结构包括主线程、I/O线程和其他辅助线程。
226 0
【赵渝强老师】MySQL的InnoDB存储引擎
|
存储 SQL 缓存
InnoDB 存储引擎以及三种日志
InnoDB 存储引擎以及三种日志
112 0
|
存储 算法 关系型数据库
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
【MySQL技术内幕】5.7- InnoDB存储引擎中的哈希算法
130 1
|
存储 关系型数据库 MySQL
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
MySQL数据库——存储引擎(2)-存储引擎特点(InnoDB、MyISAM、Memory)、存储引擎选择
218 1