Analyze slow MySQL queries

Robert AndresenProgramming, Tutorials Leave a Comment

One of the drawbacks of using MySQL community is the lack of tools to analyze the server and queries, but with a little help from the Percona Toolkit, this gets a little bit easier.

I tested this on a Ubuntu 16.04 server.


apt-get update
apt-get install percona-toolkit

(Write sudo in front if you are not root-user)

Here is a guide to analyze the queries from the slow query log, with pt-query-digest (that is included in the Percona toolkit): (dead link)

If you want a more GUI-based tool, you can install Box Anemometer (web-application): (dead link)

The array for $conf[‘datasources’][‘localhost’] was located in the file, and not the default

After Anemometer was set up, I have to run the pt-query-digest to get some data. Here I had to change the user, password and host (h).

$ pt-query-digest --user=anemometer --password=superSecurePass \
                  --review,D=slow_query_log,t=global_query_review \
                  --history,D=slow_query_log,t=global_query_review_history \
                  --no-report --limit=0% \ 
                  --filter=" \$event->{Bytes} = length(\$event->{arg}) and \$event->{hostname}=\"$HOSTNAME\"" \ 

After I opened Box Anemometer in my browser I got a red error message because I had ONLY_FULL_GROUP_BY activated by default. I didn’t need any of the SQL-modes on this server, so I just removed them by running the query: set global sql_mode=»;

Help with SQL-mode: