MySQL內(nèi)存管理，內(nèi)存分配器和操作系統(tǒng)的示例分析

這篇文章主要介紹MySQL內(nèi)存管理，內(nèi)存分配器和操作系統(tǒng)的示例分析，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

當(dāng)用戶使用任何軟件(包括MySQL)碰到內(nèi)存問題時，我們第一反應(yīng)就是內(nèi)存泄漏。正如這篇文章所示，其實(shí)并不總是這樣。

這篇文章闡述一個關(guān)于內(nèi)存的bug。

All Percona Support customers are eligible for bug fixes, but their options vary. For example, Advanced+ <鏈接1>customers are offered a HotFix build prior to the public release of software with the patch. <鏈接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.

所有percona所支持的客戶都有獲得bug修復(fù)的資格，但他們也有不同的選擇。比如，vip客戶在軟件補(bǔ)丁正式發(fā)布之前就可以獲得hotfiix版本，高級客戶甚至不需要使用percona的軟件，我們也可以為他們把補(bǔ)丁推到上游。但對于與percona產(chǎn)品來說，所有支持等級都有權(quán)得到bug修復(fù)。

Even so, this does not mean we will fix every unexpected behavior, even if we accept that behavior to be a valid bug. One of the reasons for such a decision might be that while the behavior is clearly wrong for Percona products, this is still a feature request.

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

作為學(xué)習(xí)案例的一個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<鏈接3>——這個bug可在上游復(fù)現(xiàn)并被記錄在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 a long time.

這個報告闡述了一種情況，當(dāng)訪問InnoDB的全文索引的時候會導(dǎo)致內(nèi)存使用量增長。這種情況出現(xiàn)在一些全文索引的查詢，內(nèi)存會持續(xù)增長直到達(dá)到最大值，并且很長時間不會釋放。

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

來自Percona工程團(tuán)隊的Yura Sorokin研究表明，這種情況并不屬于內(nèi)存泄漏范疇。

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 blocks ( mem_block_t ) which are not always used continuously and this, in turn, leads to memory fragmentation.

當(dāng)InnoDB解析一個全文查詢時，它會在fts_query_phrase_search函數(shù)中創(chuàng)建一個內(nèi)存堆，這個堆可能增長到80M。另外，這個過程還會使用到大量非連續(xù)塊(mem_block_t)進(jìn)而產(chǎn)生的內(nèi)存碎片。

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 allocator libraries, such as malloc or jemalloc. From the mysqld point of view, everything is done correctly: there is no memory leak.

在函數(shù)出口，這些內(nèi)存堆會被釋放。InnoDB會為其分配的每一個塊做這個操作。在函數(shù)執(zhí)行結(jié)束時，調(diào)用一個內(nèi)存分配器庫中的free()操作，比如malloc或者jemalloc。從MySQL本身來看，這都是沒問題的，不存在內(nèi)存泄漏。

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, it may still keep them 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()函數(shù)被調(diào)用時確實(shí)應(yīng)該釋放內(nèi)存，但不需要將其返回給操作系統(tǒng)。如果內(nèi)存分配器發(fā)現(xiàn)這些內(nèi)存塊馬上還需要被用到，則會將他們保留住繼續(xù)用于mysqld進(jìn)程。這就解釋了為什么mysqld在完成工作及釋放內(nèi)存都結(jié)束后還會占用大量內(nèi)存。

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 jemalloc. The latter was proven to solve the issue with PS-5312.

這個在實(shí)際生產(chǎn)中并不是一個大問題，按道理不應(yīng)該造成任何事故。但是如果你需要更快地將內(nèi)存返回給操作系統(tǒng)，你可以嘗試非傳統(tǒng)的內(nèi)存分配器，類似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 if you have multiple CPUs, then the memory allocator can dedicate one of them just for releasing memory to the operating system.

另一個改善內(nèi)存管理的因素是cpu內(nèi)核數(shù)量：在測試中，cpu核數(shù)越多，內(nèi)存返回給操作系統(tǒng)的速度會越快。這可能是你擁有多個CPU，而其中一個可專門用作內(nèi)存分配器釋放內(nèi)存給操作系統(tǒng)。

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

• The very first 5.6 commit which introduces Full Text 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所發(fā)現(xiàn)的一樣，下面兩點(diǎn)闡述了InnoDB全文索引的早期實(shí)現(xiàn)引入了這個缺陷：

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

• 實(shí)現(xiàn)WL #5538 InnoDB全文搜索支持與合并 - https://github.com/mysql/mysql-server/commit/b6169e2d944 - 也存在同樣的問題問題

修復(fù)方法

We have a few options to fix this:

1. Change implementation of InnoDB fulltext index

2. Use custom memory library like jemalloc

Both have their advantages and disadvantages.

我們有兩種方法來修復(fù)這個問題：

1.修改InnoDB全文索引的實(shí)現(xiàn)

2.使用自定義內(nèi)存庫，例如jemalloc

這兩種方法都有各自的優(yōu)缺點(diǎn)。

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 always risky in GA versions, used by our customers.

方法1 意味著我們引入了與軟件上游不兼容性的風(fēng)險，這可能會導(dǎo)致新版本中出現(xiàn)未知的錯誤。也意味著徹底重寫InnoDB全文索引部分代碼，這在用戶們使用的GA版本中是有風(fēng)險的。

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庫中專門為性能設(shè)計但不是最安全的內(nèi)存分配的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 2and look forward for the upstream fix of this bug.

因此我們不得不在這兩個并不完美的方法中選擇一個。

鑒于方法一可能導(dǎo)致percona服務(wù)與上游的不兼容，我們更傾向于用方法二來解決問題，并期待著上游修復(fù)這個bug。

結(jié)論

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 pages here and here.

如果發(fā)現(xiàn)mysqld進(jìn)程占用內(nèi)存很高，并不代表一定是內(nèi)存泄漏。我們可以在Performance Schema中使用內(nèi)存檢測來了解進(jìn)程是如何使用已分配的內(nèi)存。也可以嘗試替換內(nèi)存庫來更好地處理內(nèi)存分配與釋放。關(guān)于LD_RELOAD如何配置，請查閱MySQL用戶手冊對應(yīng)頁面 mysqld-safe<鏈接6>和using-system<鏈接7>。

以上是“MySQL內(nèi)存管理，內(nèi)存分配器和操作系統(tǒng)的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！