Maatkit (formerly MySQL Toolkit) contains essential command-line tools for MySQL. You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, check whether slaves have the same data as the master and much, much more.
Here are the list of tools available :
mk-table-checksum, mk-table-sync, mk-visual-explain, mk-heartbeat, mk-parallel-dump, mk-parallel-restore, mk-query-profiler, mk-archiver, mk-deadlock-logger, mk-query-profiler, mk-duplicate-key-checker, mk-find, mk-show-grants, mk-slave-restart, mk-slave-delay, mk-slave-prefetch
The one that I use most would be mk-visual-explain and mk-query-profiler
mk-visual-explain : If you’ve ever wished you could see MySQL’s EXPLAIN output formatted as a tree, now you can. This utility transforms EXPLAIN output into a hierarchical view of the query plan, which is significantly easier to read and understand than EXPLAIN.
mk-query-profiler : This unique tool can profile individual queries, batches of queries, external applications and commands. It measures dozens of server statistics and presents them in a way that makes sense. It goes far beyond mere execution time, and calculates such statistics as the number of disk accesses and index lookups. No other tool gives you such deep insight into how much work your queries really cause the server to do.
Maatkit now ships by default with many GNU/Linux distributions such as Debian and CentOS.
To have the tools available on you linux box, just do the following (assuming you are on an ubuntu box with your sources updated)
sudo apt-get install maatkit
mk-visual-explain
create a “query.txt” file this sample query as content changing “SAM_DB” to your own db name
SELECT s.table_name,
concat(s.index_name,'(',group_concat(s.column_name order by s.seq_in_index),')') as idx,
GROUP_CONCAT(s.cardinality ORDER BY s.seq_in_index) AS card,
t.table_rows
FROM information_schema.tables t
JOIN information_schema.statistics s USING (table_schema,table_name)
WHERE t.table_schema='SAM_DB'
AND t.table_rows > 1000
AND s.non_unique
GROUP BY s.table_name,s.index_name
HAVING (card + 0) < (t.table_rows / 3);
Now issue the following command with your settings if necessary :
mk-visual-explain --host Localhost --user root -c /home/salimane/Desktop/query.txt
Here is the output :
Filesort
+- TEMPORARY
table temporary(t,s)
+- JOIN
+- Filter with WHERE
| +- Table scan
| rows 2
| +- Table
| table s
+- Filter with WHERE
+- Table scan
rows 2
+- Table
table t
Isn’t that awesome ?
mk-query-profiler
Now issue the following command with your settings if necessary :
mk-query-profiler --host Localhost --user root /home/salimane/Desktop/query.txt
Here is the output :
+----------------------------------------------------------+
| 1 (0.0816 sec) |
+----------------------------------------------------------+
__ Overall stats _______________________ Value _____________
Total elapsed time 0.082
Questions 1
COMMIT 0
DELETE 0
DELETE MULTI 0
INSERT 0
INSERT SELECT 0
REPLACE 0
REPLACE SELECT 0
SELECT 1
UPDATE 0
UPDATE MULTI 0
Data into server 479
Data out of server 548
Optimizer cost 24.999
__ Table and index accesses ____________ Value _____________
Table locks acquired 0
Table scans 2
Join 1
Index range scans 0
Join without check 0
Join with check 0
Rows sorted 23
Range sorts 0
Merge passes 0
Table scans 1
Potential filesorts 1
