You are here
InnoDB variables and status explained
InnoDB Buffer Pool
The InnoDB Buffer Pool is the memory area where the InnoDB Storage Engine caches its data and index blocks. Each InnoDB data and index block has a size of
Innodb_page_size (16384 byte = 16 kbyte). The InnoDB Buffer Pool is configured in bytes with the
innodb_buffer_pool_size variable. On a dedicated system the InnoDB Buffer Pool can be configured up to 80% of the systems physical RAM (
The usage of the InnoDB Buffer Pool can be measured with the
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%' command. The sum of data, misc and free pages is equivalent to total pages. And the number of total pages multiplied by
Innodb_page_size corresponds to your
Innodb_buffer_pool_pages_data 1757 Innodb_buffer_pool_pages_misc + 10 Innodb_buffer_pool_pages_free + 2072 Innodb_buffer_pool_pages_total = 3839
Innodb_buffer_pool_pages_total x Innodb_page_size = innodb_buffer_pool_size 3839 x 16384 = 62898176 (= 60 Mbyte)
Innodb_buffer_pool_pages_data shows the number of dirty and clean data and index pages.
Innodb_buffer_pool_pages_misc shows the number of pages that are busy because they have been allocated for administrative overhead such as row locks or the adaptive hash index.
A small number of
Innodb_buffer_pool_pages_free pages does not necessarily indicate that you InnoDB Buffer Pool is too small. Where instead a large number of free pages over a longer period is a strong indicator that your InnoDB Buffer Pool is too big and can easily be decreased.
Innodb_buffer_pool_pages_dirty indicates the number of InnoDB buffer pool data pages that have been changed in memory, but the changes are not yet written (flushed) to the InnoDB data files. The opposite of a dirty page is a clean page.
The InnoDB main thread tries to write pages from the InnoDB Buffer Pool so that the percentage of dirty (not yet written) pages will not exceed the value of
Innodb_buffer_pool_pages_data * innodb_max_dirty_pages_pct / 100 > Innodb_buffer_pool_pages_dirty 1757 * 90 / 100 > 5
Innodb_buffer_pool_pages_flushed indicates the number of requests to flush pages from the InnoDB buffer pool to the data file.
Similiar information about the InnoDB Buffer Pool constitution can be retrieved with the command:
SHOW ENGINE INNODB STATUS\G:
---------------------- BUFFER POOL AND MEMORY ---------------------- Buffer pool size 512 Free buffers 490 Database pages 22 Modified db pages 0
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. The
Innodb_buffer_pool_wait_free counter counts how many times this has happened.
Innodb_buffer_pool_wait_free greater than 0 is a strong indicator that the InnoDB Buffer Pool is too small.
InnoDB Buffer Pool hit ratio
Innodb_buffer_pool_read_requests indicates the the number of logical read requests (read from memory) InnoDB has done.
Innodb_buffer_pool_reads indicates the number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk (physical reads).
The InnoDB Buffer Pool hit ratio is a indicator how often your pages are retrieved from memory instead of disk:
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100 = InnoDB Buffer Pool hit ratio 1600770 ( 1600770 + 1715) * 100 = 99.9%
The same ratio can be calculated over the last n seconds with the
SHOW ENGING INNODB STATUS command:
Per second averages calculated from the last 58 seconds ... ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Buffer pool hit rate 1000 / 1000
A InnoDB Buffer Pool hit ratio below 99.9% is a weak indicator that your InnoDB Buffer Pool could be increased.
InnoDB additional memory Pool
innodb_additional_mem_pool_size variables configures the size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If InnoDB runs out of memory in this pool, it starts to allocate memory from the operating system and writes warning messages to the MySQL error log.
How much memory InnoDB has allocated for this additional memory pool can be found with:
pager grep 'additional pool' SHOW ENGINE INNODB STATUS\G Total memory allocated 20618000; in additional pool allocated 676608
Questions to answer
- What happens if innodb reaches
- What is stored in innodb buffer pool beside data and index (=misc, row logs, undo?)
- How is flushed related to write requests?
Innodb_buffer_pool_read_ahead_rnd indicates the number of random read-aheads initiated by InnoDB. This happens when a query scans a large portion of a table but in random order. This variable was removed in newer MySQL releases.
Innodb_buffer_pool_read_ahead_seq indicates the number of sequential read-aheads initiated by InnoDB. This happens when InnoDB does a sequential full table scan. This variable was removed in newer MySQL releases.
Innodb_buffer_pool_write_requests indicates the number writes done to the InnoDB buffer pool.
The ratio of write requests to pages flushed should be an indicator of how many rows are changed in a block before it is flushed to disk:
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = row changes per flush 8367 / 8160 = 1.02 row changes per flush
A value much higher that 1 is an indicator of a good locality of data
State of 5.0.92