More recently, it has been found that long articles contain more information and take more time to read. And we read the article, should be using some fragments of time. So I came to the conclusion that the article is too long for you to absorb and digest. Therefore, I will reduce the length of the article, 2-3k words is about the same, can also be read quickly.

InnoDB Buffer Pool InnoDB Buffer Pool InnoDB Buffer Pool Redo Log buffers, Insert buffers, and Adaptive Hash indexes were briefly introduced.

In addition to the MySQL and InnoDB log, and two writes, generally a beginner’s level introduction, this article will introduce InnoDB memory structure in detail.

InnoDB memory structure

Its general structure is shown below.

The two main areas of InnoDB memory are the Buffer Pool and the Log Buffer, which is currently used to cache Redo logs. The Buffer Pool is a very important and core part of MySQL or InnoDB, located in main memory. That’s why it’s so efficient at accessing data, you can temporarily think of it as an in-memory database like Redis, because we update and add to it and of course it’s not, it’s just easier to understand.

Buffer Pool

Generally speaking, 80% of the host’s memory should be allocated to the Buffer Pool, because the larger the Buffer Pool is, the more data it can cache and more operations will take place in memory to improve efficiency.

Due to the large amount of data stored in the Buffer Pool, the Buffer Pool must be stored according to certain structures and be processed. Otherwise, there is no shortcut other than traversing all the data, and this inefficient operation will not support the high performance of MySQL.

As a result, the Buffer Pool is split into many pages, which was covered in the previous article and won’t be covered here. InnoDB can store a lot of data per page, and as mentioned, InnoDB must be doing something with the data.

InnoDB uses linked lists to organize pages and data stored in pages, forming a bidirectional linked list between pages, which makes it easy to jump from the current page to the next page, and uses LRU (Least Recently Used) algorithm to eliminate data that is not often Used.

The data on each page is also linked by a one-way linked list. Because the data is spread out into Buffer pools, one-way linked lists connect the scattered memory.

Log Buffer

The Log Buffer is used to store logs that are about to be flushed to disk files, such as Redo logs, and is an important part of InnoDB memory. The default value of the Log Buffer is 16MB, which can be adjusted if needed by the innodb_log_buffer_size configuration parameter.

When the Log Buffer is larger, more Redo logs can be stored, so that the Redo Log does not need to be flushed to disk before the transaction commits, but only thrown into the Log Buffer. Therefore, larger Log buffers can better support larger transactions. Similarly, increasing the size of the Log Buffer can reduce some disk I/O operations if a transaction is updating, inserting, or deleting a large number of rows.

Innodb_flush_log_at_trx_commit determines how often data in the Log Buffer is flushed to disk.

LRU algorithm for Buffer Pool

Now that we know about InnoDB’s memory structure, let’s take a closer look at how the Buffer Pool’s LRU algorithm allows recently unused data to expire.

Native LRU

First of all, the LRU algorithm here is different from our traditional LRU algorithm. Why is that? In the actual production environment, full table scan occurs. If there is a large amount of data, all hotspot data stored in the Buffer Pool may be replaced, resulting in a cliff drop in MySQL performance during this period.

MySQL has a special term for this situation called buffer pool contamination. So MySQL has optimized the LRU algorithm.

Optimized LRU

The optimized linked list is divided into two parts, New Sublist and Old Sublist, which occupy 3/4 and 1/4 of the Buffer Pool respectively.

The first three quarters of the list, the New Sublist, are the pages that are visited more frequently, and the last quarter, the Old Sublist, are the pages that are asked less frequently. Data in the Old Sublist will be removed later when the remaining Buffer Pool space is insufficient, or when new pages are added.

After understanding the overall structure and composition of the linked list, we will start with the new page being added to the list and go through the whole process. First, after a New page is put into the Buffer Pool, it will be inserted into the linked list at the intersection of New Sublist and Old Sublist, which is called MidPoint.

The linked list stores data from two parts:

  • Data preloaded by the MySQL prefetch thread
  • User actions, such as Query queries

By default, data entered into the Buffer Pool as a result of user action is immediately placed at the top of the list, in the Head of the New Sublist. However, if the data is preloaded when MySQL starts, it will be put into MidPoint. If the data has been accessed by the user, it will be put into the front of the linked list.

This way, even though the page data is in the linked list, because it has not been accessed, it is moved to the bottom quarter of the Old Sublist until it is cleaned up.

Optimized the configuration of the Buffer Pool

In a real production environment, you can change certain Settings to improve the performance of the Buffer Pool.

  • For example, we can allocate as much memory as possible to the Buffer Pool so that more data can be cached in memory
  • When sufficient memory is available, multiple Buffer Pool instances can be created to reduce data contention caused by concurrent operations
  • When we can predict the coming large number of requests, we can manually perform this part of the data prefetch request
  • We can also control how often the Buffer Pool flusher data to disk to dynamically adjust based on the current MySQL load

