QAN never shows query plan

[brett1]

Hi, I have PMM 2.11.1 installed from the AWS AMI. I’m monitoring and RDS instance with the performance schema turned on. I see queries in the QAN however I never see a query plan:

I have configured the performance schema as documented here:

Configuring Performance Schema

Is there some other setting that needs to be set?

[igroene]

Hi Brett, can you double check if the monitoring user has this grants:

GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'pmm'@'%' IDENTIFIED BY 'pass' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO 'pmm'@'%';

This is taken from:

https://www.percona.com/doc/percona-monitoring-and-management/amazon-rds.html

[kalyan]

@igroene The queries which you have mentioned doesnt work for mysql 8 version. Can you please check from your end as well. From Identified it is throwing error correct the syntax.

Regards,

Kalyan

[kalyan]

[igroene]

Hi, MySQL 8 doesn’t support creating the user and granting privileges on the same sentence. You can do it like this instead:

CREATE USER ‘pmm’@’%’ IDENTIFIED BY ‘pass’ WITH MAX_USER_CONNECTIONS 10;

GRANT SELECT, PROCESS, REPLICATION CLIENT ON . TO ‘pmm’@’%’;

GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO ‘pmm’@’%’;

Hope that helps

[brett1]

Hi @igroene indeed our pmm users has these grants:

±----------------------------------------------------------------------------------------------------------+

| Grants for percona_pmm@172.% |

±----------------------------------------------------------------------------------------------------------+

| GRANT SELECT, PROCESS, REPLICATION CLIENT ON . TO ‘percona_pmm’@‘172.%’ IDENTIFIED BY PASSWORD |

| GRANT SELECT, UPDATE, DELETE, DROP ON performance_schema.* TO ‘percona_pmm’@‘172.%’ |

±----------------------------------------------------------------------------------------------------------+

[igroene]

Ok in case you have double checked the config is correct I suggest you to open a bug in our issue tracker https://jira.percona.com/projects/PMM/issues

[scf]

please post the output of: SELECT * FROM performance_schema.setup_consumers;

those consumers should be enabled for the “example” and “explain” tabs to work (tested on Amazon Aurora MySQL 1.22 / 5.6):

events_statements_current
events_statements_history
global_instrumentation
thread_instrumentation
statements_digest
you might want to review/increase those variables too:
performance_schema_events_statements_history_size
performance_schema_max_digest_length
performance_schema_max_sql_text_length

MySQL :: MySQL 5.6 Reference Manual :: 22.12.6.1 The events_statements_current Table