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

This article mainly introduces the relevant knowledge of "what is the Buffer pool of MySQ". The editor shows you the operation process through an actual case, and the operation method is simple, fast and practical. I hope this article "what is the Buffer pool of MySQ" can help you solve the problem.

The importance of caching

Through the previous nagging, we know that for tables that use InnoDB as the storage engine, whether the indexes used to store user data (including clustered indexes and secondary indexes) or all kinds of system data are stored in tablespaces in the form of pages, and the so-called tablespaces are just InnoDB abstractions of one or more actual files on the file system. In other words, our data is stored on disk after all. But as you all know, the speed of the disk is as slow as the tortoise, how can it match the CPU which is as fast as the wind and as fast as electricity? So when the InnoDB storage engine processes the request from the client, when it needs to access the data of a page, it will load all the data of the complete page into memory, that is to say, even if we only need to access one record of a page, it also needs to load the data of the whole page into memory first. After loading the whole page into memory, you can have read-write access. After the read-write access, there is no hurry to release the memory space corresponding to the page, but to cache it, so that when there is a request to visit the page again in the future, you can save the overhead of disk IO.

InnoDB's Buffer Pool. What's a Buffer Pool?

In order to cache the pages on disk, the uncle who designed InnoDB applied to the operating system for a continuous piece of memory when the MySQL server started. They named this piece of memory Buffer Pool (the Chinese name is buffer pool). How big is it? This actually depends on the configuration of our machine, if you are tuhao, you have 512 gigabytes of memory, you can allocate a few hundred gigabytes as a Buffer Pool, of course, if you are not so rich, you can set a smaller point ~ the Buffer Pool is only 128m by default. Of course, if you dislike the fact that the 128m is too big or too small, you can configure the value of the innodb_buffer_pool_size parameter when you start the server, which represents the size of the Buffer Pool, like this:

[server] innodb_buffer_pool_size = 268435456

Of these, 268435456 of the units are bytes, which means that I specify the size of the Buffer Pool as 256m. It is important to note that the Buffer Pool cannot be too small, with a minimum value of 5m (which is automatically set to 5m when it is less than this value).

Internal composition of Buffer Pool

The default cache page size in Buffer Pool is the same as the default page size on disk, both 16KB. In order to better manage these cache pages in Buffer Pool, the uncle who designed InnoDB created some so-called control information for each cache page, including the tablespace number to which the page belongs, the page number, the address of the cache page in Buffer Pool, linked list node information, some lock information and LSN information (locks and LSN we will nag later, which we can ignore now), and of course some other control information. Let's not talk about it all over and over again, pick the important ones.

The amount of memory occupied by the control information corresponding to each cache page is the same. Let's call the memory occupied by the control information corresponding to each page as a control block. The control block corresponds to the cache page one by one, and they are stored in the Buffer Pool, where the control block is stored in front of the Buffer Pool and the cache page is stored behind the Buffer Pool, so the memory space corresponding to the whole Buffer Pool looks like this:

Huh? What is the fragment between the control block and the cache page? If you think about it, each control block corresponds to a cache page, so after allocating enough control blocks and cache pages, the remaining space may not be enough for a pair of control blocks and cache pages, so it will not be used naturally. This little bit of memory space that is not needed is called fragmentation. Of course, if you set the size of the Buffer Pool just right, it may not produce fragments.

Tip: each control block takes up about 5% of the cache page size, and in this version of MySQL5.7.21, each control block takes up 808 bytes. The innodb_buffer_pool_size we set does not include the amount of memory occupied by this control block, that is, when InnoDB applies for continuous memory space from the operating system for Buffer Pool, this continuous memory space is generally about 5% larger than the value of innodb_buffer_pool_size.

Management of free linked list

When we first start the MySQL server, we need to complete the initialization process of the Buffer Pool, that is, first apply to the operating system for the memory space of the Buffer Pool, and then divide it into several pairs of control blocks and cache pages. But no real disk pages are cached in Buffer Pool at this time (because they are not used yet), and then as the program runs, pages on disk will continue to be cached in Buffer Pool. So the question is, which cache page should be placed when reading a page from disk into Buffer Pool? Or how to tell which cache pages in Buffer Pool are free and which are already in use? We'd better record which cache pages are available in Buffer Pool somewhere, when the control blocks corresponding to cache pages come in handy. We can put the control blocks corresponding to all free cache pages into a linked list as a node, which can also be called free linked list (or idle linked list). All the cache pages in the Buffer Pool that have just been initialized are free, so the control block corresponding to each cache page is added to the free list. Assuming that the number of cache pages that can be contained in the Buffer Pool is n, the effect of the added free list looks like this:

