Introduction
Innodb has always been the most important concept in MySQL and Innodb_buffer_pool_size the most important variable to configure for ages now. Over the period of time #mysql observability has improved a lot using information_schema and performance schema metrics and one of those use cases we have covered now. In this blog we are going to cover how innodb buffer pool gets allocated and what data gets allocated to it. Also we would cover about the meta data tables available to get some insights of the data stored in the Buffer pool Memory. The below readings we have collected are from a fairly busy environment with below configuration.
Environment Overview
- RAM – 16GB
- CPU – 8 Core
- Database Size – 5 GB
- MySQL Version – Community – 8.0.33
List of Buffer Pool Variables
Here is the list of variables we will be focussing on this blog.
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_size | 8589934592 |
+-------------------------------------+----------------+
11 rows in set (0.01 sec)- innodb_buffer_pool_size – The total size of the InnoDB buffer pool in bytes.
- innodb_buffer_pool_instances – The number of instances into which the the buffer pool will be split if InnoDB buffer pool is greater than 1 GB.
- innodb_buffer_pool_chunk_size – Defines the chunk size that InnoDB buffer pool would be further breaking down to.
We must always make sure that Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
Another important thing to keep check on is while configuring innodb_buffer_pool_chunk_size is there shouldn’t be than 1000 chunks for any size innodb_buffer_pool_size. You can use below query to validate.
mysql> select @@innodb_buffer_pool_size/@@innodb_buffer_pool_chunk_size 'should not exceed 1000';
+------------------------+
| should not exceed 1000 |
+------------------------+
| 64.0000 |
+------------------------+
1 row in set (0.00 sec)
How is the InnoDB buffer allocated ?
General practice is to allocate 50-70% memory to innodb_buffer_pool_size and configure number of innodb_buffer_pool_instances in the configuration and rest of the things are handled by innodb engine when the instance comes online. What we are going to see here is how the data gets distributed in the buffer pool at a very high level and how to find out what is stored from the system metadata available for us to examine. Components we are going to cover are
- innodb_buffer_pool_instances
- innodb_buffer_pool_chunk_size
- blocks
- Pages
- space_id/file_id
What is innodb_buffer_pool_instances ?
Innodb_buffer_pool_instances is nothing but the number of instances the innodb buffer pool will get divided into. Dividing the buffer pool into separate instances can improve concurrency. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool has its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.
You can try below query which would tell you how your InnoDB is configured in your environment.
select
concat(round((@@innodb_buffer_pool_size)/(102410241024)),'G') as 'Total Innodb buffer pool',
@@innodb_buffer_pool_instances as 'Number of Instances', round(@@innodb_buffer_pool_size/@@innodb_buffer_pool_instances/@@innodb_buffer_pool_chunk_size) as 'Approx. Chunks in 1 Instance', concat(round(round(@@innodb_buffer_pool_size/@@innodb_buffer_pool_instances/@@innodb_buffer_pool_chunk_size)@@innodb_buffer_pool_chunk_size/1024/1024/1024),'G') as '1 instance size', concat(round((@@)/(10241024)),'M') as '1 chunk size', concat(round((@@innodb_page_size)/(1024)),'K') as '1 Innodb Page Size', round((@@innodb_buffer_pool_chunk_size/@@innodb_page_size)) as 'Number of pages in 1 chunk', round(((@@innodb_buffer_pool_size/@@innodb_buffer_pool_instances/@@innodb_buffer_pool_chunk_size) * @@innodb_buffer_pool GSample Output
Total Innodb buffer pool: 8G
Number of Instances: 8
Approx. Chunks in 1 Instance: 8
1 instance size: 1G
1 chunk size: 128M
1 Innodb Page Size: 16K
Number of pages in 1 chunk: 8192
Number of pages in 1 Instance: 65536What do we have in each Innodb Buffer pool Instance ?
Generally InnoDB buffer pool gets broken in terms of small memory chunks called innodb_buffer_pool_chunk_size˘ and it some how assigns ( innodb_buffer_pool_size / innodb_buffer_pool_chunk_size / innodb_buffer_pool_instances ) chunks to each innodb buffer pool instance which further get divided into 8192 Blocks
What is innodb_buffer_pool_chunk_size ?
innodb_buffer_pool_chunk_size defines the chunk size for InnoDB buffer pool resizing operations.
To avoid copying all buffer pool pages during resizing operations, the operation is performed in “chunks”. By default, innodb_buffer_pool_chunk_size is 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).
Chunk just seems to be a logical separation of 8192 blocks within an InnoDB Buffer Instance.
select POOL_ID, max(BLOCK_ID),min(BLOCK_ID) from INNODB_BUFFER_PAGE group by POOL_ID;
+---------+---------------+---------------+
| POOL_ID | max(BLOCK_ID) | min(BLOCK_ID) |
+---------+---------------+---------------+
| 0 | 8191 | 0 |
| 1 | 8191 | 0 |
| 2 | 8191 | 0 |
| 3 | 8191 | 0 |
| 4 | 8191 | 0 |
| 5 | 8191 | 0 |
| 6 | 8191 | 0 |
| 7 | 8191 | 0 |
+---------+---------------+---------------+
8 rows in set (3.63 sec)NOTE : So we have 0 – 8191 Block_ids which is 8192 blocks in each buffer_pool_instances
Number of pages in each Innodb Buffer pool instance
Each buffer pool instance would contain 65536 pages if that instance size is 1 GB.
select POOL_ID,count(1) Page_Cnt from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE group by POOL_ID;
+---------+----------+
| POOL_ID | Page_Cnt |
+---------+----------+
| 0 | 65531 |
| 1 | 65530 |
| 2 | 65532 |
| 3 | 65533 |
| 4 | 65529 |
| 5 | 65530 |
| 6 | 65529 |
| 7 | 65529 |
+---------+----------+
8 rows in set (2.95 sec)What is a Block ? How is it further broken down ?
Can be found referred as buffer pool block ID in INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table. As mentioned earlier in the diagram each block contains 8 pages.
mysql> select POOL_ID,BLOCK_ID,count(1) Page_Cnt from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where POOL_ID=0 group by POOL_ID,BLOCK_ID;
+---------+----------+----------+
| POOL_ID | BLOCK_ID | Page_Cnt |
+---------+----------+----------+
| 0 | 0 | 8 |
| 0 | 1 | 8 |
| 0 | 2 | 8 |
| 0 | 3 | 8 |
.................................
.................................
.................................
.................................
.................................
| 0 | 8187 | 8 |
| 0 | 8188 | 8 |
| 0 | 8189 | 8 |
| 0 | 8190 | 8 |
| 0 | 8191 | 3 |
+---------+----------+----------+
8192 rows in set (2.90 sec)So InnoDB page call be called as the smallest element here. Now let’s try to find some useful information from our metadata tables and make sure you perform these admin tasks with almost precaution.
Page Distribution in Innodb Buffer Pool Instance
So we now know Innodb stores the relevant data or indexes in pages and it can be assigned to any instance , block etc. But what does it actually stores in it ? How is InnoDB page further classified. Let’s try to understand this using below query.
select POOL_ID,PAGE_TYPE,PAGE_STATE,count(PAGE_STATE) from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where POOL_ID=0 group by POOL_ID,PAGE_TYPE,PAGE_STATE;This shows be the different types of pages that are being used and being allocated to buffer pool ID 0. The page types tells us the kind of information/data has been loaded from those pages into buffer pool memory.
+---------+-------------------+------------+-------------------+
| POOL_ID | PAGE_TYPE | PAGE_STATE | count(PAGE_STATE) |
+---------+-------------------+------------+-------------------+
| 0 | UNDO_LOG | FILE_PAGE | 127 |
| 0 | SYSTEM | FILE_PAGE | 4 |
| 0 | INDEX | FILE_PAGE | 17522 |
| 0 | INODE | FILE_PAGE | 9 |
| 0 | LOB_FIRST | FILE_PAGE | 3820 |
| 0 | SDI_INDEX | FILE_PAGE | 30 |
| 0 | IBUF_BITMAP | FILE_PAGE | 10 |
| 0 | FILE_SPACE_HEADER | FILE_PAGE | 8 |
| 0 | EXTENT_DESCRIPTOR | FILE_PAGE | 1 |
| 0 | LOB_DATA | FILE_PAGE | 3253 |
| 0 | ALLOCATED | FILE_PAGE | 26 |
| 0 | IBUF_INDEX | FILE_PAGE | 5 |
| 0 | UNKNOWN | NOT_USED | 40717 |
+---------+-------------------+------------+-------------------Allocated Page Types
Descriptions of page type values and pool IDs across which the data is spread across.
| Page Type | POOL_ID | Page_Count | Description |
|---|---|---|---|
ALLOCATED | 0,1,2,3,4,5,6,7 | 255 | Freshly allocated page |
EXTENT_DESCRIPTOR | 0,1,3,4,5,6,7 | 15 | Extent descriptor page |
FILE_SPACE_HEADER | 0,1,2,3,4,5,6,7 | 59 | File space header |
IBUF_BITMAP | 0,1,2,3,4,5,6,7 | 93 | Insert buffer bitmap |
IBUF_FREE_LIST | 2 | 9 | Insert buffer free list |
IBUF_INDEX | 0,1,2,3 | 85 | Insert buffer index |
INDEX | 0,1,2,3,4,5,6,7 | 112397 | B-tree node |
INODE | 0,1,2,3,4,5,6,7 | 62 | Index node |
LOB_DATA | 0,1,2,3,4,5,6,7 | 27426 | Uncompressed LOB data |
LOB_FIRST | 0,1,2,3,4,5,6,7 | 33085 | First page of uncompressed LOB |
LOB_INDEX | 1,4,5 | 5 | Uncompressed LOB index |
RSEG_ARRAY | 1,4 | 2 | Rollback segment array |
SDI_INDEX | 0,1,2,3,5,6,7 | 53 | SDI index |
SYSTEM | 0,1,2,3,4,6,7 | 387 | System page |
TRX_SYSTEM | 3 | 1 | Transaction system data |
UNDO_LOG | 0,1,2,3,4,6,7 | 1068 | Undo log page |
UNKNOWN | 0,1,2,3,4,5,6,7 | 349242 | Unknown |
Unallocated Page Types
These are the other set of page types to store BLOB , Compressed or encrypted objects as listed below. Since in this use case we don’t have any objects fitting thes
e page types they are left unused.
| Page Type | Description |
|---|---|
BLOB | Uncompressed BLOB page |
COMPRESSED_BLOB2 | Subsequent comp BLOB page |
COMPRESSED_BLOB | First compressed BLOB page |
ENCRYPTED_RTREE | Encrypted R-tree |
FIL_PAGE_TYPE_UNUSED | Unused |
PAGE_IO_COMPRESSED | Compressed page |
PAGE_IO_COMPRESSED_ENCRYPTED | Compressed and encrypted page |
PAGE_IO_ENCRYPTED | Encrypted page |
RTREE_INDEX | R-tree index |
SDI_BLOB | Uncompressed SDI BLOB [ Serialized Dictionary Information] |
SDI_COMPRESSED_BLOB | Compressed SDI BLOB |
ZLOB_DATA | Compressed LOB data |
ZLOB_FIRST | First page of compressed LOB [ Large Objects ] |
ZLOB_FRAG | Compressed LOB fragment |
ZLOB_FRAG_ENTRY | Compressed LOB fragment index |
ZLOB_INDEX | Compressed LOB index |
Pages that contain system data
Below query tells us the approximate number of pages that contain system data and the total number of buffer pool pages, and approximate percentage of pages that contain system data.
SELECT
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0)) AS system_pages,
(SELECT COUNT(*)FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ROUND((system_pages/total_pages) * 100)) AS system_page_percentage;
Sample Data:
+--------------+-------------+------------------------+
| system_pages | total_pages | system_page_percentage |
+--------------+-------------+------------------------+
| 437045 | 524244 | 83 |
+--------------+-------------+------------------------+Type of System Data loaded in Buffer Pool
The type of system data in the buffer pool can be determined by querying the PAGE_TYPE value.
SELECT PAGE_TYPE, count(1) Page_Count FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NULL OR (INSTR(TABLE_NAME, '/') = 0 AND INSTR(TABLE_NAME, '.') = 0) group by PAGE_TYPE;
Sample Data:
+-------------------+------------+
| PAGE_TYPE | Page_Count |
+-------------------+------------+
| UNDO_LOG | 1068 |
| SYSTEM | 387 |
| INODE | 66 |
| LOB_FIRST | 33366 |
| SDI_INDEX | 56 |
| IBUF_BITMAP | 94 |
| FILE_SPACE_HEADER | 62 |
| INDEX | 49730 |
| EXTENT_DESCRIPTOR | 15 |
| LOB_DATA | 27422 |
| ALLOCATED | 297 |
| IBUF_INDEX | 85 |
| UNKNOWN | 324380 |
| RSEG_ARRAY | 2 |
| LOB_INDEX | 5 |
| IBUF_FREE_LIST | 9 |
| TRX_SYSTEM | 1 |
+-------------------+------------+Pages Containing User data
Below query tells us the approximate number of pages that contain user data, the total number of buffer pool pages, and an approximate percentage of pages that contain user data
SELECT
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL AND (INSTR(TABLE_NAME, '/') > 0 OR INSTR(TABLE_NAME, '.') > 0)) AS user_pages,
(SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ROUND((user_pages/total_pages) * 100)) AS user_page_percentage;
Sample Data:
+------------+-------------+----------------------+
| user_pages | total_pages | user_page_percentage |
+------------+-------------+----------------------+
| 87199 | 524244 | 17 |
+------------+-------------+----------------------+Pages Containing Index Data
Below query tells us the query the INDEX_NAME column using the name of the index and the number of pages and total data size allocated to that index
SELECT INDEX_NAME, COUNT(*) AS Pages, ROUND(SUM(IF(COMPRESSED_SIZE = 0, @@GLOBAL.innodb_page_size, COMPRESSED_SIZE))/1024/1024) AS 'Total Data (MB)' FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE group by INDEX_NAME;
Sample Data:
+----------------------+--------+-----------------+
| INDEX_NAME | Pages | Total Data (MB) |
+----------------------+--------+-----------------+
| NULL | 437036 | 6829 |
| PRIMARY | 70087 | 1095 |
| catalog_id | 2 | 0 |
| default_collation_id | 2 | 0 |
| tablespace_id | 7 | 0 |
| file_name | 1 | 0 |
| name | 21 | 0 |
| table_id | 60 | 1 |
| srs_id | 18 | 0 |
| index_id | 22 | 0 |
+----------------------+--------+-----------------+Unused Buffer Pool Pages
Now this is my favourite one I used the below query to fetch count of unused buffer pool pages. This tells us an important metric of how much of the memory is over allocated to innodb buffer pool size which can be reclaimed or reused based on the use case. Generally when we suggest customer that memory is over allocated and we can reduce or downgrade the instance this metric would help to show how much of buffer pool is allowed but unused support our statement of over provisioning.
select POOL_ID,PAGE_TYPE,PAGE_STATE,count(PAGE_STATE) Page_Count from INFORMATION_SCHEMA.INNODB_BUFFER_PAGE where PAGE_STATE='NOT_USED' group by POOL_ID,PAGE_TYPE,PAGE_STATE;
Sample Data:
+---------+-----------+------------+------------+
| POOL_ID | PAGE_TYPE | PAGE_STATE | Page_Count |
+---------+-----------+------------+------------+
| 0 | UNKNOWN | NOT_USED | 44789 |
| 1 | UNKNOWN | NOT_USED | 44515 |
| 2 | UNKNOWN | NOT_USED | 44575 |
| 3 | UNKNOWN | NOT_USED | 45499 |
| 4 | UNKNOWN | NOT_USED | 44049 |
| 5 | UNKNOWN | NOT_USED | 44249 |
| 6 | UNKNOWN | NOT_USED | 42312 |
| 7 | UNKNOWN | NOT_USED | 44966 |
+---------+-----------+------------+------------+Resizing Buffer Pool based on Unused Pages metrics
Like I said in this use case memory was over allocated so I was able to resize the buffer_pool from 8GB to 4GB and the unused page drastically dropped and memory got free back into this system. So the general formula is 50-70% of available RAM OR closer to the size of the database whichever is lesser.
+---------+-----------+------------+------------+
| POOL_ID | PAGE_TYPE | PAGE_STATE | Page_Count |
+---------+-----------+------------+------------+
| 0 | UNKNOWN | NOT_USED | 12022 |
| 1 | UNKNOWN | NOT_USED | 11749 |
| 2 | UNKNOWN | NOT_USED | 11808 |
| 3 | UNKNOWN | NOT_USED | 12732 |
| 4 | UNKNOWN | NOT_USED | 11285 |
| 5 | UNKNOWN | NOT_USED | 11485 |
| 6 | UNKNOWN | NOT_USED | 9547 |
| 7 | UNKNOWN | NOT_USED | 12202 |
+---------+-----------+------------+------------+While I did this operation only thing you have to make sure is resizing shouldn’t effect your existing performance. Whereas in this case if you see the memory allocated to dictionary , database pages didn’t change only thing that change is free buffers which reduced significantly with 4 GB innodb buffer pool size.
| 8 GB InnoDB Buffer Pool Size | 4 GB InnoDB Buffer Pool Size |
|---|---|
BUFFER POOL AND MEMORYTotal large memory allocated 0 | BUFFER POOL AND MEMORY |
Conclusion
Innodb being the most critical portion of any mysql instance it is really important to understand how InnoDB Functions and how to find out the specifics related to innodb from the metadata tables and make use of them. Any operations on information_schema related to InnoDB can be performance impacting so please test them and use them carefully. Also I have not covered details about how Space_ID/File_ID are associated and what is its use etc ? Will be covering them shortly in the next blog.
Hope you find this information useful. Keep reading and follow us for more exclusive content for open source databases. Do not forget to reach out to our support is your would like to know about our services
Here are some of our blogs which you might find interesting.
Trackbacks/Pingbacks