One, foreword

As you know, MySQL adds, deletes, changes, and checks data in memory, which is called the Buffer Pool.

You can check the default size of your MySQL Buffer Buffer Pool as follows

The unit of 0.125 in the figure above is GB, which translates to 1024* 1/8 = 128MB

MySQL initializes the Buffer Pool when it starts. The diagram below:

You can look at the image above and read the following:

MySQL reads data from disk on a data page basis. Data pages are read into memory, which is essentially a Buffer Pool.

The data structures maintained in the Buffer Pool are cache pages, and each cache page has its own description.

Since MySQL has just started, no data pages have been read from disk into the Buffer Pool, so all the cache pages in the Buffer Pool are actually empty.

In addition to the cache pages, you can also see three bidirectional linked lists in the Buffer Pool. These are FreeList, LRUList, and FlushList. The descriptions of cached pages are maintained in these three bidirectional lists.

Two, ok, suppose you read out a data page

When you select a data page, you need to load the data page into the cache page in the Buffer Pool.

How does MySQL know which cached page to store the data you read from? I’m sure you can figure it out by looking at the picture above. FreeList is a bidirectional linked list that contains descriptions of free cached pages. Wouldn’t it be nice to fetch the description of a space cache page from FreeList? The result is the following picture:

A little bit more verbose: Interpret this picture a little bit:

InnoDB loads the data pages you read into the cache pages in the Buffer Pool, and the description of the cache page is maintained in the LRU linked list. The linked list is optimized for the separation of hot and cold data. 5/8 of the regions are hot data and 3/8 of the regions are cold data. (They are essentially two-way linked lists), and your new data pages are placed near the front of the cold data section.

If you read the data page and load it into the cache page, the interval is less than 1s, then use the cache page. InnoDB will not move this description to the hot data area of 5/8.

But after more than 1s, you read the data page again. The description of the data page will be placed in the hot data area. The diagram below:

Suppose you read many pages of data at once

In the sixth article, Daydream shared that MySQL has a pre-read mechanism. If you are interested, you can follow the public account to read it.

Assume that the MySQL prefetch mechanism is triggered. Read N more cache pages from disk at a time. You get the following picture:

Because of the prefetch, your disk IO reads a lot of data pages, but there are probably some pages that you don’t need, just the prefetch cascades them out. In this case, the old rule is to find the free cached page information from the FreeList and remove it from the FreeList. Load the data page read from disk according to the description of the found free cache page. The corresponding description of the cached page is also maintained in the cold data area of the LRU list.

That’s when you see how nice this mechanism is for separating hot and cold data. So what if it happens? There is no chance of squeezing out the description 1 of the thermal data area. When you run out of memory and need to flush some of the cached pages to disk, start with the cold data area, which is rarely used anyway.

Similarly, when you access a cold data cache page after more than 1 second, such as cache page 66 and data page 67, the description of the cache page will be promoted to the hot data area, resulting in the following image:

So, if you visit data page 67 in the figure above, does it move to the front of the node where description 66 is located?

MySQL’s LRU list has been optimized so that 67 will not run forward.

Suppose you modify a data page

Update XXX set XXX where id in (XXX, XXX, XXX, XXX);

What happens in the BufferPool if the rows that meet the criteria happen to be in the cache page that description 1, description 66, and description 67 point to?

The diagram below:

You will see that the description of the cached page that you modified is added to the FlushList.

The objects in FlushList are the descriptors of the dirty pages that have been modified.

The longer MySQL is used, the smaller the BufferPool becomes. When it is not enough use, will be part of the data in the LRU page description information removed out, then removed if it is found that the data page in FLushList, will trigger a fsync operations, triggering random disk writes. If the data page is clean, it should be removed. There’s nothing else to do.

For example, suppose that the cache pages pointed to by description 66 and 67 need to be dislocated. You get the following brain map:

Description Pages 66 and 67 are flushed to the disk. It is also removed from the FlushList and stored to FreeList. Complete a cycle

Of course, the timing of flushing dirty data pages to disk can be a lot more than what is shown above.

Let’s take a look at Buffer Pool Settings and related optimizations.

5. Set the Buffer Pool size

The larger the buffer pool, the stronger the performance of MySQL. You can configure the Buffer Pool size as follows.

Copymysql> SET GLOBAL innodb_buffer_pool_size=402653184;
Copy the code

Configure multiple Buffer Pool instances

You can configure multiple Buffer pools for your MySQL instance. Each Buffer Pool manages a portion of the cache page and has its own LRU, Free, and Flush linked lists.

MySQL performance is greatly improved when multiple concurrent requests come in from threads that can perform their own operations in different Buffer pools

Configure it in my.d

Copy[server]
innodb_buffer_pool_size = xxx
innodb_buffer_pool_instances = 4
Copy the code

Partition a buffer pool with a total capacity of XXX into four instances. Each instance has a capacity of XXX /4.

The maximum value of the innodb_buffer_pool_instances parameter is 64, and for this parameter to take effect, innodb_buffer_pool_size has a capacity of at least 1 GB.

To check the state of your MySQL Buffer Pool instance, do the following:

Reveal the true structure of a BufferPool

In reality, the Buffer Pool often occupies several GIGABytes of memory. MySQL has a more elegant implementation method than directly applying for several GIGABytes of memory to complete expansion.

To dynamically adjust the Buffer Pool size. MySQL designed the chunk mechanism.

Take a look at the image above and imagine what Buffer pools and chunks look like.

To sum up: each Buffer Pool Instance is made more granular. Split the Buffer Pool into smaller, independent units.

Each Buffer Pool is divided into multiple Chunnk, and each chunk maintains part of the cache page and the description of the cache page. Chunks belonging to the same Buffer Pool share the LRU, free, and Flush linked lists of the Buffer Pool.

The block size is controlled by the innodb_buffer_pool_chunk_size parameter, which defaults to 128 MB

This parameter can be modified as follows:

Copyshell> mysqld --innodb-buffer-pool-chunk-size=134217728
Copy the code

Or you can customize it through the configuration file

Copy[mysqld]
innodb_buffer_pool_chunk_size=13421772
Copy the code

Buffer Pool parameter #

Execute the command

Copy> mysql show engine innodb status
Copy the code

How to size your Buffer Pool

You are advised to set the total size of the Buffer Pool to 50% to 60% of the server memory

BufferPool Total size = (chunkSize * bufferPoolInstanceNum)*2

10. Buffer Pool preheating mechanism

This mechanism is intended to allow MySQL to quickly adapt to massive traffic requests after restart.

InnoDB saves a portion of the most recently used pages for each buffer pool when the server is down and restores these pages when the server is started. The proportion of cached pages saved is controlled by the innodb_buffer_pool_dump_pct parameter.

Restoring the buffer pool at startup actually reduces the time it takes to warm up.

You can configure this parameter as follows

Copy# SET GLOBAL innodb_buffer_pool_dump_pct=40; Innodb_buffer_pool_dump_pct =40 innodb_buffer_pool_dump_pct=40Copy the code

The innodb_buffer_pool_dump_at_shutdown parameter controls the state of saving the buffer pool when MySQL is shutdown. The default state is on.

The startup parameter –innodb-buffer-pool-load-at-startup restores the buffer pool state when MySQL is started. This parameter is enabled by default.