Advertisment

Maatkit: A Handy Tool for MySQL Users

author-image
PCQ Bureau
New Update

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.

Advertisment

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.

Direct Hit!

Applies To: MySQL users



USP: Command-line tools for MySQL


Primary Link: http://bit.ly/zlorb


Keywords: Maatkit for MySQL

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.

Advertisment

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:

Advertisment

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.

Advertisment

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

Advertisment
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:

Advertisment

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

Advertisment

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.

Advertisment