Friday, April 11, 2008

Variable's Day Out #6: Innodb_buffer_pool_reads


Applicable To InnoDB
Type Status Variable
Dynamic NA
Possible Values Integer
Default Value NA
Category Performance


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:


Hope you enjoyed reading this.


Raja said...

Awesome parvesh. Good Article!


bigjo said...

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?

Paul said...


If you are seeing 406 innodb_buffer_pool_reads : 20,000,000 innodb_buffer_pool_read_requests, you are basically working completely from memory.

Paul Otto