MySQL Study之--MySQL InnoDB存储引擎的参数详解及优化

淡淡的烟草味﹌ 2022-08-05 08:46 246阅读 0赞

## [MySQL Study之--MySQL InnoDB存储引擎的参数详解及优化][MySQL Study_--MySQL InnoDB] ##

**      转自：http://www.lvtao.net/database/mysql\_innodb.html 吕滔博客**

<table style="margin:0px; padding:0px; border-collapse:collapse; border-spacing:0px"> 
 <tbody> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_data_home_dir</td> 
 <td style="margin:0px; padding:0px">这是InnoDB表的目录共用设置。如果没有在&nbsp;my.cnf&nbsp;进行设置，InnoDB 将使用MySQL的datadir目录为缺省目录。如果设定一个空字串,可以innodb_data_file_path中设定绝对路径。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_data_file_path</td> 
 <td style="margin:0px; padding:0px">单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。 文件大小以 MB&nbsp; 单位指定。因此在文件大小指定后必有“M”。 InnoDB 也支持缩写“G”， 1G = 1024M。从 3.23.44&nbsp; 开始，在那些支持大文件的操作系统上可以设置数据文件大小大于 4 GB。而在另一些操作系统上数据文件必须小于 2 GB。数据文件大小总和至少要达到 10 MB。在 MySQL-3.23 中这个参数必须在&nbsp;my.cnf&nbsp;中明确指定。在 MySQL-4.0.2 以及更新版本中则不需如此，系统会默认在 MySQL&nbsp; 的datadir目录下创建一个 16 MB 自扩充(auto-extending)的数据文件ibdata1。你同样可以使用一个&nbsp; 原生磁盘分区(RAW raw disk partitions(raw devices)) 作为数据文件</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_mirrored_log_groups</td> 
 <td style="margin:0px; padding:0px">为了保护数据而设置的日志文件组的拷贝数目，默认设置为 1。在my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_group_home_dir</td> 
 <td style="margin:0px; padding:0px">InnoDB 日志文件的路径。必须与innodb_log_arch_dir设置相同值。 如果没有明确指定将默认在 MySQL 的datadir目录下建立两个 5 MB 大小的&nbsp;ib_logfile…&nbsp;文件。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_files_in_group</td> 
 <td style="margin:0px; padding:0px">日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_file_size</td> 
 <td style="margin:0px; padding:0px">日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。&nbsp; 日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB。在my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_buffer_size</td> 
 <td style="margin:0px; padding:0px">InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至&nbsp; 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。&nbsp; 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_flush_log_at_trx_commit</td> 
 <td style="margin:0px; padding:0px">通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。这个选项默认设置为 0。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_arch_dir</td> 
 <td style="margin:0px; padding:0px">The directory where fully written log files would be archived if we&nbsp; used log archiving. 这里设置的参数必须与innodb_log_group_home_dir相同。 从 4.0.6&nbsp; 开始，可以忽略这个参数。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_log_archive</td> 
 <td style="margin:0px; padding:0px">这个值通常设为 0。 既然从备份中恢复(recovery)适合于 MySQL 使用它自己的 log files，因而通常不再需要 archive InnoDB log files。这个选项默认设置为 0。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_buffer_pool_size</td> 
 <td style="margin:0px; padding:0px">InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘&nbsp; I/O。在一个专用的数据库服务器上可以将它设置为物理内存的 80 %。&nbsp; 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_additional_mem_pool_size</td> 
 <td style="margin:0px; padding:0px">InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL 的错误日志中。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_file_io_threads</td> 
 <td style="margin:0px; padding:0px">InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_lock_wait_timeout</td> 
 <td style="margin:0px; padding:0px">在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用LOCK TABLES命 令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than&nbsp; InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50&nbsp; 秒。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_flush_method</td> 
 <td style="margin:0px; padding:0px">这个参数仅仅与 Unix 相关。这个参数默认值为fdatasync。 另一个设置项为O_DSYNC。这仅仅影响日志文件的转储，在 Unix 下以fsync转储数据。InnoDB 版本从 3.23.40b 开始，在 Unix 下指定fdatasync 为使用fsync方式、指定O_DSYNC为使用O_SYNC方式。由于这在某些 Unix 环境下还有些问题所以在 ‘data’ versions 并没有被使用。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_force_recovery</td> 
 <td style="margin:0px; padding:0px">警告：此参数只能在你希望从一个被损坏的数据库中转储(dump)数据的紧急情况下使用！ 可能设置的值范围为 1 – 6。查看下面的章节&nbsp; ‘Forcing recovery’ 以了解这个参数的具体含义。参数设置大于 0 的值代表着 InnoDB 防止用户修改数据的安全度。从&nbsp; 3.23.44 开始，这个参数可用。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_fast_shutdown</td> 
 <td style="margin:0px; padding:0px">InnoDB 缺少在关闭之前清空插入缓冲。这个操作可能需要几分钟，在极端的情况下可以需要几个小时。如果这个参数据设置为 1&nbsp; ，InnoDB 将跳过这个过程而直接关闭。从 3.23.44 和 4.0.1 开始，此参数可用。从 3.23.50 开始，此参数的默认值为 1。</td> 
 </tr> 
 <tr> 
 <td style="margin:0px; padding:0px">innodb_thread_concurrency</td> 
 <td style="margin:0px; padding:0px">InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。此参数默认值为&nbsp; 8。如果计算机系统性能较低或innodb_monitor显示有很多线程等侍信号，应该将这个值设小一点。如果你的计算机系统有很我的处理器与磁盘系 统，则可以将这个值设高一点以充分利用你的系统资源。建议设值为处理器数目+ 磁盘数目。 从 3.23.44 和 4.0.1&nbsp; 开始，此参数可用。在&nbsp;my.cnf&nbsp;中以数字格式设置。</td> 
 </tr> 
 </tbody> 
