# Table\_io\_waits summary\_by\_index\_usage

**URL:** <https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372>\
**Category:** MySQL & MariaDB\
**Created:** [February 11, 2022, 11:03am UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372 "2022-02-11T11:03:09Z")\
**Posts on this page:** 18\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 11, 2022, 11:03am UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/1 "2022-02-11T11:03:10Z")

</div>

Hello,

I am trying to review index usage using the performance\_schema.table\_io\_waits\_summary\_by\_index\_usage table.

It looks to be enabled as the instrument is enabled:

±--------------------------±--------±------+  
| NAME | ENABLED | TIMED |  
±--------------------------±--------±------+  
| wait/io/table/sql/handler | YES | YES |  
±--------------------------±--------±------+

I can see the inserts count\_star and count\_insert incrementing. However. When I run a select query that returns records the count\_read is not increasing.

table\_io\_waits\_summary\_by\_index\_usage  
±-----------------------------±-----------±-----------+  
| index\_name | COUNT\_STAR | COUNT\_READ |  
±-----------------------------±-----------±-----------+  
| NULL | 409799 | 0 |  
| event | 0 | 0 |  
| event\_time\_user\_object\_event | 0 | 0 |  
| external\_grouping\_id | 0 | 0 |  
| grouping\_id | 0 | 0 |  
| object | 0 | 0 |  
| object\_key | 0 | 0 |  
| object\_property | 0 | 0 |  
| PRIMARY | 0 | 0 |  
| user | 0 | 0 |  
| user\_object | 0 | 0 |  
±-----------------------------±-----------±-----------+

Is there something else I need to configure?

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [February 11, 2022, 3:43pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/2 "2022-02-11T15:43:04Z")

</div>

Hi @Jamie_Downs , thanks for posting to the Percona forums!

Can you share us the output of:

```auto
SELECT * FROM performance_schema.setup_consumers;

```

I suspect you need to have the `events_waits_%` in the `ENABLED=YES` state

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 11, 2022, 3:55pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/3 "2022-02-11T15:55:13Z")

</div>

Thanks Michael.

```auto
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | YES |
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_history_long | NO |
| events_waits_current | NO |
| events_waits_history | NO |
| events_waits_history_long | NO |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+

```

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [February 11, 2022, 4:54pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/4 "2022-02-11T16:54:45Z")

</div>

Hi @Jamie_Downs

Please execute:

```auto
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_waits_%';

```

Then check to see if you’re getting events for your index usage table.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 11, 2022, 5:02pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/5 "2022-02-11T17:02:37Z")

</div>

I will do this but I should mention inserts are incrementing in there. Also when I did a select count one of the indexes read counters increased.  
I will do as you have suggested.  
Thanks.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 11, 2022, 5:08pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/6 "2022-02-11T17:08:20Z")

</div>

Still no joy i’m afraid.

```auto
mysql> select * from performance_schema.setup_consumers WHERE NAME LIKE 'events_waits_%';
+---------------------------+---------+
| NAME | ENABLED |
+---------------------------+---------+
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | YES |
+---------------------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT index_name,
    -> COUNT_STAR,
    -> COUNT_READ
    -> FROM performance_schema.table_io_waits_summary_by_index_usage
    -> WHERE object_name = 'db'
    -> #and (index_name = 'object' or index_name is null)
    -> and object_schema = 'instance_32021'
    -> ORDER BY index_name;
    FROM db
        WHERE object_kind IN ('SubmCountry')
        AND event_time >= '2021-11-13 14:03:17.0'
        AND (event = 'Edit' OR event = 'Delete')
        limit 1;
+------------------------------+------------+------------+
| index_name | COUNT_STAR | COUNT_READ |
+------------------------------+------------+------------+
| NULL | 325377 | 0 |
| event | 0 | 0 |
| event_time_user_object_event | 0 | 0 |
| external_grouping_id | 0 | 0 |
| grouping_id | 0 | 0 |
| object | 0 | 0 |
| object_key | 0 | 0 |
| object_property | 0 | 0 |
| PRIMARY | 0 | 0 |
| user | 0 | 0 |
| user_object | 0 | 0 |
+------------------------------+------------+------------+
11 rows in set (0.16 sec)

mysql>
mysql> SELECT object_key, grouping_id
    -> FROM db
    -> WHERE object_kind IN ('SubmCountry')
    -> AND event_time >= '2021-11-13 14:03:17.0'
    -> AND (event = 'Edit' OR event = 'Delete')
    -> limit 1;
+-----------------------------+--------------------------------------+
| object_key | grouping_id |
+-----------------------------+--------------------------------------+
| A0L0000001DG054:SubmCountry | d0f046be-12e0-4e32-b35c-56769ae48a8e |
+-----------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT object_key, grouping_id
    -> FROM db FORCE INDEX (object)
    -> WHERE object_kind IN ('SubmCountry')
    -> AND event_time >= '2021-11-13 14:03:17.0'
    -> AND (event = 'Edit' OR event = 'Delete')
    -> limit 1;
+-----------------------------+--------------------------------------+
| object_key | grouping_id |
+-----------------------------+--------------------------------------+
| A0L0000001DG054:SubmCountry | d0f046be-12e0-4e32-b35c-56769ae48a8e |
+-----------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> SELECT index_name,
    -> COUNT_STAR,
    -> COUNT_READ
    -> FROM performance_schema.table_io_waits_summary_by_index_usage
    -> WHERE object_name = 'db'
    -> #and (index_name = 'object' or index_name is null)
    -> and object_schema = 'instance_32021'
    -> ORDER BY index_name;
+------------------------------+------------+------------+
| index_name | COUNT_STAR | COUNT_READ |
+------------------------------+------------+------------+
| NULL | 325377 | 0 |
| event | 0 | 0 |
| event_time_user_object_event | 0 | 0 |
| external_grouping_id | 0 | 0 |
| grouping_id | 0 | 0 |
| object | 0 | 0 |
| object_key | 0 | 0 |
| object_property | 0 | 0 |
| PRIMARY | 0 | 0 |
| user | 0 | 0 |
| user_object | 0 | 0 |
+------------------------------+------------+------------+
11 rows in set (0.17 sec)

```

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 16, 2022, 7:05pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/7 "2022-02-16T19:05:11Z")

