Maatkit: A Handy Tool for MySQL Users

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.

Getting started

As said earlier, Perl is a prerequisite to be installed on your system
having MySQL server before you start using Maatkit. For this demonstration, we
are using MySQL 6.0 on a Windows Vista machine and ActivePerl (www.activestate.com)
for Perl installation. Once ActivePerl is installed, you have to install the
Database Interface (DBI) for Perl, which can be done by running the ppm install
DBI command from the bin folder of base install directory of Perl (default is
C:\Perl). Now to interact with MySQL database, you need to install database
drivers for Perl's DBI. The ActivePerl has many packages that can be downloaded
and installed, and it lists database drivers for MySQL as well. Through Perl
Package Manager, you can select DBD-mysql from the All Packages list and mark it
for installation. Now you can install the selected Perl driver for MySQL
database by clicking on the Run Marked action.

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

Through Perl Package Manager, install the database driver for MySQL. It can be found in 'All Packages List' and then the driver can be marked for installation.

Now you can start using various Maatkit tools/commands for MySQL management.
For the Maatkit tools demonstration, you can use a demo database( called sakila
database) which is provided by MySQL. You can download it from bit.ly/3EoEyL and
import onto MySQL using SOURCE command.

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

The mk-table-checksum tool gives the checksums for each table of the database which can later be matched with the replicated database for table's consistency.

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.

The mk-query-profiler shows the statistics for the query that is executed. It also shows the Optimizer Cost which comes from the Last_query_cost variable.

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.