Re: table_io_waits_summary_by_index_usage
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.
Subject
Views
Written By
Posted
896
February 09, 2022 01:41AM
404
February 09, 2022 10:46AM
Re: table_io_waits_summary_by_index_usage
373
February 09, 2022 01:55PM
344
February 16, 2022 01:13PM
Sorry, you can't reply to this topic. It has been closed.
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.