Does innodb_data_reads include reads from the buffer pool?

Question

We have set up some monitoring on many of our databases that serve many different applications. It seems that they all report back more writes than reads when in reality that is very unlikely. I know much of the code inside and out and we do far more reads than we do writes. It has me wondering if a read is counted if it comes out of the cache (InnoDB buffer pool)?

Most of the stats seem to be based off of the innodb_data_reads system variable. Our buffer pools are large enough to contain all of our InnoDB tables.

One server I checked says it is like 10/1 writes to reads. However, when I view these two status variables:

Innodb_buffer_pool_read_requests: 1458637157995

Innodb_buffer_pool_reads: 516322

Innodb_data_reads: 643489

Innodb_data_writes: 17209580

Something doesn't work here..

Thanks for any help you can give!

Answer 1

Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests is very small, so the caching for reads is very effective. The former is actual I/O; the latter includes cached reads.

Innodb_pages_read + Innodb_pages_written may be the number of 16KB blocks read + written. In looking around on various machines, it seems that either number can be larger.

Indexes are included. Non-leaf-node pages of BTrees are included.

I don't know what Innodb_data_* are.

Although many operations are "read-modify-write", the "read" is often cached, but the "write" needs to be flushed for persistence and syncing.

Bug #65030: "The Innodb_buffer_pool_pages_flushed status variable was incorrectly set to twice the value it should be. Its value should never exceed the value of Innodb_pages_written." (fixed in 5.5.25)

Do you want to monitor physical reads/writes? Or logical reads/writes?