Bug #74379 Rows fetched off by one in table_io_waits_summary_by_table
Submitted: 14 Oct 2014 13:16 Modified: 6 Nov 2014 21:58
Reporter: Øystein Grøvlen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.5 OS:Any
Assigned to: CPU Architecture:Any

[14 Oct 2014 13:16] Øystein Grøvlen
Description:
count_fetch/count_read is off by one in table_io_waits_summary_by_table.  This is probably because it counts the number of handler calls, not the number of rows that are actually returned.  The difference is that the last handler call will not return a row. Example:

mysql> CALL sys.ps_truncate_all_tables(false);      
+---------------------+
| summary             |
+---------------------+
| Truncated 31 tables |
+---------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select * from nation;
...
25 rows in set (0.00 sec)

mysql> select count_fetch from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME='nation';
+-------------+
| count_fetch |
+-------------+
|          26 |
+-------------+
1 row in set (0.00 sec)

For a single table scan, this difference is not to difficult to cope
with.  However, joins make things a bit more obscure:

mysql> CALL sys.ps_truncate_all_tables(false);
+---------------------+
| summary             |
+---------------------+
| Truncated 31 tables |
+---------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> select * from nation join region on n_regionkey = r_regionkey;
...
25 rows in set (0.01 sec)

mysql> select count_fetch from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME='nation';
+-------------+
| count_fetch |
+-------------+
|          30 |
+-------------+
1 row in set (0.01 sec)

In this case you have 5 rows of the outer table.  Hence, count_fetch
for the inner table will show 5 rows extra; one for each ref access.

How to repeat:
See above

Suggested fix:
Either change how this is computed, or change documentation to make sure users understand the difference between the number of rows that are fetched and the number of handler operation.  If latter, naming in sys schema also needs to be changed to not report this as number of rows fetched.
[6 Nov 2014 21:58] Paul DuBois
Noted in 5.7.6 changelog.

For the Performance Schema table_io_waits_summary_by_table table,
there was an off-by-one error for the COUNT_FETCH and COUNT_READ
values.