MySQL Forums
Forum List  »  Performance

Re: table_io_waits_summary_by_index_usage
Posted by: Jamie Downs
Date: February 09, 2022 01:55PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
162
February 09, 2022 01:41AM
Re: table_io_waits_summary_by_index_usage
73
February 09, 2022 01:55PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.