Table_io_waits summary_by_index_usage

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?

1 Like

Hi @8adger , thanks for posting to the Percona forums!

Can you share us the output of:

SELECT * FROM performance_schema.setup_consumers;

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

1 Like

Thanks Michael.

+----------------------------------+---------+
| 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     |
+----------------------------------+---------+

1 Like

Hi @8adger

Please execute:

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.

1 Like

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.

1 Like

Still no joy i’m afraid.

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)

1 Like

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

Hi Jamie,

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

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

Once enabled, you can validate by running:

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:


And in case you want to check:
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:

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
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:

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.

1 Like

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.

1 Like