如何处理mysqldump和innodb_buffer_pool_size？

Question 1

这似乎是一个老话题，但仍然是 DBA 关注的问题之一。我们可以承认，开发人员在生产数据库中执行的简单 SELECT 操作（取决于表的大小和为缓冲池保留的内存量）可能会给 InnoDB 带来麻烦，因为 InnoDB 需要移动不再访问的页面，以便再次将那些页面提供给来自应用程序的客户端请求。

减少缓冲池会导致同样的问题，因为这里的情况是尝试保持缓冲池具有“正确”的页面集（通过让应用程序接触它们而推送到缓冲池），甚至对数据运行 mysqldump。即使数据不是存档数据，问题也会在文档中指出：

默认情况下，查询读取的页面会立即移至新子列表中，这意味着它们会在缓冲池中停留更长时间。例如，对 mysqldump 操作或没有 WHERE 子句的 SELECT 语句执行的表扫描可以将大量数据带入缓冲池并驱逐等量的旧数据，即使新数据永远不会再使用。同样，由预读后台线程加载且仅访问过一次的页面将移至新列表的头部。这些情况可能会将经常使用的页面推送到旧子列表中，在那里它们将被驱逐。 https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

简单地说，如果你在主服务器上运行 mysqldump，你最终会得到一个未预热的 InnoDB 缓冲池，里面充满了你的应用程序不要求放在内存中的页面。

根据您在手册中读到的内容，您需要使缓冲池具有抗扫描能力，我发现以下内容：

在混合工作负载中，大多数活动都是 OLTP 类型，并带有定期批量报告查询，这会导致大量扫描，在批处理运行期间设置 innodb_old_blocks_time 的值有助于将正常工作负载的工作集保持在缓冲池中。当扫描无法完全放入缓冲池的大型表时，将 innodb_old_blocks_pct 设置为较小的值可防止仅读取一次的数据占用缓冲池的很大一部分。例如，设置 innodb_old_blocks_pct=5 会将仅读取一次的数据限制为缓冲池的 5%。 https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-midpoint_insertion.html

我想说的是，我会首先尝试在实验室中为 innodb_old_blocks_time例如 10000（毫秒），这意味着保留这些页面 10000（10 秒）并保护我的缓冲池免受全表扫描（该值越大，保留在旧子列表中的页面就越多）。即使这个变量的文档也这么说，所以我会先在实验室中尝试一下。然后，运行 mysqldump 命令。

bianchi@box01:/# mariadb -e "SET GLOBAL innodb_old_blocks_time=100;" -vv
--------------
SET GLOBAL innodb_old_blocks_time=10000
--------------

Query OK, 0 rows affected (0.000 sec)

Bye

bianchi@box01:/# time mysqldump --opt \
--hex-blob --routines --triggers --events \
--single-transaction --databases <db-name> > test.dump

bianchi@box01:/# mariadb -e "SET GLOBAL innodb_old_blocks_time=DEFAULT;" -vv
--------------
SET GLOBAL innodb_old_blocks_time=DEFAULT
--------------

Query OK, 0 rows affected (0.000 sec)

Bye

另一种方法是提供副本，而不是常规负载平衡轮换的一部分，这样您就可以弄乱该数据库服务器缓冲池。

我希望它有帮助。

干杯!

Answer

这似乎是一个老话题，但仍然是 DBA 关注的问题之一。我们可以承认，开发人员在生产数据库中执行的简单 SELECT 操作（取决于表的大小和为缓冲池保留的内存量）可能会给 InnoDB 带来麻烦，因为 InnoDB 需要移动不再访问的页面，以便再次将那些页面提供给来自应用程序的客户端请求。

减少缓冲池会导致同样的问题，因为这里的情况是尝试保持缓冲池具有“正确”的页面集（通过让应用程序接触它们而推送到缓冲池），甚至对数据运行 mysqldump。即使数据不是存档数据，问题也会在文档中指出：