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.
