Mysql Innodb 引擎优化

标签: Database | 发表时间:2011-04-17 10:15 | 作者:farmerluo 彦强
分享到:
出处:http://hi.baidu.com/farmerluo

http://www.mysqlsupport.cn/mysql-innodb-optimize-2/

参数篇

作/译者:吴炳锡,来源:http://imysql.cn/ & http://www.mysqlsupport.cn 转载请注明作/译者和出处,并且不能用于商业用途,违者必究。

介绍: 
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供 一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的 空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。 Innodb 的创始人:Heikki Tuuri Heikki Tuuri在Innodb的Bug社区里也是很活跃的,如果遇到Bug也可以直接提到社区,得到作者的解答。

为什么要学习Innodb的调优:  
目前来说:InnoDB是为Mysql处理巨大数据量时的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。在数据量大 的网站或是应用中Innodb是倍受青睐的。  另一方面,在数据库的复制操作中Innodb也是能保证master和slave数据一致有一定的作用。

参数调优内容:
1. 内存利用方面
2. 日值控制方面
3.文件IO分配,空间占用方面
4. 其它相关参数

1.内存利用方面:
首先介绍一个Innodb最重要的参数:innodb_buffer_pool_size  这个参数和MyISAM的key_buffer_size有 相似之处,但也是有差别的。这个参数主要缓存innodb表的索引,数据,插入数据时的缓冲。为Innodb加速优化首要参数。  该参数分配内存的原 则:这个参数默认分配只有8M,可以说是非常小的一个值。如果是一个专用DB服务器,那么他可以占到内存的70%-80%。这个参数不能动态更改,所以分 配需多考虑。分配过大,会使Swap占用过多,致使Mysql的查询特慢。如果你的数据比较小,那么可分配是你的数据大小+10%左右做为这个参数的值。
例如:数据大小为50M,那么给这个值分配innodb_buffer_pool_size=64M
设置方法:innodb_buffer_pool_size=4G这个参数分配值的使用情况可以根据show innodb status\G;中的
———————-BUFFER POOL AND MEMORY———————-
Total memory allocated 4668764894;

去确认使用情况。

第二个:innodb_additional_mem_pool
作用:用来存放Innodb的内部目录这个值不用分配太大,系统可以自动调。不用设置太高。
通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在error log有中显示的。

分配原则:用show innodb status\G;去查看运行中的DB是什么状态
(参考BUFFER POOL AND MEMORY段中),然后可以调整到适当的值。

———————-BUFFER POOL AND MEMORY———————-
Total memory allocated 4668764894; in additional pool allocated 16777216
参考:in additional pool allocated 16777216根据你的参数情况,可以适当的调整。

设置方法:innodb_additional_mem_pool=16M

2.关于日值方面:innodb_log_file_size作用:
指定日值的大小分配原则:几个日值成员大小加起来差不多和你的innodb_buffer_pool_size相等。上限为每个日值上限大小为4G.一般 控制在几个LOG文件相加大小在2G以内为佳。具体情况还需要看你的事务大小,数据大小为依据。说明:这个值分配的大小和数据库的写入速度,事务大小,异 常重启后的恢复有很大的关系。设置方法:innodb_log_file_size=256M

innodb_log_files_in_group作用:
指定你有几个日值组。
分配原则: 一般我们可以用2-3个日值组。默认为两个。
设置方法:innodb_log_files_in_group=3

innodb_log_buffer_size:

作用:事务在内存中的缓冲。分配原则:控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Oracle等数据库了解这个,一般最大指定为3M比较合适。
参考: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模型分配原则:Windows不 用设置。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;

总结:  

这里只算是列出了Innodb部分的重要参数,不能认为是对Mysql的整体调优。Mysql的参数一般分为:全局参数,具体引擎的参数。全局参数方面请参考http://imysql.cn/2007_12_08_optimize_mysql_under_linux yejr的那个Mysql调优的PPT。
阅读全文
类别:Database 查看评论

相关 [mysql innodb 引擎] 推荐:

