Analyze slow MySQL queries

Robert AndresenProgramming, Tutorials0 Comments

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.

Install

(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):

https://www.olindata.com/blog/2014/08/analysing-slow-mysql-queries-pt-query-digest

 

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

https://www.olindata.com/blog/2014/07/anemometer-mysql-slow-query-monitor

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

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).

 

 

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: http://stackoverflow.com/questions/23921117/disable-only-full-group-by