If you’ve been holding out for that golden opportunity, now’s a great time to get involved in Maatkit.

Until now I haven’t really made a conscious effort to open-source the decision process and get people involved; Maatkit has been largely driven by so-called “real-world needs,” as perceived through my little lens on the world (and emails from the whole Percona team telling me when something’s wrong). I guess I am likely to remain some kind of benevolent dictator, because I created Maatkit and historically I’m the main hacker. But it doesn’t have to stay that way, and the project and users will be better off if it doesn’t.

So I’ve been trying to break out of the rut of just having some little email exchange with

  [Read more...]

I was just talking to someone who backs up their MySQL servers once a day with mysqldump, and I said in a catastrophe, you’re going to have to reload from a backup; that’s some amount of downtime, plus up to a day of lost data. And they said “We can just promote a slave, we’ve done [...]

This is actually old news, but I never thought to file a bug report (until now) or say anything to anyone about it. If you use mysqldump to dump and restore a MySQL table that has INSERT triggers, you can get different data in your restored database than you had when you dumped. [...]

Sun has invited me to showcase Maatkit at the dot-org pavilion at the upcoming MySQL Conference and Expo 2009. At this time it’s really hard to say what I’ll be demoing! Development on Maatkit is accelerating and I don’t look for that to change, so who knows what we’ll have done by then. [...]

Last week I had to confront one of those situations where you can’t really tell what is going on with a piece of software, and the final conclusion would sound completely crazy if postulated as the initial hypothesis. The regular MySQL commands and utilities fall short in these cases, so I had to resort to the three tools reviewed in this article.

The problem we were diagnosing was this: at some point in time, a number of queries that use to take less than one minute to execute, started to take between five to 15 minutes. We needed to get an insight into what was going on inside the MySQL server.

MySQL Tuner

At some point in a long diagnosis process, MySQL’s SHOW [GLOBAL] VARIABLES and SHOW [GLOBAL] STATUS are nothing more than a long list of numbers. Going through a team mate’s notes on another issue, I came

  [Read more...]

Download Maatkit The December release is here! There are some goodies in this release, but the major one is an initial version of mk-log-parser, a slow log analysis tool that is carefully designed (with lots of input from Percona consultants) to make slow log analysis as productive and easy as possible. It’s based on a [...]

