Description:
We found that in Query Analytics, the same statement executed in different databases will only be counted once.
Steps to Reproduce:
Our database architecture uses MySQL’s sharding, for example, there are 8 databases named test0001 to test0008 in one instance, and each database has the same tables. For example:
use test0001;
select sleep(10) from t;
use test0002;
select sleep(10) from t;
In this case, Query Analytics only counts one select sleep(10) from t.
Version:
PMM 2.37.1
MySQL 5.7 + performance_schema
Hi @frank.pan
You should see only one Line in QAN for this query, but the count - should be from all databases (schema)
You should be able to see schemas in QAN, and if you add filtering by then - the count of query execution should be specific for the selected schema.
Additionally, the upcoming release next week will include an enhancement for [PMM-6279] Query metadata not visible in new UI - Percona JIRA that will improve the visibility of such scenarios.
1 Like
Hi,
Here is my test:
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select sleep(10) as qan from t limit 1;
+-----+
| qan |
+-----+
| 0 |
+-----+
1 row in set (10.00 sec)
mysql> use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select sleep(10) as qan from t limit 1;
+-----+
| qan |
+-----+
| 0 |
+-----+
1 row in set (10.00 sec)
mysql>
Filter using the Sleep keyword after waiting for a period of time.
Where did I go wrong?
@frank.pan , can you click on the Query and check the details?
10 sec in the main view - is the average time for the query, mane as Query count = queries per second.
What is clear on your screen - is the first query hasn’t tracked the schema name (test1). This might be our bug or performance schema limitation.
mysql> truncate table performance_schema.events_statements_summary_by_digest;
Query OK, 0 rows affected (0.01 sec)
mysql> use test1;
Database changed
mysql> select sleep(10) as qan2 from t limit 1;
+------+
| qan2 |
+------+
| 0 |
+------+
1 row in set (10.00 sec)
mysql> use test2;
Database changed
mysql> select sleep(10) as qan2 from t limit 1;
+------+
| qan2 |
+------+
| 0 |
+------+
1 row in set (10.03 sec)
I think the reason is that PMM does not perform aggregated analysis on the same SQL queries from different databases.
I don’t know if it’s correct.
This is the query result of events_statements_summary_by_digest.
mysql> select * from performance_schema.events_statements_summary_by_digest where digest_text like 'SELECT `sleep` (?) AS `qan2` FROM `t` LIMIT ? '\G
*************************** 1. row ***************************
SCHEMA_NAME: test1
DIGEST: 80498c1c7c6ed0f05789c9bfc29993d3
DIGEST_TEXT: SELECT `sleep` (?) AS `qan2` FROM `t` LIMIT ?
COUNT_STAR: 1
SUM_TIMER_WAIT: 10001581443000
MIN_TIMER_WAIT: 10001581443000
AVG_TIMER_WAIT: 10001581443000
MAX_TIMER_WAIT: 10001581443000
SUM_LOCK_TIME: 131000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 1
SUM_ROWS_EXAMINED: 1
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: 1
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 1
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2023-08-04 16:33:29
LAST_SEEN: 2023-08-04 16:33:29
*************************** 2. row ***************************
SCHEMA_NAME: test2
DIGEST: 80498c1c7c6ed0f05789c9bfc29993d3
DIGEST_TEXT: SELECT `sleep` (?) AS `qan2` FROM `t` LIMIT ?
COUNT_STAR: 1
SUM_TIMER_WAIT: 10021048186000
MIN_TIMER_WAIT: 10021048186000
AVG_TIMER_WAIT: 10021048186000
MAX_TIMER_WAIT: 10021048186000
SUM_LOCK_TIME: 47000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 1
SUM_ROWS_EXAMINED: 1
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: 1
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 1
SUM_NO_GOOD_INDEX_USED: 0
FIRST_SEEN: 2023-08-04 16:33:39
LAST_SEEN: 2023-08-04 16:33:39
2 rows in set (0.00 sec)
mysql>
Who could assist in resolving this issue? Thank you!
As Roma suggested, you may have uncovered a bug and it’s best to submit a ticket via https://jira.percona.com against the PMM project, specifically the QAN component. You might also consider Percona for an active support contract. In that case, one call and your issue will get escalated and prioritized as a customer vs community report. DM me if you’re interested in learning more and I’ll connect you with our team to learn more about other benefits of being a customer.
Hello @frank.pan , thank you for providing the details and report. Based on the data provided, it appears to be a bug that is affecting multiple users. We will prioritize fixing this issue as it is considered a high-priority bug in QAN.
