【MySQL】我必须得告诉大家的MySQL优化原理3（下）INNODB配置

INNODB：使用最广的存储引擎

innodb-buffer-pool-size

如果大部分是InnoDB表，那么InnoDB缓冲池或许比其他任何东西都更需要内存，InnoDB缓冲池缓冲的数据：索引、行数据、自适应哈希索引、插入缓冲、锁以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就可以合并多个写入操作，然后一起顺序写入，提升性能。总之，InnoDB严重依赖缓冲池，必须为其分配足够的内存。

当然，如果数据量不大且不会快速增长，就没有必要为缓冲池分配过多的内存，把缓冲池配置得比需要缓存的表和索引还要大很多，实际上也没有什么意义。很大的缓冲池也会带来一些挑战，例如，预热和关闭都会花费很长的时间。如果有很多脏页在缓冲池里，InnoDB关闭时可能会花很长时间来把脏页写回数据文件。虽然可以快速关闭，但是在启动时需要做更多的恢复工作，也就是说我们无法同时加速关闭和重启两个操作。当有一个很大的缓冲池，重启服务需要花费很长时间（几小时或者几天）来预热，尤其是磁盘很慢的时候，如果想加快预热时间，可以在重启后立刻进行全表扫描或者索引扫描，把索引载入缓冲池。

 

可以看到示例的配置文件中把这个值配置为12G，这不是一个标准配置，需要根据具体的硬件来估算。那如何估算？前面的小节，我们说到，MySQL中最重要的缓存有5种，可以简单的使用下面的公式计算：

InnoDB缓冲池 = 服务器总内存 - OS预留 - 服务器上的其他应用占用内存 - MySQL自身需要的内存 - InnoDB日志文件占用内存 - 其它内存(MyISAM键缓存、查询缓存等)

具体来看，至少需要为OS保留1~2G内存，如果机器内存大的话可以预留多一些，建议2GB和总内存的5%为基准，以较大者为准，如果机器上还运行着一些内存密集型任务，比如，备份任务，那么可以为OS再预留多一些内存。不要为OS缓存增加任何内存，因为OS通常会利用所有剩下的内存来做文件缓存。

一般来说，运行MySQL的服务器很少会运行其他应用程序，但如果有的话，请为这些应用程序预留足够多的内存。

MySQL自身运行还需要一些内存，但通常都不会太大。需要考虑MySQL每个连接需要的内存，虽然每个连接需要的内存都很少，但它还要求一个基本量的内存来执行任何给定的查询，而且查询过程中还需要为排序、GROUP BY等操作分配临时表内存，因此需要为高峰期执行大量的查询预留足够的内存。这个内存有多大？只能在运行过程中监控。

如果大部分表都是InnoDB，MyISAM键缓存配置一个很小值足矣，查询缓存也建议关闭。

 

innodb-log-file-size && innodb-log-files-in-group

如果对InnoDB数据表有大量的写入操作，那么选择合适的innodb-log-file-size值对提升MySQL性能很重要。InnoDB使用日志来减少提交事务时的开销。日志中记录了事务，就无须在每个事务提交时把缓冲池的脏块(缓存中与磁盘上数据不一致的页)刷新到磁盘。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机I/O。一旦日志安全的写入磁盘，事务就持久化了，即使变更还没有写到数据文件，在一些意外情况发生时(比如断电)，InnoDB可以重放日志并且恢复已经提交的事务。

InnoDB使用一个后台线程智能地刷新这些变更到数据文件。实际上，事务日志把数据文件的随机I/O转换为几乎顺序地日志文件和数据文件I/O，让刷新操作在后台可以更快的完成，并且缓存I/O压力。

整体的日志文件大小受控于innodb-log-file-size和innodb-log-files-in-group两个参数，这对写性能非常重要。日志文件的总大小是每个文件的大小之和。默认情况下，只有两个5M的文件，总共10M，对高性能工作来说太小了，至少需要几百M或者上G的日志文件。这里要注意innodb-log-files-in-group这个参数，它控制日志文件的数量，log group表示一个重做日志的文件集合，没有参数也没有必要配置有多少个日志组。

修改日志文件的大小，需要完全关闭MySQL，然后将旧的日志文件迁移到其他地方，重新配置参数，然后重启。重启时需要将旧的日志迁移回来，然后等待MySQL恢复数据后，再删除旧的日志文件，请一定要查看错误日志，确认MySQL重启成功后再删除旧的日志文件。

 

想要确定理想的日志文件大小，需要权衡正常数据变更的开销，以及崩溃时恢复需要的时间。

如果日志太小，InnoDB将必须要做更多的检查点，导致更多的日志写，在极个别情况下，写语句还会被拖累，在日志没有空间继续写入前，必须等待变更被刷新到数据文件。

另一方面，如果日志太大，在崩溃时恢复就得做大量的工作，这可能增大恢复时间。InnoDB会采用checkpoint机制来刷新和恢复数据，这会加快恢复数据的时间，具体可以参考：

• MySQL-checkpoint技术
• How InnoDB performs a checkpoint

innodb-flush-log-at-trx-commit

