- InnoDB Buffer Cache Has Sub-Optimal Hit Rate
Logical I/O is many times faster than physical I/O. Therefore,administrators should strive to keep physical I/O to a minimum. To achieve this goal, MySQL tries to load the maximum possible InnoDB table space in memory so that the I/O will be performed in memory, thereby ensuring that the InnoDB buffer cache hit-rate will be high.
The InnoDB storage engine is multi-versional. It is stored in buffer pool pages containing data and the index of the database, as well as the redo log. The InnoDB storage engine uses the SQL standard ACID. Increasing the size of the buffer pool should decrease the load average of the machine. By increasing the size of the buffer pool, more pages of the database are stored in the memory which may then be read without accessing the disk. In addtition, each page loaded in the memory should remain there longer, even if it is marked dirty after an update, due to more space in the buffer for the redo log.
However, an excessive increase in the size of the buffer pool that does not leave enough space in the system could lead to sub-optimal performance. This could be the case in a multiple engine server with MyISAM and Memory. Each engine requires memory space, while MyISAM needs enough space for the system to perform the data I/O. An increase in the size of the tables handled by InnoDB may have lead to this situation.
To correct this issue, first change the size of the innodb_buffer_pool_size variable in the options files (i.e., my.cnf or my.ini, depending on the system). Be sure not to increase the value of innodb_buffer_pool_size too much. When finished making the change, restart the server. Monitor the change using MySQL Enterprise Monitor or check the ratio after some time to let the server load the pages in the memory. The ratio is calculated like this: 100 * (1 – (innodb_buffer_pool_read_requests / innodb_buffer_pool_reads)).