注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

phperwuhan的博客

记载一个phper的历程!phperwuhan.blog.163.com

 
 
 

日志

 
 

Mysql Innodb引擎优化(参数篇)  

2010-06-19 22:31:47|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

来源:http://www.xxlinux.com/linux/article/development/database/20100423/18214.html

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 statusG;中的

  ----------------------

  BUFFER POOL AND MEMORY

  ----------------------

  Total memory allocated 4668764894;

  去确认使用情况。

  第二个:

  innodb_additional_mem_pool:

  作用:用来存放Innodb的内部目录

  这个值不用分配太大,系统可以自动调。不用设置太高。通常比较大数据设置16M够用了,如果表比较多,可以适当的增大。如果这个值自动增加,会在error log有中显示的。

  分配原则:

  用show innodb statusG;去查看运行中的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=16M2.关于日值方面:

  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的参数一般分为:全局参数,具体引擎的参数。全局参数方面请参考linux">http://imysql.cn/2007_12_08_optimize_mysql_under_linux yejr的那个Mysql调优的PPT

  评论这张
 
阅读(365)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017