How is join_buffer_size allocated?

liyihongcug

How is join_buffer_size allocated?


When examining MySQL configuration, we quite often want to know how various buffer sizes are used. This matters because some buffers (sort_buffer_size for example) are allocated to their full size immediately as soon as they are needed, but others are effectively a “max size” and the corresponding buffers are allocated only as big as needed (key_buffer_size). There are many examples of this. What about join_buffer_size?

I saw a my.cnf with a 128M join_buffer_size the other day and needed to research this quickly before I gave advice. The join buffer is a special case. Unlike many of the buffers that are allocated per-thread (i.e. per-connection), this one is allocated per-join-per-thread, in special cases. A join buffer is allocated to cache rows from each table in a join when the join can’t use an index. This is because we know that the nested loop is effectively going to do a table scan on the inner table — it has to, because there’s no index. If the query joins several tables this way, you’ll get several join buffers allocated, for example, this one will have two:

select * from a_table
join b_table on b.col1 = a.col1
join c_table on c.col2 = b.col2


You can see these un-indexed queries in SHOW STATUS as Select_full_join, and you want zero of them. Anyway, back to the question about how the buffer is allocated. Its meaning is actually “minimum join buffer size.” Here’s the code, in sql/sql_select.cc in 5.1.47 source:

14176 /*****************************************************************************
14177   Fill join cache with packed records
14178   Records are stored in tab->cache.buffer and last record in
14179   last record is stored with pointers to blobs to support very big
14180   records
14181 ******************************************************************************/
14182
14183 static int
14184 join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count)
14185 {
... snip ...
14268   cache->length=length+blobs*sizeof(char*);
14269   cache->blobs=blobs;
14270   *blob_ptr=0;             /* End sequentel */
14271   size=max(thd->variables.join_buff_size, cache->length);
14272   if (!(cache->buff=(uchar*) my_malloc(size,MYF(0))))
14273     DBUG_RETURN(1);           /* Don't use cache */ /* purecov: inspected */
14274   cache->end=cache->buff+size;
14275   reset_cache_write(cache);
14276   DBUG_RETURN(0);
14277 }


On line 14271 the server decides the size of the cache: it is the greater of the join_buffer_size or the size that’s been determined to be needed. And it’s allocated all at once. So a 128M join_buffer_size is indeed very bad!

And this leads me to an interesting possibility to run the server out of memory:

set session join_buffer_size = 1 << 30; # 1GB
select * from
(select 1 union select 1) as x1
join (select 1 union select 1) as x2
join....


That should try to allocate 1GB of memory for each join. If you execute this and nothing bad happens, you might be seeing this bug: http://bugs.mysql.com/55002
http://www.mysqlperformanceblog. ... fer_size-allocated/

liyihongcug

MYSQL性能调优概述 (2010-03-10 11:01) 转载
分类： MySQL

原文地址：http://bbs.linuxtone.org/thread-5152-1-1.html

优化思路
    硬件(高配置)
        ==>架构(服务分离,负载均衡)
           ==>单一服务器优化(系统,服务)
              ==>库结构(表合理,索引)
                 ==>DEBUG工具查询需要优化的地方

1 硬件优化
  a>内存--大内存,大内存位宽,尽量不要用SWAP ？
  b>硬盘--15000RPM,RAID5,RAID10
  c> CPU--64位,高主频,高缓存,高并行处理能力
  d>网络--标配的千兆网卡,尽可能在同一局域网,尽量避免诸如防火墙侧罗等不必要的开销
2 架构上的优化
  a>纵向拆分
     eg:一台服务器同时负责web,中间件,数据库多个角色.
        纵向拆分后就是数据库服务器专机专用,避免额外服务导致的性能下降和不稳定性
        若仍然无法满足需求,可以考虑在数据库和应用服务器之间加memcached
  b>横向拆分
     eg:主从同步,读写分离,负载均衡,高可用性集群.
        当单一MySQL数据库无法满足日益增长需求时,可以考虑在数据库这个逻辑层面
        增加多台服务器,以达到稳定,高效的目的.
3 操作系统级别的优化
  a>64位系统可以分给单个进程更多的内存,服务调优.
    禁用不必要启动的服务.修改文件描述符限制,留给更多的资源给MySQL
  b>文件系统调优:给数据仓库一个单独文件系统,推荐使用XFS,一般效率更高,更可靠
  c>可以考虑在挂载分区时启用notime选项