As can be seen from the figure, in order to manage the free linked list, we specially define a base node for the linked list, which contains the address of the head node, the address of the tail node, and the number of nodes in the current linked list. It is important to note here that the memory space occupied by the base node of the linked list is not included in a large piece of contiguous memory space requested for Buffer Pool, but a piece of memory space applied separately.

Tip: the base node of the linked list does not take up a lot of memory space. In this version of MySQL5.7.21, each base node takes up only 40 bytes. We will introduce many different linked lists later. Their base nodes are allocated in the same way as the base nodes of the free linked list. They are a 40-byte piece of memory requested separately and are not included in a large piece of contiguous memory space requested for Buffer Pool.

It is easy to have this free linked list. Whenever you need to load a page from disk into Buffer Pool, take a free cache page from the free linked list and fill in the information of the control block corresponding to the cache page (that is, the table space and page number of the page), and then remove the free linked list node corresponding to the cache page from the list, indicating that the cache page has been used.

Hash processing of cached pages

As we said earlier, when we need to access data in a page, the page is loaded from disk into Buffer Pool and can be used directly if the page is already in Buffer Pool. So the question is, how do we know if the page is in Buffer Pool? Is it necessary to traverse each cache page in Buffer Pool in turn? Isn't it exhausting to run through so many cache pages in a Buffer Pool?

In retrospect, we actually locate a page according to the table space number + page number, which is equivalent to the table space number + page number is a key, and the cache page is the corresponding value. How can we quickly find a value through a key? Haha, that must be a hash table.

Tip: what? Don't tell me you don't know what a hash table is? Our article is not about hash tables, if you can't, then find a data structure book and have a look. You can't understand the books outside? Don't worry, wait for me.

So we can use tablespace number + page number as key, cache page as value to create a hash table, when you need to access the data of a page, first from the hash table according to the table space number + page number to see if there is a corresponding cache page, if there is, just use the cache page directly, if not, then select an idle cache page from the free list, and then load the corresponding page in the disk to the location of the cache page.

Management of flush linked list

If we modify the data of a cache page in Buffer Pool, it is inconsistent with the page on disk, and such cache page is also called dirty page (English name: dirty page). Of course, the easiest way is to synchronize to the corresponding page on the disk every time a change occurs, but writing data to the disk frequently can seriously affect the performance of the program (after all, the disk is as slow as a tortoise). So every time we modify the cache page, we are not in a hurry to synchronize the changes to disk immediately, but at some point in the future. As for the time point of synchronization, we will explain it later. Don't worry about it now.

But if we don't synchronize to disk immediately, how do we know which pages in Buffer Pool are dirty and which have never been modified when we synchronize later? It is impossible to synchronize all cache pages to disk. If Buffer Pool is set to be very large, say 300G, it would be slow to synchronize so much data at one time. Therefore, we have to create another linked list to store dirty pages, and the control blocks corresponding to the modified cache pages will be added to a linked list as a node, because the cache pages corresponding to this linked list node need to be refreshed to disk, so it is also called flush linked list. The construction of the linked list is similar to that of the free linked list. If the number of dirty pages in the Buffer Pool at a certain point in time is n, then the corresponding flush linked list looks like this:

Management of LRU linked list

The dilemma of insufficient caching

After all, the memory size corresponding to Buffer Pool is limited. What if the memory size of the pages that need to be cached exceeds the size of Buffer Pool, that is, there are no spare cache pages in the free linked list? Of course, it is to remove some old cache pages from Buffer Pool, and then put new pages in. So the question is, which cache pages should be removed?

To answer this question, we also need to go back to why we set up Buffer Pool. We just want to reduce the IO interaction with the disk, and it is best to cache it in Buffer Pool every time a page is visited. Suppose we have visited a total of n pages, then the number of times the visited page has been in the cache divided by n is the so-called cache hit rate. Our expectation is to make the cache hit rate as high as possible. From this point of view, think back to our Wechat chat list, the ones at the front are frequently used recently, and those at the bottom are naturally rarely used recently, if the list can accommodate a limited number of contacts. Will you keep the ones that have been used frequently recently or those that have been rarely used recently? Nonsense, of course, it has been used very frequently recently.