</table>

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

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

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

**１.内存利用方面：**首先介绍一个Innodb最重要的参数：  
**innodb\_buffer\_pool\_size**  
这个参数和MyISAM的key\_buffer\_size有相似之处，但也是有差别的。这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。为Innodb加速优化首要参数。  
该参数分配内存的原则：这个参数默认分配只有8M，可以说是非常小的一个值。如果是一个专用ＤＢ服务器，那么他可以占到内存的70%-80%。这个参数不  
能动态更改，所以分配需多考虑。分配过大，会使Swap占用过多，致使Mysql的查询特慢。如果你的数据比较小，那么可分配是你的数据大小＋１０％左右  
做为这个参数的值。例如：数据大小为５０Ｍ,那么给这个值分配innodb\_buffer\_pool\_size＝６４Ｍ  
设置方法：  
**innodb\_buffer\_pool\_size=4G**  
这个参数分配值的使用情况可以根据**show innodb statusG;**中的  
**———————-  
BUFFER POOL AND MEMORY  
———————-  
Total memory allocated 4668764894;**去确认使用情况。  
第二个：  
**innodb\_additional\_mem\_pool：**  
作用：用来存放Innodb的内部目录  
这个值不用分配太大，系统可以自动调。不用设置太高。通常比较大数据设置16Ｍ够用了，如果表比较多，可以适当的增大。如果这个值自动增加，会在error log有中显示的。  
分配原则：  
用**show innodb statusG;**去查看运行中的ＤＢ是什么状态（参考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**  
２.关于日值方面：  
**innodb\_log\_file\_size**  
作用：指定日值的大小  
分配原则：几个日值成员大小加起来差不多和你的innodb\_buffer\_pool\_size相等。上限为每个日值上限大小为4G.一般控制在几个ＬＯＧ文件相加大小在２Ｇ以内为佳。具体情况还需要看你的事务大小，数据大小为依据。  
说明：这个值分配的大小和数据库的写入速度，事务大小，异常重启后的恢复有很大的关系。  
设置方法：  
**innodb\_log\_file\_size=256M  
innodb\_log\_files\_in\_group**作用：指定你有几个日值组。  
分配原则：　一般我们可以用２－３个日值组。默认为两个。  
设置方法：  
**innodb\_log\_files\_in\_group=3  
innodb\_log\_buffer\_size：**作用：事务在内存中的缓冲。  
分配原则：控制在2-8M.这个值不用太多的。他里面的内存一般一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。在Ｏｒａｃｌｅ等数据库了解这个，一般最大指定为３Ｍ比较合适。  
参考：Innodb\_os\_log\_written(show global status 可以拿到)  
如果这个值增长过快，可以适当的增加innodb\_log\_buffer\_size  
另外如果你需要处理大理的ＴＥＸＴ，或是ＢＬＯＢ字段，可以考虑增加这个参数的值。  
设置方法：  
**innodb\_log\_buffer\_size=3M**

