I haven't gotten any responses to my question from Friday, so I figured I'd post a more direct question:
The two variables 'innodb_pages_read' and 'innodb_buffer_pool_reads' are different. Â 'Innodb_pages_read' seems consistently higher than the buffer_pool_reads. Â Certainly the buffer pool reads 'pages', but what else would be counted as a page read?
On the flip side, 'innodb_pages_written' and 'innodb_buffer_pool_pages_flushed' seem consistent. Â
Anyone have any clues?
8 Comments
from this link http://johnjacobm.wordpress.com/2007/06/
Innodb_buffer_pool_reads = "The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read."
Innodb_pages_read="The number of pages read"
I hope that makes it a bit more clear.
Unfortunately, no. Â That appears to be exactly what the manual states as well. Â
innodb_page_reads are the actual number of pages read from the innodb_buffer_pool
-Bruce
Ok, but how does that differ from "Innodb_buffer_pool_reads".
BTW, I'm going to sit down and grok the source tomorrow to figure this out.
Innodb_buffer_pool_reads is how many it had to go to disk for because it couldn't get the data from the innodb buffer pool itself. Compare that with innodb_buffer_pool_read_requests...
buffer pool read performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests.
Each read request can read multiple pages AFAIK.
I did grok the source and that's what I found for innodb_page_reads. : )
-Bruce
I get that, but what about 'innodb_page_reads' It seems to equal neither of those two metrics.
It doesn't equal the other metrics because I believe multiple pages can be read from the innodb_buffer_pool at one time.
It is simply the actual number of pages that have been read from the innodb_buffer_pool, nothing more.
Innodb_pages_read is incremented whenever a file page is read from buffer pool. And when the page is not there in the buffer, InnoDB reads from disk to a new buffer while incrementing Innodb_buffer_pool_reads.
I would like to put it this way. Innodb_pages_read are the pages read from buffer pool and Innodb_buffer_pool_reads are the pages read for the buffer pool.
References:
1. http://forge.mysql.com/wiki/MySQL_Internals_Files_In_InnoDB_Sources [\buf (Buffering) section]
2. buf0buf.c
3. buf0rea.c