Maatkit, essential MySQL toolkit

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

You can get more information from here or here