MySQL 进阶教程：升级内存后 MySQL 优化

在这篇文章中，我们来探讨下当您向实例添加更多内存时该怎么做。在扩展资源时，向运行 MySQL 的服务器添加内存是常见的做法。

先看一些背景

扩展资源只是为您的环境添加更多资源，这可以分为两种主要方式：垂直扩展和水平扩展。

垂直扩展正在增加给定实例的硬件容量，从而拥有更强大的服务器，而水平扩展正在添加更多服务器，这是一种非常标准的负载平衡和分片方法。

随着流量的增长，工作数据集变得越来越大，因此我们开始受到影响，因为无法放入内存的数据必须从磁盘中检索。这是一项代价高昂的操作，即使使用现代 NVME 驱动器也是如此，因此在某些时候，我们将需要处理我们提到的任何一种扩展解决方案。

在这种情况下，我们将讨论添加更多内存，这通常是垂直扩展硬件的最快和最简单的方法，并且拥有更多内存可能是 MySQL 的主要好处。

如何计算内存利用率

首先，我们需要清楚在 MySQL 操作期间哪些变量分配了内存，我们将只介绍常见的变量，因为它们有很多。此外，我们需要知道一些变量将全局分配内存，而其他变量将进行每个线程的分配。

为简单起见，我们将考虑使用标准存储引擎InnoDB来讨论这个主题。

我们有全局分配的变量：

key_buffer_size：MyISAM 设置应设置为 8-16M，任何高于此值的设置都是错误的，因为除非出于特殊原因，否则我们不应使用 MyISAM 表。一个典型的场景是 MyISAM 只被系统表使用，这些系统表很小（这对 5.7 以上的版本有效），并且在 MySQL 8 中系统表被迁移到 InnoDB 引擎。所以这个变量的影响可以忽略不计。

query_cache_size : 0 是默认值，在 8.0 中被删除，所以我们不会考虑它。

innodb_buffer_pool_size：这是 InnoDB 放置页面以执行操作的缓存。越大越好。?

当然，还有其他的，但在使用默认值运行时它们的影响很小。

此外，在每个线程（或打开的连接）上分配了其他变量：
read_buffer_size、 read_rnd_buffer_size 、sort_buffer_size、join_buffer_size和tmp_table_size ，以及其他一些变量。默认情况下，所有这些都工作得很好，因为分配小而高效。因此，主要的潜在问题变成了我们分配的许多连接可以保持这些缓冲区一段时间并增加额外的内存压力。理想的情况是控制打开（和使用）的连接数，并尝试将该数量减少到不损害应用程序的足够数量。

但是我们不要忘记重点，我们有更多的内存，我们需要知道该如何正确地调优它以获得最佳使用效果。

我们需要关注的对内存影响最大的设置是innodb_buffer_pool_size，因为这是几乎所有魔法发生的地方，并且通常是更重要的内存消耗者。有一条古老的经验法则说，“此设置的大小应设置为可用内存的 75% 左右”，一些云供应商将此值设置为total_memory*0.75。

我说“旧”是因为当运行具有 8G 或 16G RAM 的实例很常见时，该规则很好，因此从 8G 中分配大约 6G 或从 16G 中分配 13G 过去是合乎逻辑的。

但是如果我们遇到一个100G甚至200G的实例呢？现在这种硬件已经不少见了，那么我们会用100G中的80G还是200G中的160G呢？意思是，我们会避免分配 20G 到 40G 的内存并将其留给文件系统缓存操作吗？虽然这些文件系统操作并非毫无用处，但我认为操作系统在专用数据库上为此需要超过 4G-8G 的内存。此外，建议对 InnoDB 使用 O_DIRECT 刷新方法来绕过文件系统缓存。

示例

现在我们了解了分配内存的主要变量，让我们检查一下我目前正在处理的一个好的用例。假设这个系统：

$ free -m
      total      used     free    shared    buff/cache    available
Mem: 385625    307295    40921         4         37408        74865

*这里有一点免责声明，虽然这个查询并不完全准确，因此它可能与实际结果不同，但我们可以了解可能要分配的内容，我们可以利用 performance_schema 数据库，但这可能需要启用一些仪器默认禁用：

mysql > show global status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections |    67 |
+----------------------+-------+
1 row in set (0.00 sec)

因此，最多使用 67 个连接，我们可以得到：

mysql > SELECT ( @@key_buffer_size
-> + @@innodb_buffer_pool_size
-> + 67 * (@@read_buffer_size
-> + @@read_rnd_buffer_size
-> + @@sort_buffer_size
-> + @@join_buffer_size
-> + @@tmp_table_size )) / (1024*1024*1024) AS MAX_MEMORY_GB;
+---------------+
| MAX_MEMORY_GB |
+---------------+
| 316.4434      |
+---------------+
1 row in set (0.00 sec)

mysql > SELECT (@@innodb_buffer_pool_size) / (1024*1024*1024) AS BUFFER_POOL_SIZE;
+------------------+
| BUFFER_POOL_SIZE |
+------------------+
| 310.0000         |
+------------------+
1 row in set (0.01 sec)

好的，现在让我们进入正题，如何正确配置内存以供 MySQL 有效使用。从 pt-mysql-summary 我们知道缓冲池已满：

Buffer Pool Size | 310.0G
Buffer Pool Fill | 100%

这是否意味着我们需要更多内存？也许吧，所以让我们使用以下命令检查我们知道的实例中有多少磁盘操作，而工作数据集不适合内存（这正是我们增加内存大小的原因）：

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99857480858|
| Innodb_buffer_pool_reads         | 598600690  |
| Innodb_buffer_pool_read_requests | 274985     |
| Innodb_buffer_pool_reads         | 1602       |
| Innodb_buffer_pool_read_requests | 267139     |
| Innodb_buffer_pool_reads         | 1562       |
| Innodb_buffer_pool_read_requests | 270779     |
| Innodb_buffer_pool_reads         | 1731       |
| Innodb_buffer_pool_read_requests | 287594     |
| Innodb_buffer_pool_reads         | 1567       |
| Innodb_buffer_pool_read_requests | 282786     |
| Innodb_buffer_pool_reads         | 1754       |

Innodb_buffer_pool_reads：从磁盘读取页面（坏）

您可能会注意到，我们仍然会从磁盘读取一些数据，并且我们希望避免它们，所以让我们将缓冲池大小增加到 340G（总内存的 90%）并再次检查：

mysqladmin -r -i 1 -c 60 extended-status | egrep "Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads"
| Innodb_buffer_pool_read_requests | 99937722883 |
| Innodb_buffer_pool_reads         | 599056712   |
| Innodb_buffer_pool_read_requests | 293642      |
| Innodb_buffer_pool_reads         | 1           |
| Innodb_buffer_pool_read_requests | 296248      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 294409      |
| Innodb_buffer_pool_reads         | 0           |
| Innodb_buffer_pool_read_requests | 296394      |
| Innodb_buffer_pool_reads         | 6           |
| Innodb_buffer_pool_read_requests | 303379      |
| Innodb_buffer_pool_reads         | 0           |

概括

如果您增加服务器的内存大小，您主要需要关注innodb_buffer_pool_size，因为这是最关键的调整变量。在大型系统上分配 90% 到 95% 的总可用内存一点也不坏，因为操作系统只需要几 GB 即可正常运行，而更多的内存交换应该足以正常运行。

此外，请检查您需要（和已使用）的最大连接数，因为这是导致内存问题的常见错误，如果您需要在打开 1000 个连接的情况下运行，则可能无法分配缓冲池的 90% 内存，并且可能需要一些额外的操作（即，添加代理层或连接池）。

从 MySQL 8 开始，我们有一个名为innodb_dedicated_server的新变量，它将自动计算内存分配。虽然这个变量对于初始方法非常有用，但它可能会在 RAM 超过 4G 的系统中分配不足，因为它设置缓冲池大小 =（检测到的服务器内存 * 0.75），所以在 200G 的服务器中，我们有缓冲池只有 150 个。

结论

垂直扩展是提高性能的最简单、最快的方法，而且成本更低——但并不神奇。正确调整变量需要分析和理解内存的使用方式。这篇文章重点介绍了调整内存分配时要考虑的基本变量，特别是 innodb_buffer_pool_size 和 max_connections。不需要时不要过度调整，并注意这两者如何影响您的系统。

The post MySQL 进阶教程：升级内存后 MySQL 优化 first appeared on Linux迷.