Mysql Innodb 引擎优化

- 彦强 - 阿辉的空间
作/译者:吴炳锡,来源:http://imysql.cn/ & http://www.mysqlsupport.cn 转载请注明作/译者和出处,并且不能用于商业用途,违者必究. InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎. InnoDB锁定在行级并且也在SELECT语句提供 一个Oracle风格一致的非锁定读.

MySQL 5.1安装InnoDB引擎

- - Gsion's Blog
安装 innodb 引擎(mysql5.1默认不安装). 可以在编译安装时,在configrue的时候,加上--with-plugins=innobase这个参数. 如果之前已经安装过,也可补装innodb引擎. 首先确定,在mysql的'plugin_dir'下有ha_innodb_plugin.so和ha_innodb.so两个文件.

MySQL Innodb 存储引擎参数优化

- jinbiaozhao - 服务器运维与网站架构|Linux运维|互联网研究
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎. InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读. 这些特色增加了多用户部署和性能. 没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间.

MySQL 預設儲存引擎: InnoDB 介紹

- - 小惡魔 - 電腦技術 - 工作筆記 - AppleBOY
MySQL 是一套眾所皆知的 Database System,今天來簡介 InnoDB 儲存引擎,在 MySQL 5.5.5 之前預設的儲存引擎是 MyISAM,但是為什麼在 5.5 之後官方要將預設儲存引擎換成 InnoDB 呢. 開始升級伺服器,一起體驗 InnoDB 的功能,如果已經上線很久的網站,作者不建議轉換,因為可能會遇到很多雷.

[MySQL] 浅谈InnoDB存储引擎

- - CSDN博客数据库推荐文章
InnoDB是事务安全的存储引擎,设计上借鉴了很多Oracle的架构思想,一般而言,在OLTP应用中,InnoDB应该作为核心应用表的首先存储引擎. InnoDB是由第三方的Innobase Oy公司开发,现已被Oracle收购,创始人是Heikki Tuuri,芬兰赫尔辛基人,和著名的Linux创始人Linus是校友.

MySQL存储引擎--MyISAM与InnoDB区别

- - CSDN博客互联网推荐文章
MyISAM 和InnoDB 讲解   InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定. 基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持. MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能.

Mysql InnoDB锁

- - 数据库 - ITeye博客
抄自:http://www.cnblogs.com/qq78292959/archive/2013/01/30/2882745.html. Mysql常用存储引擎的锁机制. MyISAM和MEMORY采用表级锁(table-level locking). BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁.

MySQL存储引擎MyISAM与InnoDB的主要区别对比

- timo - 服务器运维与网站架构|Linux运维|互联网研究
本文整理了Mysql 两大常用的存储引擎MyISAM,InnoDB的六大常见区别,来源于Mysql手册以及互联网的资料. InnoDB与Myisam的六大区别. MyISAM InnoDB 构 成上的区别: 每个MyISAM在磁盘上存储成三个文件. 第一个 文件的名字以表的名字开始,扩展名指出文件类型.

mysql技术内幕-innodb存储引擎读书笔记(上)

- - MySQLOPS 数据库与运维自动化技术分享
第一章、mysql体系结构和存储引擎. 1.1、数据库和实例的区别. 数据库:物理操作系统或其他形式文件类型的集合. 在mysql下数据库文件可以是frm,myd,myi,ibd结尾的文件. 数据库实例:由数据库后台进程/线程以及一个共享内存区组成. mysql数据库是但进程多线程的程序. 1.2、mysql的体系结构.

mysql技术内幕-innodb存储引擎读书笔记(中)

- - MySQLOPS 数据库与运维自动化技术分享
4.1、innodb存储引擎表类型. innodb表类似oracle的IOT表(索引聚集表-indexorganized table),在innodb表中每张表都会有一个主键,如果在创建表时没有显示的定义主键则innodb如按照如下方式选择或者创建主键. 首先表中是否有唯一非空索引(unique not null),如果有则该列即为主键.