mysql 翻译系列 四十一

10.5.9 优化InnoDB配置变量

不同的设置对于负载较轻且可预测的服务器，与那些始终接近满负荷运行或经历高活动峰值的服务器而言，效果各异。

由于InnoDB存储引擎会自动执行许多优化操作，许多性能调优任务都涉及监控，以确保数据库运行良好，并在性能下降时更改配置选项。有关详细的InnoDB性能监控信息，请参见17.16节 “InnoDB与MySQL性能模式的集成”。

你可以执行的主要配置步骤包括：

• 控制变更缓冲类型：控制InnoDB对哪些类型的数据更改操作进行缓冲，以避免频繁的小磁盘写入。详情见 “配置变更缓冲”。由于默认设置是缓冲所有类型的数据更改操作，因此只有在需要减少缓冲量时才更改此设置。
• 开关自适应哈希索引：使用innodb_adaptive_hash_index选项开启或关闭自适应哈希索引功能。更多信息，请参见17.5.3节 “自适应哈希索引”。你可以在异常活动期间更改此设置，之后再恢复为原始设置。
• 限制并发线程数量：如果上下文切换成为瓶颈，可以设置InnoDB处理的并发线程数量限制。详情见17.8.4节 “为InnoDB配置线程并发”。
• 控制预取量：控制InnoDB在预读操作中的预取量。当系统有未使用的I/O容量时，增加预读量可以提高查询性能。但在负载较重的系统中，过多的预读可能会导致性能周期性下降。详情见17.8.3.4节 “配置InnoDB缓冲池预取（预读）”。
• 增加读写后台线程：如果高端I/O子系统未被默认值充分利用，可以增加用于读或写操作的后台线程数量。详情见17.8.5节 “配置InnoDB后台I/O线程数量”。
• 控制后台I/O量：控制InnoDB在后台执行的I/O量。详情见17.8.7节 “配置InnoDB I/O容量”。如果你观察到性能周期性下降，可以缩减此设置。
• 调整后台写入算法：控制InnoDB执行某些类型后台写入操作的算法。详情见17.8.3.5节 “配置缓冲池刷新”。该算法适用于某些类型的工作负载，但不适用于其他工作负载，因此如果你观察到性能周期性下降，可以禁用此功能。
• 利用多核处理器：利用多核处理器及其缓存内存配置，最大限度地减少上下文切换的延迟。详情见17.8.8节 “配置自旋锁轮询”。
• 避免干扰缓存数据：防止诸如表扫描之类的一次性操作干扰存储在InnoDB缓冲缓存中的频繁访问数据。详情见17.8.3.3节 “使缓冲池抗扫描”。
• 调整日志文件大小：将日志文件调整到一个兼顾可靠性和崩溃恢复的合理大小。InnoDB日志文件通常设置得较小，以避免崩溃后启动时间过长。MySQL 5.5中引入的优化加快了崩溃恢复过程的某些步骤。特别是，由于内存管理算法的改进，扫描重做日志和应用重做日志的速度更快。如果你为了避免启动时间过长而人为地将日志文件设置得较小，现在可以考虑增大日志文件的大小，以减少因重做日志记录循环使用而产生的I/O操作。
• 配置缓冲池实例：配置InnoDB缓冲池的大小和实例数量，这对于拥有多GB缓冲池的系统尤为重要。详情见17.8.3.2节 “配置多个缓冲池实例”。
• 提高并发事务上限：增加最大并发事务数量，这对于最繁忙的数据库而言，可显著提高其可扩展性。详情见17.6.6节 “撤销日志”。
• 后台执行清理操作：将清理操作（一种垃圾回收类型）转移到后台线程中执行。详情见17.8.9节 “清理配置”。为了有效衡量此设置的效果，首先要调整其他与I/O和线程相关的配置设置。
• 减少线程切换：减少InnoDB在并发线程之间的切换量，以便繁忙服务器上的SQL操作不会排队形成 “交通堵塞”。为innodb_thread_concurrency选项设置一个值，对于高性能的现代系统，该值大约可设置为32 。将innodb_concurrency_tickets选项的值增加到通常为5000左右。这两个选项的组合设置了InnoDB在任何时刻处理的线程数量上限，并允许每个线程在被换出之前执行大量工作，从而使等待线程的数量保持较低水平，操作可以在不过度进行上下文切换的情况下完成。

10.5.10 针对包含大量表的系统优化InnoDB

如果配置了非持久化优化器统计信息（非默认配置），InnoDB会在启动后首次访问表时计算该表的索引基数，而不是将这些值存储在表中。在将数据划分为许多表的系统上，这一步可能会耗费大量时间。由于这种开销仅在首次打开表操作时出现，为了让表在后续使用时能快速响应，可以在启动后立即通过执行SELECT 1 FROM <tbl_name> LIMIT 1这样的语句来访问它，以此“预热”表。

默认情况下，优化器统计信息会持久化到磁盘，由innodb_stats_persistent配置选项启用。有关持久化优化器统计信息的内容，请参见17.8.10.1节 “配置持久化优化器统计参数”。