This is the fifth day of my participation in the August More text Challenge. For details, see:August is more challenging

Introduction: Mysql introduced the concept of buffer pool in Innodb engine to avoid reading and writing data from disk frequently. In addition, the buffer pool is an important component in mysql. Let’s take a look at this component.

What is a buffer pool

As mentioned in the introduction, mysql introduced the concept of buffer pools to avoid frequent access to disk data. Buffer pools allow direct access to commonly used data from memory to improve processing speed.

For example, mysql may save the data to the buffer pool and return it to the buffer pool instead of returning the data directly. In this case, mysql may read the data directly from the buffer pool.Copy the code

Of course, there is more to buffer pools than that, and their implementation and internal structure can be quite complex.

The internal structure of the buffer pool

A buffer pool is basically an in-memory component of a database. It consists of free lists, Flush lists, and LRU lists. The default size of the buffer pool is 128Mb.

Data management unit of the buffer pool

The buffer pool is a memory component of the database and must be managed. What is the unit of data management? Obviously it’s not going to be a one-by-one SQL statement. Data management is actually managed on a page by page basis. Pages stored in the buffer pool are called cache pages. The size is the same as the default page size on disk, which is 16KB. In addition, each cache page has a corresponding control block to store the description of the memory page. Its structure is similar to the following figure:

This includes the table space to which the data page belongs, the number of the data page, the address of the cached page in the Buffer Pool, and so on. Each cache page has its own description, which is also data and stored at the front of the Buffer Pool, while the cache page is stored at the back.

Data page

The core data model of database is: table + field + row, so mysql abstracted a concept of data page for data. A data page is a collection of data. A data page stores multiple rows of data. Its structure is roughly as shown in the figure below:

What problem does the buffer pool solve

Having said that, let’s talk about what a buffer pool actually implements. First directly above:

Mysql has added a redo log mechanism to innoDB to deal with the problem of database crashes, which can cause the loss of updated data in memory. It writes a copy of your actions to the redo log. For details, see steps 4 through 8 in the above picture. This way, even if your database crashes, mysql will be able to read the redo log from the redo log and restore it.

Conclusion: That’s the end of today’s introduction to buffer pools!