閒談 MySQL記憶體管理，記憶體分配器和作業系統

推薦（免費）：mysql影片教學

當使用者使用任何軟體(包括MySQL)碰到記憶體問題時，我們第一個反應就是記憶體洩漏。正如這篇文章所示，其實並不總是這樣。

這篇文章闡述一個關於記憶體的bug。

All Percona Support customers are eligible for bug fixes、 2>Premium customers do not even have to use Percona software: we may port our patches to upstream for them. But for Percona products all Support levels have the right to have a fix.##per#ix.##percona.都有獲得bug修復的資格，但他們也有不同的選擇。例如，vip客戶在軟體修補程式正式發布之前就可以獲得hotfiix版本，高級客戶甚至不需要使用percona的軟體，我們也可以為他們把補丁推到上游。但對於與percona產品來說，所有支援等級都有權利得到bug修復。

Even so, this does not mean we will fix every unexpected behavior, even if we accept 那Percona products, this is still a feature request.

即便如此，這並不意味著我們會修復所有的意外情況，即使我們接受這種情況為一個有效bug。做出這樣的決定的原因之一可能是這個意外情況雖然很明確是錯誤的，但對於percona產品本身來說確實一個產品需求

作為學習案例的一個bug

# A good recent example of such a case is PS-5312 – the bug is repeatable with upstream and reported at bugs.mysql.com/95065

#最近一個很好的案例是PS-5312<連結33> —這個bug可在上游復現並被記錄在bugs.mysql.com/95065<連結4>。

This reports a situation whereby access to InnoDB fulltext indexes leads to growth in memory usage. It starts when someone queries a fulltext index, grows until a maximum, and is not freed for quite long#.

這個報告闡述了一個情況，當存取InnoDB的全文索引的時候會導致記憶體使用量成長。這種情況出現在一些全文索引的查詢，記憶體會持續成長直到達到最大值，而且很長時間不會釋放。

Yura Sorokin from the Percona Engineering Team investigated if this is a memory leak and found that it is not.

來自Percona工程團隊的Yura Sorokin研究表明，這種情況並不屬於內存洩漏範疇。

When InnoDB resolves a fulltext query, it creates a memory heap in the function fts_query_phrase_search This heap may grow up to 80MB. Additionally, it has a big number of blon (s_bumber of blokly )_bare's this number of blocks cons_boo connectron_s _blockcons conn ad blon _blockconn _b , in turn, leads to memory fragmentation.

當InnoDB解析一個全文查詢時，它會在

fts_query_phrase_search

函數中創建一個內存堆，這個堆可能增長到80M。另外，這個過程也會使用到大量非連續區塊(

mem_block_t)進而產生的記憶體碎片。 In the function exit , the memory heap is freed. InnoDB does this for each of the allocated blocks. At the end of the function, it calls free() which belongs to one of the memory aluchator libraries, locongs to one of the memory aluchator libraries, locongs as malloc or jemalloc. From the mysqld point of view, everything is done correctly: there is no memory leak.

#在函數出口，這些記憶體堆會被釋放。 InnoDB會為其指派的每一個區塊做這個操作。在函數執行結束時，呼叫一個記憶體分配器庫中的

free()

操作，例如

malloc或jemalloc。從MySQL本身來看，這都是沒問題的，不存在記憶體洩漏。 However while free() should release memory when called, it is not required to return it back to the operating system. If the memory allocator decides that the same memory blocks will be required soon, may still for the mysqld process. This explains why you might see that mysqld still uses a lot of memory after the job is finished and all de-allocations are done.

然而，free()##說內存，但不需要將其返回給作業系統。如果記憶體分配器發現這些記憶體區塊馬上還需要被用到，則會將他們保留住繼續用於mysqld進程。這就解釋了為什麼mysqld在完成工作及釋放記憶體都結束後還會佔用大量記憶體。

This in practice is not a big issue and should not cause any harm. But if you need the memory to be returned to the operating system quicker, you could try alternative memory allocators, such as  loc. The latter the issue with PS-5312.

這個在實際生產中並不是一個大問題，按道理不應該造成任何事故。但如果你需要更快地將記憶體回傳給作業系統，你可以嘗試非傳統的記憶體分配器，類似jemallolc。它被證明可以解決PS-5312<連結5>的問題。

