MySQL series (3)-Buffer Pool data structure

2022-05-06 0 By

Configure the size of the Buffer Pool. A Buffer Pool (BP for short) is a memory component of the DB. It can understand a piece of memory data structure, so it must have a size.The default value is 128M, which is actually too small. The BP size can be adjusted in production environment.For example, if DB is 16 core 32 GB, then you can allocate 2 GB memory to BP using the following configuration.[server] Innodb_buffer_POOL_size = 2147483648 Data page Data unit abstracted from MySQL.Suppose there must be a Buffer Pool in DB. How do we put our data in the Buffer Pool?The core data model of DB is the concept of table + field + row, that is, we all know that DB has a table, a table with many segments, and then a table with many rows of data, each row of data has its own field value.So our data is line by line in BP?No!MySQL data abstracted from the concept of a data page, he put a lot of rows of data in a data page, that there are a lot of data from a disk file page, each page put too many rows of data: data update a row, so at this point the DB will find this row of data in data page, then the row data from the disk file data page to load directly to the BP.That is, BP stores data pages: how do data pages on disk correspond to cached pages in BP?The size of data pages stored in the default disk is 16 KB, that is, one page of data contains 16 KB content.The data pages stored in BP are usually called cache pages. After all, BP is a buffer pool, in which the data is cached from disk to memory.By default, the size of a cache page corresponds to the size of a data page on disk, which is 16K.BP is assumed to be 128M and a data page is 16K.Description of a cache page Each cache page has a description.Include: the tablespace to which the data page belongs, the number of the data page, the address of the cache page in BP, etc.The description information itself is also block data. In BP, the description data of each cache page is placed first and each cache page is followed:The description data in BP is approximately 5% of the size of the cache page, that is, 800K. Assuming that the size of the buffer pool is set to 128M, the actual size of the buffer pool will actually be larger than 130 MB, because it will also store the description data of each cache page.