MySQL最大内存使用量

Question

MySQL最大内存使用量

mysqlmemoryram

125

我想知道如何在Linux服务器上设置MySQL使用内存的上限。

目前，每次请求新查询时，MySQL都会继续占用内存，以至于最终会耗尽内存。是否有一种方法可以设置一个上限，以便MySQL不会使用超过该数量的内存？

- Timothy Mifsud

4

MySQL不会“为每个新查询占用内存并最终用尽”。内存使用情况比那复杂得多。 - Rick James

这篇博客文章帮助我了解了影响MySQL所需的最小和最大内存量的因素。https://tech.labelleassiette.com/how-to-reduce-the-memory-usage-of-mysql-61ea7d1a9bd - Aleks

现在已经过去了13年。MySQL现在[大多数情况下]可以直接使用，无需进行太多配置。 - Rick James

@RickJames 当我看到max_connect_errors仍然达到100万时，我不禁想知道发布组织如何期望获得“信誉”。为什么会有人允许100万次尝试猜测密码？我建议将限制设置为10次，以便在一定数量的错误后使黑客/破解者感到沮丧。他们将被锁定直到服务器重新启动或FLUSH HOSTS启用另一个尝试。 - Wilson Hauck

简单来说：黑客。我同意1M是不合理的高。大多数服务器很少出现连接错误。我见过很少有超过50万个连接错误，更具体地说是：Connection_errors_internal。 - Rick James

7个回答

39

我们使用以下设置：

etc/my.cnf
innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100

对于以下规格的服务器：

Dell Server
CPU cores: Two
Processor(s): 1x Dual Xeon
Clock Speed: >= 2.33GHz
RAM: 2 GBytes
Disks: 1×250 GB SATA

- Ignacio Pascual

16

我认为您（和您提供链接的作者）把query_cache_size和query_cache_limit搞反了。您告诉MySQL：分配1MB缓存，但不要将任何大于128MB的查询放入其中。参考链接：http://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html - agtb

我会降低 max_connections。我会决定使用哪个引擎，而不是为两者分配大量空间。 - Rick James

其中一些在 MySQL 8 中已被弃用，不确定该如何更新。但只需添加 innodb_buffer_pool_size 和 table_definition_cache 就可以帮助很多了。 - Dave Ankin

查询缓存已被弃用，这些设置默认情况下已关闭，现在该功能已从MySQL中完全删除。 - Dave Ankin

键缓冲区大小不应该是key_buffer吗？ - PYK

20

mysqld.exe在内存中使用了480MB。我发现我将这个参数添加到了my.ini文件中。

table_definition_cache = 400

将内存使用量从400,000+ kb降至105,000kb

- Sarvar Nishonboyev

这属于哪个部分？我加到我的代码里后，服务就拒绝启动了。 - Syntax Error

没关系，我把它移到了[wampmysqld]下面，效果非常好，而且显著减少了我使用的内存。我认为这可能也加快了我的本地主机页面加载速度，现在它们看起来更快了。 - Syntax Error

虽然默认和最小值是400，但在您的情况下，是什么使它超过了400？ - Wadih M.

19

数据库内存使用是一个复杂的话题。 MySQL性能博客对您的问题进行了很好的覆盖，并列出了许多原因，说明“保留”内存是非常不实际的。

如果您真的想要强制限制内存使用量，可以这样做，但必须在操作系统级别上执行，因为没有内置设置。在Linux中，您可以利用ulimit，但可能需要修改MySQL启动方式才能实施此操作。

最佳解决方案是将服务器调整到较低的设置，使通常的MySQL内存设置的组合在一般情况下会导致MySQL安装使用的内存更少。当然，这会对数据库的性能产生负面影响，但您可以在my.ini中调整一些设置：

key_buffer_size
query_cache_size
query_cache_limit
table_cache
max_connections
tmp_table_size
innodb_buffer_pool_size

建议从这里开始，看看是否能够得到您想要的结果。有很多文章讨论调整MySQL内存设置的问题，这里和这里都有。

编辑：

请注意，一些变量名称在新版本的MySQL 5.1.x中已更改。

例如：

table_cache

现在是：

table_open_cache

- zombat

2

嗨！感谢你的回答。我注意到人们引用的方程式如下：key_buffer_size+(read_buffer_size+sort_buffer_size)*max_connections=总内存。我已将以下设置为：key_buffer_size=128M，read_buffer_size=1M，sort_buffer_size=2M，max_connections=120，并且服务器上的总内存为512M。然而，在许多查询之后，可用内存已降至12M，而且可能会随着进一步使用而继续下降。这是为什么，能否防止？谢谢！ - Timothy Mifsud

或许我需要考虑的不是服务器上的总内存（512M），而是可用内存（即在加载所有操作系统相关和其他程序后可用的内存）？ - Timothy Mifsud

1

如果您打算修改tmp_table_size以增加可以存储在RAM中的临时表的大小，请记得同时增加max_heap_table_size - 因为MySQL使用两者中的最小值... - Dave Rix

1

@TimothyMilsud - 没有像那样的公式真正有效。当一个公式声称使用太多的RAM时，大多数服务器运行得非常好。 - Rick James

7

关于MYSQL如何占用内存：https://dev.mysql.com/doc/refman/8.0/en/memory-use.html 在 /etc/my.cnf 中：

[mysqld]
...

performance_schema = 0

table_cache = 0
table_definition_cache = 0
max_connect_errors = 10

