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

目前創(chuàng)新互聯(lián)已為成百上千的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管運(yùn)營(yíng)、企業(yè)網(wǎng)站設(shè)計(jì)、臨川網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。

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

這篇文章闡述一個(gè)關(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版本，高級(jí)客戶甚至不需要使用percona的軟件，我們也可以為他們把補(bǔ)丁推到上游。但對(duì)于與percona產(chǎn)品來(lái)說(shuō)，所有支持等級(jí)都有權(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.

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

作為學(xué)習(xí)案例的一個(gè)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

最近一個(gè)很好的案例是 PS-5312<鏈接3>——這個(gè)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.

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

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

來(lái)自Percona工程團(tuán)隊(duì)的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解析一個(gè)全文查詢(xún)時(shí)，它會(huì)在fts_query_phrase_search函數(shù)中創(chuàng)建一個(gè)內(nèi)存堆，這個(gè)堆可能增長(zhǎng)到80M。另外，這個(gè)過(guò)程還會(huì)使用到大量非連續(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)存堆會(huì)被釋放。InnoDB會(huì)為其分配的每一個(gè)塊做這個(gè)操作。在函數(shù)執(zhí)行結(jié)束時(shí)，調(diào)用一個(gè)內(nèi)存分配器庫(kù)中的free()操作，比如malloc或者jemalloc。從MySQL本身來(lái)看，這都是沒(méi)問(wèn)題的，不存在內(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í)確實(shí)應(yīng)該釋放內(nèi)存，但不需要將其返回給操作系統(tǒng)。如果內(nèi)存分配器發(fā)現(xiàn)這些內(nèi)存塊馬上還需要被用到，則會(huì)將他們保留住繼續(xù)用于mysqld進(jìn)程。這就解釋了為什么mysqld在完成工作及釋放內(nèi)存都結(jié)束后還會(huì)占用大量?jī)?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.

這個(gè)在實(shí)際生產(chǎn)中并不是一個(gè)大問(wèn)題，按道理不應(yīng)該造成任何事故。但是如果你需要更快地將內(nèi)存返回給操作系統(tǒng)，你可以嘗試非傳統(tǒng)的內(nèi)存分配器，類(lèi)似jemallolc。它被證明可以解決PS-5312<鏈接5>的問(wèn)題。

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.

另一個(gè)改善內(nèi)存管理的因素是cpu內(nèi)核數(shù)量：在測(cè)試中，cpu核數(shù)越多，內(nèi)存返回給操作系統(tǒng)的速度會(huì)越快。這可能是你擁有多個(gè)CPU，而其中一個(gè)可專(zhuān)門(mén)用作內(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 – /tupian/20230522/b6169e2d944 – also has this problem.

正如我們的工程師Yura Sorokin所發(fā)現(xiàn)的一樣，下面兩點(diǎn)闡述了InnoDB全文索引的早期實(shí)現(xiàn)引入了這個(gè)缺陷：

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

• 實(shí)現(xiàn)WL #5538 InnoDB全文搜索支持與合并 - /tupian/20230522/b6169e2d944 - 也存在同樣的問(wèn)題問(wèn)題

修復(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.

我們有兩種方法來(lái)修復(fù)這個(gè)問(wèn)題：

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

2.使用自定義內(nèi)存庫(kù)，例如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)險(xiǎn)，這可能會(huì)導(dǎo)致新版本中出現(xiàn)未知的錯(cuò)誤。也意味著徹底重寫(xiě)InnoDB全文索引部分代碼，這在用戶們使用的GA版本中是有風(fēng)險(xiǎn)的。

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 則意味著我們可能會(huì)命中一些jemalloc庫(kù)中專(zhuān)門(mén)為性能設(shè)計(jì)但不是最安全的內(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.

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

鑒于方法一可能導(dǎo)致percona服務(wù)與上游的不兼容，我們更傾向于用方法二來(lái)解決問(wèn)題，并期待著上游修復(fù)這個(gè)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)存檢測(cè)來(lái)了解進(jìn)程是如何使用已分配的內(nèi)存。也可以嘗試替換內(nèi)存庫(kù)來(lái)更好地處理內(nèi)存分配與釋放。關(guān)于LD_RELOAD如何配置，請(qǐng)查閱MySQL用戶手冊(cè)對(duì)應(yīng)頁(yè)面 mysqld-safe<鏈接6>和using-system<鏈接7>。

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

名稱(chēng)欄目：MySQL內(nèi)存管理，內(nèi)存分配器和操作系統(tǒng)的示例分析-創(chuàng)新互聯(lián)
分享地址：http://vcdvsql.cn/article16/cscodg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、企業(yè)網(wǎng)站制作、網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站改版、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)