4 数据库服务的优化
  a>使用linux/bsd操作系统进行编译安装,对编译参数进行性能优化,精简不必要启用的功能
  b>合适的应用程序接口
  c>标尺每个表都不要太大,对大表做横切和纵切
    eg:比如我要取得某ID的lastlogin,完全可以做一张只有"ID"和"lastlogin"的小表,
         而非几十,几百列数据的并排大表
       因为对一个有1000万条记录的表做更新比对10个100万的表做更新一般来的要慢
  d>myisam引擎,表级锁,但所开销小,但影响范围大,适合读多写少的表,不支持失误日志;
       表锁定不存在死锁
  e>innodb引擎,行级锁,锁定行的开销要比锁定全表要大,但影响小,适合写操作比较频繁
       的数据表;行级锁可能存在死锁
5 my.cnf内参数的优化
  优化总原则:给MySQL的资源太小,则MySQL施展不开
             给MySQL的资源太多,可能会拖累整个OS
  a>总体资源占用的优化
    open_file_limit————mysqld可以打开的文件数量
    max_connections————允许并行客户端连接数
    max_connect_errors————允许主机错误连接数
    table_cache————每个连接允许打开表的数量
    max_allowed_packet————从服务器接收包的大小
    thread_cache_size————缓存多少个待用线程
  b>具体buffer的优化
    sort_buffer_size————每个线程可以分配的缓冲区的大小
    join_buffer_size————不走索引的join操作可分配的缓冲区的大小
    query_cache_size————为查询分配的缓存
    query_cache_limit————不缓存大于该限制的查询结果
    query_cache_min_res_unit————不缓存小于限制的查询结果
    tmp_table_size————内存内的临时性表超过该限制值,则写入硬盘
    binlog_cache_size————二进制日志文件的缓存
    key_buffer_size————myisam引擎的索引块公用缓冲区
    read_buffer_size————为从数据表顺序读取数据的读操作保留的缓冲区的长度
    innodb_additional_mem_pool_size————InnoDB用来存储数据目录信息&
      其它内部数据结构的内存池的大小.你应用程序里的表越多,需要在这里分配的越多内存
    innodb_buffer_pool_size————InnoDB用来缓存它的数据和索引的内存缓冲区的大小.
      理论上越大越好,但不要设置过大.否则由于无力内存的竞争可能导致操作系统换页颠覆
    innodb_data_file_path————innodb表空间的制定以及大小,初始表空间大一些可以减少
      日后自增加表空间的系统开销
    innodb_thread_concurrency————在InnoDB核心内的允许线程数量
    innodb_log_buffer_size————InnoDB用来往磁盘上的日志文件写操作的缓冲区的大小.
      当日志大小超过该限制时,日志会被写入磁盘,比写入内存的I/O开销大
    innodb_log_file_size————每个日志文件的大小
    max_allowed_packet————包服务所能处理的请求包的最大大小以及服务所能处理的最大
      请求大小
6 查询优化
  a>建表时表结构要合理,每个表不宜过大.在任何情况下均应使用最精确的类型.
      eg:如果ID列用Int是一个好主意,而用text类型则是个蠢办法
         TIME列酌情使用DATE或DATETIME
  b>索引,所有的查询都是走科学的索引,单个索引命中率低时使用联合索引
  c>查询时尽量减少逻辑运算(与运算,或运算.大于小于某值的运算)
  d>减少不当的查询语句,不要查询应用中不需要的列.
      eg:select * from 等操作
  e>减少事务包的大小
  f>将多个小的查询适当合并成一个大的查询,减少每次建立/关闭查询时的开销
  g>将某些国语复杂的查询拆分成多个小查询(与上一条恰好相反)
  h>建立和优化存储过程来代替大量的外部程序交互