</div>

I was wondering if anyone else has any suggestions or seen this before? Thanks.

---

<div class="post-metadata">

**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\
**Post date:** [February 19, 2022, 9:11pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/8 "2022-02-19T21:11:03Z")

</div>

Hi Jamie,

Let’s recap the necessary steps to enable this monitoring:

```auto
UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_waits_%';

```

Once enabled, you can validate by running:

```auto
SELECT * FROM performance_schema.setup_consumers;
SELECT * FROM performance_schema.setup_instruments WHERE name LIKE 'wait%';

```

Once we verified, we can go through the metrics.

Interesting to mention that PMM monitors these metrics:

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/5/5922414608a2fe975a601105a0c787931eda10c2.png)  
And in case you want to check:  
[https://pmmdemo.percona.com/graph/d/mysql-performance-schema/mysql-performance-schema-details?orgId=1&refresh=1m](https://pmmdemo.percona.com/graph/d/mysql-performance-schema/mysql-performance-schema-details?orgId=1&refresh=1m)

Now, remember these metrics are monitoring I/O waits. this means that if when the query is executed there are no I/O waits, then the value won’t be incremented.

We can reproduce this with sysbench. Here are the commands for your convenience:

```auto
sysbench --db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
  --mysql-port=45008 --mysql-host=127.0.0.1 --mysql-db=test --range_size=100 \
  --table_size=100000 --tables=100 --threads=1 --events=0 --time=60 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua --mysql_storage_engine=InnoDB prepare

```

```auto
sysbench --db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
  --mysql-port=45008 --mysql-host=127.0.0.1 --mysql-db=test --range_size=100 \
  --table_size=100000 --tables=100 --threads=10 --events=0 --time=6000 \
  --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua --db-ps-mode=auto --report-interval=1 run

```

You need to have more data than the buffer pool can hold so you can observe the reads:

```auto
mysql> select count_star, count_read, object_name, index_name from performance_schema.table_io_waits_summary_by_index_usage where count_read > 0 limit 10;
+------------+------------+-------------+------------+
| count_star | count_read | object_name | index_name |
+------------+------------+-------------+------------+
| 2047 | 2047 | joinit | PRIMARY |
| 283450 | 281451 | sbtest1 | PRIMARY |
| 276924 | 274922 | sbtest2 | PRIMARY |
| 278904 | 276902 | sbtest3 | PRIMARY |
| 284712 | 282763 | sbtest4 | PRIMARY |
| 276233 | 274226 | sbtest5 | PRIMARY |
| 284196 | 282252 | sbtest6 | PRIMARY |
| 281585 | 279588 | sbtest7 | PRIMARY |
| 279939 | 277893 | sbtest8 | PRIMARY |
| 279518 | 277494 | sbtest9 | PRIMARY |
+------------+------------+-------------+------------+
10 rows in set (0.00 sec)

```

So to conclude, to observe these counters you need to have enough data so MySQL needs to fetch the data from the disk.

Let me know if this clarifies your question.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [February 23, 2022, 2:20pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/9 "2022-02-23T14:20:29Z")

</div>

Hi Vinicius,  
Many thanks for taking the time to explain. As you suggested I think the issue was that the data was already in the buffer or the tables I was using were too small.  
I tried the same test against a table with 146 million rows and the numbers incremented as you would expect.  
Thanks again.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 13, 2022, 12:19pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/10 "2022-07-13T12:19:23Z")

</div>

@vinicius.grippa Sorry me again. I’m not convinced we have got to the bottom of this.

Even with the buffer pool at 1GB. The numbers in table\_io\_waits\_summary\_by\_table and table\_io\_waits\_summary\_by\_index\_usage are not incrementing for reads. The query takes 5 mins so it must be doing some IO. I should point out the table is partitioned if this makes any difference?

Steps.

