Analyze MySQL load with Percona toolkit pt-query-digest

[Idan_Ahal]

We have a problem when sometimes we have a lot of load on the MySQL but the cause is a lot of small queries and not slow ones.

I had an idea to use the slow log and log all queries > 0 (Meaning all) for several minutes and then analyze this file with pt-query-digest. This way if there is a very short query that happens a ton of times it will be aggregated and I can find it as the problem (Or the problematic user).

Am I missing something? Does pt-query-digest can do that?

[kedarpercona]

Responded on Original question

Percona toolkit find where load is coming from

Hi @Idan_Ahal, If you want to log all the queries consider adjusting the long query time. set global long_query_time=0 # or 0.1 Collect the log for the desired period, ensure performance / disk usage are acceptable. You can rotate your slow log to easily parse them directly with the digest OR you can use the --since and --until options to parse required information. The digest will not tell you what resource a query is using but you can get the stats for sure. Thanks, K

[matthewb]

@Idan_Ahal I would recommend you install/setup Database Monitoring Tools for MySQL, MongoDB, PostgreSQL & More | Percona

PMM is effectively a constant "pt-query-digest’. It watches your slow log for new queries, processes them live, and updates the dashboards. The UI is far easier than pt-query-digest.