MySQL 5.7 安装完成后，立即要调整的性能选项

妖狐艹你老母 2023-06-25 04:34 6阅读 0赞

MySQL 5.7 提供了更加合适的默认值。Morgan Tocker 制作了[一份关于 MySQL 5.7 的特性完整列表][MySQL 5.7]，这是一个很好的参考资料。例如，下面的几个变量都是默认设置的：

*  [innodb\_file\_per\_table][innodb_file_per_table] = ON
 *  [innodb\_stats\_on\_metadata][innodb_stats_on_metadata] = OFF
 *  [innodb\_buffer\_pool\_instances][innodb_buffer_pool_instances] = 8 (or 1 if innodb\_buffer\_pool\_size < 1GB)
 *  [query\_cache\_type][query_cache_type] = 0; query\_cache\_size = 0; (disabling mutex)

在 MySQL 5.7 中，实际上只有 4 个重要的选项需要去调整。然而，还有其他的 InnoDB 或者全局的 MySQL 选项可能需要根据特定的负载和硬件来做出调整。

首先，在 my.cnf 中的\[mysqld\] 节下面添加如下配置。配置完成之后，需要重启 MySQL 服务：

[mysqld]
    # other variables here
    innodb_buffer_pool_size = 1G # (adjust value here, 50%-70% of total RAM)
    innodb_log_file_size = 256M
    innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
    innodb_flush_method = O_DIRECT

说明：

<table> 
 <thead> 
  <tr> 
   <th>选项</th> 
   <th>值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>innodb_buffer_pool_size</td> 
   <td>设置为 RAM 大小的 50%-70%，不需要大于数据库的大小</td> 
  </tr> 
  <tr> 
   <td>innodb_flush_log_at_trx_commit</td> 
   <td>1（默认值），0/2 （性能更好，但稳定性更差）</td> 
  </tr> 
  <tr> 
   <td>innodb_log_file_size</td> 
   <td>128M – 2G (不需要大于 buffer pool)</td> 
  </tr> 
  <tr> 
   <td>innodb_flush_method</td> 
   <td>O_DIRECT (避免双缓冲技术)</td> 
  </tr> 
 </tbody> 
</table>

## 下一步 ##

对于新安装的实例而言，那些设置都是很好的起点。还有许多其他的选项，在某些场景下，可以提高 MySQL 的性能。通常，我会部署一套 MySQL 监控/图形工具（例如，[Percona 监控语句管理平台][Percona]，俗称：PMMM），然后根据 MySQL 监控面板提供的信息来执行进一步的调优。

## 根据监控图表，我们能进一步做什么样的优化? ##

InnoDB 缓冲池大小的信息，看下面这个图：

