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 (free).

The innodb_buffer_pool_awe_mem_mb variable is relevant only on 32-bit Windows systems with more than 4 Gbyte of RAM using the so-called Address Windowing Extensions (AWE).

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_size.

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_dirty.

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 STATUScommand:

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

The 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 innodb_max_dirty_pages_pct?
• What is stored in innodb buffer pool beside data and index (=misc, row logs, undo?)
• How is flushed related to write requests?

buffer pool lru buffer pool instances

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