MySQL 最大内存使用量

MySQL maximum memory usage

提问by Till

I would like to know how it is possible to set an upper limit on the amount of memory MySQL uses on a Linux server.

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

Right now, MySQL will keep taking up memory with every new query requested so that it eventually runs out of memory. Is there a way to place a limit so that no more than that amount is used by MySQL?

现在，MySQL 会在每次请求新查询时继续占用内存，最终会耗尽内存。有没有办法设置限制，以便 MySQL 使用的数量不超过该数量？

回答by Till

MySQL's maximum memory usage very much depends on hardware, your settings andthe database itself.

MySQL 的最大内存使用量很大程度上取决于硬件、您的设置和数据库本身。

Hardware

硬件

The hardware is the obvious part. The more RAM the merrier, faster disks ftw. Don't believe those monthly or weekly news letters though. MySQL doesn't scale linear - not even on Oracle hardware. It's a little trickier than that.

硬件是显而易见的部分。RAM 越大越好，磁盘ftw越快。不过，不要相信那些每月或每周的新闻通讯。MySQL 不能线性扩展 - 即使在 Oracle 硬件上也不行。这比那要棘手一些。

The bottom line is: there is no general rule of thumb for what is recommend for yourMySQL setup. It all depends on the current usage or the projections.

底线是：有经验的，什么是推荐不一般的规则你的MySQL安装。这一切都取决于当前的使用情况或预测。

Settings & database

设置和数据库

MySQL offers countless variables and switches to optimize its behavior. If you run into issues, you really need to sit down and read the (f'ing) manual.

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

As for the database -- a few important constraints:

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

• table engine (InnoDB, MyISAM, ...)
• size
• indices
• usage

• 表引擎 ( InnoDB, MyISAM, ...)
• 尺寸
• 指数
• 用法

Most MySQL tips on stackoverflow will tell you about 5-8 so called important settings. First off, not all of them matter - e.g. allocating a lot of resources to InnoDB and not using InnoDB doesn't make a lot of sense because those resources are wasted.

大多数关于 stackoverflow 的 MySQL 技巧都会告诉你大约 5-8 个所谓的重要设置。首先，并非所有这些都重要——例如，为 InnoDB 分配大量资源而不使用 InnoDB 没有多大意义，因为这些资源被浪费了。

Or - a lot of people suggest to up the max_connectionvariable -- well, little do they know it also implies that MySQL will allocate more resources to cater those max_connections-- if ever needed. The more obvious solution might be to close the database connection in your DBAL or to lower the wait_timeoutto free those threads.

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

If you catch my drift -- there's really a lot, lot to read up on and learn.

如果你明白我的意思——真的有很多很多东西需要阅读和学习。

Engines

引擎

Table engines are a pretty important decision, many people forget about those early on and then suddenly find themselves fighting with a 30 GB sized MyISAMtable which locks up and blocks their entire application.

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

I don't mean to say MyISAM sucks, but InnoDBcan be tweaked to respond almost or nearly as fast as MyISAMand offers such thing as row-locking on UPDATEwhereas MyISAMlocks the entire table when it is written to.

我的意思并不是说MyISAM 很烂，但InnoDB可以调整以几乎或几乎与响应一样快，MyISAM并提供诸如行锁定之类的东西，UPDATE而MyISAM在写入时锁定整个表。

If you're at liberty to run MySQL on your own infrastructure, you might also want to check out the percona serverbecause among including a lot of contributions from companies like Facebook and Google (they know fast), it also includes Percona's own drop-in replacement for InnoDB, called XtraDB.

如果您可以自由地在自己的基础架构上运行 MySQL，您可能还想查看percona 服务器，因为其中包括来自 Facebook 和 Google（他们知道很快）等公司的大量贡献，它还包括 Percona 自己的 drop-代替InnoDB，称为XtraDB。

See my gist for percona-server (and -client) setup (on Ubuntu): http://gist.github.com/637669

请参阅我的 percona-server（和 -client）设置要点（在 Ubuntu 上）：http://gist.github.com/637669

Size

尺寸

Database size is very, very important -- believe it or not, most people on the Intarwebs have never handled a large and write intense MySQL setup but those do really exist. Some people will troll and say something like, "Use PostgreSQL!!!111", but let's ignore them for now.

数据库大小非常非常重要——信不信由你，Intarwebs 上的大多数人从未处理过大型和编写密集的 MySQL 设置，但这些确实存在。有些人会吐槽并说“使用 PostgreSQL！！！111”之类的话，但我们现在先忽略它们。

The bottom line is: judging from the size, decision about the hardware are to be made. You can't really make a 80 GB database run fast on 1 GB of RAM.

底线是：从大小来看，要做出有关硬件的决定。您无法真正让 80 GB 的数据库在 1 GB 的 RAM 上快速运行。

Indices

指数

It's not: the more, the merrier. Only indices needed are to be set and usage has to be checked with EXPLAIN. Add to that that MySQL's EXPLAINis really limited, but it's a start.

不是：越多越好。只需要设置索引，并且必须使用 来检查使用情况EXPLAIN。此外，MySQL 的EXPLAIN功能确实有限，但这是一个开始。

Suggested configurations

建议配置

About these my-large.cnfand my-medium.cnffiles -- I don't even know who those were written for. Roll your own.

关于这些my-large.cnf和my-medium.cnf文件——我什至不知道那些是为谁写的。自己卷。

Tuning primer

调谐引物

A great start is the tuning primer. It's a bash script (hint: you'll need linux) which takes the output of SHOW VARIABLESand SHOW STATUSand wraps it into hopefully useful recommendation. If your server has ran some time, the recommendation will be better since there will be data to base them on.