1. Restart mysql
2. Set b-pool 1GB (SET GLOBAL innodb\_buffer\_pool\_size=1073741824;)
3. Ensure event\_wats enabled (UPDATE performance\_schema.setup\_consumers SET ENABLED=‘YES’ WHERE NAME LIKE ‘events\_waits\_%’😉

```auto
+----------------------------------+---------+
| NAME | ENABLED |
+----------------------------------+---------+
| events_stages_current | NO |
| events_stages_history | NO |
| events_stages_history_long | NO |
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | NO |
| events_transactions_current | NO |
| events_transactions_history | NO |
| events_transactions_history_long | NO |
| events_waits_current | YES |
| events_waits_history | YES |
| events_waits_history_long | YES |
| global_instrumentation | YES |
| thread_instrumentation | YES |
| statements_digest | YES |
+----------------------------------+---------+

```

```auto

```

Execute the Query

```auto
SELECT count(*) FROM xyz AS o FORCE INDEX (event_time_user_object_event) WHERE o.event_time >= '2021-06-09 00:00:00.0' AND o.event_time <= '2022-07-12 23:59:00.0' AND (o.object_kind IN ('OSY000000001G12'));
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (5 min 48.65 sec)

```

Look at file stats in file\_summary\_by\_instance. This suggests 39GB of reads.

```auto
+--------------------------------+------------+------------+-------------+------------+---------------+-----------+-----------+
| file | count_star | count_read | count_write | total_read | total_written | total | write_pct |
+ -------------------------------+------------+------------+-------------+------------+---------------+-----------+-----------+
| @@datadir/_data_audit_2022.ibd | 2620498 | 2620493 | 0 | 39.99 GiB | 0 bytes | 39.99 GiB | 0.00 |
| @@datadir/_data_audit_2021.ibd | 863793 | 863788 | 0 | 13.18 GiB | 0 bytes | 13.18 GiB | 0.00 |
| @@datadir/_data_audit_2020.ibd | 10 | 5 | 0 | 80.00 KiB | 0 bytes | 80.00 KiB | 0.00 |
+--------------------------------+------------+------------+-------------+------------+---------------+-----------+-----------+

```

Still no data in table\_io\_waits\_summary\_by\_index\_usage or table\_io\_waits\_summary\_by\_table

```auto
+----------------+------------------+---------------------------------+------------+----------------+----------------+----------------+----------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ |
+----------------+------------------+---------------------------------+------------+----------------+----------------+----------------+----------------+------------+
| db_name | xyz_data_audit | PRIMARY | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | object_key | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | event_time_user_object_event | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | user | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | object | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | user_object | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | external_grouping_id | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | grouping_id | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | event | 0 | 0 | 0 | 0 | 0 | 0 |
| db_name | xyz_data_audit | object_property | 0 | 0 | 0 | 0 | 0 | 0 |
+----------------+------------------+---------------------------------+------------+----------------+----------------+----------------+----------------+------------+
11 rows in set (0.00 sec)

+----------------+------------------+------------+----------------+----------------+----------------+----------------+------------+
| OBJECT_SCHEMA | OBJECT_NAME | COUNT_STAR | SUM_TIMER_WAIT | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MAX_TIMER_WAIT | COUNT_READ |
+----------------+------------------+------------+----------------+----------------+----------------+----------------+------------+
| db_name | xyz_data_audit | 0 | 0 | 0 | 0 | 0 | 0 |
+----------------+------------------+------------+----------------+----------------+----------------+----------------+------------+

```

Thanks…

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 13, 2022, 12:20pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/11 "2022-07-13T12:20:18Z")

</div>

setup\_instruments

