It seems to be a popular misconception that mk-slave-prefetch is designed to keep a MySQL replica server “warm” and ready to serve production traffic in case the master is demoted or fails. This is not what mk-slave-prefetch does. It’s related, and easy to confuse, but its purpose is different.

The mk-slave-prefetch tool is designed to try to execute a read-only approximation of the write workload that the replica is about to have to perform. It is meant to do this just a little bit before the replication thread (which can only be true if replication is lagging), so that when the replica replays writes to execute replication, it doesn’t have to wait for disk I/O.

Keeping caches warmed up for production traffic requires that the read workload, which does not flow through relay logs, is executed on the

About a month ago I needed to compare tens of thousands of tables in hundreds of databases between a few different servers. The obvious choice was, mk-table-checksum! The only problem was, that the tool needs to know the minimum and maximum value of the column by which each table is to be subdivided into chunks and checksummed. This select min(col), max(col) from table locks all write operations on the table and on a big table it meant downtime.

Looking at the source it was clear we could make mk-table-checksum run the select min(col), max(col) from table on the read-only slave and use the values to checksum the master.

It was subtle code changes in function:
get_range_statistics adding

my $cxn_string_dc = “DBI:mysql:;host=slavehost;port=3306;mysql_read_default_group=client”;
my $user = ‘user’;

MySQL databases are great work horses of the internet.  They back tons of modern websites, from blogs and checkout carts, to huge sites like Facebook.  But these technologies don't run themselves.  When you're faced with a system that is slowing down, you'll need the right tools to diagnose and troubleshoot the problem.  MySQL has a huge community following and that means scores of great tools for your toolbox. Here are 7 ways to troubleshoot MySQL.

1. Use innotop

Innotop is a great tool for MySQL which despite the name monitors MySQL generally as well as InnoDB usage.  It's fairly easy to install, just download the perl script. Be sure to include a [client] section to your local users .my.cnf file (you have one don't you?).  Inside that section, place one line with "user=xyz" and one line with "password=abc".

If you're concerned that

I’ve just sent an email to the Maatkit discussion list to announce a planned change to how Maatkit (and Aspersa) are developed. In short, Percona plans to create a Percona Toolkit of MySQL-related utilities, as a fork of Maatkit and Aspersa. I’m very happy about this change, and I welcome your responses to that thread on the discussion list.

Related posts:

This month’s Maatkit release includes a new tool that’s kind of an old tool at the same time. We wrote it a couple years ago for a client who has a very large set of tables and many queries and developers, and wants the database’s schema and queries to self-document for data-flow analysis purposes. At the time, it was called mk-table-access and was rather limited — just a few lines of code wrapped around some existing modules, with an output format that wasn’t generic enough to be broadly useful. Thus we didn’t release it with Maatkit. We recently changed the name to mk-table-usage (to match mk-index-usage), included it in the Maatkit suite of tools, and enhanced the functionality a lot.

What’s this tool good for? Well, imagine that you’re a big MySQL user and you hire a new

This isn’t actually new — it has been out for a few releases. The mk-query-digest tool from Maatkit now outputs information about each class of queries’ variance-to-mean ratio. The new output goes in a couple of places, including perhaps most usefully the “profile” report. Here’s an example from a real MySQL system:

# Profile
# Rank Query ID           Response time    Calls R/Call Apdx V/M   Item
# ==== ================== ================ ===== ====== ==== ===== =======
#    1 0xBFCF8E3F293F6466 11256.3618 68.1% 78069 0.1442 1.00  0.21 SELECT [redacted]
#    2 0x620B8CAB2B1C76EC  2029.4730 12.3% 14415 0.1408 1.00  0.21 SELECT [redacted]
#    3 0xB90978440CC11CC7  1345.3445  8.1%  3520 0.3822 1.00  0.00 SHOW STATUS
#    4 0xCB73D6B5B031B4CF

This month’s Maatkit release, which just hit the download servers, contains a new feature for mk-query-digest: Apdex scores. The Apdex performance index is a sensible metric of how satisfactory the response time to a request is. Of all the scientific metrics I’ve seen, it’s the most practical; and of all the practical ones I’ve seen, it’s the most scientific.

To use this feature, you need to know what the Apdex threshold means, and you need to know how to configure this in mk-query-digest.