Simple LRU linked list

The same is true for managing Buffer Pool cache pages. When there are no more free cache pages in Buffer Pool, you need to eliminate some cache pages that have been rarely used recently. However, how do we know which cache pages are frequently used recently and which are rarely used recently? Oh, once again, the magic linked list came in handy, we can create another linked list, because this linked list is in accordance with the principle of the least recently used to eliminate cache pages, so this linked list can be called LRU linked list (English full name of LRU: Least Recently Used). When we need to access a page, we can deal with the LRU linked list like this:

If the page is not in Buffer Pool, when the page is loaded from disk into the cache page in Buffer Pool, the control block corresponding to the cache page is stuffed into the header of the linked list as a node.

If the page is already cached in the Buffer Pool, the control block corresponding to the page is moved directly to the header of the LRU linked list.

That is to say: as long as we use a cache page, adjust the cache page to the head of the LRU linked list, so that the tail of the LRU linked list is the least used cache page recently ~ so when the free cache pages in the Buffer Pool are used up, go to the end of the LRU linked list to find some cache pages to eliminate the OK.

LRU linked list divided into regions

Happy too early, the above simple LRU linked list did not take long to find the problem, because there are these two more embarrassing situations:

Case 1: InnoDB provides a seemingly intimate service-pre-reading (English name: read ahead). The so-called pre-read is that InnoDB loads certain pages into Buffer Pool in advance because it thinks that some pages may be read after the current request is executed. According to the different trigger methods, pre-reading can be subdivided into the following two categories:

Pre-reading is originally a good thing, if the page in Buffer Pool is successfully used, it can greatly improve the efficiency of statement execution. But what if you don't need it? These pre-read pages will be placed in the head of the LRU linked list, but if the capacity of Buffer Pool is not too large and many pre-read pages are not used, this will cause some cache pages at the end of the LRU linked list to be quickly eliminated, that is, the so-called Bad money drives out good, which will greatly reduce the cache hit rate.

Linear pre-reading

The uncle who designed the InnoDB provides a system variable innodb_read_ahead_threshold. If the pages that visit a certain extent in sequence exceed the value of this system variable, it will trigger a request to asynchronously read all the pages in the next area to Buffer Pool. Note that asynchronous reading means that loading these pre-read pages from disk will not affect the normal execution of the current worker thread. The default value of this innodb_read_ahead_threshold system variable is 56. We can adjust the value of the system variable directly through the startup parameters or during the server operation when the server is started, but it is a global variable, so use the SET GLOBAL command to modify it.

Tip: how does InnoDB achieve asynchronous reading? On Windows or Linux platforms, the AIO interface provided by the operating system kernel may be called directly. In other Unix-like operating systems, a way to simulate the AIO interface is used to achieve asynchronous reading, which actually allows other threads to read the pages that need to be read in advance. If you can't read the above paragraph, it's not necessary to understand, it doesn't really have much to do with our topic, you just need to know that asynchronous reading will not affect the normal execution of the current worker thread. In fact, this process involves the operating system how to deal with IO and multithreading, find a book about the operating system, what? The writing of the operating system is difficult to understand? It's all right. Wait for me.

Random pre-reading

If 13 consecutive pages of an area have been cached in Buffer Pool, regardless of whether the pages are read sequentially or not, a request to asynchronously read all its pages in the area to Buffer Pool will be triggered. The uncle who designed InnoDB also provides the innodb_random_read_ahead system variable, which defaults to OFF, which means that InnoDB does not enable random pre-reading by default. If we want to enable this function, we can change the startup parameters or directly use the SET GLOBAL command to set the value of this variable to ON.

Case 2: some partners may write queries that need to scan the whole table (such as queries that do not have a proper index or have no WHERE clause at all).

What does it mean to scan the whole table? It means that all pages on which the table is located will be accessed! Assuming that there are a lot of records in this table, the table will occupy a lot of pages, and when you need to access these pages, they will all be loaded into Buffer Pool, which means that, by the way, all the pages in Buffer Pool have been changed, and other query statements have to be loaded from disk to Buffer Pool again when executed. However, the execution frequency of this kind of full table scan statement is not high, and each execution has to change the cache page in Buffer Pool, which seriously affects the use of Buffer Pool in other queries, thus greatly reducing the cache hit rate.

Summarize the two scenarios mentioned above that may reduce Buffer Pool:

Pages loaded into Buffer Pool are not necessarily used.

If a very large number of underused pages are loaded into Buffer Pool at the same time, those pages that are used very frequently may be eliminated from Buffer Pool.

Because of these two situations, the uncle who designed InnoDB divided the LRU linked list into two parts according to a certain proportion, which are:

Some of the cache pages are stored very frequently, so this part of the linked list is also called hot data, or young area.

The other part stores cache pages that are not very frequently used, so this part of the linked list is also called cold data, or old area.

In order to make it easier for you to understand, we have simplified the schematic diagram so that you can understand the spirit:

We should pay special attention to one thing: we divide the LRU list into two halves according to a certain proportion, not some nodes are fixed in the young area, some nodes are fixed in the old area, and the area to which a node belongs may also change as the program runs. Then how to determine the proportion that is divided into two parts? For the InnoDB storage engine, we can determine the proportion of the old region in the LRU linked list by looking at the value of the system variable innodb_old_blocks_pct, such as this:

Mysql > SHOW VARIABLES LIKE 'innodb_old_blocks_pct' +-+-+ | Variable_name | Value | +-+-+ | innodb_old_blocks_pct | 37 | +-+-+ 1 row in set (0.01sec)

As you can see from the results, by default, the old region accounts for 37% of the LRU linked list, which means that the old region accounts for about 3max 8 of the LRU linked list. We can set this ratio. We can modify the innodb_old_blocks_pct parameter at startup to control the proportion of the old area in the LRU linked list, for example, by modifying the configuration file:

[server] innodb_old_blocks_pct = 40

In this way, after we start the server, the old zone accounts for 40% of the LRU linked list. Of course, we can also modify the value of this system variable while the server is running, but it should be noted that this system variable belongs to a global variable, and once modified, it will take effect on all clients, so we can only modify it like this:

SET GLOBAL innodb_old_blocks_pct = 40

With this LRU linked list divided into young and old regions, the uncle who designed InnoDB can optimize for the two situations mentioned above that may reduce the cache hit rate:

Subsequent visits may not be optimized for pre-read pages

The uncle who designed the InnoDB stipulated that when a page on disk was first loaded into a cache page in Buffer Pool, the corresponding control block of the cache page would be placed in the header of the old area. In this way, pages that are pre-read to Buffer Pool but do not make subsequent visits will be gradually expelled from the old area without affecting the more frequently used cache pages in the young area.

Optimization of visiting a large number of pages with very low frequency in a short time when scanning a full table

In the full table scan, although the page that was loaded into Buffer Pool for the first time is placed in the header of the old area, it will be accessed immediately later, and the page will be placed in the header of the young area every time you visit, so that the frequently used pages will still be pushed down. Some students will wonder if you can not move the page from the old area to the head of the young area when you visit the page for the first time, and then move it to the head of the young area when you visit it later. The answer is: it won't work! Because the uncle who designed InnoDB stipulates that every time he reads a record in a page, he visits the page, and a page may contain many records, that is to say, reading the record of a page is equivalent to visiting the page many times.

What are we going to do? Full table scan has a characteristic, that is, its execution frequency is very low, no one can always write full table scan statements to play with, and in the process of performing full table scan, even if there are many records in a page, that is, it takes very little time to visit this page multiple times. So we only need to specify that the access time is recorded in its corresponding control block when making the first access to a cache page in the old area, and if the subsequent access time is within a certain time interval with the time of the first visit, then the page will not be moved from the old area to the head of the young area, otherwise it will be moved to the head of the young area. The above interval is controlled by the system variable innodb_old_blocks_time, you see:

Mysql > SHOW VARIABLES LIKE 'innodb_old_blocks_time' +-+-+ | Variable_name | Value | +-+-+ | innodb_old_blocks_time | 1000 | +-+-+ 1 row in set (sec)

The default value of this innodb_old_blocks_time is 1000, which is in milliseconds, which means that for a page loaded from disk into the old area of the LRU linked list, if the interval between the first visit to the page and the last visit to the page is less than 1s (obviously, during a full table scan, the time for multiple visits to a page will not exceed 1s), then the page will not be added to the young area. Like innodb_old_blocks_pct, we can also set the value of innodb_old_blocks_time when the server is started or running, so I won't go into detail here. Try it yourself. What we need to note here is that if we set the value of innodb_old_blocks_time to 0, then every time we visit a page, we will put the page in the head of the young area.