![image][]  
![Screen-Shot-2016-10-03-at-12.48.13-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-12.48.13-PM.png][]![uploading.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-12.48.13-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![image][Screen-Shot-2016-10-03-at-12.48.13-PM.png]

从上面可以看出，可用的 RAM 和空闲的页的数量小于缓冲池的总大小，我们可以把 InnoDB 缓冲池的大小增加到 10G 从而获得更好的性能。

InnoDB redo 日志的大小的信息，看下面这个图：

![Screen-Shot-2016-10-03-at-12.43.52-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-12.43.52-PM.png][]![uploading.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-12.43.52-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![image][Screen-Shot-2016-10-03-at-12.43.52-PM.png]

可以看到，InnoDB 通常每小时写 2.26GB 的数据，这已经超出了 redo 日志的大小（2G）。现在我们可以增大 `innodb_log_file_size` 选项，然后重启 MySQL。另外，使用 "show engine innodb status" 命令来[计算一个合适的 InnoDB redo 日志的大小值][InnoDB redo]。

## 其他选项 ##

有很多其他的 InnoDB 选项可以进一步调整：

*  innodb\_autoinc\_lock\_mode

设置 innodb\_autoinc\_lock\_mode = 2 (interleaved mode) 可以去掉表级 AUTO-INC 锁的必要性（还可以提高在使用多行插入语句来插入数据到拥有自增主键的表的时候的性能）。这要求 `binlog_format=ROW` 或者 `MIXED`（在 MySQL 5.7 中，默认值是 ROW）。

*  innodb\_io\_capacity 和 innodb\_io\_capacity\_max

这是一个更加高级的调优，只有当你在频繁写操作的时候才有意义（它不适用于读操作，例如 SELECTs）。若你真的需要对它进行调整，最好的方法是要了解系统可以支持多大的 IOPS。譬如，假设服务器有一块 SSD 硬盘，我们可以设置 `innodb_io_capacity_max=6000` 和 `innodb_io_capacity=3000`（最大值的一半）。运行 sysbench 或者任何其他基准工具来对磁盘吞吐量来进行基准测试是一个好方法。

然而，我们需要去担心这个选项吗？看下面这张缓冲池的"[脏页][Link 1]"

![Screen-Shot-2016-10-03-at-7.19.47-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-7.19.47-PM.png][]![uploading.gif][]转存失败重新上传取消![Screen-Shot-2016-10-03-at-7.19.47-PM.png][]![uploading.4e448015.gif][]转存失败重新上传取消![image][Screen-Shot-2016-10-03-at-7.19.47-PM.png]

在这种情况下，脏页的总量很大，而且看起来 InnoDB 刷新脏页的速度跟不上脏页的速度。如果我们有一个高速的磁盘子系统（例如：SSD），可以增加 `innodb_io_capacity` 和 innodb\_io\_capacity\_max 来得到更好的性能。

## 总结（太长,请不要看版本） ##

新的 MySQL 5.7 配置项的默认值对于一般的负载更加友好。与此同时，我们仍然需要配置 InnoDB 的选项来发挥出大内存的优势。  
安装完毕之后，遵循以下步骤来操作：

1.  如上所述，在 my.cnf 中配置 InnoDB 选项，并重启 MySQL 服务
2.  部署一套监控系统（例如：Percona 监控语句管理平台）
3.  通过监控图形来判断是否对 MySQL 进一步调优

[MySQL 5.7]: http://www.thecompletelistoffeatures.com/
[innodb_file_per_table]: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_file_per_table
[innodb_stats_on_metadata]: https://www.percona.com/blog/2013/12/03/innodb_stats_on_metadata-slow-queries-information_schema/
[innodb_buffer_pool_instances]: http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances
[query_cache_type]: https://www.percona.com/blog/2015/01/02/the-mysql-query-cache-how-it-works-and-workload-impacts-both-good-and-bad/
[Percona]: http://pmmdemo.percona.com/
[image]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly93d3cucGVyY29uYS5jb20vYmxvZy93cC1jb250ZW50L3VwbG9hZHMvMjAxNi8xMC9TY3JlZW4tU2hvdC0yMDE2LTEwLTAzLWF0LTEyLjQ5LjIyLVBNLnBuZw?x-oss-process=image/format,png
[Screen-Shot-2016-10-03-at-12.48.13-PM.png]: https://www.percona.com/blog/wp-content/uploads/2016/10/Screen-Shot-2016-10-03-at-12.48.13-PM.png
[uploading.4e448015.gif]: https://csdnimg.cn/release/mp/img/uploading.4e448015.gif
[uploading.gif]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9jc2RuaW1nLmNuL3JlbGVhc2UvcGhvZW5peC93cml0ZS9hc3NldHMvdXBsb2FkaW5nLmdpZg
[Screen-Shot-2016-10-03-at-12.43.52-PM.png]: https://www.percona.com/blog/wp-content/uploads/2016/10/Screen-Shot-2016-10-03-at-12.43.52-PM.png
[InnoDB redo]: https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
[Link 1]: http://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_dirty_page
[Screen-Shot-2016-10-03-at-7.19.47-PM.png]: https://www.percona.com/blog/wp-content/uploads/2016/10/Screen-Shot-2016-10-03-at-7.19.47-PM.png