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.