MySQL 最大内存使用量 [英] MySQL maximum memory usage

问题描述

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

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 会在每次请求新查询时继续占用内存，以致最终会耗尽内存.有没有办法设置限制，使 MySQL 使用的数量不超过该数量?

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's maximum memory usage very much depends on hardware, your settings and the database itself.

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

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.

底线是:对于您的 MySQL 设置的推荐，没有一般的经验法则.这一切都取决于当前的使用情况或预测.

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

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

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.

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

As for the database -- a few important constraints:

• 表引擎(InnoDB、MyISAM、...)
• 尺寸
• 指标
• 用法

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

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.

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

Or - a lot of people suggest to up the max_connection variable -- 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_timeout to free those threads.

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

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

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

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 MyISAM table which locks up and blocks their entire application.

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

I don't mean to say MyISAM sucks, but InnoDB can be tweaked to respond almost or nearly as fast as MyISAM and offers such thing as row-locking on UPDATE whereas MyISAM locks the entire table when it is written to.

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

If you're at liberty to run MySQL on your own infrastructure, you might also want to check out the percona server because 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.

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

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

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

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.

底线是:从大小来看，要做出关于硬件的决定.你真的不能让 80 GB 的数据库在 1 上快速运行GB 内存.

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.

不是:越多越好.只需要设置索引，使用 EXPLAIN 检查使用情况.此外，MySQL 的 EXPLAIN 确实有限，但它只是一个开始.

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 EXPLAIN is really limited, but it's a start.

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

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

一个很好的开始是调优入门.这是一个 bash 脚本(提示:你需要 linux)，它接受 SHOW VARIABLES 和 SHOW STATUS 的输出，并将其包装成希望有用的推荐.如果您的服务器已经运行了一段时间，建议会更好，因为会有数据作为它们的基础.

A great start is the tuning primer. It's a bash script (hint: you'll need linux) which takes the output of SHOW VARIABLES and SHOW STATUS and 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.

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

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

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

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.

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

Another great resource is planet-mysql, of course.

这篇关于MySQL 最大内存使用量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！