query_cache_size = 0
query_cache_limit = 0

...

服务器只有256MB的内存，工作表现很不错。

- shilovk

为什么 table_definition_cache = 0？能否解释一下原因。而且你基本上没有缓存查询...如果你将 query_cache_type = 0，那么效果是相同的 :) - Khom Nazid

@KhomNazid 缓存会消耗内存，在我的情况下，服务器只有256Mb内存，所以这个配置有助于节省内存。更多关于内存的信息请参考 https://dev.mysql.com/doc/refman/8.0/en/memory-use.html "MySQL还需要用于表定义缓存的内存。table_definition_cache系统变量定义可以存储在表定义缓存中的表定义数量。如果您使用大量的表格，则可以创建大型表定义缓存以加快打开表格的速度。表定义缓存占用较少的空间，不使用文件描述符，而不像表缓存一样。" - shilovk

这对我来说没有启动，我不得不注释掉table_cache / query_cache_size / query_cache_limit，然后它才开始工作。 - Owl

@shilovk 你有什么好理由允许黑客/破解者尝试10000次猜测密码来攻击你的系统吗？这就是当你使用max_connect_errors = 10000时所允许的。建议将其限制为10以保持你的理智。 - Wilson Hauck

@shilovk 感谢您提出更为合理的建议。 - Wilson Hauck

1

由于我没有足够的声望点来为之前的答案点赞，我同意“table_definition_cache = 400”这个答案适用于我的旧Centos服务器。

- Nick Xu

0

如果您正在寻找优化docker mysql容器的方法，那么下面的命令可能会有所帮助。我能够将mysql docker容器从默认的480mb运行到仅100mb。

docker run -d -p 3306:3306 -e MYSQL_DATABASE=test -e MYSQL_ROOT_PASSWORD=tooor -e MYSQL_USER=test -e MYSQL_PASSWORD=test -v /mysql:/var/lib/mysql --name mysqldb mysql --table_definition_cache=100 --performance_schema=0 --default-authentication-plugin=mysql_native_password

- Rohit Salecha

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Till · Accepted Answer

MySQL的最大内存使用量非常依赖于硬件、设置和数据库本身。

硬件方面很明显，内存越多越好，更快的磁盘也很重要。不过不要相信那些每月或每周的新闻简报。MySQL并不是线性扩展的，即使在Oracle硬件上也是如此。它比这要棘手一些。

总之，没有通用的经验法则适用于你的MySQL设置。一切都取决于当前的使用情况或预测。

MySQL提供了无数的变量和开关来优化其行为。如果你遇到问题，你真的需要坐下来阅读（该死的）手册。

至于数据库——有一些重要的约束条件：

- 表引擎（InnoDB、MyISAM等） - 大小 - 索引 - 使用情况

Stackoverflow上的大多数MySQL提示会告诉你5-8个所谓的重要设置。首先，并不是所有的设置都重要——例如，将大量资源分配给InnoDB而不使用InnoDB是没有意义的，因为这些资源是浪费的。

另外，很多人建议增加max_connection变量——他们很少知道这也意味着MySQL将分配更多的资源来满足那些max_connections——如果需要的话。更明显的解决方案可能是在你的DBAL中关闭数据库连接，或者降低wait_timeout以释放这些线程。

如果你能理解我的意思——需要阅读和学习的内容真的很多。

引擎

表引擎是一个非常重要的决定，许多人在早期忘记了这些，然后突然发现自己正在与一个大小为30 GB的MyISAM表格作斗争，它会锁定并阻塞整个应用程序。

我不是说 MyISAM很糟糕，但InnoDB可以被调整为几乎或接近于MyISAM的反应速度，并且提供了诸如UPDATE上的行锁定等功能，而MyISAM在写入时锁定整个表格。

如果您有自己的基础设施运行MySQL，您可能还想检查一下percona server，因为除了包括来自Facebook和Google（他们知道快速）等公司的许多贡献外，它还包括Percona自己的InnoDB的可插拔替换，称为XtraDB。

请参见我的gist以获取percona-server（和-client）设置（在Ubuntu上）：http://gist.github.com/637669 尺寸

数据库大小非常重要-信不信由你，在Intarwebs上大多数人从未处理过大型且写入强度很高的MySQL设置，但这确实存在。有些人会发表恶意评论，比如“使用PostgreSQL！!!111”，但我们现在先忽略它们。

总之，从规模上来看，需要做出关于硬件的决策。在1GB的RAM上，你无法使80GB的数据库运行速度快。

索引

并不是越多越好。只有需要设置的索引才需要设置，并且必须使用EXPLAIN进行检查。此外，MySQL的EXPLAIN非常有限，但它是一个开始。

建议的配置

关于这些my-large.cnf和my-medium.cnf文件 - 我甚至不知道它们是为谁编写的。自己动手吧。

调优指南

一个很好的起点是tuning primer。它是一个bash脚本（提示：您需要linux），它将SHOW VARIABLES和SHOW STATUS的输出包装成有用的建议。如果您的服务器已经运行了一段时间，建议会更好，因为有数据可以基于。

调优指南并不是万能的解决方案。您仍然应该阅读建议更改的所有变量。

阅读材料

我真的很喜欢推荐mysqlperformanceblog。它是一个关于各种MySQL相关技巧的重要资源。它不仅仅是MySQL，他们还了解正确的硬件或为AWS推荐设置等等。这些人有多年的经验。

当然，另一个很棒的资源是planet-mysql。