Query QPS metric in PMM2 QAN dashboard

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

Hello,

We have this settings configured for slow query logging:

long_query_time=0
log_slow_rate_limit=1000
log_slow_rate_type=query
log_slow_verbosity=full
slow_query_log_always_write_time=0.1
slow_query_log_use_global_control=all

For PMM2 QAN dashboards we use slow query log as a source.
Some queries report over 1.24k QPS but I don’t see so much queries logged in slow query log.

Can you please share info how PMM2 calculates QPS for QAN dashboard if slow query is used as a source. Is it possible that count is increased even if query is suppressed from the log because of “log_slow_rate_limit”?

Thank you

Best regards,
Marko

2

Hi @msutic. Yes, because of this variable, you don’t have all queries logged in slow log (Slow Query Log - Percona Server for MySQL), and PMM uses this to calculate the real number of Queries.

QPS is calculated with the number of queries for some interval (pmm uses 1 min bracket) to convert to query per second based on this data.

1 Like
3

@msutic, these parameters:

long_query_time=0
log_slow_rate_limit=1000
log_slow_rate_type=query

tell MySQL to only log every 1000th query executed. Unless you are running at 15,000+ qps, you should not need to rate-limit/filter anything. And even at 15K, your rate limit should be around 2-10, so that MySQL logs every other query (2) or every 10th query (10).

The rule of thumb is “log everything until logging itself impacts performance”. You can view in PMM Performance Schema dashboard when/if slog log impacts IO.

1 Like
4

Hi @matthewb,
We also have “slow_query_log_always_write_time=0.1” to log all slow query occurrence on which rate will not be applied.

I agree with you that “log_slow_rate_limit=1000” is maybe too high but this was just (pessimistic) starting point which we decided to adjust gradually. As for Perfomance Schema I wasn’t aware that slow query logging was instrumented - you were probably referring on “wait/io/file/sql/slow_log write”.

Thank you very much on valuable info and suggestion.

Best Regards,
Marko

5

Hi @Roma_Novikov ,

It turns out that now this feature ( showing the real number of queries, taking rate_limit into account) works only for Percona. Will PMM take rate_limit into account if Mariadb would add a similar line to the slow-log?
I’m talking about # Log_slow_rate_type: query Log_slow_rate_limit: 100.

Thank you.

6

@bukem, as it is named the same - I would expect it to work.
If it does not, please feel free to report a bug in our Jira (https://perconadev.atlassian.net/)