MySQL 101：MySQL性能之参数调优

译自 MySQL 101: Parameters to Tune for MySQL Performance

尽管没有MySQL调优的银弹，但还是有一些方面可以显着提高MySQL的性能。这些年来，尽管已经发布了许多有关此主题的信息，但我想逐一讲述一些最关键的任何人都毋庸置疑都参数设置。

根据您所运行的MySQL版本的不同，本文中使用的某些默认值可能与您的安装版本有所不同，但前提基本相同。

最基本的MySQL性能调优可以分为以下几类：

• 硬件调优
• 最佳性能/最佳实践调优
• 负载调优

硬件调优

根据MySQL的硬件规格，需要根据计算机（或VM）规范设置一些变量。以下变量在很大程度上取决于您的硬件：

innodb_buffer_pool_size

• 通常，将其设置为总RAM的50％-70％作为起点。
• 不需要将其设置为大于数据库的总大小。
• Percona监视和管理（PMM）工具可以提供更多的缓冲池使用情况参考依据，并允许您进行相应的调整。

innodb_log_file_size

• 通常设置在128M – 2G之间。
• 应该足够大，最多可以容纳一个小时左右的日志。
  • 数值应保证MySQL可以在flush和checkpoint过程中对写进行重新排序，以使用顺序I/O。
    This is more than enough so that MySQL can reorder writes to use sequential I/O during the flushing and checkpointing processes.
• PMM可以提供更多的参考依据，比如您使用的日志空间超过50％，您可能会因及时对日志文件大小的增加而受益。

innodb_flush_log_at_trx_commit

• 设置为“ 1”（5.7中的默认值）可提供最大的持久性。
• 设置为“ 0”或“ 2”将提供更高的性能，但持久性会更低。

innodb_flush_method

• 将此设置为O_DIRECT将避免双重缓冲带来的性能损失。

最佳性能/最佳实践调优

innodb_file_per_table

将此设置为“ ON”将为数据库中的每个表生成一个独立的InnoDB表空间。

innodb_stats_on_metadata

将其设置为“ OFF”可以避免不必要的InnoDB统计信息更新，并可以大大提高读取速度。

innodb_buffer_pool_instances

最佳做法是将其设置为“ 8”，除非缓冲池大小小于1G，在这种情况下，设置为“ 1”。

query_cache_type和query_cache_size

将它们都设置为“ 0”将完全禁用查询缓存。

负载调优

为了进一步调整，将需要更多信息。收集此信息的最佳方法是安装MySQL监视/绘图工具，例如Percona Monitoring and Management平台。一旦安装了工具，我们就可以深入研究各个指标并开始根据数据进行自定义。

我建议从影响最大的变量之一开始-innodb_buffer_pool_size。将实例上的RAM和可用页数与总缓冲池大小进行比较。根据这些指标，您可以确定是否需要增加或减小整体缓冲池大小设置。

接下来，查看InnoDB日志文件使用情况的指标。经验法则是您的日志文件应保存大约一小时的数据。如果您发现每小时写入日志文件的数据超过日志文件的总大小，则需要增加innodb_log_file_size变量并重新启动MySQL。您也可以使用“ SHOW ENGINE INNODB STATUS”进行验证；通过MySQL CLI帮助计算一个好的InnoDB日志文件大小。

其他设置

可以进一步调优以获得更好性能的其他InnoDB设置是：

innodb_autoinc_lock_mode

将其设置为“ 2”（交错模式）可以消除对自动增量锁定的需要（在表级别），并且在使用多行插入语句通过自动增量主键将值插入表中时可以提高性能。请注意，这需要ROW或MIXED binlog格式。

innodb_io_capacity / innodb_io_capacity_max

如果您使用大量写工作流，这些设置将影响您的数据库。这不适用于读取（选择）流量。要调整这些值，最好知道系统可以执行多少次iops。最好运行sysbench或其他基准测试工具来确定您的存储吞吐量。
PMM可以提供更多的指标参数，显示您的IO使用情况并允许您进行相应的调整。

总结

虽然这绝不是有关MySQL调优的全面文章，但以上建议应清除一些低调的成果，并使您的系统更接近理想的设置。与所有数据库调整一样，您的过程应基于当前信息进行。

• 检查上面建议的设置，并实施对您的环境/工作量是否有意义的设置。
• 安装一个好的监视工具以深入了解数据库（Percona监视和管理是我们的建议）。
• 随时关注您的监视图以确定