MySQL Bugs: #60905: table_io_waits_summary_by_index_usage aggregates non-insert DML wrongly

Bug #60905	table_io_waits_summary_by_index_usage aggregates non-insert DML wrongly
Submitted:	18 Apr 2011 14:31	Modified:	22 Apr 2011 13:38
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.2	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any

Description:
table_io_waits_summary_by_index_usage tracks the number of handler (row) calls against storage engines, either by index or not depending on the access method. 

For the DELETE and UPDATE cases, it appears to not matter whether we use an index - we always seem to increment only within the "null row", rather than against the index that was used to satisfy the query (if so).

This was undefined within WL#5379, citing only the INSERT case. 

I believe for DELETE and UPDATE handler calls, we should increment the appropriate index row based on the previous handler fetch call/instrument access method. This would more easily show which kinds of statements are causing full scan operations against the tables. 

How to repeat:
/* init data */
USE test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT, j INT, KEY(i)) ENGINE = InnoDB;
INSERT INTO t1 VALUES
(1,2), (3,4), (5,6), (7,8), (9,10);

/* should delete with a "single" PRIMARY lookup (2 PRIMARY fetch, 1 PRIMARY delete) */
DELETE FROM t1 WHERE i = 1;

/* should delete with a full scan (5 NULL fetch, 1 NULL delete) */
DELETE FROM t1 WHERE j = 8;

/* show the instrument data */

SELECT object_schema, 
       object_name, 
       index_name, 
       count_fetch,
       count_insert,
       count_update,
       count_delete
  FROM performance_schema.table_io_waits_summary_by_index_usage 
 WHERE object_schema = 'test' 
   AND object_name = 't1';

session wait stack graph for the above delete cases

Attachment: bug#60905-delete-stack.svg (image/svg+xml, text), 260.13 KiB.

Results after the two deletes being instrumented only:

mysql> SELECT object_schema, 
    ->        object_name, 
    ->        index_name, 
    ->        count_fetch,
    ->        count_insert,
    ->        count_update,
    ->        count_delete
    ->   FROM performance_schema.table_io_waits_summary_by_index_usage 
    ->  WHERE object_schema = 'test' 
    ->    AND object_name = 't1';
+---------------+-------------+------------+-------------+--------------+--------------+--------------+
| object_schema | object_name | index_name | count_fetch | count_insert | count_update | count_delete |
+---------------+-------------+------------+-------------+--------------+--------------+--------------+
| test          | t1          | i          |           2 |            0 |            0 |            0 |
| test          | t1          | NULL       |           5 |            0 |            0 |            2 |
+---------------+-------------+------------+-------------+--------------+--------------+--------------+
2 rows in set (0.00 sec)

Noted in 5.6.3 changelog.

Table I/O for the Performance Schema
table_io_waits_summary_by_index_usage table was counted as using no 
index for UPDATE and DELETE statements, even when an index was used.

CHANGESET - http://lists.mysql.com/commits/135684