MySQLTuner 等工具将全局分配的内存加起来,并将其添加到最大连接数和每个连接内存需求的乘积中。如果使用 100% 的 max_connections,我制定了以下查询以更好地估计需要多少最大内存。
有人可以验证它看起来是否正确以及是否可以进行任何调整?
我意识到它排除了 MySQL 和服务器的许多其他 RAM 要求,但这个想法是为了更好地估计。
SELECT
sys.FORMAT_BYTES(
@@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size +
(
@@max_connections
* (
((select_scans / queries) * @@read_buffer_size)
+ ((sort_operations / queries) * (@@read_rnd_buffer_size + @@sort_buffer_size))
+ ((join_operations / queries) * @@join_buffer_size)
+ @@binlog_cache_size + @@thread_stack
+ ((temp_tables / queries) * LEAST(@@tmp_table_size, @@max_heap_table_size))
#need a better way to determine average packet size
+ (@@max_allowed_packet * 0.5) + @@net_buffer_length
)
)
) AS MAX_MEMORY
FROM (
SELECT
SUM(COUNT_STAR) AS queries,
SUM(SUM_SELECT_SCAN) AS select_scans,
SUM(
GREATEST(
(CASE WHEN UPPER(DIGEST_TEXT) LIKE '%ORDER BY%' THEN COUNT_STAR ELSE 0 END),
(CASE WHEN SUM_SORT_MERGE_PASSES > 0 OR SUM_SORT_RANGE > 0 OR SUM_SORT_ROWS > 0 OR SUM_SORT_SCAN > 0 THEN COUNT_STAR ELSE 0 END)
)
) AS sort_operations,
SUM(CASE WHEN UPPER(DIGEST_TEXT) LIKE '%JOIN%' THEN COUNT_STAR ELSE 0 END) AS join_operations,
SUM(SUM_CREATED_TMP_TABLES) AS temp_tables
FROM performance_schema.events_statements_summary_by_digest
) t;
即使您可以获得更准确的估计,这也不是一个有用的计算数字,因为理论上的“最大内存使用量”永远不会发生。
在我分析过的每一个生产 MySQL 服务器中(我作为顾问访问了几十家公司,并作为 DBA 支持了数千个 MySQL 服务器实例),MySQLTuner 类型计算总是给出一个理论上的最大内存使用量是数量的许多倍服务器上的物理 RAM。但是您永远不会看到交换内存使用量等于 10 倍物理 RAM。
无论如何,您显示的估计公式有一些不准确之处:
某些缓冲区在每次使用时都没有分配到它们的完整大小(例如:
tmp_table_size
等)在给定的查询中,某些缓冲区可能会被多次分配(例如:
join_buffer_size
和tmp_table_size
)配置变量中无法使用 RAM 的某些使用(例如:优化器在估计多值范围查询的成本时使用可变数量的 RAM)
无论如何,要让 MySQL 服务器达到此公式的最大内存使用量,您必须有
max_connections
客户端,所有查询同时执行,并且每个查询都必须以最大允许大小分配所有缓冲区。这根本不会发生。您的数据库服务器在达到此并发使用水平之前很久就会崩溃。
当我看到人们依赖 MySQLTuner 的建议时,我感到畏缩。它经常给出非常糟糕和误导性的建议。我不认为作者对 MySQL 内部知识非常了解。
那么我们应该怎么做才能准确估计内存使用量呢?
观察。
获取一些指标收集服务来绘制
mysqld
进程的实际内存大小随时间的变化,因为它处理来自服务器上应用程序的实际流量。这将是准确的,它将向您显示真实信息,最小值和最大值,以及增加和减少的模式。这是一个更好的公式的好尝试。但我仍然认为不可能获得“正确”的上限。
SELECT
,其中包括子查询。你试图解释这种情况。JOINs
单个查询中有多个时,可能有多个临时表。(我认为你不允许这样做。)一些设置:
key_buffer_size
应该是“小”(除了 MySQL 8.0,它应该是 0)。query_cache_size
,除了少数例外,应该为 0。(该变量甚至在 8.0 中消失了,从而使查询无效。)感觉好像缺少了一些其他可调参数,但要花点时间来挖掘我的笔记。你想继续这个讨论吗?