Properties:
| Applicable To | InnoDB |
| Type | Status Variable |
| Scope | GLOBAL | SESSION |
| Dynamic | NA |
| Possible Values | Integer |
| Default Value | NA |
| Category | Performance |
Description:
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.
Best value:
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.
What else:
I always used to wonder the difference between Innodb_buffer_pool_reads and 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.
Read more:
- 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.
3 comments:
Awesome parvesh. Good Article!
regards,
Raja
Analyst,
www.realestate-in-tamilnadu.com
And how can you decrease that number?
I have 20 M innodb_buffer_pool_read_requests, so from your article I assume 406 innodb_buffer_pool_reads can be ok. But can I optimise my MySql database to decrease that number?
bigjo,
If you are seeing 406 innodb_buffer_pool_reads : 20,000,000 innodb_buffer_pool_read_requests, you are basically working completely from memory.
Regards,
Paul Otto
Post a Comment