Re: table_io_waits_summary_by_index_usage

[Jamie Downs]

Thanks for the response Peter. Is there any way of testing this theory? I tried forcing one of the indexes and it still didn't get any counts.

On a cold server: (a replica)

We can see just inserts:

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

Do a select count

mysql> select count(*) from table;
+-----------+
| count(*) |
+-----------+
| 141565004 |
+-----------+

We can see the reads are incremented:

+------------------------------+------------+------------+
| index_name | COUNT_STAR | COUNT_READ |
+------------------------------+------------+------------+
| NULL | 409932 | 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 | 141565004 | 141565004 |
| user_object | 0 | 0 |
+------------------------------+------------+------------+

Do the count again

mysql> select count(*) from table;
+-----------+
| count(*) |
+-----------+
| 141565112 |
+-----------+

The reads are doubled. Surely most of the count data would be cached?
+------------------------------+------------+------------+
| index_name | COUNT_STAR | COUNT_READ |
+------------------------------+------------+------------+
| NULL | 409976 | 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 | 283130116 | 283130116 |
| user_object | 0 | 0 |
+------------------------------+------------+------------+

This is the index: object index: KEY `object` (`object_kind`,`event_time`,`event`(80)),

Example query forcing index:

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 10;

+-----------------------------+--------------------------------------+
| object_key | grouping_id |
+-----------------------------+--------------------------------------+
| A0L0000001DG054:SubmCountry | d0f046be-12e0-4e32-b35c-56769ae48a8e |
| A0L0000001DB094:SubmCountry | 342d7324-be8d-40b6-924a-d343732649f5 |
| A0L0000001AN123:SubmCountry | f794e05f-ab53-486d-9ea9-7234cae37a4d |
| A0L0000001DU013:SubmCountry | e429198c-777d-470f-a1f1-65b131e1cb94 |
| A0L00000022B001:SubmCountry | ef8d106b-134a-4445-89b2-a3ca3d609328 |
| A0L00000022C001:SubmCountry | af222326-b34a-473d-ab94-3ae7484d4723 |
| A0L00000022D001:SubmCountry | 5672e3f3-19a1-4609-9d96-4786b493859e |
| A0L0000001RO306:SubmCountry | c89b432b-7238-4149-9191-ca169ab5c20d |
| A0L00000022B002:SubmCountry | 837b2d08-131b-4a70-a9d7-73ab055e90ef |
| A0L00000022D002:SubmCountry | 57d76154-beb3-4bc8-8879-d724f282ddff |
+-----------------------------+--------------------------------------+

Still nothing registers for that table.

+------------------------------+------------+------------+
| index_name | COUNT_STAR | COUNT_READ |
+------------------------------+------------+------------+
| NULL | 417590 | 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 | 283130116 | 283130116 |
| user_object | 0 | 0 |
+------------------------------+------------+------------+

Thanks in advance.