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