| Bug #79009 | Duplicate records in perf schema digests table | ||
|---|---|---|---|
| Submitted: | 28 Oct 2015 21:44 | Modified: | 28 Sep 2016 8:43 |
| Reporter: | Trey Raymond | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S3 (Non-critical) |
| Version: | 5.6.24 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[27 Jul 2016 17:53]
Trey Raymond
It's getting worse! check this out (5.6.29 host):
mysql> select * from events_statements_summary_by_digest where schema_name='sbtest1' and digest='f68bce38a0814cfc93da84c55aa7ed26'\G
*************************** 1. row ***************************
SCHEMA_NAME: sbtest1
DIGEST: f68bce38a0814cfc93da84c55aa7ed26
DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2016-07-26 19:00:01
LAST_SEEN: 2016-07-26 19:00:01
*************************** 2. row ***************************
SCHEMA_NAME: sbtest1
DIGEST: f68bce38a0814cfc93da84c55aa7ed26
DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
COUNT_STAR: 0
SUM_TIMER_WAIT: 0
MIN_TIMER_WAIT: 0
AVG_TIMER_WAIT: 0
MAX_TIMER_WAIT: 0
SUM_LOCK_TIME: 0
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2016-07-26 19:00:01
LAST_SEEN: 2016-07-26 19:00:01
*************************** 3. row ***************************
SCHEMA_NAME: sbtest1
DIGEST: f68bce38a0814cfc93da84c55aa7ed26
DIGEST_TEXT: SELECT DISTINCTROW `c` FROM `sbtest3` WHERE `id` BETWEEN ? AND ? + ? ORDER BY `c`
COUNT_STAR: 54226
SUM_TIMER_WAIT: 103364227384000
MIN_TIMER_WAIT: 1230608000
AVG_TIMER_WAIT: 1906174000
MAX_TIMER_WAIT: 10798202000
SUM_LOCK_TIME: 2175628000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 5422800
SUM_ROWS_EXAMINED: 16268400
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 54228
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 54228
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 5422800
SUM_SORT_SCAN: 54228
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2016-07-27 17:45:02
LAST_SEEN: 2016-07-27 17:50:14
3 rows in set (0.00 sec)
[28 Sep 2016 8:43]
Marc ALFF
Duplicate with: Bug #79533 events_statements_summary_by_digest: no unique rows by DIGEST/SCHEMA_NAME Please track 79533 instead.
Description: seeing the following on some installs. it doesn't happen with all digests, just a few out of the bunch. timestamps show that it was written when the query pattern was first seen, and the other row got all the updates, while the duplicate just sat with count=0. *************************** 1. row *************************** SCHEMA_NAME: [db] DIGEST: [digest 1] DIGEST_TEXT: [digest text 1] COUNT_STAR: 11222 SUM_TIMER_WAIT: 3502903096000 MIN_TIMER_WAIT: 129067000 AVG_TIMER_WAIT: 312146000 MAX_TIMER_WAIT: 33191536000 SUM_LOCK_TIME: 1590981000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 11223 SUM_ROWS_EXAMINED: 11223 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2015-08-05 13:42:51 LAST_SEEN: 2015-08-19 12:12:38 *************************** 2. row *************************** SCHEMA_NAME: [db] DIGEST: [digest 1] DIGEST_TEXT: [digest text 1] COUNT_STAR: 0 SUM_TIMER_WAIT: 0 MIN_TIMER_WAIT: 0 AVG_TIMER_WAIT: 0 MAX_TIMER_WAIT: 0 SUM_LOCK_TIME: 0 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 0 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2015-08-05 13:42:51 LAST_SEEN: 2015-08-05 13:42:51 *************************** 3. row *************************** SCHEMA_NAME: [db] DIGEST: [digest 2] DIGEST_TEXT: [digest text 2] COUNT_STAR: 549 SUM_TIMER_WAIT: 295066494000 MIN_TIMER_WAIT: 292858000 AVG_TIMER_WAIT: 537461000 MAX_TIMER_WAIT: 905152000 SUM_LOCK_TIME: 142504000000 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 549 SUM_ROWS_EXAMINED: 1098 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2015-09-23 14:52:19 LAST_SEEN: 2015-10-01 10:53:05 *************************** 4. row *************************** SCHEMA_NAME: [db] DIGEST: [digest 2] DIGEST_TEXT: [digest text 2] COUNT_STAR: 0 SUM_TIMER_WAIT: 0 MIN_TIMER_WAIT: 0 AVG_TIMER_WAIT: 0 MAX_TIMER_WAIT: 0 SUM_LOCK_TIME: 0 SUM_ERRORS: 0 SUM_WARNINGS: 0 SUM_ROWS_AFFECTED: 0 SUM_ROWS_SENT: 0 SUM_ROWS_EXAMINED: 0 SUM_CREATED_TMP_DISK_TABLES: 0 SUM_CREATED_TMP_TABLES: 0 SUM_SELECT_FULL_JOIN: 0 SUM_SELECT_FULL_RANGE_JOIN: 0 SUM_SELECT_RANGE: 0 SUM_SELECT_RANGE_CHECK: 0 SUM_SELECT_SCAN: 0 SUM_SORT_MERGE_PASSES: 0 SUM_SORT_RANGE: 0 SUM_SORT_ROWS: 0 SUM_SORT_SCAN: 0 SUM_NO_INDEX_USED: 0 SUM_NO_GOOD_INDEX_USED: 0 FIRST_SEEN: 2015-09-23 14:52:19 LAST_SEEN: 2015-09-23 14:52:19 4 rows in set (0.05 sec) How to repeat: Can't find a deterministic way to reproduce, we get anywhere from zero to a dozen of these on prod systems. The following query will display them: select * from performance_schema.events_statements_summary_by_digest zero_rows left join performance_schema.events_statements_summary_by_digest real_rows on real_rows.schema_name=zero_rows.schema_name and real_rows.digest=zero_rows.digest and real_rows.count_star>0 where zero_rows.count_star=0; Suggested fix: find out why the second row gets inserted at first_seen date, and make sure it only inserts one row. the tuple (schema_name,digest) should always be unique for the data to make sense.