|Scope||GLOBAL | SESSION|
According to the official documentation, this variable defines "The number of logical reads that
InnoDB could not satisfy from the buffer pool and had to do a single-page read.".
But we can go a little further to see the what happens in and around this variable. As per the MySQL Internals documentation, a buffer is defined as a memory area that contains copies of pages that ordinarily are in the main data file (read innodb tablespace). And "buffer pool" is simply a set of all the buffers. Now, when InnoDB needs to access a new page it looks first in the buffer pool, in case page isn't there InnoDB reads it from the disk to a new buffer. InnoDB chucks old buffers (basing its decision on LRU algorithm) when it has to make space for a new buffer. This is the time when this variable gets incremented.
Without doubt, 0 (zero). But that is only possible if you are not using InnoDB. :)
So, while using InnoDB, you have to see that this variable remains as small as possible when compared to Innodb_buffer_pool_read_requests. In case you are seeing a high value for this, you might like to increase your innodb_buffer_pool_size and/or look at the size of your data/indexes at large.
I always used to wonder the difference between
Innodb_pages_read, but never too seriously until I read Jay Janssen's query about it. And then thought of figuring out myself. As I stated there also "Innodb_pages_read are the pages read from buffer pool and Innodb_buffer_pool_reads are the pages read for the buffer pool", so no confusion.
- MySQL manual entry on Innodb_buffer_pool_reads
- MySQL Internals documentation of InnoDB source files - read [\buf (BUFFERING)] section.
- Jay Janssen's post asking differnence and many answers
- Jay Janssen's own answer on the same.
Hope you enjoyed reading this.