```auto
+--------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO |
| wait/synch/mutex/sql/LOCK_des_key_file | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_xids | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_commit_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_done | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_flush_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_index | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_log | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_sync_queue | NO | NO |
| wait/synch/mutex/sql/MYSQL_RELAY_LOG::LOCK_xids | NO | NO |
| wait/synch/mutex/sql/hash_filo::lock | NO | NO |
| wait/synch/mutex/sql/Gtid_set::gtid_executed::free_intervals_mutex | NO | NO |
| wait/synch/mutex/sql/LOCK_crypt | NO | NO |
| wait/synch/mutex/sql/LOCK_error_log | NO | NO |
| wait/synch/mutex/sql/LOCK_global_user_client_stats | NO | NO |
| wait/synch/mutex/sql/LOCK_global_table_stats | NO | NO |
| wait/synch/mutex/sql/LOCK_global_index_stats | NO | NO |
| wait/synch/mutex/sql/LOCK_gdl | NO | NO |
| wait/synch/mutex/sql/LOCK_global_system_variables | NO | NO |
| wait/synch/mutex/sql/LOCK_manager | NO | NO |
| wait/synch/mutex/sql/LOCK_prepared_stmt_count | NO | NO |
| wait/synch/mutex/sql/LOCK_sql_slave_skip_counter | NO | NO |
| wait/synch/mutex/sql/LOCK_slave_net_timeout | NO | NO |
| wait/synch/mutex/sql/LOCK_slave_trans_dep_tracker | NO | NO |
| wait/synch/mutex/sql/LOCK_server_started | NO | NO |
| wait/synch/mutex/sql/LOCK_keyring_operations | NO | NO |
| wait/synch/mutex/sql/LOCK_socket_listener_active | NO | NO |
| wait/synch/mutex/sql/LOCK_start_signal_handler | NO | NO |
| wait/synch/mutex/sql/LOCK_status | NO | NO |
| wait/synch/mutex/sql/LOCK_system_variables_hash | NO | NO |
| wait/synch/mutex/sql/LOCK_table_share | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_temporary_tables | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_thd_data | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_thd_query | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_thd_sysvar | NO | NO |
| wait/synch/mutex/sql/LOCK_user_conn | NO | NO |
| wait/synch/mutex/sql/LOCK_uuid_generator | NO | NO |
| wait/synch/mutex/sql/LOCK_sql_rand | NO | NO |
| wait/synch/mutex/sql/LOG::LOCK_log | NO | NO |
| wait/synch/mutex/sql/Master_info::data_lock | NO | NO |
| wait/synch/mutex/sql/Master_info::run_lock | NO | NO |
| wait/synch/mutex/sql/Master_info::sleep_lock | NO | NO |
| wait/synch/mutex/sql/Master_info::info_thd_lock | NO | NO |
| wait/synch/mutex/sql/Slave_reporting_capability::err_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::data_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::sleep_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::info_thd_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::log_space_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::run_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::pending_jobs_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::exit_count_lock | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::temp_tables_lock | NO | NO |
| wait/synch/mutex/sql/Worker_info::jobs_lock | NO | NO |
| wait/synch/mutex/sql/Query_cache::structure_guard_mutex | NO | NO |
| wait/synch/mutex/sql/TABLE_SHARE::LOCK_ha_data | NO | NO |
| wait/synch/mutex/sql/LOCK_error_messages | NO | NO |
| wait/synch/mutex/sql/LOCK_log_throttle_qni | NO | NO |
| wait/synch/mutex/sql/Gtid_state | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_query_plan | NO | NO |
| wait/synch/mutex/sql/Cost_constant_cache::LOCK_cost_const | NO | NO |
| wait/synch/mutex/sql/THD::LOCK_current_cond | NO | NO |
| wait/synch/mutex/sql/key_mts_temp_table_LOCK | NO | NO |
| wait/synch/mutex/sql/LOCK_reset_gtid_table | NO | NO |
| wait/synch/mutex/sql/LOCK_compress_gtid_table | NO | NO |
| wait/synch/mutex/sql/key_mts_gaq_LOCK | NO | NO |
| wait/synch/mutex/sql/thd_timer_mutex | NO | NO |
| wait/synch/mutex/sql/Commit_order_manager::m_mutex | NO | NO |
| wait/synch/mutex/sql/Relay_log_info::slave_worker_hash_lock | NO | NO |
| wait/synch/mutex/sql/LOCK_offline_mode | NO | NO |
| wait/synch/mutex/sql/LOCK_default_password_lifetime | NO | NO |
| wait/synch/mutex/mysys/BITMAP::mutex | NO | NO |
| wait/synch/mutex/mysys/IO_CACHE::append_buffer_lock | NO | NO |
| wait/synch/mutex/mysys/IO_CACHE::SHARE_mutex | NO | NO |
| wait/synch/mutex/mysys/KEY_CACHE::cache_lock | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_charset | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_heap | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_lock | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_malloc | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK::mutex | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_myisam | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_net | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_open | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_threads | NO | NO |
| wait/synch/mutex/mysys/TMPDIR_mutex | NO | NO |
| wait/synch/mutex/mysys/THR_LOCK_myisam_mmap | NO | NO |
| wait/synch/mutex/sql/LOCK_audit_mask | NO | NO |
| wait/synch/mutex/session/LOCK_srv_session_threads | NO | NO |
| wait/synch/mutex/sql/LOCK_event_queue | NO | NO |
| wait/synch/mutex/sql/Event_scheduler::LOCK_scheduler_state | NO | NO |
| wait/synch/mutex/sql/LOCK_thd_list | NO | NO |
| wait/synch/mutex/sql/LOCK_thd_remove | NO | NO |
| wait/synch/mutex/sql/LOCK_thread_ids | NO | NO |
| wait/synch/mutex/sql/LOCK_load_client_plugin | NO | NO |
| wait/synch/mutex/sql/LOCK_item_func_sleep | NO | NO |
| wait/synch/mutex/sql/LOCK_thread_cache | NO | NO |
| wait/synch/mutex/sql/LOCK_connection_count | NO | NO |
| wait/synch/mutex/sql/MDL_wait::LOCK_wait_status | NO | NO |
| wait/synch/mutex/sql/Partiton_share::auto_inc_mutex | NO | NO |
| wait/synch/mutex/sql/LOCK_open | NO | NO |
| wait/synch/mutex/sql/LOCK_table_cache | NO | NO |
| wait/synch/mutex/sql/LOCK_slave_list | NO | NO |
| wait/synch/mutex/sql/LOCK_transaction_cache | NO | NO |
| wait/synch/mutex/sql/LOCK_plugin | NO | NO |
| wait/synch/mutex/sql/LOCK_plugin_delete | NO | NO |
| wait/synch/mutex/innodb/commit_cond_mutex | NO | NO |
| wait/synch/mutex/innodb/innobase_share_mutex | NO | NO |
| wait/synch/mutex/innodb/autoinc_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_flush_state_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_LRU_list_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_free_list_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_zip_free_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_zip_hash_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_pool_zip_mutex | NO | NO |
| wait/synch/mutex/innodb/cache_last_read_mutex | NO | NO |
| wait/synch/mutex/innodb/dict_foreign_err_mutex | NO | NO |
| wait/synch/mutex/innodb/dict_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/recalc_pool_mutex | NO | NO |
| wait/synch/mutex/innodb/file_format_max_mutex | NO | NO |
| wait/synch/mutex/innodb/fil_system_mutex | NO | NO |
| wait/synch/mutex/innodb/flush_list_mutex | NO | NO |
| wait/synch/mutex/innodb/fts_bg_threads_mutex | NO | NO |
| wait/synch/mutex/innodb/fts_delete_mutex | NO | NO |
| wait/synch/mutex/innodb/fts_optimize_mutex | NO | NO |
| wait/synch/mutex/innodb/fts_doc_id_mutex | NO | NO |
| wait/synch/mutex/innodb/fts_pll_tokenize_mutex | NO | NO |
| wait/synch/mutex/innodb/log_flush_order_mutex | NO | NO |
| wait/synch/mutex/innodb/hash_table_mutex | NO | NO |
| wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO |
| wait/synch/mutex/innodb/ibuf_mutex | NO | NO |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO |
| wait/synch/mutex/innodb/log_bmp_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/log_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/log_sys_write_mutex | NO | NO |
| wait/synch/mutex/innodb/log_cmdq_mutex | NO | NO |
| wait/synch/mutex/innodb/mutex_list_mutex | NO | NO |
| wait/synch/mutex/innodb/page_cleaner_mutex | NO | NO |
| wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | NO | NO |
| wait/synch/mutex/innodb/purge_sys_pq_mutex | NO | NO |
| wait/synch/mutex/innodb/recv_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/redo_rseg_mutex | NO | NO |
| wait/synch/mutex/innodb/noredo_rseg_mutex | NO | NO |
| wait/synch/mutex/innodb/rw_lock_list_mutex | NO | NO |
| wait/synch/mutex/innodb/rw_lock_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_innodb_monitor_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_misc_tmpfile_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_monitor_file_mutex | NO | NO |
| wait/synch/mutex/innodb/buf_dblwr_mutex | NO | NO |
| wait/synch/mutex/innodb/trx_undo_mutex | NO | NO |
| wait/synch/mutex/innodb/trx_pool_mutex | NO | NO |
| wait/synch/mutex/innodb/trx_pool_manager_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/lock_mutex | NO | NO |
| wait/synch/mutex/innodb/lock_wait_mutex | NO | NO |
| wait/synch/mutex/innodb/trx_mutex | NO | NO |
| wait/synch/mutex/innodb/srv_threads_mutex | NO | NO |
| wait/synch/mutex/innodb/rtr_active_mutex | NO | NO |
| wait/synch/mutex/innodb/rtr_match_mutex | NO | NO |
| wait/synch/mutex/innodb/rtr_path_mutex | NO | NO |
| wait/synch/mutex/innodb/rtr_ssn_mutex | NO | NO |
| wait/synch/mutex/innodb/trx_sys_mutex | NO | NO |
| wait/synch/mutex/innodb/thread_mutex | NO | NO |
| wait/synch/mutex/innodb/sync_array_mutex | NO | NO |
| wait/synch/mutex/innodb/zip_pad_mutex | NO | NO |
| wait/synch/mutex/innodb/row_drop_list_mutex | NO | NO |
| wait/synch/mutex/innodb/master_key_id_mutex | NO | NO |
| wait/synch/mutex/innodb/scrub_stat_mutex | NO | NO |
| wait/synch/mutex/csv/tina | NO | NO |
| wait/synch/mutex/csv/TINA_SHARE::mutex | NO | NO |
| wait/synch/mutex/myisam/MI_SORT_INFO::mutex | NO | NO |
| wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock | NO | NO |
| wait/synch/mutex/myisam/MI_CHECK::print_msg | NO | NO |
| wait/synch/mutex/myisammrg/MYRG_INFO::mutex | NO | NO |
| wait/synch/mutex/archive/Archive_share::mutex | NO | NO |
| wait/synch/mutex/blackhole/blackhole | NO | NO |
| wait/synch/mutex/sql/tz_LOCK | NO | NO |
| wait/synch/rwlock/sql/Binlog_transmit_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/Binlog_relay_IO_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/LOCK_grant | NO | NO |
| wait/synch/rwlock/sql/LOGGER::LOCK_logger | NO | NO |
| wait/synch/rwlock/sql/LOCK_sys_init_connect | NO | NO |
| wait/synch/rwlock/sql/LOCK_sys_init_slave | NO | NO |
| wait/synch/rwlock/sql/LOCK_system_variables_hash | NO | NO |
| wait/synch/rwlock/sql/Query_cache_query::lock | NO | NO |
| wait/synch/rwlock/sql/gtid_commit_rollback | NO | NO |
| wait/synch/rwlock/sql/gtid_mode_lock | NO | NO |
| wait/synch/rwlock/sql/channel_map_lock | NO | NO |
| wait/synch/rwlock/sql/channel_lock | NO | NO |
| wait/synch/rwlock/sql/Trans_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/LOCK_consistent_snapshot | NO | NO |
| wait/synch/rwlock/sql/Server_state_delegate::lock | NO | NO |
| wait/synch/rwlock/sql/Binlog_storage_delegate::lock | NO | NO |
| wait/synch/rwlock/mysys/SAFE_HASH::lock | NO | NO |
| wait/synch/rwlock/session/LOCK_srv_session_collection | NO | NO |
| wait/synch/rwlock/sql/LOCK_dboptions | NO | NO |
| wait/synch/rwlock/sql/MDL_lock::rwlock | NO | NO |
| wait/synch/rwlock/sql/MDL_context::LOCK_waiting_for | NO | NO |
| wait/synch/sxlock/innodb/btr_search_latch | NO | NO |
| wait/synch/sxlock/innodb/dict_operation_lock | NO | NO |
| wait/synch/sxlock/innodb/fil_space_latch | NO | NO |
| wait/synch/sxlock/innodb/checkpoint_lock | NO | NO |
| wait/synch/sxlock/innodb/fts_cache_rw_lock | NO | NO |
| wait/synch/sxlock/innodb/fts_cache_init_rw_lock | NO | NO |
| wait/synch/sxlock/innodb/trx_i_s_cache_lock | NO | NO |
| wait/synch/sxlock/innodb/trx_purge_latch | NO | NO |
| wait/synch/sxlock/innodb/index_tree_rw_lock | NO | NO |
| wait/synch/sxlock/innodb/index_online_log | NO | NO |
| wait/synch/sxlock/innodb/dict_table_stats | NO | NO |
| wait/synch/sxlock/innodb/hash_table_locks | NO | NO |
| wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock | NO | NO |
| wait/synch/rwlock/myisam/MYISAM_SHARE::mmap_lock | NO | NO |
| wait/synch/rwlock/validate/LOCK_dict_file | NO | NO |
| wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock | NO | NO |
| wait/synch/rwlock/sql/THR_LOCK_servers | NO | NO |
| wait/synch/rwlock/sql/THR_LOCK_udf | NO | NO |
| wait/synch/cond/sql/PAGE::cond | NO | NO |
| wait/synch/cond/sql/TC_LOG_MMAP::COND_active | NO | NO |
| wait/synch/cond/sql/TC_LOG_MMAP::COND_pool | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::COND_done | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::update_cond | NO | NO |
| wait/synch/cond/sql/MYSQL_BIN_LOG::prep_xids_cond | NO | NO |
| wait/synch/cond/sql/MYSQL_RELAY_LOG::COND_done | NO | NO |
| wait/synch/cond/sql/MYSQL_RELAY_LOG::update_cond | NO | NO |
| wait/synch/cond/sql/MYSQL_RELAY_LOG::prep_xids_cond | NO | NO |
| wait/synch/cond/sql/Query_cache::COND_cache_status_changed | NO | NO |
| wait/synch/cond/sql/COND_manager | NO | NO |
| wait/synch/cond/sql/COND_server_started | NO | NO |
| wait/synch/cond/sql/COND_socket_listener_active | NO | NO |
| wait/synch/cond/sql/COND_start_signal_handler | NO | NO |
| wait/synch/cond/sql/COND_thr_lock | NO | NO |
| wait/synch/cond/sql/Item_func_sleep::cond | NO | NO |
| wait/synch/cond/sql/Master_info::data_cond | NO | NO |
| wait/synch/cond/sql/Master_info::start_cond | NO | NO |
| wait/synch/cond/sql/Master_info::stop_cond | NO | NO |
| wait/synch/cond/sql/Master_info::sleep_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::data_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::log_space_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::start_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::stop_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::sleep_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::pending_jobs_cond | NO | NO |
| wait/synch/cond/sql/Worker_info::jobs_cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::mts_gaq_cond | NO | NO |
| wait/synch/cond/sql/TABLE_SHARE::cond | NO | NO |
| wait/synch/cond/sql/User_level_lock::cond | NO | NO |
| wait/synch/cond/sql/Gtid_state | NO | NO |
| wait/synch/cond/sql/COND_compress_gtid_table | NO | NO |
| wait/synch/cond/sql/Commit_order_manager::m_workers.cond | NO | NO |
| wait/synch/cond/sql/Relay_log_info::slave_worker_hash_lock | NO | NO |
| wait/synch/cond/mysys/IO_CACHE_SHARE::cond | NO | NO |
| wait/synch/cond/mysys/IO_CACHE_SHARE::cond_writer | NO | NO |
| wait/synch/cond/mysys/THR_COND_threads | NO | NO |
| wait/synch/cond/sql/Event_scheduler::COND_state | NO | NO |
| wait/synch/cond/sql/COND_queue_state | NO | NO |
| wait/synch/cond/sql/COND_thd_list | NO | NO |
| wait/synch/cond/sql/COND_thread_cache | NO | NO |
| wait/synch/cond/sql/COND_flush_thread_cache | NO | NO |
| wait/synch/cond/sql/COND_connection_count | NO | NO |
| wait/synch/cond/sql/MDL_context::COND_wait_status | NO | NO |
| wait/synch/cond/sql/COND_open | NO | NO |
| wait/synch/cond/innodb/commit_cond | NO | NO |
| wait/synch/cond/myisam/MI_SORT_INFO::cond | NO | NO |
| wait/synch/cond/myisam/keycache_thread_var::suspend | NO | NO |
| wait/io/file/sql/map | YES | YES |
| wait/io/file/sql/binlog | YES | YES |
| wait/io/file/sql/binlog_cache | YES | YES |
| wait/io/file/sql/binlog_index | YES | YES |
| wait/io/file/sql/binlog_index_cache | YES | YES |
| wait/io/file/sql/relaylog | YES | YES |
| wait/io/file/sql/relaylog_cache | YES | YES |
| wait/io/file/sql/relaylog_index | YES | YES |
| wait/io/file/sql/relaylog_index_cache | YES | YES |
| wait/io/file/sql/io_cache | YES | YES |
| wait/io/file/sql/casetest | YES | YES |
| wait/io/file/sql/dbopt | YES | YES |
| wait/io/file/sql/des_key_file | YES | YES |
| wait/io/file/sql/ERRMSG | YES | YES |
| wait/io/file/sql/select_to_file | YES | YES |
| wait/io/file/sql/file_parser | YES | YES |
| wait/io/file/sql/FRM | YES | YES |
| wait/io/file/sql/global_ddl_log | YES | YES |
| wait/io/file/sql/load | YES | YES |
| wait/io/file/sql/LOAD_FILE | YES | YES |
| wait/io/file/sql/log_event_data | YES | YES |
| wait/io/file/sql/log_event_info | YES | YES |
| wait/io/file/sql/master_info | YES | YES |
| wait/io/file/sql/misc | YES | YES |
| wait/io/file/sql/partition_ddl_log | YES | YES |
| wait/io/file/sql/pid | YES | YES |
| wait/io/file/sql/query_log | YES | YES |
| wait/io/file/sql/relay_log_info | YES | YES |
| wait/io/file/sql/send_file | YES | YES |
| wait/io/file/sql/slow_log | YES | YES |
| wait/io/file/sql/tclog | YES | YES |
| wait/io/file/sql/trigger_name | YES | YES |
| wait/io/file/sql/trigger | YES | YES |
| wait/io/file/sql/init | YES | YES |
| wait/io/file/mysys/proc_meminfo | YES | YES |
| wait/io/file/mysys/charset | YES | YES |
| wait/io/file/mysys/cnf | YES | YES |
| wait/io/file/innodb/innodb_bmp_file | YES | YES |
| wait/io/file/innodb/innodb_data_file | YES | YES |
| wait/io/file/innodb/innodb_log_file | YES | YES |
| wait/io/file/innodb/innodb_temp_file | YES | YES |
| wait/io/file/innodb/innodb_parallel_dblwrite_file | YES | YES |
| wait/io/file/csv/metadata | YES | YES |
| wait/io/file/csv/data | YES | YES |
| wait/io/file/csv/update | YES | YES |
| wait/io/file/myisam/data_tmp | YES | YES |
| wait/io/file/myisam/dfile | YES | YES |
| wait/io/file/myisam/kfile | YES | YES |
| wait/io/file/myisam/log | YES | YES |
| wait/io/file/myisammrg/MRG | YES | YES |
| wait/io/file/partition/ha_partition::parfile | YES | YES |
| wait/io/file/archive/metadata | YES | YES |
| wait/io/file/archive/data | YES | YES |
| wait/io/file/archive/FRM | YES | YES |
| wait/io/table/sql/handler | YES | YES |
| wait/lock/table/sql/handler | YES | YES |
| wait/io/socket/sql/server_tcpip_socket | NO | NO |
| wait/io/socket/sql/server_unix_socket | NO | NO |
| wait/io/socket/sql/client_connection | NO | NO |
| wait/lock/metadata/sql/mdl | YES | YES |
+--------------------------------------------------------------------+---------+-------+

```

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 13, 2022, 1:16pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/12 "2022-07-13T13:16:59Z")

