Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "MYSQL memory mechanism analysis and monitoring". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1 Why should there be buffer pool or memory, and what is the purpose of caching in the MYSQL database?

It is estimated that many people will answer quickly, performance. Yes, the memory in the database is actually for performance, imagine if the speed of the disk is as fast as memory, do you still need memory, buffer pool, cache?

2 what role does Buffer pool cache and memory play in MYSQL?

First of all, all data processing and changes are in memory, and the data on disk is only for persistence, so the most complex processing relationships, such as deadlocks, block, mutexes, data dictionaries, bulabula, and so on, exist in memory. So when asked about deadlock, don't tell anyone that it's because the disk is slow or something, because it doesn't happen on disk, but in memory.

3 is the data page of disk related to the data page of memory?

Yes, it does matter. The data pages of the disk are mapped to memory, which involves another thing, that is, the size of the page page. In PG, the page size of the disk can be adjusted. MYSQL defaults to 16KB, ORACLE is 8KB, and SQL SERVER is also 8KB. In other words, some database pages are resizable, some are not, and their disk pages are mapped directly to memory.

4 what is the impact of page size on database performance?

In fact, I have been looking for this question in books and google, but there is no answer or convincing answer. Try to analyze it yourself. Take the page 16KB of MYSQL as an example. If the data read at one time is 8KB, then 16KB can only use half of it. If you are reading 8KB data later, you can use the other half of memory, but if you read 4KB data, you can use the other half of memory. It may be a waste of 4KB's cache, so the allocated pages are often wasted. Some people say that 8KB's is smaller than 16KB's, so it wastes less space, but from another point of view, if I want to read 12KB's data, will it be divided into two page? Is it more efficient than the data that a page can carry, and a data page is divided into two, is there anything else that needs to be done, such as connections, etc.?

So the size of a data page varies, not black and white, and needs to be analyzed according to specific problems. If your MYSQL uses compressed pages, 16KB or larger pages may be better. So sometimes, the pages of the database can be adjusted, according to the business or according to certain characteristics to set, will ensure better performance, while databases such as SQL SERVER, ORACLE can not be adjusted.

5 is there an exit mechanism for data in memory?

The answer is yes, the data in the memory of any database has an exit mechanism, otherwise the memory should be the same as the size of the data, in the current hardware technology, this is unlikely, so memory must have an exit mechanism. From another thing, it also reflects the changes in hardware, in fact, the software design of the database is linked or partially decisive.

6 what do you do when managing memory pages in memory

Memory management in MYSQL mainly uses LRU algorithm (variant), Least Recently Used, which includes New SUBLIST and Old Sublist in MYSQL's BUFFER POOL

As you can see in the above figure, roughly LRU (evolution) algorithm, newly read into the memory are put into the young area 5max 8 buffer pool, while frequently called pages will be put in an algorithm from 5max 8 to 3 buffer pool 8, in fact, if you have read the memory management of ORACLE, there is a paragraph and MYSQL is roughly the same, are the concept of queues + frequently extracted data in advance.

Of course, the data in the data page changes, that is to say, the changed data page is the dirty page, and the dirty page has to be brushed into the disk to merge and make it permanent. Of course, this process can be written separately or more text, so we won't expand here.

Here's a question: what if the data page changes and I'm going to read it?

The probability here is that you are going to read change buffer, (insert buffer replacement) instead of waiting for your dirty pages to be flushed to disk, read to memory, and do subsequent work.

Then how to query these memory information becomes particularly important to the performance of the system.

Check the memory ITEM information that can of course be collected

SELECT * FROM performance_schema.setup_instruments

WHERE NAME LIKE'% memory/innodb%'

Collect some memory information that is not collected

Update performance_schema.setup_instruments set enabled = 'yes' where name like' memory/innodb/buf_buf_pool'

At the same time, the allocation information in MYSQL can be obtained by statement.

In fact, with regard to the memory management of MYSQL, of course, we can also access it through other methods, as shown in the following figure, of course, this should be the most basic should be mastered. How do I check Do you get it?

This is the end of "MYSQL memory Mechanism Analysis and Monitoring". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.