How do we know the state of the Buffer Pool in MySQL? To check this, run the show engine Innodb status command. InnoDB Buffer Pool monitor InnoDB Buffer Pool And Memory monitor InnoDB Buffer Pool monitor InnoDB Buffer Pool And Memory monitor InnoDB Buffer Pool

Here’s an example.

---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 972752 Buffer pool size 8191 Free buffers 4596 Database pages 3585 Old database pages 1303 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, Flush list 0, single Page 0 Pages made Young 1171, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages Read 655, Written 173255 0.00 reads/s, 0.00 creates/s 0.00 index /s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read Ahead 0.00/s LRU len: 3585, unzip_LRU Len: 0 I/O sum[0]:cur[0]Copy the code

Explain what some of the key indicators mean:

  • Total Memory Allocated: Total memory allocated to the Buffer Pool
  • Dictionary Memory Allocated: Total memory allocated to InnoDB data Dictionary
  • Buffer pool size: The size of memory allocated to pages in the Buffer pool
  • Free buffers: Specifies the memory size allocated to the Free List in the Buffer Pool
  • Database Pages: The size of memory allocated to the LRU list
  • Old Database Pages: The size of memory allocated to LRU sublists
  • Modified DB Pages: The number of updated pages in the current Buffer Pook
  • Pending reads: Number of pages currently waiting to be read into the Buffer Pool
  • Pending writes LRU: The number of dirty pages currently in the LRU list waiting to be flushed to disk

These are pretty general configuration items, and you might be wondering what a Free List is, because the Free List holds unused pages. Because when MySQL starts InnoDB will pre-apply some pages. If the current page is not already in use, it will be saved in the Free List.

If you know the Free List, you should also know the Flush List, which holds all the dirty pages that have been changed and need to be flushed to disk.

Adaptive hash index

Adaptive Hash Index is a function that works with the Buffer Pool. Adaptive hash indexes bring MySQL performance closer to that of a memory server.

If you want to enable adaptive hash indexing, you can do so by changing the innodb_adaptive_hash_index configuration. If you do not want to enable this function, you can run the skip-Innodb-adaptive -hash-index command to disable it during startup.

An adaptive hash index is built based on the prefix of an index Key. InnoDB has its own mechanism for monitoring indexes. When InnoDB detects that creating a hash index for a current index page would improve efficiency, InnoDB creates a hash index for that page. If a table has a small amount of data and all of its data is in the Buffer Pool, then the adaptive hash index becomes the familiar pointer.

Of course, there is an overhead associated with creating and maintaining an adaptive hash index, but this overhead is negligible compared to the performance gain. However, whether to enable the adaptive hash index depends on the specific business situation. For example, when our business features a large number of concurrent Join queries, the access to the adaptive hash index will be competitive. And if the business uses wildcards such as LIKE or %, the hash index will not be used at all, so the adaptive hash index becomes a burden on the system.

So, to minimize concurrency contention, InnoDB partitioned adaptive hash indexes, with each index bound to a specific partition and each partition protected by a separate lock. In fact, popular point of understanding, is to reduce the granularity of the lock. The number of partitions can be changed by configuring innodb_adaptive_hash_index_parts, which can be configured in a range of [8, 512].

Change Buffer

Buffer Pool index correlation. Change Buffer. The Change Buffer is a special area used to store modified secondary indexes that are not currently in the Buffer Pool.

When we update the data of the non-clustered index (secondary index), we should directly update the corresponding data in the Buffer Pool. Unfortunately, the secondary index is not in the Buffer Pool. Pulling it from the disk to the Buffer Pool at this point is not optimal because the secondary index may not be used at all later on and the expensive disk I/O will be wasted.

Therefore, we need a place to temporarily store changes made to these secondary indexes. When the cached secondary index page is loaded into the Buffer Pool by another request, the Change Buffer is merged into the Buffer Pool.

Of course, Change Buffer is not without its drawbacks. If the Change Buffer contains a large amount of data, it may take several hours to merge all the data into the Buffer Pool. During the merge, disk I/O operations will be frequent, which will occupy some CPU resources.

Then you might ask, does a merge occur only if the cached page is loaded into the Buffer Pool? If it is never loaded, the Change Buffer will burst. InnoDB is clearly designed with this in mind. In addition to the corresponding page load, the merge is triggered by a commit transaction, service downtime, and service restart.

Welcome to wechat search to follow [SH full stack notes], reply [queue] to get a detailed explanation of the message queue, including basic concept analysis and RocketMQ detailed source code, continue to update.

That’s all for this blog post. If you found it helpful, please give it a thumbs up, a comment, a share and a comment.