How to Exclude PMM Queries from MariaDB Slow Query Log?

Percona Monitoring and Management (PMM) PMM 2.x
1

Hello everyone,

I’m using Percona Monitoring and Management (PMM) to monitor my MariaDB servers, but I’ve noticed that my slow query log is getting flooded with queries that PMM runs against the performance_schema. This makes it challenging to analyze the slow queries that are impacting my application’s performance.

Here’s what I’m seeing:

  • Issue: The slow query log is filled with SELECT statements from PMM querying performance_schema.
  • Goal: I want to exclude these PMM-generated queries from the slow query log.

My Question:

  • Is there a way to configure PMM to disable slow query logging for its own sessions? For example, can PMM execute SET SESSION slow_query_log=0; when it connects to the database?
  • Alternatively, is there a server-side setting in MariaDB that allows me to exclude queries from being logged to the slow query log based on the user or the database (performance_schema in this case)?

What I’ve Tried:

  • Searched MariaDB Documentation: Couldn’t find a built-in option to exclude queries from performance_schema in the slow query log.
  • Considered log_slow_exclude_databases: Realized this variable isn’t available in MariaDB.
  • Adjusting long_query_time: Not ideal, as it may exclude other genuine slow queries. Also I guess it’s log_queries_not_using_indexes what triggers the log entries.

Any guidance or suggestions would be greatly appreciated!

Thank you,

Andreas

2

Hello @Futureweb,
If you’re using PMM to monitor your servers, why not also use PMM’s Query Analyzer (QAN) to “analyze the slow queries that are impacting my application’s performance”? Using the QAN allows you to do exactly what you want: filter out PMM queries. You can filter queries against specific databases, users, and even filter based on key-value pairs within your SQL comments (ex: SELECT /* key=value */, col1, col2 FROM table)

Such an option does not exist in MySQL either, unfortunately.

1 Like
3

Hi Matthew,

Thank you for your response.

I’m already using the Query Analyzer (QAN) in PMM, but I was under the impression that it relies on the slow query log to analyze queries. Given what you’ve mentioned, does QAN operate using the performance_schema without needing the slow query log enabled?

If that’s the case, can I safely disable the slow query log, and QAN will continue to function as expected? This would effectively resolve the issue of PMM queries cluttering the slow query log.

Thanks again for the clarification!

Best regards,
Andreas

4

I guess I can’t exclude performance_schema from the general MySQL Stats, can I?
Most of those Temp Tables and Slow Queries (no index) are from performance_schema as far as I can see … :thinking:

grafik
grafik3745×512 133 KB

grafik
grafik3307×823 387 KB

5

The QAN can use the perf schema for query metrics, however, you will lose some detail as there are some metrics only available in the slow log (oh, wait, you’re on MariaDB. Might be the same then? Percona has advanced slow logs with much more detail)

You need to remove mysql from pmm, then re-add it and pass the correct option to use the perf schema as slow query source. Check --help for the right flag.

1 Like