Download Maatkit The newest release of Maatkit contains mk-audit bug fixes, lots of new features for mk-table-checksum so you can check replication more frequently (see my earlier post about this), and more bug fixes for other tools. Here’s the changelog: Changelog for mk-audit: 2008-10-17: version 0.9.3 * mk-audit died if no /etc/*release file existed (issue 62). [...]

I just wrote about how Maatkit has gotten some new features for helping people make sure their slaves have an exact copy of the master’s data. This work was largely sponsored by one of Percona’s customers, whose name I have permission to mention: Vantage Media. Thanks Vantage! Your sponsorship helps many people and businesses.

I have recently added some features to Maatkit’s mk-table-checksum tool that can make it easy to checksum the relevant parts of your data more frequently (i.e. continually, but not continuously). This in turn makes it possible for you to find out much sooner if a slave becomes different from its master, and then you [...]

Download Maatkit There’s a new release with a lot of goodies — speed, efficiency, user-friendliness, and new features. In particular some of Percona’s clients have sponsored features for things they need such as the ability to more frequently verify that slaves are in sync with their masters. If you need features, please ask Percona [...]

Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.

There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.

The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple

SELECT user,host FROM mysql.user WHERE Super_priv='Y';

is sufficient. If it is “who has write access to the foo database”, you might write:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';

but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:
(more…)

Slave delay can be a nightmare. I battle it every day and know plenty of people who curse the serialization problem of replication. For those who are not familiar with it, replication on MySQL slaves runs commands in series – one by one, while the master may run them in parallel. This fact usually causes bottlenecks. Consider these 2 examples:

• Between 1 and 100 UPDATE queries are constantly running on the master in parallel. If the slave IO is only fast enough to handle 50 of them without lagging, as soon as 51 start running, the slaves starts to lag.
• A more common problem is when one query takes an hour to run (let's say, it's an UPDATE with a big WHERE clause that doesn't use an index). In this case, the query runs on the master for an

  [Read more...]

Over in Maatkit-land, Daniel Nichter and I practice test-first programming, AKA test-driven development. That is, we write tests for each new feature or to catch regressions on each bug we fix. And — this is crucial — we write the tests before we write the code.* The tests should initially fail, which is a validation that the new code actually works and the tests actually verify this. If we don’t first write a failing testcase, then our code lacks a very important guarantee: “if you break this code, then the test case will tell you so.” (A test that doesn’t fail when the code fails isn’t worth writing.)

Most of the time when I do this, I write a test, it fails

  [Read more...]

The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it’s necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.

–replicate

This option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used

  [Read more...]

Dear LazyWeb, I want to use my Ubuntu laptop (on amd64 BTW) to build an RPM of Maatkit that will work on all RPM-based distros. Is it possible? Or are there enough differences between the RPM-based distros that I can’t do it? Mind you, the finished RPM ought to just have some man pages and Perl scripts, so I don’t think it will be platform- or distro-specific. But I am just not an expert on it.

The second question is, what do I need to put into my Makefile to do this? My ‘make all’ currently builds a .zip, a .tar.gz, and a .deb package — what needs to change to make that include .rpm?

Someone who is willing to help create .spec files, etc, etc will be immediately given commit rights to Maatkit’s SVN repository!

Debian, packaging, RPM

Download Maatkit

Maatkit version 2152 is ready for download. This release is also known as the “is this project really alive?” release. We thought we should delay until MySQL released a new Community Server version. Just kidding — it has nothing to do with that.

This release is also very significant in that it’s the first one that has large code contributions by someone other than myself. As you may know, Percona (my employer) has hired the very talented Daniel Nichter, author of mysqlreport and other goodies, to help with Maatkit. So far it is a match made in heaven, and

  [Read more...]

Few days back, Baron re-introduced MySQL's command line pager command and described some cool tricks with maatkit's mk-visual-explain (one of my favorite tools). Soon after reading it, I wished if it was possible to describe regex based (on query) paging. I have written a small hack, christened mysmartpager, that can actually do regex based paging for you. The idea is simple, write a relay that will redirect the output based on to the desired pager. The problem was complex, there was no direct way of getting to know the original query. There are a couple of indirect ways of doing so, but of course with hurdles:

• Run mysql client with --xml option: This will print the output of each command in xml and the command itself is included in the xml. The downside

  [Read more...]

Percona is looking to hire someone to develop Maatkit, among other things.

If I weren’t having so much fun being the consulting team lead, I’d be doing it myself. (In fact, I’m still hacking on it a lot. Got some pretty fun stuff done this weekend.) I don’t know what the rest of the world thinks, but I think Maatkit is a damn enjoyable project to work on. Hopefully someone else will have the same kind of mindset and want to get paid for it, unlike poor working-on-the-weekends me.

I’m not stepping away from the project. It’s just grown a lot, and there is room and money to grow it much more. This is actually the best compliment to the project: that it is worth

  [Read more...]

Download Maatkit

Before I tell you what’s new, let me tell you how cool I think it would be if Maatkit were voted Sourceforge.net project of the year. Just something to think about :-) I suggest the “Best Tool or Utility for SysAdmins” category. You can actually click the Back button and nominate it for several categories. Not that anyone would do that, of course.

Also, if anyone wants to jump in and help out with bug fixes and new features, please, by all means. Maatkit is a true open-source project as well as being Free Software. If you can follow coding conventions and understand Perl, I’m a very benevolent dictator and would gladly grant commit rights. As it turns out, since I’ve

  [Read more...]

I have been using Maatkit in a different way since I joined Percona as a consultant. When I’m working on a system now, it’s a new, unfamiliar system — not one where I have already installed my favorite programs. And that means I want to grab my favorite productivity tools fast.

I intentionally wrote the Maatkit tools so they don’t need to be “installed.” You just run them, that’s all. But I never made them easy to download.

I fixed that. Now, at the command line, you can just run this:

wget http://www.maatkit.org/get/mk-table-sync

Now it’s ready to run. Behind the scenes are some Apache mod_rewrite rules, a Perl script or two, and Subversion. When you do this, you’re getting the latest code from

  [Read more...]

Update: Karanbir says “Just one thing to keep in mind is that we dont want too many people using it from the Testing repository - we only need enough feedback to move it from testing to stable ( and to be honest, there are already 8 people who have said yes it works - so move to stable should happen within the next 24 - 48 hrs ). Once the package is in stable, users on CentOS4 and 5 wont need to do anything more than just ‘yum install maatkit’ and it will install for them.”

At least one person (Karanbir Singh) is working to get Maatkit into the CentOS repositories, and I believe there might be movement towards RHEL also. From an email to the Maatkit discussion list a little while ago,

I am in

  [Read more...]

I did an interview with Barton George from Sun while I was at the conference last week. Barton has now posted the interview. If you’re quick, you can listen to it before I do.

Topics: everything and anything, including Maatkit and PostgreSQL.

Baron Schwartz, Barton George, maatkit, mysqluc08, mysqluc2008, Podcast, Sun

Here’s a rundown of Thursday (day 3) of the MySQL Conference and Expo. This day’s sessions were much more interesting to me than Wednesday’s, and in fact I wanted to go to several of them in a single time slot a couple of times.

Inside the PBXT Storage Engine

This session was, as it sounds, a look at the internals of PBXT, a transactional storage engine for MySQL that has some interesting design techniques. I had been looking forward to this session for a while, and Paul McCullagh’s nice explanations with clear diagrams were a welcome aid to understanding how PBXT works. Unlike some of the other storage engines, PBXT is being developed in full daylight, with an emphasis on community involvement and input. (Indeed, I may be contributing to it myself, in order to make its monitoring

  [Read more...]

Day two of the conference was a little disappointing, as far as sessions went. There were several time blocks where I simply wasn’t interested in any of the sessions. Instead, I went to the expo hall and tried to pry straight answers out of sly salespeople. Here’s what I attended.

Paying It Forward: Harnessing the MySQL Contributory Resources

This was a talk focused on how MySQL has made it possible for community members to contribute to MySQL. There was quite a bit of talk about IRC channels, mailing lists, and the like. However, the talk gave short shrift to how MySQL plans to become truly open source (in terms of its development model, not its license). I think there was basically nothing to talk about there. I had a good conversation about some of my concerns with the speaker and some others from MySQL right afterwards.

  [Read more...]

Today is the first day at the conference (aside from the tutorials, which were yesterday). Here’s what I went to:

New Subquery Optimizations in 6.0

By Sergey Petrunia. This was a similar session to one I went to last year. MySQL has a few cases where subqueries are badly optimized, and this session went into the details of how this is being addressed in MySQL 6.0. There are several new optimization techniques for all types of subqueries, such as inside-out subqueries, materialization, and converting to joins. The optimizations apply to scalar subqueries and subqueries in the FROM clause. Performance results are very good, depending on which data you choose to illustrate. The overall point is that the worst-case subquery nastiness should be resolved. I’m speaking of WHERE NOT IN(SELECT…) and friends. It remains to be seen how

  [Read more...]

MySQL just gave me an award at this morning’s keynote, along with Sheeri Kritzer Cabral (for the second year in a row!) and Diego Medina, for my code contributions to the MySQL community, specifically Maatkit, which makes it easier to make MySQL reliable, fast, and robust. It’s an honor to be recognized. And while I could leave it at that, I’d like to say a word or two more.

The economy, community, and ecosystem that’s building around Free Software can often be very rewarding financially. This is a great motivation; being rewarded for your efforts is one of the chief virtues of a culture of entrepreneurship, along with the idea that to try and fail is just as noble as to succeed. But I find that isn’t enough. If I were only rewarded financially and with

  [Read more...]

I’m at the MySQL Conference and the t-shirts I created for Maatkit have arrived. Come get yours! They are high-quality, attractive shirts you’ll be proud to wear, and they are a nice rich wine-red color.

Harrison Fisk (co-author of MySQL Clustering) got the first one, because he told me that he recommends Maatkit to MySQL Support customers about twice a week. I made sure to save one for Jay Pipes too, because his luggage got lost so he has nothing to wear. Unfortunately, I didn’t

  [Read more...]

Effective April 1, I will join Percona full-time as a consultant. I’ll be helping people build high-performance applications with MySQL, but I’ll also be continuing to develop and improve tools such as Maatkit.

This career change has been a long time in progress. I’m really looking forward to it, but at the same time it’s hard to leave my current employer, The Rimm-Kaufman Group (RKG). Working with them has been the best job I’ve ever had. But ultimately, my dream job is to help as many people as I can, and consulting will be a better way to do that.

At a time like this, I like to reflect on the trail that has led here. It’s a good opportunity to realize how fortunate I really am and fill up my gratitude

  [Read more...]

If you're serious about MySQL, it doesn't hurt to know the people closely tied to its development and maintenance as well as famous bloggers. Here's my ongoing list of people I consider important:

Peter Zaitsev - MySQL Performance Blog, former head of MySQL AB High Performance Group. His company Percona is available for consulting. He's a co-author of High Performance MySQL 2nd edition (great book).

Baron Schwartz - also know as Xaprb, Baron a co-author of

  [Read more...]

Download Maatkit

Maatkit contains essential command-line utilities for MySQL, such as a table checksum tool and query profiler. It provides missing features such as checking slaves for data consistency, with emphasis on quality and scriptability.

This release contains major bug fixes and new features. Some of the changes are not backwards-compatible. It also contains new tools to help you discover replication slaves and move them around the replication hierarchy.

Changelog for mk-archiver:

2008-03-16: version 1.0.8

   * Added --setvars option (bug #1904689, bug #1911371).
   * Added --charset option (bug #1877548).
   * Changed short form of --analyze to -Z to avoid conflict with --charset.

Changelog for mk-deadlock-logger:

2008-03-16: version 1.0.9

   * Added --setvars option (bug

  [Read more...]