MySQL / Percona profiler

Hey, this is designed to profiles SQL queries on Percona Cluster, however it would run on normal MySQL without any issues.

Log into mysql console and run the following (don’t logout of console until finished profiling, or take note of the changed variables):

-- Save current settings and see what you've got
SELECT @@global.slow_query_log_use_global_control INTO @__slow_query_log_use_global_control;
SELECT @@global.log_slow_verbosity INTO @__log_slow_verbosity;
SELECT @@global.slow_query_log INTO @__slow_query_log;
SELECT @@global.long_query_time INTO @__long_query_time;
SELECT @@global.log_slow_slave_statements INTO @__log_slow_slave_statements;

SELECT @@global.slow_query_log_use_global_control, @@global.log_slow_verbosity, @@global.slow_query_log, @@global.long_query_time, @@global.log_slow_slave_statements;

-- Keep this in safe place, we'll need to run pt-query-digest
SELECT NOW() AS "Time Since";

-- Set values to enable query collection
SET GLOBAL slow_query_log_use_global_control='log_slow_verbosity,long_query_time';
SET GLOBAL log_slow_verbosity='full';
SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0;
SET GLOBAL log_slow_slave_statements=1;

-- Verify settings are OK
SELECT @@global.slow_query_log_use_global_control, @@global.log_slow_verbosity, @@global.slow_query_log, @@global.long_query_time, @@global.log_slow_slave_statements;

Wait for some time – 30-60 minutes, allow the queries to be logged. The more you wait, the more data you get!

Then go and disable logging:

-- Keep this one too, also for pt-query-digest
SELECT NOW() AS "Time Until";

-- Revert to previous values
SET GLOBAL slow_query_log_use_global_control=@__slow_query_log_use_global_control;
SET GLOBAL log_slow_verbosity=@__log_slow_verbosity; -- if percona server
SET GLOBAL slow_query_log=@__slow_query_log;
SET GLOBAL long_query_time=@__long_query_time;
SET GLOBAL log_slow_slave_statements=@__log_slow_slave_statements;

-- Verify settings are back to previous values
SELECT @@global.slow_query_log_use_global_control, @@global.log_slow_verbosity, @@global.slow_query_log, @@global.long_query_time, @@global.log_slow_slave_statements;

At this point you should have a *-slow.log file in /var/lib/mysql/

Run profiler against that log file and print output into a file – don’t use STDOUT as the output can be from hundreds of kylobytes up to megabytes!

$ pt-query-digest --since='' --until='' --limit=100% /var/lib/mysql/*-slow.log > /path/to/report.out

Open the report.out and look in first 2 sections for the dodgy time consuming queries. That’s your problem!