</div>

If it helps at all? The mysql documentations states:  
_The [`table_io_waits_summary_by_index_usage`](https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/performance-schema-table-io-waits-summary-by-index-usage-table.html) 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._

There seems to be no wait time associated with this.

```auto
select * from performance_schema.events_waits_summary_global_by_event_name where event_name like 'wait/io/table%'\G
***************************1. row***************************
    EVENT_NAME: wait/io/table/sql/handler
    COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0

```

It looks to be enabled in setup\_instruments

```auto
+--------------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+--------------------------------------------------------------------+---------+-------+
| wait/lock/table/sql/handler | YES | YES |
+--------------------------------------------------------------------+---------+-------+

```

---

<div class="post-metadata">

**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\
**Post date:** [July 18, 2022, 3:33pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/13 "2022-07-18T15:33:02Z")

</div>

Hi Jamie,

With regards to the instrumentation, it looks like everything is correct.

About the issue. This table aggregate waits for indexes. What about the [`table_io_waits_summary_by_table`](https://dev.mysql.com/doc/mysql-perfschema-excerpt/5.7/en/performance-schema-table-io-waits-summary-by-table-table.html) table?

Also, if you have good hardware, that will limit the number of waits. Try setting a small buffer pool and a small innodb\_log\_file\_size. And use sysbench with several threads running.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 21, 2022, 1:16pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/14 "2022-07-21T13:16:29Z")