7 DEBUG工具
  a>vmstat————vmstat命令报告关于内核线程,虚拟内存,磁盘,陷阱和CPU活动的统计信息
  b>iostat————iostat命令报告CPU,硬盘灯设备的输入输出情况,可能需要安装sysstat rpm包
  c>top————动态显示当前系统的资源占用,与iostat比,top更侧重于进程
  d>free————显示内存和SWAP占用情况
  e>show processlist————显示当前运行或等待的线程,判断哪些查询语句总是处于等待状态
  f>EXPLAIN————“EXPLAIN + SQL语句"查看索引使用情况
  g>show create table + "table_name" ————查看指定表的表结构
  h>select count(distinct "row_name") from "table_name"; 查看列内数据的唯一性,确定给哪一列创建索引
  i>create index 创建索引,并用show processlist,top 观察创建索引后的效果.
http://blog.chinaunix.net/space. ... o=blog&id=75659

http://www.mysqlperformanceblog. ... server-memory-usage
译者：叶金荣(http://imysql.cn/node/97)

Every so often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use.

经常有人问我配置MySQL时该如何估算内存的消耗。那么该使用什么公式来计算呢？

The reasons to worry about memory usage are quite understandable. If you configure MySQL Server so it uses too small amount of memory it will likey perform suboptimally. If you however configure it so it consumes too much memory it may be crashing , failing to execute queries or make operation to swap seriously slowing down. On now legacy 32bit platforms you could also run out of address space so that had to be watched as well.
Having said so, I do not think looking for the secret fomula to compute your possible memory usage is the right approach to this problem. The reasons are – this formula is very complex nowadays and what is even more important “theoretically possible” maximum it provides have nothing to do with real memory consumptions. In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more. Furthermore there is no easy “overcommit factor” you can use – it really depends on application and configuration. Some applications will drive server to 10% of theoretical memory consumptions others only to 1%.

关心内存怎么使用的原因是可以理解的。如果配置MySQL服务器使用太少的内存会导致性能不是最优的；如果配置了太多的内存则会导致崩溃，无法执行查询或者导致交换操作严重变慢。在现在的32位平台下，仍有可能把所有的地址空间都用完了，因此需要监视着。
话虽如此，但我并不觉得找到什么可以计算内存使用的秘诀公式就能很好地解决这个问题。原因有 — 如今这个公式已经很复杂了，更重要的是，通过它计算得到的值只是“理论可能”并不是真正消耗的值。事实上，有8GB内存的常规服务器经常能运行到最大的理论值 — 100GB甚至更高。此外，你轻易不会使用到“超额因素” — 它实际上依赖于应用以及配置。一些应用可能需要理论内存的 10% 而有些仅需 1%。

So what could you do instead ? First take a look at global buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to use for your table. Take this number add to it number of memory Operation System and other applications need, add might be 32MB more for MySQL Server code and various small static buffers. This is memory which you can consider used when you just start MySQL Server. The rest of memory is available for connections. For exampe with 8GB server you might have everything listed adding up to 6GB, so you have 2GB left for your threads.

那么，我们可以做什么呢？首先，来看看那些在启动时就需要分配并且总是存在的全局缓冲 –key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size。如果你大量地使用MyISAM表，那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。把这些也都加到操作系统和应用程序所需的内存值之中，可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在 MySQL服务器启动时所需的内存。其他剩下的内存用于连接。例如有8GB内存的服务器，可能监听所有的服务就用了6GB的内存，剩下的2GB内存则留下来给线程使用。

Each thread connecting to MySQL server will needs its own buffers. About 256K is allocated at once even if thread is idle – they are used by default thread stack, net buffer etc. If transaction is started some more space can add up. Running small queries might only barely increase memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size of memory might be allocated. But they are only allocated upon the need and freed once given stage of query is done. Some of them are allocated as single chunk at once others, for example tmp_table_sizeis rather maximum amount of memory MySQL will allocate for this operation. Note it is more complicated than once may think – multiple buffers of the same type might be allocated for exampe to handle subqueries. For some special queries memory usage might be even larger – bulk inserts may allocatebulk_insert_buffer_size bytes of memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands.

每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时 — 它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块，例如 tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 — 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 — 如果在MyISAM表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存。执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

For OLTP applications with simple queries memory consumption is often less than 1MB per thread with default buffers, and you really do not need to increase per thread buffers unless you have complex queries. Sorting 10 rows will be as fast with 1MB sort buffer as with 16MB (actually 16MB might be even slower but it is other story).

只有简单查询OLTP应用的内存消耗经常是使用默认缓冲的每个线程小于1MB，除非需要使用复杂的查询否则无需增加每个线程的缓冲大小。使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的（实际上16MB可能会更慢，不过这是其他方面的事了）。

Another approach you may take is to come up with amount of memory you want MySQL Server to consume at peak. This can be easily computed by memory needed for OS, File Cache and other applications. For 32bit envinronment you also should keep 32bit limits into account and probably limit “mysqld” size to about 2.5GB (exact number depens on a lot of factors). Now you can use “ps aux” to see VSZ – Virtual Memory allocated by MySQL process. You can also look at “Resident Memory” but I find it less helpful as it may down because of swapping – not what you would like to see. Monitor how the value changes so you know memory requirements with current settings and increase/decrease values appropriately.

另外，就是找出MySQL服务器内存消耗的峰值。这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。在32位环境下，还需要考虑到32位的限制，限制 “mysqld” 的值大约为2.5G（实际上还要考虑到很多其他因素）。现在运行 “ps aux” 命令来查看 VSZ 的值 — MySQL 进程分配的虚拟内存。也可以查看 “Resident Memory” 的值，不过我想它可能没多大用处，因为它会由于交换而变小 — 这并不是你想看到的。监视着内存变化的值，就能知道是需要增加/减少当前的内存值了。

Some may say, Hey we want to have 100% guarantee our server will never run out of memory, no matter which queries or users will decide to run. Unfortunately this is as much close to impossible to be impractical. Here is why:

可能有的人想说，我们想要让服务器能保证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 (yourmax_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_connectionsincreases peak memory consumption just 30MB not 3000MB as you might have counted.

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

http://lightgis.com/2011/mysql-server-memory-usage.html

liyihongcug

http://lightgis.com/2011/mysql-server-memory-usage.html http://www.mysqlperformanceblog. ... server-memory-usage
译者：叶金荣(http://imysql.cn/node/97)

Every so often people ask me the question how should they estimate memory consumption by MySQL Server in given configuration. What is the formula they could use.

经常有人问我配置MySQL时该如何估算内存的消耗。那么该使用什么公式来计算呢？

The reasons to worry about memory usage are quite understandable. If you configure MySQL Server so it uses too small amount of memory it will likey perform suboptimally. If you however configure it so it consumes too much memory it may be crashing , failing to execute queries or make operation to swap seriously slowing down. On now legacy 32bit platforms you could also run out of address space so that had to be watched as well.
Having said so, I do not think looking for the secret fomula to compute your possible memory usage is the right approach to this problem. The reasons are – this formula is very complex nowadays and what is even more important “theoretically possible” maximum it provides have nothing to do with real memory consumptions. In fact typical server with 8GB of memory will often run with maximum theoretical memory usage of 100GB or more. Furthermore there is no easy “overcommit factor” you can use – it really depends on application and configuration. Some applications will drive server to 10% of theoretical memory consumptions others only to 1%.

关心内存怎么使用的原因是可以理解的。如果配置MySQL服务器使用太少的内存会导致性能不是最优的；如果配置了太多的内存则会导致崩溃，无法执行查询或者导致交换操作严重变慢。在现在的32位平台下，仍有可能把所有的地址空间都用完了，因此需要监视着。
话虽如此，但我并不觉得找到什么可以计算内存使用的秘诀公式就能很好地解决这个问题。原因有 — 如今这个公式已经很复杂了，更重要的是，通过它计算得到的值只是“理论可能”并不是真正消耗的值。事实上，有8GB内存的常规服务器经常能运行到最大的理论值 — 100GB甚至更高。此外，你轻易不会使用到“超额因素” — 它实际上依赖于应用以及配置。一些应用可能需要理论内存的 10% 而有些仅需 1%。

So what could you do instead ? First take a look at global buffers which are allocated at start and always where – these are key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. If you’re using MyISAM seriously you can also add the size of Operation System cache you would like MySQL to use for your table. Take this number add to it number of memory Operation System and other applications need, add might be 32MB more for MySQL Server code and various small static buffers. This is memory which you can consider used when you just start MySQL Server. The rest of memory is available for connections. For exampe with 8GB server you might have everything listed adding up to 6GB, so you have 2GB left for your threads.

那么，我们可以做什么呢？首先，来看看那些在启动时就需要分配并且总是存在的全局缓冲 –key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size。如果你大量地使用MyISAM表，那么你也可以增加操作系统的缓存空间使得MySQL也能用得着。把这些也都加到操作系统和应用程序所需的内存值之中，可能需要增加32MB甚至更多的内存给MySQL服务器代码以及各种不同的小静态缓冲。这些就是你需要考虑的在 MySQL服务器启动时所需的内存。其他剩下的内存用于连接。例如有8GB内存的服务器，可能监听所有的服务就用了6GB的内存，剩下的2GB内存则留下来给线程使用。

Each thread connecting to MySQL server will needs its own buffers. About 256K is allocated at once even if thread is idle – they are used by default thread stack, net buffer etc. If transaction is started some more space can add up. Running small queries might only barely increase memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size of memory might be allocated. But they are only allocated upon the need and freed once given stage of query is done. Some of them are allocated as single chunk at once others, for example tmp_table_sizeis rather maximum amount of memory MySQL will allocate for this operation. Note it is more complicated than once may think – multiple buffers of the same type might be allocated for exampe to handle subqueries. For some special queries memory usage might be even larger – bulk inserts may allocatebulk_insert_buffer_size bytes of memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands.

每个连接到MySQL服务器的线程都需要有自己的缓冲。大概需要立刻分配256K，甚至在线程空闲时 — 它们使用默认的线程堆栈，网络缓存等。事务开始之后，则需要增加更多的空间。运行较小的查询可能仅给指定的线程增加少量的内存消耗，然而如果对数据表做复杂的操作例如扫描、排序或者需要临时表，则需分配大约 read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size 大小的内存空间。不过它们只是在需要的时候才分配，并且在那些操作做完之后就释放了。有的是立刻分配成单独的组块，例如 tmp_table_size 可能高达MySQL所能分配给这个操作的最大内存空间了。注意，这里需要考虑的不只有一点 — 可能会分配多个同一种类型的缓存，例如用来处理子查询。一些特殊的查询的内存使用量可能更大 — 如果在MyISAM表上做成批的插入时需要分配 bulk_insert_buffer_size 大小的内存。执行 ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分配 myisam_sort_buffer_size 大小的内存。

For OLTP applications with simple queries memory consumption is often less than 1MB per thread with default buffers, and you really do not need to increase per thread buffers unless you have complex queries. Sorting 10 rows will be as fast with 1MB sort buffer as with 16MB (actually 16MB might be even slower but it is other story).

只有简单查询OLTP应用的内存消耗经常是使用默认缓冲的每个线程小于1MB，除非需要使用复杂的查询否则无需增加每个线程的缓冲大小。使用1MB的缓冲来对10行记录进行排序和用16MB的缓冲基本是一样快的（实际上16MB可能会更慢，不过这是其他方面的事了）。

Another approach you may take is to come up with amount of memory you want MySQL Server to consume at peak. This can be easily computed by memory needed for OS, File Cache and other applications. For 32bit envinronment you also should keep 32bit limits into account and probably limit “mysqld” size to about 2.5GB (exact number depens on a lot of factors). Now you can use “ps aux” to see VSZ – Virtual Memory allocated by MySQL process. You can also look at “Resident Memory” but I find it less helpful as it may down because of swapping – not what you would like to see. Monitor how the value changes so you know memory requirements with current settings and increase/decrease values appropriately.

另外，就是找出MySQL服务器内存消耗的峰值。这很容易就能计算出操作系统所需的内存、文件缓存以及其他应用。在32位环境下，还需要考虑到32位的限制，限制 “mysqld” 的值大约为2.5G（实际上还要考虑到很多其他因素）。现在运行 “ps aux” 命令来查看 VSZ 的值 — MySQL 进程分配的虚拟内存。也可以查看 “Resident Memory” 的值，不过我想它可能没多大用处，因为它会由于交换而变小 — 这并不是你想看到的。监视着内存变化的值，就能知道是需要增加/减少当前的内存值了。

Some may say, Hey we want to have 100% guarantee our server will never run out of memory, no matter which queries or users will decide to run. Unfortunately this is as much close to impossible to be impractical. Here is why:

可能有的人想说，我们想要让服务器能保证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 (yourmax_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_connectionsincreases peak memory consumption just 30MB not 3000MB as you might have counted.

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