Xaprb

MySQL Administrator's Bible

MySQL Administrator’s Bible by Sheeri K. Cabral and Keith Murphy, 2009. Page count: 800+ pages. (Here’s a link to the publisher’s site.)

This book is a comprehensive reference guide to MySQL that’s accessible to beginning DBAs or DBAs familiar with another database. It has enough detail to be a useful companion throughout a DBA’s career. It also covers many related technologies, such as memcached, at a moderate-but-useful level of detail. This isn’t exactly a how-to book, and it isn’t exactly a reference manual; it’s more of a blend of the two.

The audience will depend on personal preferences. Some of the reference material is the type of thing I would look up with command-line --help options or the MySQL manual. But there are times when the reference aspect of the book is uniquely valuable. For example, the online documentation tends to list things alphabetically; the book might break them down into groups by function. An example is the sql_mode parameters, which it groups into categories like “Getting rid of silent failures, silent conversions, and silently allowing invalid data.”

The non-reference aspect of the book has a lot of examples of how to do things, such as how to set up replication over SSL. This is exactly what I’d look for in a book. Otherwise, you’re reduced to reading documentation (inefficient, mind-numbing) or trusting the information you find online, which is generally not something I do.

Speaking of trusting information, I was happy to see very few typos or errors. Occasionally I caught a minor slip. For example, when discussing the limited memory a 32-bit mysqld can use because it runs in a single process, there’s a typo that mis-states this architectural feature as “mysqld is currently single-threaded,” which is not quite the same thing. Overall, you can rely on the information you’ll read in this book.

The book is divided into four parts: first steps, developing with MySQL, core administration, and a set of chapters and appendixes grouped under extending your skills. I think this organization works well. You can read the full Table Of Contents at the publisher’s site linked above.

Coverage is for MySQL 5.1 and 6.0. As we know, 5.1 was GA’ed and 6.0 has been killed and replaced by a new release policy. This gives a slightly odd feeling to some passages, which speak about 5.1 in the past tense and 6.0 in the present tense! As far as I know, however, this book contains the most complete coverage of MySQL 5.1 in print. The only other similar book I’ve read that covers 5.1 is High Performance MySQL 2nd Edition — and that one is a bit light on details because there wasn’t a lot of production knowledge of 5.1 yet (I’m the lead author of HPM2e, by the way).

Speaking of which, I think that MySQL Administrator’s Bible is a very good complement to High Performance MySQL, 2nd Edition. The former is useful to people who have varying levels of knowledge, while the latter assumes a lot of experience and doesn’t cover introductory material much. And the books have different topics, of course. So if you’re new to databases, or if you’re new to MySQL, you might do well to start with Sheeri’s book, then continue or supplement your education with ours.

All in all, this is a book that’s well worth buying if you’re going to administer a serious MySQL installation. I tip my hat to Sheeri — I don’t know how she did it. It’s a huge project and she pulled through the last (and by far most difficult) part of it by herself.

Disclosure: I tried to be a tech reviewer for this book, but I was over-committed and had to back out after a while.

I’ll be attending the IT Management Conference later this year in New York. My proposals weren’t chosen so I won’t be speaking, but I wanted to attend whether I’m speaking or not. It should be a very useful conference for those in or near management or leadership positions. Email me if you want a $50 discount off the registration, by the way.

PS: .me is a new top-level domain? Ugh, when will it end.

Suppose that you’re writing a new Maatkit tool (just a random example, really) and its job is to measure the difference in execution of queries. The simplest metric is execution time.

Now suppose that you’re trying to figure out a metric of badness. The query executes in a second on machine 1 and 1000 seconds on machine 2.  That’s a pretty bad change.  How do you quantify this?

Now you’ve got a query that executes in 1ms on machine 1, and 10ms on machine 2.  It’s a tenfold change.  Is it a bad change?  Maybe it’s just the difference in which files were cached in memory, or network latency because someone flooded the TCP pipe and the packets had to be backed off and retried, or something like that.  Is this significant?  How should it contribute to the badness score?

Let’s think of another example too.  Later in this mythical tool’s life, we’ll be examining EXPLAIN and looking at row estimates.  There are important differences between estimates of 1 row, 2 rows, 20 rows and 2000 rows.  But from 2 to 3 rows, or from 100 to 125 rows — is that a significant change? How should it contribute to the badness score?  What about this: a full table scan vs. an index scan, how should that contribute?

The general problem that I’m gesturing at here is a kind of generic badness score, which can be an accumulation of lots (dozens) of factors.  From my thinking on it so far, it’s a very complex problem, because you want to avoid false positives, you want to really capture a badness score in a way that’s quantifiable and sortable (this query is badder than that one, all things considered) and you don’t want to miss small things in the noise (these queries are the same in 23 of the 24 metrics, and that 24th metric is enough to trigger the alarm).

The other thing that’s worked its way into my small brain is this: it’s got to be a solved problem (unless it’s really intractable).  But I can’t think of the right combination of things to point me to the right Computer Science literature.

Help?