**innodb\_flush\_logs\_at\_trx\_commit**  
作用：控制事务的提交方式  
分配原则：这个参数只有３个值，０，１，２请确认一下自已能接受的级别。默认为１，主库请不要更改了。  
性能更高的可以设置为０或是２，但会丢失一秒钟的事务。  
**说明：**  
这个参数的设置对Innodb的性能有很大的影响，所以在这里给多说明一下。  
当这个值为1时：innodb 的事务LOG在每次提交后写入日值文件，并对日值做刷新到磁盘。这个可以做到不丢任何一个事务。  
当这个值为2时：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新,在对日志文件的刷新在值为2的情况也每秒发生一次。但需要注意的  
是，由于进程调用方面的问题，并不能保证每秒１００％的发生。从而在性能上是最快的。但操作系统崩溃或掉电才会删除最后一秒的事务。  
当这个值为0时：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。mysqld进程的崩溃会删除崩溃前最后一秒的事务。

从以上分析，当这个值不为１时，可以取得较好的性能，但遇到异常会有损失，所以需要根据自已的情况去衡量。  
设置方法：  
**innodb\_flush\_logs\_at\_trx\_commit=1**

3.　文件IO分配，空间占用方面  
**innodb\_file\_per\_table**  
作用：使每个Innodb的表，有自已独立的表空间。如删除文件后可以回收那部分空间。  
分配原则：只有使用不使用。但ＤＢ还需要有一个公共的表空间。  
设置方法：  
**innodb\_file\_per\_table=1**

**innodb\_file\_io\_threads**  
作用：文件读写ＩＯ数，这个参数只在Windows上起作用。在LINUX上只会等于４  
设置方法：  
**innodb\_file\_io\_threads=4**

**innodb\_open\_files**  
作用：限制Innodb能打开的表的数据。  
分配原则：如果库里的表特别多的情况，请增加这个。这个值默认是３００。  
设置方法：  
**innodb\_open\_files=800**请适当的增加table\_cache  
**4. 其它相关参数**  
这里说明一个比较重要的参数：  
**innodb\_flush\_method**  
作用：Innodb和系统打交道的一个ＩＯ模型  
分配原则：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的内存分配过大，致使Ｓｗａｐ占用严重时，可以适当的减小调整这个值，使达到Ｓｗａｐ空间释放出来。建义：这个值最大在９０％，最小在１５％。太大，缓存中每次更新需要致换数据页太多，太小，放的数据页太小，更新操作太慢。  
设置方法：  
innodb\_max\_dirty\_pages\_pct＝90  
动态更改需要有Super权限：  
set global innodb\_max\_dirty\_pages\_pct=50;

**总结：**  
这里只算是列出了Innodb部分的重要参数，不能认为是对MySQL的整体调优。MySQL的参数一般分为：全局参数，具体引擎的参数。

[MySQL Study_--MySQL InnoDB]: http://www.lvtao.net/database/mysql_innodb.html