And then you can ask and answer practical questions such as “which classes of queries against this MySQL server are not satisfying users?”

It doesn’t exist until it is nicely documented. Now Aspersa has documentation. Writing these tools has taught me how powerful and flexible Bash can be. Solving MySQL problems is a lot easier with good tools!

Related posts:

I recently started exploring the wonders of mk-table-checksum after spending too long dismissing the magic-like maatkit toolkit. What follows is not an exhaustive treatment of mk-table-checksum, but just an overview and some things I had to learn by trying the tool out.

The basic principle is based on the CHECKSUM TABLE table maintenance statement supported in the MySQL Server. The mk-table-checksum runs a CHECKSUM TABLE command on all (or some) tables. The results of that are stored in another table on the server.

read more

OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor tools for MySQL. I wrote a blog post about it a while ago. Automated analysis of bad query patterns is a good thing to write tools to do, because catching buggy queries is hard work if you do it manually.

Let’s dive right in and analyze these subtle bugs. Warning: if you don’t understand how SQL handles NULL, you’re not going to understand the following. Many

In October 2008, Baron posted How to Check MySQL Replication Integrity Continually. Here at Pythian we have developed a method based on that post, and added “verifying that masters and slaves are in sync” to our standard battery of tests.

We call it “Continual replication sync checking”. This article will explain how it works, how to test and make the procedure non-blocking, benchmarks from the real world, issues we encountered along the way, and finally

For those of you who miss what Maatkit’s mk-audit tool (now retired) gave you, there’s a pair of tools in Aspersa that more than replaces it. I wrote previously about the summary tool. I don’t think I have mentioned the mysql-summary tool. It has been under development for a while, and at this point it has quite a lot of functionality. You can see a sample of the output on its wiki page.

Related posts:

The Maatkit article on Wikipedia was removed some time ago, after being deemed not notable. I believe this is no longer the case. It’s hard to find a credible book published on MySQL in the last few years that doesn’t mention Maatkit, there’s quite a bit of blogging about it from MySQL experts and prominent community members, and the toolkit is certainly in wide use — it’s important enough that notable companies are supporting its development. It’s available through every major Unix-like operating system’s package repository. On Debian, it’s actually part of the mysql-client package, so if you install MySQL, you automatically get Maatkit too. I believe

The Maatkit toolkit for MySQL has a lot of functionality that’s common across the tools. It’s not a good idea to document this in each tool’s man page, of course. So there is an overall maatkit man page. It explains concepts such as configuration file syntax. This and all the other Maatkit man pages are online.

Related posts:

The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.

Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.

The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?

One of those lines is the list of hosts that connected to MySQL for a query, for example.

# Hosts                  4 192.168.40... (2), 192.168.40... (2)... 2

While I use this tcpdump command for MySQL query analysis with mk-query-digest, I found recently that it didn’t work on FreeBSD

$ tcpdump -i bge0 port 3306 -s 65535 -x -n -q -tttt -c 5
tcpdump: syntax error

It left me perplexed and reading the man page seemed to indicate my options were valid. I tried a few variances just to be sure without success.

$ tcpdump -i bge0 -c 5 port 3306 -x
tcpdump: syntax error
$ tcpdump -i bge0 -c 5 port 3306 -q
tcpdump: syntax error
$ tcpdump -i bge0 -c 5 port 3306 -tttt
tcpdump: syntax error

The solution was actually quite simple in the end, it had nothing to do with the commands, it had everything to do with the order of them. Placing port as the last option solved the problem.

$

Daniel changed mk-query-digest to use much less memory. It parsed and aggregated a 2GB MySQL slow query log file in a few dozen megabytes of memory for me yesterday. Thanks to Facebook for sponsoring this work.

Related posts:

If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

DatabaseJournal – Wonders of Maatkit

I spent a little time yesterday doing some things with the “summary” tool from Aspersa. I added support for summarizing status and configuration of Adaptec and LSI MegaRAID controllers. I also figured out how to write a test suite for Bash scripts, so most major parts of the tool are fully tested now. I learned a lot more sed and awk this weekend.