前面讨论了很多缓存，InnoDB日志也是有缓存的。当InnoDB变更任何数据时，会写一条变更记录到日志缓存区。在缓冲慢的时候、事务提交的时候，或者每一秒钟，InnoDB都会将缓冲区的日志刷新到磁盘的日志文件。如果有大事务，增加日志缓冲区大小可以帮助减少I/O，变量innodb-log-buffer-size可以控制日志缓冲区的大小。通常不需要把日志缓冲区设置的非常大，毕竟上述3个条件，任一条件先触发都会把缓冲区的内容刷新到磁盘，所以缓冲区的数据肯定不会太多，出入你的数据中有很多相当大的BLOB记录。通常来说，配置1M~8M即可。

既然存在缓冲区，怎样刷新日志缓冲就是我们需要关注的问题。日志缓冲必须刷新到磁盘，以确保提交的事务完全被持久化。如果和持久化相比，更在乎性能，可以修改innodb-flush-log-at-trx-commit变量来控制日志缓冲刷新的频率。

• 0：每1秒钟将日志缓冲写到日志文件并刷新到磁盘，事务提交时不做任何处理
• 1：每次事务提交时，将日志缓冲写到日志文件并刷新到磁盘
• 2：每次事务提交时，将日志缓冲写到日志文件，然后每秒刷新一次到磁盘

1是最安全的设置，保证不会丢失任何已经提交的事务，这也是默认的设置。0和2最主要的区别是，如果MySQL挂了，2不会丢失事务，但0有可能，2在每次事务提交时，至少将日志缓冲刷新到操作系统的缓存，而0则不会。如果整个服务器挂了或者断电了，则还是可能会丢失一些事务。

 

innodb-flush-method

innodb-flush-method选项可以配置InnoDB如何跟文件系统相互作用。从名字上看，影响InnoDB怎么写读数据。windows和非Windows操作系统下这个选项的值是互斥的，也就是说有些值只能Windows下使用，有些只能在非Windows下使用，其中Windows下可取值：async_unbuffered、unbuffered、normal、Nosync与littlesync，非Windows取值：fdatasync、0_DIRECT、 0_DSYNC。

这个选项既会影响日志文件，也会影响数据文件，而且有时候对不同类型的文件的处理也不一样，导致这个选项有些难以理解。如果有一个选项来配置日志文件，一个选项来配置数据文件，应该会更好，但实际上它们混合在同一个配置项中。这里只介绍类Unix操作系统下的选项。

 

fdatasync

InnoDB调用fsync()和fdatasync()函数来刷新数据和日志文件，其中fdatasync()只刷文件的数据，但不包含元数据(比如：访问权限、文件拥有者、最后修改时间等描述文件特征的系统数据)，因此fsync()相比fdatasync()会产生更多的I/O，但在某些场景下fdatasync()会导致数据损坏，因此InnoDB开发者决定用fsync()来代替fdatasync()。

fsync()的缺点是操作系统会在自己的缓存中缓冲一些数据，理论上双重缓冲是浪费的，因为InnoDB自己会管理缓冲，而且比操作系统更加智能。但如果文件系统能有更智能的I/O调度和批量操作，双重缓冲也并不一定是坏事：

• 有的文件系统和os可以累积写操作后合并执行，通过对I/O的重排序来提升效率、或者并发写入多个设备
• 有的还可以做预读优化，比如连续请求几个顺序的块，它会通知硬盘预读下一个块

这些优化在特定的场景下才会起作用，fdatasync为innodb-flush-method的默认值。

 

0_DIRCET

这个设置不影响日志文件并且不是所有的类Unix系统都有效，但至少在Linux、FreeBSD以及Solaris是支持的。这个设置依然使用fsync来刷新文件到磁盘，但是它完全关闭了操作系统缓存，并且是所有的读和写都直接通过存储设置，避免了双重缓冲。如果存储设备支持写缓冲或预读，那么这个选项并不会影响到设备的设置，比如RAID卡。

0_DSYNC

这个选项使得所有的写同步，即只有数据写到磁盘后写操作才返回，但它只影响日志文件，而不影响数据文件。

建议：如果使用类Unix操作系统并且RAID控制器带有电池保护的写缓存，建议使用0_DIRECT，如果不是，默认值或者0_DIRECT都可能是最好的选择。

innodb-file-per-table

最后一个配置，说说InnoDB表空间，InnoDB把数据保存在表空间内，它本质上是一个由一个或者多个磁盘文件组成的虚拟文件系统。InnoDB表空间并不只是存储表和索引，它还保存了回滚日志、插入缓冲、双写缓冲以及其他内部数据结构，除此之外，表空间还实现了很多其它的功能。可以通过innodb-data-file-path配置项定制表空间文件，innodb-data-home-dir配置表空间文件存放的位置，比如：

innodb-data-home-dir = /var/lib/mysql 
innodb-data-file-path = ibdata1:1G;ibdata2:1G;ibdata3:1G

这里在3个文件中创建了3G表空间，为了允许表空间在超过了分配的空间时还能增长，可以像这样配置最后一个文件自动扩展

innodb-data-file-path =ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend 

innodb-file-per-table选项让InnoDB为每张表使用一个文件，这使得在删除一张表时回收空间容易很多，而且特别容易管理，并且可以通过查看文件大小来确定表大小，所以这里建议打开这个配置。

 

总结

MySQL有太多的配置项，这里没有办法一一列举，重要的是了解每个配置的工作原理，从一个基础配置文件开始，设置符合服务器软硬件环境与工作负载的基本选项。

 

参考资料

[1] Baron Scbwartz 等著；宁海元 周振兴等译；高性能MySQL（第三版）; 电子工业出版社， 2013

作者：CHEN川
链接：https://www.jianshu.com/p/78b6b7e2bb7f
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。