Bug #116328 Incorrect result performance_schema.events_waits_current when using PK index
Submitted: 10 Oct 2024 6:36 Modified: 10 Oct 2024 9:08
Reporter: Mitani Satoshi (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:8.0.39 OS:Any
Assigned to: CPU Architecture:Any

[10 Oct 2024 6:36] Mitani Satoshi
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
[10 Oct 2024 9:08] MySQL Verification Team
Hello Mitani,

Thank you for the report and feedback.

regards,
Umesh