</div>

Thanks for getting back to me Vinicius,  
Please bear with me. I will get back to you.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 25, 2022, 1:43pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/15 "2022-07-25T13:43:21Z")

</div>

Hi Vinicius,  
Apologies for the delay in getting back to you. I had some other priorities.  
As per your suggestion I have throtlled the box.  
The instance is now a r5.xlarge 4\*vcpu 32 GB RAM. The buffer pool is only 1 GB.  
A freshly rebooted box.  
Execute the query. It takes 11 minutes.

```auto
SELECT count(*) FROM xyz_data_audit AS o FORCE INDEX (event_time_user_object_event) WHERE o.event_time >= '2021-06-09 00:00:00.0' AND o.event_time <= '2022-07-12 23:59:00.0' AND (o.object_kind IN ('OSY000000001G12'));

```

Plan

```auto
+----+-------------+-------+---------------------------------------------+-------+------------------------------+------------------------------+---------+------+-----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+---------------------------------------------+-------+------------------------------+------------------------------+---------+------+-----------+----------+--------------------------+
| 1 | SIMPLE | o | xyz_data_audit_2021,xyz_data_audit_2022 | range | event_time_user_object_event | event_time_user_object_event | 5 | NULL | 457932990 | 0.00 | Using where; Using index |
+----+-------------+-------+---------------------------------------------+-------+------------------------------+------------------------------+---------+------+-----------+----------+--------------------------+

```

