MySQL内存管理，内存分配器和利用系统

野性稳江山

导读
  

            做者：Sveta Smirnova
     翻译：郑志江
     校正：缓朝明
     本文 ：MySQL Memory Management, Memory Allocators and Operating System
         

  本文触及链接正在文终展现
  

---

  
  
When users experience memory usage issues with any software, including MySQL®, their first response is to think that it’s a symptom of a memory leak. As this story will show, this is not always the case.
This story is about a bug
当用户利用任何硬件(包罗MySQL)碰着内乱存成绩时，我们第一反响便是内乱存走漏。正如那篇文章所示，实在其实不老是如许。
那篇文章论述一个闭于内乱存的bug。
All Percona Support customers are eligible for bug fixes, but their options vary. For example, Advanced+ customers are offered a HotFix build prior to the public release of software with the patch. 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建复的资历，但他们也有差别的挑选。好比，vip客户正在硬件补钉正式公布之前就能够得到hotfiix版本，初级客户以至没有需求利用percona的硬件，我们也能够为他们把补钉推到上游。但关于取percona产物来讲，一切撑持品级皆有权获得bug建复。
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.
即使云云，那其实不意味着我们会建复一切的不测状况，即便我们承受这类状况为一个有用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——那个bug可正在上游复现并被纪录正在bugs.mysql.com/95065。
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.
那个报告论述了一种状况，当会见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 blocks ( mem_block_t ) which are not always used continuously and this, 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 allocator libraries, such 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, 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()函数被挪用时的确该当开释内乱存，但没有需求将其返回给操纵体系。假如内乱存分派器发明那些内乱存块即刻借需求被用到，则会将他们保留住持续用于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 jemalloc. The latter was proven to solve the issue with PS-5312.
那个正在实践消费中并非一个年夜成绩，按道理不该该形成任何变乱。可是假如您需求更快天将内乱存返回给操纵体系，您能够测验考试非传统的内乱存分派器，相同jemallolc。它被证实能够处理PS-5312的成绩。
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.
另外一个改进内乱存办理的身分是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 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所发明的一样，上面两面论述了InnoDB齐文索引的晚期完成引进了那个缺点：

• 5.6版本MySQL最早对InnoDB WL齐文索引功用引进的引见：#5538: InnoDB齐文搜刮撑持 – https://dev.mysql.com/worklog/task/?id=5538
  
• 完成WL #5538 InnoDB齐文搜刮撑持取兼并 - https://github.com/mysql/mysql-server/commit/b6169e2d944 - 也存正在一样的成绩成绩
  
  

建复办法
We have a few options to fix this:

• Change implementation of InnoDB fulltext index
  
• Use custom memory library like jemalloc
  

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 always risky in GA versions, used by our customers.
办法1 意味着我们引进了取硬件上游没有兼容性的风险，那大要会招致新版本中呈现已知的毛病。也意味着完全重写InnoDB齐文索引部门代码，那正在用户们利用的GA版本中是有风险的。
Option 2 means we may hit flaws in the jemalloc 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 2and look forward for the upstream 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 pages here and here.
假如发明mysqld历程占用内乱存很下，其实不代表必然是内乱存走漏。我们能够正在Performance Schema中利用内乱存检测去理解历程是怎样利用已分派的内乱存。也能够测验考试交换内乱存库去更好天处理内乱存分派取开释。闭于LD_RELOAD怎样设置，请查阅MySQL用户脚册对应页里 mysqld-safe战using-system。


链接1：
https://www.percona.com/services/support/support-tiers-mysql
链接2：
https://www.percona.com/services/support/support-tiers-mysql
链接3：
https://jira.percona.com/browse/PS-5312
链接4：
https://www.percona.com/blog/2019/05/02/mysql-memory-management-memory-allocators-and-operating-system/
链接5：
https://jira.percona.com/browse/PS-5312
链接6：
https://dev.mysql.com/doc/refman/8.0/en/mysqld-safe.html
链接7：
https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/using-systemd.html


END

---

         

      

扫码参加MySQL妙技Q群
          （群号：650149401）
       
   

         
  


免责声明：假如进犯了您的权益，请联络站少，我们会实时删除侵权内乱容，感谢协作！