2

I have multiple MariaDB instances that I monitor with mysql exporter on grafana. I have spotted some slow queries recently which took more than 300 seconds by looking at the process list of the instance. My monitoring dashboard did not detect it because it looks for the number of slow queries and not their time.

What should i monitor to detect those slow queries so i can fix them ? Is there any metrics i can use to plot it on grafana ?

Improve this question
3
  • 1
    slow_query_log?
    – mustaccio
    Commented Jun 8, 2022 at 14:28
  • I will edit my post to clarify. I'm wondering if there is any way to have a graph on grafana to monitor this
    – Ror
    Commented Jun 8, 2022 at 14:37
  • Are you aware of the query analytics capability in Percona Monitoring and Management?
    – Bill Karwin
    Commented Jun 8, 2022 at 15:39

3 Answers 3

Reset to default
1

There are several sets of tools that will do what you want. First of all, enable the slow query log:

log_slow_verbosity = query_plan,explain
long_query_time = 0    
slow_query_log = ON

This will result in all queries getting logged with additional metadata.

From here you can use:

mysqldumpslow comes bundled with MariaDB/MySQL, but it isn't that great in terms of how it presents data.

pt-query-digest from Percona Toolkit. Still a command line tool, but quite good.

Percona's PMM v2 or Shattered Silicon's SSM improved fork of PMM v1. These are Grafana+Prometheus stacks deployable using docker with about 3 lines of bash. This will give you full, detailed web interface with various graphs including query load, latency and frequency, along with query analysis information.

Disclosure: I work with current maintainers of the SSM fork of PMM.

Improve this answer
2
  • long_query_time=0 seem a little on the short side. It can accept sub 1 second like =0.1 to represent 100ms.
    – danblack
    Commented Sep 7, 2023 at 23:34
  • If the workload of recording all queries becomes too great (10K+/second), yes. But until then, it is a good idea to get a clear view of all of the queries that hit the system. It is not uncommon to have queries that take less than 100ms to run that are further optimizable and consume 80% of server's expended CPU time.
    – Gordan Bobić
    Commented Sep 9, 2023 at 15:18
0

Turn on the slowlog, then 'digest' the results to get the 'worst' queries'

See this for further details: SlowLog

Improve this answer
-1

Have you tried JetProfiler for this? You can record over a period of time and then zoom in on certain time periods to get slow queries, locked queries etc in that time span. I found it great for tracking down and fixing slow queries.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.