Bug #82345 Explanation of status variables lacks information
Submitted: 26 Jul 2016 10:52 Modified: 4 Aug 2016 19:50
Reporter: Jörg Brühe (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:Any OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb, read, STATUS

[26 Jul 2016 10:52] Jörg Brühe
Description:
The documentation does not provide sufficient information about several status variables.
The quotes below are taken from the MySQL 5.6 manual, but the problem affects all versions.

My current issue is with read operations done by InnoDB. See this extract of "show global status" from a 5.6.19 instance:

joerg@trift$ grep -i 'innodb.*read' status-20160721-1111
| Innodb_buffer_pool_read_ahead_rnd             | 0             |
| Innodb_buffer_pool_read_ahead                 | 37839         |
| Innodb_buffer_pool_read_ahead_evicted         | 0             |
| Innodb_buffer_pool_read_requests              | 40254656787   |
| Innodb_buffer_pool_reads                      | 36304         |
| Innodb_data_pending_reads                     | 0             |
| Innodb_data_read                              | 1235111936    |
| Innodb_data_reads                             | 76036         |
| Innodb_pages_read                             | 75380         |
| Innodb_rows_read                              | 57783679559   |

"buffer pool read requests" and "buffer pool reads" are explained in the manual, completely ok.
But what are "data reads" and "pages read"?

"The total number of data reads" (the text about "data reads") is not really helpful. It makes me assume there are other data read operations in addition to those fulfilling a buffer pool read request - if so, which ones?

And "The number of pages read by operations on InnoDB tables" (the text about "pages read") isn't any better.
In my case, it is lower than "data reads" - does that imply there are "data reads" which are not about InnoDB tables?

In my attempts to understand it, I tried to calculate the page count from the total byte count:

joerg@trift$ expr 1235111936 / 16384
75385
joerg@trift$ expr 75385 \* 16384
1235107840
joerg@trift$ expr 1235111936 - 1235107840
4096

The first observation is that "data read" (bytes) divided by page size is closer to "pages read" than to "data reads".
The second is that the byte count is no multiple of the page size. If the byte count is correct, at least one read operation did not return a whole page.

How to repeat:
Do a "show global status" and try to understand all counters about read operations.
[27 Jul 2016 8:11] MySQL Verification Team
Hello Jörg,

Thank you for the report and feedback!

Thanks,
Umesh
[27 Jul 2016 8:11] MySQL Verification Team
Hello Jörg,

Thank you for the report and feedback!

Thanks,
Umesh
[4 Aug 2016 19:50] Daniel Price
Posted by developer:
 
The documentation for the following status variables was updated for MySQL 5.5, 5.6, and 5.7:

Innodb_data_read: The amount of data read since the server was started (in bytes).
Innodb_data_reads: The total number of data reads (OS file reads).
Innodb_pages_read: The number of pages read from the InnoDB buffer pool by operations on InnoDB tables.

The changes should appear online soon.

Thank you for the bug report.