There is really only one way to get status of Adaptec controllers (/usr/StorMan/arcconf), but the LSI controllers can be queried through multiple tools. I added support for MegaCli64, as long as it’s located in the usual place at /opt/MegaRAID/MegaCli/MegaCli64. I am looking for feedback and/or help on supporting other methods of getting status from the LSI controllers, such as megarc and omreport. If you can contribute

I was just reading up on the syntax for index hints in MySQL, and noticed this:

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

mysql> create table t(a int primary key);
mysql> select * from t force index(PRIMAR);
Empty set (0.00 sec)

mysql> alter table t add key PRIMARY_2(a);

mysql> select * from t force index(PRIMAR);
ERROR 1176 (HY000): Key 'PRIMAR' doesn't

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with

The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query

Here’s a sneak peek at a video matrix — this is all the videos that include Pythian Group employees at the MySQL conference. I hope to have all the rest of the videos processed and uploaded within 24 hours, with a matrix similar to the one below (but of course with many more sessions).

TitlePresenterSlidesVideo link
(hr:min:sec)Details (Conf. site link)

We have an early draft of a new tool available for you to use and test. It uses heuristics to find problems in SQL. Please use it and give feedback! Here’s how:

$ wget http://www.maatkit.org/trunk/mk-query-advisor
$ perl mk-query-advisor /var/log/mysql/slow.log

By default it consumes a log file in MySQL’s slow query log format, but it can also parse the general-log format, and if you have any other kind of log you can feed it through mk-query-digest to transform the log into something it can recognize. Tell me if you’d be able to spot the mixture of join styles and use of leading % wildcards in the following query without help:

# Query ID 0x643E813A9ABDA151 at byte 2001701
# CRIT JOI.001 Mixing comma and ANSI joins.
# WARN ARG.001 Argument with leading

I’ve submitted a Birds of a Feather session for Maatkit at the upcoming MySQL conference. It’s not on the public schedule yet, but it has been accepted and scheduled for 19:00 on 13 Apr 2010. See you there!

Related posts:

Related posts brought to you by Yet Another Related Posts Plugin.

Cary Millsap has a concise, readable paper on performance. Anyone involved in database performance optimization should read it. Cary’s writing has heavily influenced the mk-query-digest tool for analyzing MySQL/PostgreSQL/Memcached/HTTP query performance, and I think you’ll get a lot more from mk-query-digest if you read this paper — and you should also read his book, reviewed here. It’s one of the top books on my Essential Books List.

Related posts:

I’ll be attending PgEast this year, as I’ve done for the last couple of years, and this year I’ll also be speaking. The topic is query analysis with mk-query-digest. The official description of my talk is as follows:

mk-query-digest is a powerful open-source tool for capturing, filtering, transforming, and aggregating queries, with the ability to do all sorts of other advanced tasks too. By default, it aggregates similar queries together and presents a designed-for-DBAs report with statistics about the most important queries, so you can see where to focus your optimization efforts. This talk shows you how to use mk-query-digest to analyze your Postgres server’s

What do you do when you're in a situation where you REALLY need your
favorite Maatkit tools, but are not allowed to load software on the
server running MySQL, AND you're not allowed to install software on the
provided workstation, especially a workstation that's running software
from a large corporation in Redmond? Portable Maatkit to the rescue!

DISCLAIMER:
I've only tested a few maatkit tools, namely mk-find and
mk-query-digest, but not completely. Some features and some tools may
not function, such as mk-audit. Use your best judgment. I'm not
responsible if your workstation or server looses all your data, your
USB key bursts into flames, or your significant other leaves you. YMMV,
use at YOUR OWN RISK!

Strawberry Perl
has been out for some time now, and recently, a portable

What do you do when you're in a situation where you REALLY need your
favorite Maatkit tools, but are not allowed to load software on the
server running MySQL, AND you're not allowed to install software on the
provided workstation, especially a workstation that's running software
from a large corporation in Redmond? Portable Maatkit to the rescue!

DISCLAIMER:
I've only tested a few maatkit tools, namely mk-find and
mk-query-digest, but not completely. Some features and some tools may
not function, such as mk-audit. Use your best judgment. I'm not
responsible if your workstation or server looses all your data, your
USB key bursts into flames, or your significant other leaves you. YMMV,
use at YOUR OWN RISK!

Strawberry Perl
has been out for some time now, and recently, a portable