There are operations that require collecting stats and metrics and you want to have the most accurate of them. If you hit this bug, you can apply this workaround to confidently trust the data in the perf_schema
The table_io_waits_summary_by_index_usage Table
The table_io_waits_summary_by_index_usage table aggregates all table index I/O wait events, as generated by the wait/io/table/sql/handler instrument. The grouping is by table index.
- A value of PRIMARY indicates that table I/O used the primary index.
- A value of NULL means that table I/O used no index.
- Inserts are counted against INDEX_NAME = NULL.
Note: A DDL operation that changes the index structure of a table may cause the per-index statistics to be reset
select OBJECT_NAME, index_name, count_star,COUNT_READ, COUNT_WRITE, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where OBJECT_NAME='video';
+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | index_name | count_star | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| videos | (index 0) | 112021305 | 89548627 | 22472678 | 89548627 | 0 | 22472678 | 0 |
| videos | (index 1) | 1897792865 | 1897792865 | 0 | 1897792865 | 0 | 0 | 0 |
| videos | (index 2) | 5535110 | 5535110 | 0 | 5535110 | 0 | 0 | 0 |
| videos | (index 5) | 280759 | 280759 | 0 | 280759 | 0 | 0 | 0 |
| vidoes | (index 6) | 29207869 | 29207869 | 0 | 29207869 | 0 | 0 | 0 |
| vidoes | <null> | 24059932 | 0 | 24059932 | 0 | 24059585 | 0 | 347 |
+-------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+bug - index name not visible
babel-W-test>select OBJECT_NAME, index_name, count_star,COUNT_READ, COUNT_WRITE, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage;
+---------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| OBJECT_NAME | index_name | count_star | COUNT_READ | COUNT_WRITE | COUNT_FETCH | COUNT_INSERT | COUNT_UPDATE | COUNT_DELETE |
+---------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+
| config_system | (index 0) | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
| config_system | <null> | 61 | 61 | 0 | 61 | 0 | 0 | 0 |
| sessions | (index 0) | 6496 | 3248 | 3248 | 3248 | 0 | 3247 | 1 |
| sessions | <null> | 1090 | 0 | 1090 | 0 | 1090 | 0 | 0 |
| members | (index 0) | 2 | 0 | 2 | 0 | 0 | 2 | 0 |
| baskets | (index 0) | 2 | 1 | 1 | 1 | 0 | 0 | 1 |
| baskets | <null> | 1084 | 0 | 1084 | 0 | 1084 | 0 | 0 |
| albums | <null> | 2078 | 0 | 2078 | 0 | 2078 | 0 | 0 |
| cache_log | (index 1) | 2 | 2 | 0 | 2 | 0 | 0 | 0 |
+---------------+------------+------------+------------+-------------+-------------+--------------+--------------+--------------+Clean up summary tables
Summary tables can be truncated with TRUNCATE TABLE. Generally, the effect is to reset the summary columns to 0 or NULL, not to remove rows. This enables you to clear collected values and restart aggregation. That might be useful, for example, after you have made a runtime configuration change. Exceptions to this truncation behavior are noted in individual summary table sections.
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;index order
babel-W-test>show create table videos \G
***************************[ 1. row ]***************************
Table | videos
Create Table | CREATE TABLE `videos` (
<!-- ... -->
(index 0) PRIMARY KEY (`id`),
(index 1) KEY `video_album_id_i` (`album_id`),
(index 2) KEY `video_member_id_i` (`member_id`),
(index 3) KEY `video_delflag_storage_server_lastupdated_i` (`delflag`,`storage_server`,`lastupdated`),
(index 4) KEY `video_delflag_lastupdated_i` (`delflag`,`lastupdated`),
(index 5) KEY `video_lastupdated_i` (`lastupdated`),
(index 6) KEY `video_album_original_checksum_i` (`album_id`,`original_checksum`)
)
+---------------------------------------------+--------------+-------------------+-------------+
| Key_name | Seq_in_index | Column_name | Cardinality |
+---------------------------------------------+--------------+-------------------+-------------+
| PRIMARY | 1 | id | 122285 |
| videos_album_id_i | 1 | album_id | 27455 |
| videos_member_id_i | 1 | member_id | 28104 |
| videos_delflag_storage_server_lastupdated_i | 1 | delflag | 3 |
| videos_delflag_storage_server_lastupdated_i | 2 | storage_server | 3 |
| videos_delflag_storage_server_lastupdated_i | 3 | lastupdated | 42206 |
| videos_delflag_lastupdated_i | 1 | delflag | 3 |
| videos_delflag_lastupdated_i | 2 | lastupdated | 42206 |
| videos_lastupdated_i | 1 | lastupdated | 41477 |
| videos_album_original_checksum_i | 1 | album_id | 27817 |
| videos_album_original_checksum_i | 2 | original_checksum | 29458 |
+---------------------------------------------+--------------+-------------------+-------------+
return indexes that have not been used since then:
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
ORDER BY object_schema, object_name;