There is 76GB of reads for this query.

```auto
+-----------------------------------------------------------------------+------------+------------+-------------+------------+---------------+------------+-----------+
| file | count_star | count_read | count_write | total_read | total_written | total | write_pct |
+-----------------------------------------------------------------------+------------+------------+-------------+------------+---------------+------------+-----------+
| @@datadir/xxxxxxxxxxxxxx/xxxxx_data_audit#P#xxxxx_data_audit_2022.ibd | 3300183 | 3300178 | 0 | 50.36 GiB | 0 bytes | 50.36 GiB | 0.00 |
| @@datadir/xxxxxxxxxxxxxx/xxxxx_data_audit#P#xxxxx_data_audit_2021.ibd | 1706389 | 1706384 | 0 | 26.04 GiB | 0 bytes | 26.04 GiB | 0.00 |
| @@datadir/ibdata1 | 808 | 800 | 2 | 14.52 MiB | 32.00 KiB | 14.55 MiB | 0.21 |
| @@datadir/ibtmp1 | 50 | 0 | 46 | 0 bytes | 12.53 MiB | 12.53 MiB | 100.00 |

```

Even after this there is still no wait time recorded in table\_io\_waits\_summary\_by\_table or table\_io\_waits\_summary\_by\_index\_usage

```auto
 select * from performance_schema.table_io_waits_summary_by_table\G
***************************1. row***************************
     OBJECT_TYPE: TABLE
   OBJECT_SCHEMA: XXXXXXXXXXX
     OBJECT_NAME: ***** _data_audit
      COUNT_STAR: 0
  SUM_TIMER_WAIT: 0
  MIN_TIMER_WAIT: 0
  AVG_TIMER_WAIT: 0
  MAX_TIMER_WAIT: 0
      COUNT_READ: 0
  SUM_TIMER_READ: 0
  MIN_TIMER_READ: 0
  AVG_TIMER_READ: 0
  MAX_TIMER_READ: 0
     COUNT_WRITE: 0
 SUM_TIMER_WRITE: 0
 MIN_TIMER_WRITE: 0
 AVG_TIMER_WRITE: 0
 MAX_TIMER_WRITE: 0
     COUNT_FETCH: 0
 SUM_TIMER_FETCH: 0
 MIN_TIMER_FETCH: 0
 AVG_TIMER_FETCH: 0
 MAX_TIMER_FETCH: 0
    COUNT_INSERT: 0
SUM_TIMER_INSERT: 0
MIN_TIMER_INSERT: 0
AVG_TIMER_INSERT: 0
MAX_TIMER_INSERT: 0
    COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
    COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0

 select * from performance_schema.table_io_waits_summary_by_index_usage\G
***************************1. row********************************************************************************************************************************************************************************************************************************
     OBJECT_TYPE: TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE TABLE
   OBJECT_SCHEMA: XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX XXXXXXXXXXX
     OBJECT_NAME: XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit	XXXXX_data_audit
      INDEX_NAME: PRIMARY object_key event_time_user_object_event	user object user_object external_grouping_id	grouping_id event object_property
      COUNT_STAR: 0 0 0 0 0 0 0 0 0 0
  SUM_TIMER_WAIT: 0 0 0 0 0 0 0 0 0 0
  MIN_TIMER_WAIT: 0 0 0 0 0 0 0 0 0 0
  AVG_TIMER_WAIT: 0 0 0 0 0 0 0 0 0 0
  MAX_TIMER_WAIT: 0 0 0 0 0 0 0 0 0 0
      COUNT_READ: 0 0 0 0 0 0 0 0 0 0
  SUM_TIMER_READ: 0 0 0 0 0 0 0 0 0 0
  MIN_TIMER_READ: 0 0 0 0 0 0 0 0 0 0
  AVG_TIMER_READ: 0 0 0 0 0 0 0 0 0 0
  MAX_TIMER_READ: 0 0 0 0 0 0 0 0 0 0
     COUNT_WRITE: 0 0 0 0 0 0 0 0 0 0
 SUM_TIMER_WRITE: 0 0 0 0 0 0 0 0 0 0
 MIN_TIMER_WRITE: 0 0 0 0 0 0 0 0 0 0
 AVG_TIMER_WRITE: 0 0 0 0 0 0 0 0 0 0
 MAX_TIMER_WRITE: 0 0 0 0 0 0 0 0 0 0
     COUNT_FETCH: 0 0 0 0 0 0 0 0 0 0
 SUM_TIMER_FETCH: 0 0 0 0 0 0 0 0 0 0
 MIN_TIMER_FETCH: 0 0 0 0 0 0 0 0 0 0
 AVG_TIMER_FETCH: 0 0 0 0 0 0 0 0 0 0
 MAX_TIMER_FETCH: 0 0 0 0 0 0 0 0 0 0
    COUNT_INSERT: 0 0 0 0 0 0 0 0 0 0
SUM_TIMER_INSERT: 0 0 0 0 0 0 0 0 0 0
MIN_TIMER_INSERT: 0 0 0 0 0 0 0 0 0 0
AVG_TIMER_INSERT: 0 0 0 0 0 0 0 0 0 0
MAX_TIMER_INSERT: 0 0 0 0 0 0 0 0 0 0
    COUNT_UPDATE: 0 0 0 0 0 0 0 0 0 0
SUM_TIMER_UPDATE: 0 0 0 0 0 0 0 0 0 0
MIN_TIMER_UPDATE: 0 0 0 0 0 0 0 0 0 0
AVG_TIMER_UPDATE: 0 0 0 0 0 0 0 0 0 0
MAX_TIMER_UPDATE: 0 0 0 0 0 0 0 0 0 0
    COUNT_DELETE: 0 0 0 0 0 0 0 0 0 0
SUM_TIMER_DELETE: 0 0 0 0 0 0 0 0 0 0
MIN_TIMER_DELETE: 0 0 0 0 0 0 0 0 0 0
AVG_TIMER_DELETE: 0 0 0 0 0 0 0 0 0 0
MAX_TIMER_DELETE: 0 0 0 0 0 0 0 0 0 0

```

