Introduction
So till now what we covered how Innodb Buffer is Distributed in part 1 is how the data is distributed in InnoDB Buffer and what is getting stored in memory , how to retrieve the metadata information from the system etc. Now what we are going to see is how the data stored in memory is mapped with the files tables on disk and we will discuss on some of the important system variables and status variables.
Lets start with an example, If you have a 300GB database hosted on a 16-32 GB memory host. So we know its practically not possible to store whole 300GB worth data in 16GB memory so InnoDB would constantly move the not so frequently used pages based on an algorithm called LRU ( Least Recently Used ) out of memory and bring in new data from the disk and they would be referred as young pages.
There is a innodb table space management blog by Oracle team which has clearly explained how a table space is created and stored on the disk. Please refer that for better understanding of your innodb IBD file.
How is the in-memory InnoDB buffer mapped with Disk objects?
What is Space or Space_ID ?
Let’s go back to the buffer pool distribution. Here we will be covering about few important metadata tables that would give us more details. If you refer the structure of below listed tables we would have SPACE or FILE_ID column in the Innodb_buffer tables and also should be available mostly in all Inoodb meta data tables
+---------------------------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+---------------------------------+-------------+
| FILES | FILE_ID |
| INNODB_DATAFILES | SPACE |
| INNODB_TABLESPACES_BRIEF | SPACE |
| INNODB_SESSION_TEMP_TABLESPACES | SPACE |
| INNODB_BUFFER_PAGE | SPACE |
| INNODB_TEMP_TABLE_INFO | SPACE |
| INNODB_TABLES | SPACE |
| INNODB_BUFFER_PAGE_LRU | SPACE |
| INNODB_INDEXES | SPACE |
| INNODB_TABLESPACES | SPACE |
+---------------------------------+-------------+This is an high level overview of how Innodb Buffer Pool in memory data is structured and mapped with On Disk objects
INFORMATION_SCHEMA.FILES
Whenever a schema and a table is created in InnoDB engine a FILE_ID is allocated to that table which can be referred as TABLESPACE ID as well. That FILE_ID/TABLESPACE_ID is mapped to INNODB_BUFFER tables as SPACE. so whenever it has to flush the modified pages to disk it knows to which FILE_ID at which Page_ID it has to be flushed.
select SPACE,NAME,SPACE_TYPE from INFORMATION_SCHEMA.INNODB_TABLESPACES where ( SPACE_TYPE IN (‘General’,’System’,’Undo’) OR NAME =’sys/sys_config’);
System tablespaces
These files are created when the data directory is initialised and a FILE_ID is assigned by the system when it comes up.
+------------+-------------+------------+------------------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME |
+------------+-------------+------------+------------------+
| 4294967294 | ./mysql.ibd | TABLESPACE | mysql |
| 0 | ./ibdata1 | TABLESPACE | innodb_system |
| 4294967293 | ./ibtmp1 | TEMPORARY | innodb_temporary |
| 4294967279 | ./undo_001 | UNDO LOG | innodb_undo_001 |
| 4294967278 | ./undo_002 | UNDO LOG | innodb_undo_002 |
+------------+-------------+------------+------------------+User tablespaces
Below are examples of user table spaces so whenever a table is created a FILE_ID gets assigned to it and
+---------+-------------------------------------+------------+-------------------------------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME |
+---------+-------------------------------------+------------+-------------------------------+
| 695 | ./m*************** | TABLESPACE | mys********* |
| 699 | ./s************ | TABLESPACE | sbt****** |
| 702 | ./t********** | TABLESPACE | tes**** |
| 703 | ./m***************************** | TABLESPACE | med*********************** |
| 704 | ./m****************************** | TABLESPACE | med************************ |
| 705 | ./m**************************** | TABLESPACE | med********************** |
| 707 | ./m********************** | TABLESPACE | med**************** |
| 710 | ./m******************************** | TABLESPACE | mam************************** |
| 711 | ./m************************* | TABLESPACE | mam******************* |
| 712 | ./g****************** | TABLESPACE | gen************ |
+---------+-------------------------------------+------------+-------------------------------+INFORMATION_SCHEMA.INNODB_TABLESPACES
The File_ID created in INFORMATION_SCHEMA.FILES can be mapped with SPACE ID assigned in INFORMATION_SCHEMA.INNODB_TABLESPACES to the respective tablespaces as you can see below. So Innodb has to track the file id/space id of each table so it would know when the a related query comes where to load the data from.
Try the below query.
select SPACE,PAGE_SIZE,SPACE_TYPE,FILE_SIZE,ALLOCATED_SIZE,FS_BLOCK_SIZE from INFORMATION_SCHEMA.INNODB_TABLESPACES order by SPACE;
+------------+-------------------------------+------------+------------+----------------+
| SPACE | NAME | SPACE_TYPE | FILE_SIZE | ALLOCATED_SIZE |
+------------+-------------------------------+------------+------------+----------------+
| 703 | med*********************** | Single | 1199570944 | 1199575040 |
| 704 | med************************ | Single | 121634816 | 121638912 |
.........................................................................................
| 705 | med********************** | Single | 147456 | 147456 |
| 707 | med**************** | Single | 62914560 | 62918656 |
| 710 | mam************************** | Single | 19922944 | 19927040 |
| 711 | mam******************* | Single | 41943040 | 41947136 |
| 4294967278 | inn************ | Undo | 16777216 | 16777216 |
| 4294967279 | inn************ | Undo | 33554432 | 33554432 |
| 4294967293 | inn************* | System | 12582912 | 12587008 |
| 4294967294 | mys** | General | 37748736 | 37748736 |
+------------+-------------------------------+------------+------------+----------------+Sys Schema based Views
The sys schema contains many views that provides you insightful data to perform detail analysis of your system. Here we are going to discuss about only sys.innodb_buffer_stats_by_schema and sys.innodb_buffer_stats_by_table views.
sys.innodb_buffer_stats_by_schema
This view summarises the information in the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, grouped by schema.
select object_schema object_schema,allocated,data,pages,pages_hashed,pages_old,rows_cached from sys.innodb_buffer_stats_by_schema;
+------------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+------------------+------------+------------+-------+--------------+-----------+-------------+
| med***** | 964.67 MiB | 677.96 MiB | 61739 | 0 | 33392 | 73375 |
| mam******** | 266.33 MiB | 175.87 MiB | 17045 | 0 | 5512 | 5253 |
| mys** | 12.19 MiB | 7.74 MiB | 780 | 0 | 523 | 3109 |
| med********** | 1.58 MiB | 962.89 KiB | 101 | 0 | 19 | 2327 |
| mam************* | 880.00 KiB | 511.45 KiB | 55 | 0 | 5 | 1491 |
| Inn********** | 80.00 KiB | 9.40 KiB | 5 | 0 | 0 | 10 |
+------------------+------------+------------+-------+--------------+-----------+-------------+sys.innodb_buffer_stats_by_table or sys.x$innodb_buffer_stats_by_table
This view summarises the information in the INFORMATION_SCHEMA.INNODB_BUFFER_PAGE table, grouped by schema and table.
+--------------------------------+------------+------------+-------+-----------+-------------+
| objectname | allocated | data | pages | pages_old | rows_cached |
+--------------------------------+------------+------------+-------+-----------+-------------+
| med************** | 459.02 MiB | 301.02 MiB | 29377 | 12777 | 161733 |
| med*********************** | 266.81 MiB | 199.50 MiB | 17076 | 8815 | 134127 |
| mam*************************** | 227.19 MiB | 144.54 MiB | 14540 | 5469 | 11433 |
| med************************ | 108.58 MiB | 71.95 MiB | 6949 | 6336 | 91767 |
| med************************ | 87.34 MiB | 73.66 MiB | 5590 | 5162 | 54605 |
| med**************** | 41.48 MiB | 31.31 MiB | 2655 | 278 | 24757 |
| mam******************* | 26.91 MiB | 23.25 MiB | 1722 | 0 | 18830 |
| mam************************** | 7.84 MiB | 5.63 MiB | 502 | 0 | 3222 |
| mys********** | 4.67 MiB | 3.58 MiB | 299 | 224 | 14966 |
| mys********* | 1.25 MiB | 905.17 KiB | 80 | 6 | 921 |
+--------------------------------+------------+------------+-------+-----------+-------------+Warning: Querying views that access the INNODB_BUFFER_PAGE table can affect database performance. Do not query these views on a production system and make sure you perform any such analysis on lower environment and get aware of possible impacts and try it in prod carefully.
Innodb Buffer Pool System Variables
These are the important system variables which directly impacts the performance or behaviour of the Innodb Buffer pool.
show global variables like 'innodb_buffer_pool%';| System Variable_Name | Description |
|---|---|
| innodb_buffer_pool_chunk_size | innodb_buffer_pool_chunk_size defines the chunk size for InnoDB buffer pool resizing operations. |
| innodb_buffer_pool_dump_at_shutdown | If enabled innodb records the pages cached in InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart |
| innodb_buffer_pool_dump_now | When it is enabled InnoDB will record the pages cached in the buffer pool immediately. |
| innodb_buffer_pool_dump_pct | Specifies the percentage of the most recently used pages for each buffer pool to read out and dump with default setting as 25. For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped. Should be adjusted based of workload. |
| innodb_buffer_pool_filename | Defines the filename to save the list of page numbers created by innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_dump_now. |
| innodb_buffer_pool_in_core_file | Disabling it reduces the size of core files by excluding InnoDB buffer pool pages |
| innodb_buffer_pool_instances | The number of instances into which the the buffer pool will be split across which takes effect only if the Memory size is larger than 1 GB. |
| innodb_buffer_pool_load_abort | Interrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now. |
| innodb_buffer_pool_load_at_startup | If enabled will load the pages dumped during innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now into the buffer pool while starting MySQL. |
| innodb_buffer_pool_load_now | Immediately loads the pages stored in innodb buffer pool without restarting the mysql. Can be useful to bring cache memory back to a known state during benchmarking or to ready the MySQL server to resume its normal workload after running queries for reports or maintenance. |
| innodb_buffer_pool_size | It is the memory area where InnoDB caches its table and index data. |
NOTE : Most of the parameters are safe in its default values unless you have more concurrency issues which you can analyze through innodb engine status and configure innodb_buffer_pool_instances, innodb_buffer_pool_dump_pct accordingly . Or Feel free to drop a note to our support and our team would be more than happy to help you.
Innodb Buffer Pool Status Variables
These are the important system status variables through which we can monitor how innodb buffer pool related settings are being used.
show global status like 'innodb_buffer_pool%';| Status Variable_Name | Description |
|---|---|
| Innodb_buffer_pool_dump_status | The progress of an operation to record the pages held in the InnoDB buffer pool, triggered by the setting of innodb_buffer_pool_dump_at_shutdown orinnodb_buffer_pool_dump_now. |
| Innodb_buffer_pool_load_status | The progress of loading the pages recorded by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now back into the buffer pool. |
| Innodb_buffer_pool_resize_status | The status of resizing operation of the InnoDB buffer pool can be monitored using this variable |
| Innodb_buffer_pool_resize_status_code | Reports status codes for tracking online buffer pool resizing operations. Each status code represents a resizing operation stage as below 0: No Resize operation in progress 1: Starting Resize 2: Disabling AHI (Adaptive Hash Index) 3: Withdrawing Blocks 4: Acquiring Global Lock 5: Resizing Pool 6: Resizing Hash 7: Resizing Failed |
| Innodb_buffer_pool_resize_status_progress | Reports a percentage value indicating the progress of the current stage of an online buffer pool resizing operation. This variable is used in conjunction with Innodb_buffer_pool_resize_status_code, which reports a status code indicating the current stage of an online buffer pool resizing operation. |
| Innodb_buffer_pool_bytes_data | The total number of bytes in the InnoDB buffer pool containing data. The number includes both dirty and clean pages. |
| Innodb_buffer_pool_bytes_dirty | The total current number of bytes held in dirty pages in the InnoDB buffer pool. |
| | |
| Innodb_buffer_pool_pages_data | The number of pages with data in the buffer pool. |
| Innodb_buffer_pool_pages_dirty | The number of pages in memory which has been changed after loading from disk. NOTE : The change would be in the InnoDB log files, so the changes are persistent provided the log files are flushed to disk on regularly. |
| Innodb_buffer_pool_pages_flushed | The number of requests to flush pages from the InnoDB buffer pool. |
| Innodb_buffer_pool_pages_free | The number of free pages in the InnoDB buffer pool. |
| Innodb_buffer_pool_pages_misc | The number of pages in the InnoDB buffer pool that are busy because they have been allocated for administrative overhead, such as row locks or the adaptive hash index. |
| Innodb_buffer_pool_pages_total | The total number of pages in the buffer pool. |
| Innodb_buffer_pool_read_ahead_rnd | The number of “random” read-aheads initiated by InnoDB. This happens when a query randomly scans a large portion of a table. |
| Innodb_buffer_pool_read_ahead | The number of pages read into the InnoDB buffer pool by the read-ahead background thread. |
| Innodb_buffer_pool_read_ahead_evicted | he number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries. |
| Innodb_buffer_pool_read_requests | The number of logical read requests InnoDB has done. |
| Innodb_buffer_pool_reads | The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk. |
| Innodb_buffer_pool_wait_free | Normally, writes to the InnoDB buffer pool happen in the background. However, if it is necessary to read or create a page and no clean pages are available, it is also necessary to wait for pages to be flushed first. This counter counts instances of these waits. If the buffer pool size has been set properly, this value should be small. |
| Innodb_buffer_pool_write_requests | The number writes done to the InnoDB buffer pool. |
Conclusion
As we said before 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. In this blog we tried to cover how the innodb pages are referenced and mapped with the innodb table spaces along with the important system variables and status variables related to innodb buffer pool alone.
Be mindful in performing any analytical operations on information_schema related to InnoDB which can be performance impacting so please test them in lower environment and use them carefully.
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
Please find some other reference blogs which have detailed innodb storage management.
- innodb table space management blog by Oracle team has explained in detail how user defined table and its corresponding index data, in InnoDB are stored in files that have an extension .ibd.
- The impacts of fragmentation in mysql by Percona team have beautifully explained about the fragmentation with mysql