QAN not showing examples and explains

Hi, we’re using PMM on percona 8.0 DB. Problem that QAN dashboard do not show some SQL queries examples and explains. We’re increased variable values as instructed on [PMM-6692] Cannot see long queries - Percona JIRA

+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| max_digest_length | 4000 |
| performance_schema_max_digest_length | 4000 |
+--------------------------------------+-------+

But that didn’t help. PMM user has following permissions

+------------------------------------------------------------------------------+
| Grants for pmm@%                                                             |
+------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `pmm`@`%` |
| GRANT SELECT, UPDATE, DELETE, DROP ON `performance_schema`.* TO `pmm`@`%`    |
+------------------------------------------------------------------------------+
1 Like

Hi @justas ,

Thanks for reaching out.My name is Ankit and I am here to help you. I understand that you are not able to see some queries on QAN dashboard. Can you please elaborate what queries are not visible on QAN ? Is there some specific set of queries ?

1 Like

Hi, thanks for your response. I can see queries but they do not have Examples/ Explains

Sorry, no examples found for this query
No classic explain found
No JSON explain found
No table info from example nor explain
1 Like

Hi @justas

It would be great if you could provide us with the following information:

  • Version of PMM
  • Consumers enabled (select * from performance_schema.setup_consumers)
  • Properly anonymized versions of:
    • Query that shows the explain properly (if any)
    • Query that does not show the explain
  • Are you using targeting only one database?

Thank you!

Pep

1 Like

@justas, can you please confirm that when you ran pmm-admin add mysql that you specified the performance_schema as the source for slow queries?

1 Like

Database was added using UI so it is using performance_schema.

PMMVersion: 2.31.0
mysql> select * from performance_schema.setup_consumers;
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_stages_current            | NO      |
| events_stages_history            | NO      |
| events_stages_history_long       | NO      |
| events_statements_current        | YES     |
| events_statements_history        | YES     |
| events_statements_history_long   | YES     |
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | NO      |
| events_waits_current             | NO      |
| events_waits_history             | NO      |
| events_waits_history_long        | NO      |
| global_instrumentation           | YES     |
| thread_instrumentation           | YES     |
| statements_digest                | YES     |
+----------------------------------+---------+

Working query

SELECT
  `t0`.`column0` AS `column0_1`,
  `t0`.`column1` AS `column1_2`,
  `t0`.`column2` AS `column2_3`,
  `t0`.`column3` AS `column3_4`,
  `t0`.`column4` AS `column4_5`,
  `t0`.`column5` AS `column5_6`,
  `t0`.`column6` AS `column6_7`,
  `t0`.`column7` AS `column7_8`,
  `t0`.`column8` AS `column8_9`,
  `t0`.`column9` AS `column9_10`,
  `t0`.`column10` AS `column10_11`,
  `t0`.`column11` AS `column11_12`,
  `t0`.`column12` AS `column12_13`,
  `t0`.`column13` AS `column13_14`,
  `t0`.`column14` AS `column14_15`
FROM
  `column1_column2` `t0`
WHERE
  `t0`.`column12` = ?
  AND `t0`.`column13` = ?
  AND `t0`.`column14` = ?

Not working query

SELECT
  `b0_`.`column0` AS `column0_0`,
  `b0_`.`column1` AS `column1_1`,
  `b0_`.`column3` AS `column3_2`,
  `b0_`.`column4` AS `column4_3`,
  `b0_`.`column5` AS `column5_4`,
  `b0_`.`column6` AS `column6_5`,
  `b0_`.`column7` AS `column7_6`,
  `b0_`.`column8` AS `column8_7`,
  `b0_`.`column9` AS `column9_8`,
  `b0_`.`column10` AS `column10_9`,
  `b0_`.`column11` AS `column11_10`,
  `b0_`.`column12` AS `column12_11`,
  `b0_`.`column13` AS `column13_12`,
  `b0_`.`column14` AS `column14_13`,
  `b0_`.`column15` AS `column15_14`,
  `b0_`.`column16` AS `column16_15`,
  `b0_`.`column17` AS `column17_16`,
  `b0_`.`column18` AS `column18_17`,
  `b0_`.`column19` AS `column19_18`,
  `b0_`.`column20` AS `column20_19`
FROM
  `table` `b0_`
  LEFT JOIN `table0` `b1_` ON `b0_`.`column0` = `b1_`.`table_column0`
  LEFT JOIN `table1` `b2_` ON `b0_`.`column0` = `b2_`.`table_column0`
  LEFT JOIN `table2` `t3_` ON `b1_`.`table4_string` = `t3_`.`string`
  LEFT JOIN `table2` `t4_` ON `b2_`.`table2_string` = `t4_`.`string`
  LEFT JOIN `table3` `t5_` ON `b1_`.`table3_string` = `t5_`.`string`
WHERE
  `b0_`.`column4` < ?
  AND (
    (
      `b0_`.`column8` IS NULL
      OR `b0_`.`column8` < ?
    )
  )
  AND `b0_`.`column18` = ?
  AND `b0_`.`column10` = ?
  AND `b0_`.`column11` IN (...)
  AND (
    (
      (
        `t5_`.`string` IS NOT NULL
        AND `t5_`.`column18` = ?
      )
      OR `t5_`.`string` IS NULL
    )
  )
  AND (
    (
      (
        `t3_`.`string` IS NOT NULL
        AND `t3_`.`column18` = ?
      )
      OR `t3_`.`string` IS NULL
    )
  )
  AND (
    (
      (
        `t4_`.`string` IS NOT NULL
        AND `t4_`.`column18` = ?
      )
      OR `t4_`.`string` IS NULL
    )
  )

We’re connected multiple databases to PMM.

1 Like

Did you pull the two query examples above from the ‘Examples’ tab in PMM? “Very long” queries (IIRC, over 1000 characters) are truncated by MySQL’s performance_schema. Since PMM reads the data from P_S, if P_S truncates, then PMM also sees truncated and thus cannot parse it to show you EXPLAIN and TABLES data.

1 Like

Hi, I pulled those queries from little i symbol next to query. As I mentioned problem is that examples/explain are empty for mentioned query. At first it was truncated, but after increasing max_digest_length now it shows full query.

1 Like

After you did this, do explain/tables tabs now work?

1 Like

No, examples/explains still empty.

1 Like