Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Example analysis of MySQL memory management, memory allocator and operating system

2025-01-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL memory management, memory allocator and operating system example analysis, the article is very detailed, has a certain reference value, interested friends must read it!

When users encounter memory problems with any software (including MySQL), our first reaction is a memory leak. As this article shows, this is not always the case.

This article describes a bug about memory.

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.

All customers supported by percona are eligible for bug repair, but they also have different options. For example, vip customers can get the hotfiix version before the software patch is officially released, and advanced customers don't even need to use percona software, and we can push the patch upstream for them. But for percona products, all support levels are entitled to bug fixes.

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.

Even so, this does not mean that we will fix all unexpected situations, even if we accept it as a valid bug. One of the reasons for such a decision may be that although this unexpected situation is clearly wrong, it is indeed a product requirement for the percona product itself.

As a bug of a learning case

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

A recent good case is that the bug of PS-5312-- can be reproduced upstream and recorded in 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.

This report describes a situation where access to InnoDB's full-text index can lead to an increase in memory usage. This occurs in some full-text indexed queries, where memory continues to grow until it reaches its maximum and will not be released for a long time.

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

Yura Sorokin research from the Percona engineering team shows that this situation does not fall into the category of memory leaks.

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.

When InnoDB parses a full-text query, it creates a memory heap in the fts_query_phrase_search function that may grow to 80m. In addition, this process uses a large number of memory fragments resulting from discontiguous blocks (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.

At the function exit, these memory heaps are released. InnoDB does this for each block it allocates. At the end of function execution, a free () operation in a memory allocator library, such as malloc or jemalloc, is called. From the point of view of MySQL itself, this is all fine, there is no memory leak.

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.

However, it is true that the free () function should free memory when called, but it does not need to be returned to the operating system. If the memory allocator finds that these memory blocks need to be used soon, they will be reserved for use in the mysqld process. This explains why mysqld takes up a lot of memory after the work is done and the memory is freed.

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.

This is not a big problem in actual production and should not cause any accidents according to reason. But if you need to return memory to the operating system faster, you can try an unconventional memory allocator, like jemallolc. It has been proved to solve the problem of 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.

Another factor that improves memory management is the number of cpu cores: in tests, the more cpu cores, the faster the memory will be returned to the operating system. This may be because you have multiple CPU, and one of them can be used specifically as a memory allocator to release memory to the operating system.

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

The very first 5.6commit 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.

As our engineer Yura Sorokin discovered, the following two points illustrate the introduction of this flaw in the early implementation of InnoDB full-text indexing:

Introduction to InnoDB WL full-text indexing function in version 5.6 of MySQL: # 5538: InnoDB full-text search support-https://dev.mysql.com/worklog/task/?id=5538

Implementing WL # 5538 InnoDB full-text search support and merging-https://github.com/mysql/mysql-server/commit/b6169e2d944-has the same problem

Repair method

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.

We have two ways to fix this problem:

1. Modify the implementation of InnoDB full-text index

two。 Use custom memory libraries, such as jemalloc

Both methods have their own advantages and disadvantages.

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.

Method 1 means that we introduce the risk of incompatibility with the upstream of the software, which may lead to unknown errors in the new version. It also means completely rewriting part of the code for InnoDB full-text indexing, which is risky in the version of GA that users use.

Option 2 means we may hit flaws in the jemalloc library which is designed for performance and not for the safest memory allocation.

Method 2 means that we may hit bug in some jemalloc libraries designed for performance but not the safest memory allocation.

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.

So we have to choose one of these two imperfect methods.

Given that method one may lead to incompatibility between percona services and upstream, we prefer to use method two to solve the problem and look forward to upstream fixing the bug.

Conclusion

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.

If it is found that the mysqld process takes up a lot of memory, it does not necessarily mean that it is a memory leak. We can use memory detection in Performance Schema to understand how processes use allocated memory. You can also try to replace the memory library to better handle memory allocation and release. For information on how to configure LD_RELOAD, please refer to the corresponding pages of the MySQL user manual, mysqld-safe and using-system.

The above is all the contents of the article "MySQL memory Management, memory allocator and operating system sample Analysis". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 208

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report