Description:
When selecting with a specified THREAD_ID from performance_schema.events_waits_current, incorrect results are returned.
Despite existing values, the result becomes empty. Also, rows with a thread_id different from the specified thread_id are returned.
mysql> SELECT THREAD_ID FROM performance_schema.events_waits_current ORDER BY THREAD_ID;
+-----------+
| THREAD_ID |
+-----------+
| 1 |
| 2 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 15 |
| 19 |
| 20 |
+-----------+
10 rows in set (0.00 sec)
mysql> SELECT THREAD_ID FROM performance_schema.events_waits_current WHERE THREAD_ID = 19;
Empty set (0.00 sec)
---
# 79548 matched 79356
mysql> SELECT THREAD_ID FROM performance_schema.events_waits_current
WHERE THREAD_ID = 79548 \G
*************************** 1. row ***************************
THREAD_ID: 79356
I believe this synopsis happens only when there is loss in performance_schema.
mysql> show global status like '%Performance_schema_%lost%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 918 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 63 |
| Performance_schema_hosts_lost | 0 |
| Performance_schema_index_stat_lost | 47 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_memory_classes_lost | 0 |
| Performance_schema_metadata_lock_lost | 713 |
| Performance_schema_mutex_classes_lost | 0 |
| Performance_schema_mutex_instances_lost | 15106 |
| Performance_schema_nested_statement_lost | 0 |
| Performance_schema_prepared_statements_lost | 0 |
| Performance_schema_program_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 1535 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 84 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_lost | 0 |
| Performance_schema_table_handles_lost | 0 |
| Performance_schema_table_instances_lost | 30 |
| Performance_schema_table_lock_stat_lost | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 233 |
| Performance_schema_users_lost | 0 |
+-----------------------------------------------+-------+
30 rows in set (0.00 sec)
We found that it returns correct result with NO_INDEX(events_waits_current PRIMARY) hint (thanks to @yoku0825 -san).
```
mysql> SELECT THREAD_ID FROM performance_schema.events_waits_current WHERE THREAD_ID = 19;
Empty set (0.00 sec)
mysql> SELECT /*+ NO_INDEX(events_waits_current PRIMARY) */ THREAD_ID FROM performance_schema.events_waits_current WHERE THREAD_ID = 19;
+-----------+
| THREAD_ID |
+-----------+
| 19 |
+-----------+
1 row in set (0.00 sec)
```
How to repeat:
1.
To reproduce P_S loss, set small performance_schema max size.
performance_schema = 'ON'
performance_schema_consumer_events_waits_current=ON
performance_schema_instrument = 'wait/io/%=on'
performance_schema_instrument = 'thread/%=on'
performance_schema_instrument = 'statement/%=on'
performance_schema_max_cond_instances=20
performance_schema_max_file_instances=20
performance_schema_max_index_stat=20
performance_schema_max_metadata_locks=20
performance_schema_max_mutex_instances=20
performance_schema_max_prepared_statements_instances=20
performance_schema_max_program_instances=20
performance_schema_max_rwlock_instances=20
performance_schema_max_socket_instances=20
performance_schema_max_table_handles=20
performance_schema_max_table_instances=20
performance_schema_max_table_lock_stat=20
performance_schema_max_thread_instances=20
2. run something heavy workload
$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --table-size=1000000 --mysql-host=localhost --mysql-password=password --mysql-user=root --time=10 --db-ps-mode=disable --threads=100 --report-interval=1 run
3. check
$ cat checkincorr.sh
for THREAD_ID in $(mysql -uroot -NB -e "SELECT THREAD_ID FROM performance_schema.events_waits_current ORDER BY THREAD_ID")
do
RESULT_WITH_INDEX=$(mysql -uroot -NB -e "SELECT THREAD_ID FROM performance_schema.events_waits_current WHERE THREAD_ID = $THREAD_ID")
RESULT_NO_INDEX=$(mysql -uroot -NB -e "SELECT /*+ NO_INDEX(events_waits_current PRIMARY) */ THREAD_ID FROM performance_schema.events_waits_current WHERE THREAD_ID = $THREAD_ID")
if [ "$RESULT_WITH_INDEX" = "$RESULT_NO_INDEX" ]; then
LABEL="[ OK ]"
else
LABEL="[ NG ]"
fi
echo "$LABEL $THREAD_ID $RESULT_WITH_INDEX $RESULT_NO_INDEX"
done
$ sh checkincorr.sh
[ OK ] 1 1 1
[ NG ] 2 2
[ OK ] 8 8 8
[ OK ] 9 9 9
[ OK ] 10 10 10
[ OK ] 11 11 11
[ OK ] 12 12 12
[ OK ] 15 15 15
[ NG ] 19 19
[ NG ] 20 20