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，...)
• 大小
• 索引
• 用法

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

大多数关于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_connections-如果需要的话.更明显的解决方案可能是关闭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，则可能还需要查看

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服务器(和-client)设置(在Ubuntu上)的要点: http://gist. github.com/637669

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

数据库的大小非常非常重要-信不信由你，Intarweb上的大多数人从未处理过很大的工作，也不编写密集的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的RAM.

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屋！