MySQL 服务器内存使用

核心提示： 可能有的人想说，我们想要让服务器能保证100%不会耗尽内存，MySQL 服务器内存使用(5)，不管决定用什么样的查询、什么样的用户，很不幸，例如，把 sort_buffer_size 从1MB增加到4MB并且在 max_connections 为 1000 的情况下，这其实很不明智也不可能

可能有的人想说，我们想要让服务器能保证100%不会耗尽内存，不管决定用什么样的查询、什么样的用户。很不幸，这其实很不明智也不可能，因为：

List of rarely considered MySQL Server Memory Requirements

以下是很少考虑的MySQL服务器内存需求

Thread buffers can be allocated more than once for each thread. Consider for example subqueries - each layer may need its own read_buffer,sort_buffer, tmp_table_size etc
每个线程可能会不止一次需要分配缓冲。 考虑到例如子查询 -- 每层都需要有自己的 read_buffer,sort_buffer, tmp_table_size 等。
Many variabes can be set per connection. So you can’t relay on global values if developers may use their local values to run some queries.
在每个连接中很多变量都可能需要重新设置。 如果开发者想设定自己的变量值来运行某些查询就不能继续使用全局值。
There can be mutiple key caches. Multiple key caches can be created to accomodate query executions
可能有多个索引缓存。 为了配合执行查询可能会创建多个索引缓存。
Query Parsing and optimization needs memory. This is usually small to be ignored but certain queries can have very large memory requrement for this step, especially specially crafted ones.
解析查询和优化都需要内存。 这些内存通常比较小，可以忽略，不过如果是某些查询在这个步骤中则需要大量内存，尤其是那些设计的比较特别的查询。
Stored Procedures. Compex stored procedures may require a lot of memory
存储过程。 复杂的存储过程可能会需要大量内存。
Prepared statements and Cursors. Single connection may have many prepared statements and cursors. Their number finally can be limited but each of them still can have very large memory consumption
准备查询语句以及游标。 单次链接可能会有很多的准备好的语句以及游标。它们的数量最后可以限定，但是仍然会消耗大量的内存。
Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from the start is stored. It is never purged and may be large if you have a lot of tables. It also means user having CREATE TABLE privilege should be able to run MySQL server out of memory
Innodb表缓存。 Innnodb表有自己的缓存，它保存了从一开始访问每个表的元数据。它们从未被清除过，如果有很多Innodb表的话，那么这个量就很大了。这也就意味着拥有 CREATE TABLE 权限的用户就可能把MySQL服务器的内存耗尽。
MyISAM buffers. MyISAM may allocate buffer which is large enough to contain largest record in the given table which is held until table is closed.
MyISAM缓冲。 MyISAM表可能会分配一个足以装下指定表最大记录的缓冲，而且这个缓冲直到表关闭了才释放。
Federated Storage Engine. This may have unbound memory requirements retriving result sets from remove queries.
FEDERATED存储引擎。 This may have unbound memory requirements retriving result sets from remove queries.
Blobs may require 3x time of memory. This is important if you’re deaing with large Blobs (your max_allowed_packet is large) Processing of 256MB of blob may require 768MB of memory.
Blobs可能需要3倍的内存。 这在处理很大（max_allowed_packet 的值较大）的Blobs数据时很重要，如果处理256MB的数据可能需要768MB的内存。
Storage Engines. In general storage engines may have their own per thread or global memory allocations which are not tuned as buffers. Watch for these especially now with many storage engines being released for MySQL by various parties.
存储引擎。 通常情况下，存储引擎会设置自己的每个线程的全局分配内存，它通常不能像缓存一样可以调节。现在应该通过各种方式来特别关注MySQL释放出来的存储引擎。
I do not pretend this to be complete list. On the contrary I’m quite sure I’ve missed something (drop me a note if you have something to add). But the main point is - there are a lot of memory consumers out where and trying to find peak possible usage for each is impractical - so my advice would be measure what you get in practice and how memory consumption reacts to changing various variables. For example you may find out increasing sort_buffer_size from 1MB to 4MB and 1000 max_connections increases peak memory consumption just 30MB not 3000MB as you might have counted.

我想这还不是完成的列表，相反地，我觉得还是漏掉了一些（如果你知道，请给我回复加上）。但主要的原因是 -- 找到每次内存消耗峰值是不切实际的，因此我的这些建议可以用来衡量一下你实际修改一些变量值产生的反应。例如，把 sort_buffer_size 从1MB增加到4MB并且在 max_connections 为 1000 的情况下，内存消耗增长峰值并不是你所计算的3000MB而是30MB。