11 Dec 2014

My Most Useful MySQL Performance Tools

Here is a list of my most useful tools that I use when doing performance audits.
Please note, I am writing this mainly for myself, because I sometimes end up trying to find them in my other blog post about mastering indexing and this may save me time as well as a few changes that have happened over the years.

Regular Slow Log Report
pt-query-digest slow_query.log  >slow.txt

All Queries (that use indexes) for a certain table
pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select|update|delete)/) &&  ($event->{arg} =~ m/mytable /) ' --limit=100% >mytable.txt

Longest Running Select Queries - most painful queries with response time % right next to them.
pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select)/)'  --order-by=Query_time:max > select.txt

Filter the slow log for all Select queries for a certain table
pt-query-digest slow_query.log  --filter '($event->{fingerprint} =~ m/^(!?select)/) &&  ($event->{arg} =~ m/mytable /) ' --no-report --output=slowlog >mytable.log

Find unused indexes from pre-filtered table's logs  
pt-index-usage mytable.log --host 127.0.0.1 --tables mytable >mytable_indexes.txt 


Find Top 15 Largest tables on a server (use with caution) - (from www.mysqlperformanceblog.com)
-----------------------------
delimiter $$
create procedure dba.largest()
begin

SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS TABLE_NAME,
CONCAT(ROUND(TABLE_ROWS / 1000000, 2), 'M') ROWS,
CONCAT(ROUND(DATA_LENGTH / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
CONCAT(ROUND(INDEX_LENGTH / ( 1024 * 1024 * 1024 ), 2), 'G') IDX,
CONCAT(ROUND(( DATA_LENGTH + INDEX_LENGTH ) / ( 1024 * 1024 * 1024 ), 2), 'G') TOTAL_SIZE,
ROUND(INDEX_LENGTH / DATA_LENGTH, 2) IDXFRAC
FROM INFORMATION_SCHEMA.TABLES
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT 15;
end $$
delimiter ;
---------------------------------

ToDo:
Run filtered log against database with the percona toolkit log player to test index improvements.