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_size is 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 allocate bulk_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 (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。