When the query is running there are records in the events\_waits\_current table. But these are all wait/io/file/innodb/innodb\_data\_file none are wait/io/table/sql/handler.

```auto
***************************46. row***************************
            THREAD_ID: 31
             EVENT_ID: 2
         END_EVENT_ID: 2
           EVENT_NAME: wait/io/file/innodb/innodb_data_file
               SOURCE:
           TIMER_WAIT: 1943942400
            Wait_time: 1.94 ms
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: /xx/xxx/xxxx/xxx_data_audit#P#xxx.ibd
           INDEX_NAME: NULL
          OBJECT_TYPE: FILE
OBJECT_INSTANCE_BEGIN: 140335980738112
     NESTING_EVENT_ID: NULL
   NESTING_EVENT_TYPE: NULL
            OPERATION: read
      NUMBER_OF_BYTES: 16384
                FLAGS: NULL

```

Thanks in advance for taking the time to help.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [July 27, 2022, 8:01pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/16 "2022-07-27T20:01:43Z")

</div>

@vinicius.grippa In case you take a look over the next few days I’m on PTO for two weeks. Thanks.

---

<div class="post-metadata">

**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\
**Post date:** [August 7, 2022, 10:22pm UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/17 "2022-08-07T22:22:41Z")

</div>

Jamie,

I retested using the steps I had used previously, and even yours, which are working as expected. Which MySQL version are you using?

`mysq > UPDATE performance_schema.setup_consumers SET ENABLED='YES' WHERE NAME LIKE 'events_waits_%';`

In my case, I obtained faster results using sysbench with massive operations.

---

<div class="post-metadata">

**Author:** ![Jamie\_Downs](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jamie_downs/32/5836_2.png) [@Jamie\_Downs](https://forums.percona.com/u/Jamie_Downs)\
**Post date:** [August 17, 2022, 11:27am UTC](https://forums.percona.com/t/table-io-waits-summary-by-index-usage/14372/18 "2022-08-17T11:27:39Z")

</div>

Hi @vinicius.grippa,  
Thanks for the reply. We are using an early version of 5.7.  
I’m not blaming you and I greatly appreciate your suggestions but I don’t think we are going to get to the bottom of this.  
I’m trying to remove unused indexes from some large 1TB+ tables. As it stands there doesn’t seem to be a reliable method within MySQL to see which indexes are used.  
Even though a query runs for 11 minutes on a tiny box with a small buffer cache no time is registered in the table\_io\_waits\_summary\_by\_table table.

As ever thanks very much for your suggestions.