"N/A" show but not displaying username on the QAN dashboard for Mysql queries

[Duygu]

Description:

Hello,
I am using PMM (managed via Docker on a VM). I want to display “User” information in the QAN dashboard for my MySQL and MongoDB instances and filter queries based on it. While QAN can accurately display “User” and “Client Host” information for PostgreSQL, it shows “n/a” for MySQL and MongoDB. How can I resolve this?
Let me explain the issue using a test MySQL server (PMM-TEST) as an example.

In the QAN dashboard filters, the “User” information for MySQL and MongoDB appears as “n/a.”

However, in the “MySQL/MySQL User Details” dashboard, I can see detailed user information. But when I click on a query and try to access it in the QAN dashboard, I encounter an error, and the filter does not work.

Example link: https:///graph/d/pmm-qan/pmm-query-analytics?var-service_name=$%7B__field.labels.service_name%7D&var-username=$%7B__field.labels.user%7D&var-environment=All&var-cluster=All&var-replication_set=All&var-database=All&var-schema=All&var-node_name=All&var-client_host=All&var-service_type=All&var-node_type=All&var-city=All&var-az=All&var-interval=auto&columns=%5B%22load%22,%22num_queries%22,%22query_time%22%5D&group_by=username&order_by=-load&from=now-12h&to=now&page_number=1&page_size=25&totals=false&details_tab=details

Additionally, I can view the user metrics received from the VM UI. The parameters recommended by PMM are already configured in my MySQL configuration file.MySQL - Percona Monitoring and Management

Version:

Mysql used Performance Schema, MySQL version 8.3 , PMM Server and PMM2 agent v2.43.2

[matthewb]

Hello @Duygu,

MySQL version 8.3

?? That’s not considered a GA version of MySQL. 8.1-8.3 were considered “innovation” releases, and not production worthy.

What is your QAN source? Slow log, or perf schema? User information is not available when using perf_schema, only when using slow log.

[Duygu]

Hi @matthewb ,
Yes, I use perfs_schema as QAN source. Slow log is not effective for my environment. But I can see user statistics from info_schema from prometheus metrics. Even data comes from Mysql User Details Dashboard. QAN cannot read user information from info_schema? What can I do other than slow log?

[matthewb]

@Duygu

QAN cannot read user information from info_schema?

No, it cannot. There’s no way to correlate query data from perf-schema to user data in info-schema.

[Duygu]

Sorry, I was wondering about this: in the QAN dashboard variable, an info-schema query has been assigned to “username”. And this query metrics are displayed in Explore> Metrics. There must be an explanation for this. Could it be a bug for QAN?

[matthewb]

If you are using the slow log as QAN source, then the ‘username’ filter can be used because the slow log contains a mapping of query->user. The perf schema does not contain this info, thus PMM cannot correlate the two data points together.

[Duygu]

Okay thanks, my last question is why there is no user information in the queries for mongodb in QAN. What can I do for this?

[matthewb]

Not sure on Mongo; I’m not a mongo guy I would assume a similar reason. Check the query logs in mongo and verify user information is visible.