Maatkit: A handy tool for MySQL users

BANGALORE, INDIA: MySQL comes packaged with some command-line utilities that help manage a MySQL environment. Also, there are some additional MySQL GUI tools that help you do the same. But the functionalities of these tools are limited and you can't do everything to monitor the MySQL database.

For instance, if you want to ensure that the data in tables that are being replicated between master and slave servers is consistent, then checksum is an easy way, which MySQL lacks. Before firing a query one would like to know the exact implications of its execution. A query profiler can tell you how much load your query is going to cost to the database server. Such utilities if present in MySQL, can really be handy for MySQL users.

About Maatkit
Maatkit, formerly MySQL Toolkit, makes MySQL easier to manage, and is supported on both Linux as well as Windows. With tools that are missing in MySQL, Maatkit lets you check replication, perform query profiling, have a visual representation of EXPLAIN output, and much more. Maatkit commands are Perl scripts, thus making installation of Perl a prerequisite.

You can download the tools package of Maatkit from Google Code at http://bit.ly/zlorb and find documentation for its commands at Maatkit.org. The commands documentation can also be viewed by using PerlDoc command. Let's get started with Maatkit by installing it for MySQL on a Windows platform.

Alternatively, you can install the DBD-mysql driver through this command:

C:\Perl\bin>ppm install http://cpan.uwinnipeg.ca/PPMPackages/10xx/DBD-mysql.ppd

Now you are set for Maatkit installation. Maatkit is available as a zipped file for download. Download and extract it, and you are ready to use it. All you need is to run the Makefile script before using Maatkit commands. Go to the base folder of Maatkit directory and execute Makefile script as follows:

C:\maatkit-4334> perl Makefile.PL

Performing Checksums
Most of the Maatkit tools are targeted towards monitoring replication process between master and slave MySQL server instances. So, when a database replication is in progress between master and slave servers, you need to know about the data consistency in the tables in master and slave database. The 'mk-table-checksum' command of Maatkit lets you check the slave's database consistency with its master. 'mk-table-checksum' generates table checksums for MySQL tables, typically useful in verifying that your slaves are in sync with the master. The checksums are generated by a query on the server, and does not generate much network traffic as a result.

There are three different checksum algorithms that mk-table-ckecksum tool can use. You can choose BIT_XOR, CHECKSUM or ACCUM algorithms by using --algorithm argument along with the command. You can provide different databases as arguments along with their respective host details. The following example lists the checksum details for the tables of the demo database, Sakila. Where databases argument is to specify databases, h is to host, P is to the port address, and u and p being arguments for user name and password:

perl mk-table-checksum --databases sakila h=127.0.0.1,P=4406,u=root,p=password

In case, while using checksum utility for replication process of databases, you find inconsistency between table data of the database, then by using mk-table-sync command, you can re-sync the table that has become corrupt on the slave server. There may be tables with no rows. For this, this tool returns the checksum result as 0 or NULL. Also, this tool facilitates parallel table comparison across different servers simultaneously.

Profiling Queries
'mk-query-profiler' is another command-line tool that reads a file containing one or more SQL queries , executes them and analyzes the output of SHOW STATUS. This utility presents you with statistics of how the query has performed. For instance, it tells the number of table scans, page reads, temporary tables, rows sorted, etc. It not only reports about the execution details of the query, but also reports execution time statistics as well, through which the load on the server can be determined. For this example, we take a query that will join two more tables to the film_actor table of the Sakila database. Write the query on a notepad and save it as query.txt file. The query is as follows:

select * from sakila.film_actor join (sakila.film, sakila.actor) using(film_id, actor_id) order by first_name DESC;

Now we can pass this file as a parameter to query profiler. The following example shows how the mk-query-profiler command will look like:

perl mk-query-profiler --host localhost -P 4406 --user root --ask-pass query.txt

If there are multiple queries that you want to profile, you can save them all in a single file separated by blank lines. In absence of blank lines serving as delimiter, mk-query-profiler won't be able to differentiate between different queries, and when it executes those on MySQL, syntax errors would be reported.

Visual EXPLAIN
MySQL's EXPLAIN statement can be used to obtain information about how MySQL executes a given query. The output of the statement is in a tabular format, which is not easy to comprehend given the fact that the table columns can run beyond the command console width. If the same information can be represented in a tree-like format, it would be easier to understand. This is now achievable through Maatkit's mk-visual-explain tool. This tool transforms the EXPLAIN statements's output into a hierarchical view of the query plan by doing reverse engineering on the MySQL's EXPLAIN output and making it more understandable.mk-visual-explain also reads the query from a file. For this demonstration also, we use the same query as we did earlier .

perl mk-visual-explain --connect --host localhost --user root query.txt
Thus it can be said, Maatkit is an essential toolkit that can help you maintain consistency between different MySQL instances and manage them better.