mysql innodb优化

默认情况下,innodb的参数设置的非常小,在生产环境中远远不够用。比如最重要的两个参数:

innodb_buffer_pool_size 默认是8M

innodb_flush_logs_at_trx_commit 默认设置的是1 也就是同步刷新log(可以这么理解)

innodb_buffer_pool_size: 这是InnoDB最重要的设置,对InnoDB性能有决定性的影响。默认的

设置只有8M,所以默认的数据库设置下面InnoDB性能很差。在只有 InnoDB存储引擎的数据库服

务器上面,可以设置60-80%的内存。更精确一点,在内存容量允许的情况下面设置比InnoDB

tablespaces大10%的内存大小。

innodb_data_file_path:指定表数据和索引存储的空间,可以是一个或者 多个文件。最后一个数

据文件必须是自动扩充的,也只有最后一个文件允许自动扩充。这样,当空间用完后,自动扩充数

据文件就会自动增长(以8MB为单位)以 容纳额外的数据。例如:

innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件

放在不同的磁盘上。数据首先放在ibdata1 中,当达到900M以后,数据就放在ibdata2中。一旦

达到50MB,ibdata2将以8MB为单位自动增长。如果磁盘满了,需要在另外的磁盘上面 增加一个

数据文件。

innodb_data_home_dir:放置表空间数据的目录,默认在mysql的数据目录,设置到和MySQL

安装文件不同的分区可以提高性能。

innodb_log_file_size:该参数决定了recovery speed。太大的话recovery就会比较慢,太小了

影响查询性能,一般取256M可以兼顾性能和recovery的速度

innodb_log_buffer_size:磁盘速度是很慢的,直接将log写道磁盘会影响InnoDB的性能,该参数

设定了log buffer的大小,一般4M。如果有大的blob操作,可以适当增大。

innodb_flush_logs_at_trx_commit=2: 该参数设定了事务提交时内存中log信息的处理。

1) =1时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新。Truly

ACID。速度慢。

2) =2时,在每个事务提交时,日志缓冲被写到文件,但不对日志文件做到磁盘操作的刷新。只有

操作系统崩溃或掉电才会删除最后一秒的事务,不然不会丢失事务。

3) =0时, 日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新。任何

mysqld进程的崩溃会删除崩溃前最后一秒的事务

innodb_file_per_table:可以存储每个InnoDB表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED: 如果应用程序可以运行在READ-COMMITED隔离级别,

做此设定会有一定的性能提升。

innodb_flush_method: 设置InnoDB同步IO的方式:

1) Default – 使用fsync()。

2) O_SYNC 以sync模式打开文件,通常比较慢。

3) O_DIRECT,在Linux上使用Direct IO。可以显著提高速度,特别是在RAID系统上。避免额外

的数据复制和double buffering(mysql buffering 和OS buffering)。

innodb_thread_concurrency: InnoDB kernel最大的线程数。

1) 最少设置为(num_disks+num_cpus)*2。

2) 可以通过设置成1000来禁止这个限制

=========================================

介绍:

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:

开源时代 2010年1月刊 – 59 – 投稿邮箱:rmzhou@staff.chinaunix.net

ChinaUnix.net —–全球最大的Linux/Unix应用与开发者中文社区

作用:用来存放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

开源时代 2010年1月刊 – 60 – 投稿邮箱:rmzhou@staff.chinaunix.net

ChinaUnix.net —–全球最大的Linux/Unix应用与开发者中文社区

作用:控制事务的提交方式

分配原则:这个参数只有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的整体调优。My

sql的参数一般分为:全局参数,具体引擎的参数。全局参数方面请参考

http://imysql.cn/2007_12_08_optimize_mysql_under_linux yejr的那个Mysql调优的PPT。

========================================

通过这次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也

不宜太大。

本文固定链接: http://www.ccsbbs.com.cn/archives/5915.html | 极限手指

该日志由 极限手指 于2013年03月05日发表在 数据库 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: mysql innodb优化 | 极限手指
【上一篇】
【下一篇】

mysql innodb优化:等您坐沙发呢!

发表评论

您必须 [ 登录 ] 才能发表留言!