一个很好的开始是调谐入门。这是一个bash脚本（提示：你需要的Linux），这需要的输出SHOW VARIABLES，并SHOW STATUS和它包装成希望有用的建议。如果您的服务器已经运行了一段时间，建议会更好，因为会有数据作为它们的基础。

The tuning primer is not a magic sauce though. You should still read up on all the variables it suggests to change.

不过，调谐底漆并不是万能的。您仍然应该阅读它建议更改的所有变量。

Reading

读

I really like to recommend the mysqlperformanceblog. It's a great resource for all kinds of MySQL-related tips. And it's not just MySQL, they also know a lot about the right hardware or recommend setups for AWS, etc.. These guys have years and years of experience.

我真的很想推荐mysqlperformanceblog。它是各种 MySQL 相关技巧的绝佳资源。不仅是 MySQL，他们还非常了解正确的硬件或 AWS 的推荐设置等。这些人拥有多年的经验。

Another great resource is planet-mysql, of course.

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

回答by Ignacio Pascual

We use these settings:

我们使用这些设置：

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

for a server with the following specifications:

对于具有以下规格的服务器：

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

回答by zombat

Database memory usage is a complex topic. The MySQL Performance Blogdoes a good job of covering your question, and lists many reasons why it's hugely impractical to "reserve" memory.

数据库内存使用是一个复杂的话题。在MySQL性能博客做涵盖你的问题的一个很好的工作，并列出许多原因，这是巨大的不切实际的“储备”的记忆。

If you really want to impose a hard limit, you could do so, but you'd have to do it at the OS level as there is no built-in setting. In linux, you could utilize ulimit, but you'd likely have to modify the way MySQL starts in order to impose this.

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

---

---

The best solution is to tune your server down, so that a combination of the usual MySQL memory settings will result in generally lower memory usage by your MySQL installation. This will of course have a negative impact on the performance of your database, but some of the settings you can tweak in my.iniare:

最好的解决方案是调低您的服务器，这样通常的 MySQL 内存设置的组合将导致您的 MySQL 安装通常会降低内存使用量。这当然会对数据库的性能产生负面影响，但您可以调整的一些设置my.ini是：

key_buffer_size
query_cache_size
query_cache_limit
table_cache
max_connections
tmp_table_size
innodb_buffer_pool_size

I'd start there and see if you can get the results you want. There are manyarticlesout there about adjusting MySQL memory settings.

我会从那里开始，看看你是否能得到你想要的结果。有很多文章有关调整MySQL的内存设置在那里。

---

---

Edit:

编辑：

Note that some variable names have changed in the newer 5.1.x releases of MySQL.

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

For example:

例如：

table_cache

Is now:

就是现在：

table_open_cache

回答by Sarvar Nishonboev

mysqld.exe was using 480 mb in RAM. I found that I added this parameter to my.ini

mysqld.exe 在 RAM 中使用了 480 mb。我发现我在my.ini中添加了这个参数

table_definition_cache = 400

that reduced memory usage from 400,000+ kb down to 105,000kb

将内存使用量从 400,000+ kb 减少到 105,000kb

回答by shilovk

in /etc/my.cnf:

在/etc/my.cnf：

[mysqld]
...

performance_schema = 0

table_cache = 0
table_definition_cache = 0
max-connect-errors = 10000

query_cache_size = 0
query_cache_limit = 0

...

Good work on server with 256MB Memory.

在具有 256MB 内存的服务器上运行良好。

回答by Rohit Salecha

If you are looking for optimizing your docker mysql container then the below command may help. I was able to run mysql docker container from a default 480mb to mere 100 mbs

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

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

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