To sum up, it is precisely because the LRU linked list is divided into young and old areas, and the system variable innodb_old_blocks_time is added, the problem of cache hit rate reduction caused by pre-reading mechanism and full table scanning is contained, because unused pre-read pages and full table scanned pages will only be placed in the old area, without affecting the cache pages in the young area.

Further optimize the LRU linked list

Are we done with the LRU linked list? No, no, Yes, in order to solve this problem, we can also propose some optimization strategies. For example, only if the accessed cache page is located after 1max 4 in the young area will it be moved to the head of the LRU linked list, which can reduce the frequency of adjusting the LRU linked list, thus improving performance (that is, if the node corresponding to a cache page is in 1max 4 in the young area. The cache page is also not moved to the LRU linked list header when it is accessed again.

Tip: when we introduced random pre-reading, we said that if there are 13 consecutive pages in a certain area in Buffer Pool, random pre-reading will be triggered, which is actually not rigorous (unfortunately, that's what the MySQL document says [showdown]). In fact, it also requires that these 13 pages are very hot pages, which means that these pages are in the first 1 and 4 places of the entire young area.

Are there any other optimization measures for LRU linked lists? Of course there is, if you study hard, write a paper, write a book is not a problem, but after all, this is an introduction to the basic knowledge of MySQL, and more space can not stand, but also affect everyone's reading experience, so enough, want to know more optimization knowledge, read the source code or more knowledge about LRU linked list, but no matter how to optimize Don't forget our original intention: to improve the cache hit rate of Buffer Pool as efficiently as possible.

Some other linked lists

In order to better manage the cache pages in Buffer Pool, in addition to the measures mentioned above, the uncles who designed InnoDB also introduced some other linked lists, such as unzip LRU linked list to manage extracted pages, zip clean linked list to manage undecompressed compressed pages, each element in the zip free array represents a linked list, which form a so-called partner system to provide memory space for compressed pages, and so on. Anyway, in order to better manage this Buffer Pool, various linked lists or other data structures have been introduced, and the specific ways to use them will not be verbose. If you are interested in looking for deeper books or looking directly at the source code, you can also come to me directly.

Tip: we haven't nagged the compressed pages in InnoDB at all, and the list above is just for completeness, by the way, don't be depressed if you can't read it, because I'm not going to introduce them at all.

Refresh dirty pages to disk

There is a special thread in the background that is responsible for flushing dirty pages to disk at regular intervals, so that it does not affect the user thread's processing of normal requests. There are two main refresh paths:

Refresh some pages to disk from the cold data in the LRU linked list.

The background thread periodically scans some pages from the end of the LRU linked list. The number of scanned pages can be specified by the system variable innodb_lru_scan_depth. If dirty pages are found inside, they will be refreshed to disk. This way of refreshing the page is called BUF_FLUSH_LRU.

Refresh part of the page from the flush linked list to disk.

Background threads also periodically refresh some pages from the flush list to disk, depending on whether the system is busy at the time. This way of refreshing the page is called BUF_FLUSH_LIST.

Sometimes the progress of the background thread refreshing dirty pages is slow, causing the user thread to load a disk page into Buffer Pool without available cache pages, then it will try to see if there are any unmodified pages that can be released directly at the end of the LRU linked list, if not, it will have to synchronously refresh a dirty page at the end of the LRU linked list to the disk (it is very slow to interact with the disk, which will slow down the speed of processing user requests). This method of refreshing a single page to disk is called BUF_FLUSH_SINGLE_PAGE.

Of course, sometimes when the system is particularly busy, user threads may refresh dirty pages in batches from the flush linked list. Obviously, refreshing dirty pages during user requests is a behavior that seriously slows down the processing speed (after all, the disk is so slow), which is a last resort, but it has to be said later when nagging the checkpoint of the redo log.

Multiple Buffer Pool instances

As we said above, Buffer Pool is essentially a continuous memory space that InnoDB applies to the operating system. In a multithreaded environment, accessing various linked lists in Buffer Pool requires locking and so on. When the Buffer Pool is particularly large and multi-threaded concurrent access is particularly high, a single Buffer Pool may affect the speed of request processing. So when the Buffer Pool is particularly large, we can split them into several small Buffer Pool, each Buffer Pool is called an instance, they are independent, independent to apply for memory space, independent management of various linked lists, independent bar, so in multi-threaded concurrent access will not affect each other, so as to improve concurrent processing capabilities. We can modify the number of Buffer Pool instances by setting the value of innodb_buffer_pool_instances when the server starts, for example:

[server] innodb_buffer_pool_instances = 2

This indicates that we are going to create two Buffer Pool instances, as shown in the diagram:

Tip: for simplicity, I only draw the base nodes of each linked list, and you should know that the nodes of these linked lists are actually the control blocks corresponding to each cache page!

How much memory does each Buffer Pool instance actually take up? In fact, it is calculated by using this formula:

Innodb_buffer_pool_size/innodb_buffer_pool_instances

This is the total size divided by the number of instances, and the result is the size occupied by each Buffer Pool instance.

However, it does not mean that the more Buffer Pool instances are created, the better. Managing each Buffer Pool separately also requires performance overhead. The uncles who designed the InnoDB stipulated that it is not valid to set multiple instances when the value of innodb_buffer_pool_size is less than 1G, and InnoDB will change the value of innodb_buffer_pool_instances to 1 by default. We encourage you to set up multiple Buffer Pool instances when Buffer Pool is greater than or equal to 1G.

Innodb_buffer_pool_chunk_size

Prior to MySQL 5.7.5, the size of Buffer Pool can only be resized by configuring the innodb_buffer_pool_size startup parameters when the server is started, and this value is not allowed during server operation. However, the uncle who designed MySQL supports the ability to resize Buffer Pool while the server is running, but there is a problem, that is, every time we want to resize Buffer Pool, we need to re-apply to the operating system for a continuous piece of memory space, and then copy the contents of the old Buffer Pool to this new space, which is extremely time-consuming. So the uncles who designed MySQL decided not to apply for a large piece of continuous memory space from the operating system for a Buffer Pool instance at once, but to apply for space from the operating system in a so-called chunk unit. In other words, an Buffer Pool instance is actually composed of several chunk, and a chunk represents a continuous memory space, which contains several control blocks corresponding to the cache pages. This is shown by drawing a graph:

The Buffer Pool represented in the figure above consists of two instances, and each instance contains two chunk.

It is because of the invention of the concept of chunk that when we resize Buffer Pool while the server is running, we increase or delete memory space in units of chunk without having to re-apply for a large piece of memory from the operating system and then copy the cache page. The size of this so-called chunk is specified by the innodb_buffer_pool_chunk_size startup parameter when we start the operation MySQL server, and its default value is 134217728, which is 128m. It is important to note, however, that the value of innodb_buffer_pool_chunk_size can only be specified when the server is started and cannot be modified while the server is running.

Tip: why not allow the value of innodb_buffer_pool_chunk_size to be modified while the server is running? Not because the value of innodb_buffer_pool_chunk_size represents the size of a continuous memory space that InnoDB applied to the operating system, if you modify this value while the server is running, it means re-applying to the operating system for continuous memory space and copying the original cache pages and their corresponding control blocks to this new memory space, which is a very time-consuming operation! In addition, the value of this innodb_buffer_pool_chunk_size does not include the memory space of the control block corresponding to the cache page, so in fact, when InnoDB applies for continuous memory space from the operating system, the size of each chunk is larger than the value of innodb_buffer_pool_chunk_size, about 5%.

Considerations when configuring Buffer Pool

Innodb_buffer_pool_size must be a multiple of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances (this is mainly to ensure that each Buffer Pool instance contains the same number of chunk).

Suppose that the value of the innodb_buffer_pool_chunk_size we specify is 128m, and the value of the pure buffer is 16, then the product of the two values is 2G, which means that the value of innodb_buffer_pool_size must be 2G or an integer multiple of 2G. For example, when we start the MySQL server, we specify the startup parameters as follows:

Mysqld-innodb-buffer-pool-size=8G-innodb-buffer-pool-instances=16

The default value of innodb_buffer_pool_chunk_ size is 128m, and the specified value of innodb_buffer_pool_instances is 16, so the value of innodb_buffer_pool_size must be 2G or an integer multiple of 2G. The value of innodb_buffer_pool_size specified in the above example is 8G, which meets the requirements, so after the server is started, let's check that the value of this variable is the 8G (8589934592 bytes) we specified:

Mysql > show variables like 'innodb_buffer_pool_size' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 8589934592 | +- -+-+ 1 row in set (0.00 sec)

If the specified innodb_buffer_pool_size is greater than 2G and is not an integral multiple of 2G, the server will automatically adjust the value of innodb_buffer_pool_size to an integral multiple of 2G, for example, the value of innodb_buffer_pool_size specified when we start the server is 9G:

Mysqld-innodb-buffer-pool-size=9G-innodb-buffer-pool-instances=16

Then the server will automatically adjust the value of innodb_buffer_pool_size to 10G (10737418240 bytes). Look at this:

Mysql > show variables like 'innodb_buffer_pool_size' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 10737418240 | +- -+-+ 1 row in set (0.01 sec)

If the value of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances is already greater than the value of innodb_buffer_pool_size when the server starts, then the value of innodb_buffer_pool_chunk_size is automatically set to the value of innodb_buffer_pool_size/innodb_buffer_pool_instances by the server.

For example, when we start the server, the value of innodb_buffer_pool_size specified is 2G, the value of innocence bufferpoolation is 16, and the value of innocence bufferpoolization chunksize is 256m:

Mysqld-innodb-buffer-pool-size=2G-innodb-buffer-pool-instances=16-innodb-buffer-pool-chunk-size=256M

Because 256m × 16 = 4G and 4G > 2G, the innodb_buffer_pool_chunk_size value will be rewritten by the server to the value of innodb_buffer_pool_size/innodb_buffer_pool_instances, that is, 2G/16 = 128m (134217728 bytes). If you don't believe it, look:

Mysql > show variables like 'innodb_buffer_pool_size' +-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_size | 2147483648 | +- -+-+ 1 row in set (0.01sec) mysql > show variables like 'innodb_buffer_pool_chunk_size' +-+-+ | Variable_name | Value | +-+-+ | innodb_buffer_pool_chunk_size | 134217728 | +-- -- + + other information stored in 1 row in set (0.00 sec) Buffer Pool

In addition to caching pages on disk, Buffer Pool cache pages can also store lock information, adaptive hash index and other information, which will be discussed in detail when we encounter later.

View the status information of Buffer Pool

The uncle who designed MySQL kindly provided us with SHOW ENGINE INNODB STATUS statements to view some status information about the running process of InnoDB storage engine, including some information about Buffer Pool. Let's take a look (to highlight the point, we only extracted the part about Buffer Pool in the output):

Mysql > SHOW ENGINE INNODB STATUS\ G (... Omit many previous states)-- BUFFER POOL AND MEMORY--Total memory allocated 13218349056 Dictionary memory allocated 4014231Buffer pool size 786432Free buffers 8174Database pages 710576Old database pages 262143Modified db pages 124941Pending reads 0Pending writes: LRU 0, flush list 0, single page 0Pages made young 6195930012, not young 78247510485108.18 youngs/s, 226.15 non-youngs/sPages read 2748866728, created 29217873, written 4845680877160.77 reads/s, 3.80 creates/s, 190.16 writes/sBuffer pool hit rate 956 / 1000, young-making rate 30 / 1000 not 605 / 1000Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/sLRU len: 710576 Unzip_LRU len: 118I/O sum [134264]: cur [134264], unzip sum [16]: cur [0]-(. Omit many subsequent states) mysql >

Let's take a closer look at what each value represents:

Total memory allocated: the amount of continuous memory space that Buffer Pool requests from the operating system, including the size of all control blocks, cache pages, and fragments.

Dictionary memory allocated: the amount of memory space allocated for data dictionary information. Note that this memory space has nothing to do with Buffer Pool and is not included in Total memory allocated.

Buffer pool size: represents how many cached pages the Buffer Pool can hold. Note that the unit is pages!

Free buffers: represents how many free cache pages are left in the current Buffer Pool, that is, how many nodes are left in the free linked list.

Database pages: represents the number of pages in the LRU linked list, including the number of nodes in the young and old regions.

Old database pages: represents the number of nodes in the old area of the LRU linked list.

Modified db pages: represents the number of dirty pages, that is, the number of nodes in the flush linked list.

Pending reads: the number of pages waiting to be loaded from disk into Buffer Pool.

When a page is ready to be loaded from disk, a cache page and its corresponding control block are assigned to the page in Buffer Pool, and then the control block is added to the header of the old area of the LRU, but at this time the real disk page is not loaded, and the value of Pending reads is incremented by 1.

Pending writes LRU: the number of pages to be flushed to disk from the LRU linked list.

Pending writes flush list: the number of pages to be flushed to disk from the flush linked list.

Pending writes single page: the number of pages to be flushed to disk as a single page.

Pages made young: represents the number of nodes in the LRU linked list that have moved from the old area to the head of the young region.

It should be noted here that a node will only add 1 to the value of Pages made young every time it is moved from the old area to the head of the young region, that is, if the node is already in the young area, it will also be moved to the head of the young area the next time it visits the page, because it meets the requirements after the young area 1max 4, but this process will not cause the value of Pages made young to be added by 1.

Page made not young: when innodb_old_blocks_time is set to a value greater than 0, when a node in the old area is visited for the first time or subsequently, it cannot be moved to the head of the young area because it does not meet the time interval limit, the value of Page made not young will be increased by 1.

It is important to note that for a node in the young region, if it is not moved to the head of the young region because it is at 1 young 4 in the young region, such access does not add 1 to the value of Page made not young.

Youngs/s: represents the number of nodes that are moved from the old region to the young region header per second.

Non-youngs/s: represents the number of nodes per second that cannot be moved from the old region to the head of the young region because they do not meet the time limit.

Pages read, created, written: represents how many pages have been read, created, and written. This is followed by the rate of reading, creating, and writing.

Buffer pool hit rate: indicates how many times the page has been cached to Buffer Pool, averaging 1000 visits at some point in the past.

Young-making rate: indicates that the page has been visited an average of 1000 times over a period of time, and how many visits have moved the page to the head of the young area.

It is important to note that the number of times the page is moved to the head of the young region here includes not only the number of times the page was moved from the old area to the head of the young region, but also the number of times it was moved from the young area to the head of the young region (visit a node in the young area, as long as the node is moved to the head of the young region after 1x4 in the young area).

Not (young-making rate): indicates that the page has been visited an average of 1000 times over a period of time, and how many visits have not moved the page to the head of the young area.

It should be noted that the number of times the page was not moved to the header in the young area counted here includes not only the number of times that the nodes in the old area were visited but not moved to the young area because the innodb_old_blocks_time system variable was set, but also the number of times that the node was not moved to the head of the young area because the node was in the first 1max 4 of the young area.

LRU len: represents the number of nodes in the LRU linked list.

Unzip_LRU: represents the number of nodes in the unzip_LRU linked list (since we haven't specifically nagged the linked list, we can ignore its value now).

I take O sum: the total number of disk pages read in the last 50 seconds.

I take O cur: the number of disk pages being read now.

I take O unzip sum: the number of pages unzipped in the last 50 seconds.

I get O unzip cur: the number of pages being decompressed.

Summary

The disk is too slow, so it is necessary to use memory as the cache.

Buffer Pool is essentially a contiguous piece of memory requested by InnoDB from the operating system, which can be resized through innodb_buffer_pool_size.

The continuous memory requested by Buffer Pool from the operating system consists of control blocks and cache pages, each of which corresponds one to one. After filling in enough combination of control blocks and cache pages, the remaining space of Buffer Pool may not be enough to fill a set of control blocks and cache pages, this part of space can not be used, also known as fragmentation.

InnoDB uses a number of linked lists to manage Buffer Pool.

Each node in the free list represents a free cache page, and when a page on disk is loaded into Buffer Pool, it looks for free cache pages in the free list.

To quickly locate whether a page is loaded into Buffer Pool, a hash table is established using the tablespace number + page number as the key and the cache page as the value.

The pages modified in Buffer Pool are called dirty pages, which are not refreshed immediately, but are added to the flush linked list and synchronized to disk at some point after that.

The LRU linked list is divided into two areas: young and old, and the proportion of old regions can be adjusted through innodb_old_blocks_pct. A page that is first loaded into Buffer Pool from disk is placed in the header of the old area, and accessing the page during the innodb_old_blocks_time interval does not move it to the header of the young area. When there are no free cache pages available in Buffer Pool, some pages in the old area are eliminated first.

We can control the number of Buffer Pool instances by specifying innodb_buffer_pool_instances. Each Buffer Pool instance has its own linked list, which does not interfere with each other.

Since MySQL version 5.7.5, you can resize Buffer Pool while the server is running. Each Buffer Pool instance consists of several chunk, and the size of each chunk can be adjusted by startup parameters when the server starts.

You can view the status information of Buffer Pool with the following command:

This is the end of SHOW ENGINE INNODB STATUS\ G's introduction to "what is the Buffer pool of MySQ". Thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.