Another factor which improves memory management is the number of CPU cores: the more we used for the test, the faster the memory was returned to the operating system. This, probably, can be explained by the fact that ifly, can be explained by the fact that ifly, can be explained by the fact that ifly, can be explained by the fact that ifly, can be explained by the fact that ifly, can be explained by the fact that if you have multiple CPUs, then the memory allocator can dedicate one of them just for releasing memory to the operating system.

另一個改善記憶體管理的因素是cpu內核數量：在測試中，cpu核數越多，記憶體回傳給作業系統的速度會越快。這可能是你擁有多個CPU，而其中一個可專門用作記憶體分配器釋放記憶體給作業系統。

The very first implementation of InnoDB full text indexes introduced this flaw. As our engineer Yura Sorokin found:

• #The very first 5.6 commit

• The very first 5.6 commit

The 1 Search Functionality for InnoDB WL#5538: InnoDB Full-Text Search Support – https://dev.mysql.com/worklog/task/?id=5538

• Implement WL #5538 InnoDB Full-Text Search Support, merge – https://github.com/mysql/mysql-server/commit/b6169e2d944 – also has this problem.

• 就像我們的工程師

  Yura Sorokin
所發現的一樣，下面兩點闡述了InnoDB全文索引的早期實作引入了這個缺陷：

5.6版本MySQL最早對InnoDB WL全文索引功能引入的介紹：#5538: InnoDB全文搜尋支援– https://dev.mysql.com/worklog/task/?id=5538

1. ##實作WL #5538 InnoDB全文搜索支援與合併- https://github.com/mysql/mysql-server/commit/b6169e2d944 - 也有相同的問題問題

2. #We have a few options to fix this:

Change implementation of InnoDB fulltext index

Use custom memory library like jemalloc

########################Use library like jemalloc#######################################################################################1 #####Both have their advantages and disadvantages.######我們有兩種方法來修復這個問題：######1.修改InnoDB全文索引的實作######2.使用自訂記憶體庫，例如###jemalloc#########這兩種方法都有各自的優缺點。 ######Option 1 means we are introducing an incompatibility with upstream, which may lead to strange bugs in future versions. This also means a full rewrite of the InnoDB fulltext code which is alwaysions in GAed is always .######方法1 意味著我們引入了與軟體上游不相容性的風險，這可能會導致新版本中出現未知的錯誤。也意味著徹底重寫InnoDB全文索引部分程式碼，這在使用者使用的GA版本中是有風險的。 ######Option 2 means we may hit flaws in the jemalloc<連結5> library which is designed for performance and not for the safest memory allocation.#######2 則表示我們可能會命中一些jemalloc庫中專門為效能設計但不是最安全的記憶體分配的bug。 ######So we have to choose between these two not ideal solutions.######Since option 1 may lead to a situation when Percona Server will be incompatible with upstream, we prefer option for andstream fix of this bug.######因此我們不得不在這兩個不完美的方法中選擇一個。 ######鑑於方法一可能導致percona服務與上游的不相容，我們更傾向於用方法二來解決問題，並期待上游修復這個bug。 #########結論#########If you are seeing a high memory usage by the mysqld process, it is not always a symptom of a memory leak. You can use memory instrumentation in Performance Schema to find out how allocated memory is used. Try alternative memory libraries for better processing of allocations and freeing of memory. Search the user manual for LD_PRELOADto find out how to set it up at these#here.如果發現mysqld進程佔用記憶體很高，並不代表一定是記憶體洩漏。我們可以在Performance Schema中使用記憶體檢測來了解進程是如何使用已分配的記憶體。也可以嘗試替換記憶體庫來更好地處理記憶體分配與釋放。關於LD_RELOAD如何配置，請查閱MySQL使用者手冊對應頁面 mysqld-safe<連結6>和using-system<連結7>。 ###

更多程式相關知識，請造訪：程式設計教學！ ！

以上是閒談 MySQL記憶體管理，記憶體分配器和作業系統的詳細內容。更多資訊請關注PHP中文網其他相關文章！