Xaprb

I’ve been profiling a Perl program recently with Devel::FastProf, and I had a little bit of a hard time finding one of my hot spots. I had a construct like the following:

if ( $condition_one ) {
  # some code
}
elsif ( $other_condition ) {
  # code
}
elsif ( my (@temp) = $text =~ m/(complex) (regex)/g ) {
  # some other code
}
elsif {
  # and so on
}

Devel::FastProf showed me that my hot spot in the code was the very first line. I could not understand why. I tried a few different things — always the same result.

Then it hit me. The way I write the code and what the Perl compiler turns it into aren’t the same things at all. Ever tried to debug an if/elsif/elsif statement in Perl’s debugger? You get to step up to the first line, but then immediately afterwards you drop into the case that matched — you don’t get to step over each condition check in turn.

Perl treats them all as one statement. And my hot spot was really the third conditional check. I fixed it by replacing that with a simple regular expression and doing the complex parsing inside the block.

It’s been a while since I’ve written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31st I managed to finish a first draft of the last big chapter (Scaling and High Availability)! Now I’m back to full-time work at my employer, and I’m working on the book in the evenings and weekends only.

This doesn’t mean the book is close to being done, though. The editor is sending out some chapters for technical review, and there’s still a lot more writing and revising to be done.

Last weekend I revised the Security chapter from the first edition, which I think will be the only chapter that we’ll just revise and update, rather than completely rewriting (well, maybe the Architecture chapter could be considered a revision instead of a rewrite, but it’s a stretch; we changed it a lot). I removed a lot of the material that repeated the MySQL manual, and added a lot of information and best practices on grants, new privileges and objects in MySQL 5, common tasks, common mistakes, and so on. The chapter ended up being nearly as long, even though I stripped out all the code listings and so on from the first edition (in fact, I reduced the first edition’s material to a few paragraphs).

Beyond that, though, there are little details to finish out in many of the chapters. Examples that need to be finished, figures that need to be re-drawn, material that doesn’t quite fit and needs to be re-arranged or even moved to another chapter; it’s a lot of work. Peter Zaitsev has been reviewing some of the core chapters on query and schema optimization etc, and I’m revising them in response to his comments. That’s what I spent today doing.

I think the biggest chunks of work that remain are going to be making chapters 3, 4, 5, 6 and 7 (benchmarking, profiling, schema, indexing, query optimization, advanced features, and server tuning) flow together well. The challenge here is how to organize the vast amount of material so it reads well, without too many forward references, and still be useful as a reference work. The detail we’ve gone into is incredible. It makes it very hard to find the single best place to mention each little bit of wisdom, because all of this material is completely inter-related. It’s tough to flatten the graph of knowledge into a one-dimensional narrative.

It’s not just these chapters that have a lot of inter-related material, of course. It’s hard to talk about tuning the server settings (chapter 7) without bringing the OS and hardware (chapter 8) into it, and whenever you do this you also need to think about measuring and monitoring status information (chapter 14). Of course, you need to do that for benchmarking and profiling, too (chapter 3). I’m sure you see the dilemma!

The good news is, if we succeed in doing this well, you will find the book enormously useful. Stay tuned!

Download MySQL Toolkit

This release of MySQL Toolkit fixes some minor bugs and adds new functionality to four of the tools. Some of the changes I made were in response to feedback I got at the recent MySQL Camp. I’m still working on some of the feature requests, such as daemon-izing certain tools.

For those who requested features for MySQL Query Profiler, the tab-separated format should give you the desired output: no zero rows, and variables are not renamed.

Here’s a changelog:

Changelog for mysql-duplicate-key-checker:

2007-09-01: version 1.1.0

   * Column printout was misaligned one space.
   * Refactored into a runnable module and added tests.
   * Redundant indexes were only detected if the shorter index was first.
   * Redundant foreign keys sometimes weren't detected.
   * All indexes on MEMORY tables were reported as HASH.
   * Added --clustered option to report appended PK columns as dupes for InnoDB and solidDB.

Changelog for mysql-query-profiler:

2007-09-01: version 1.1.4

   * SHOW STATUS inconsistencies after a FLUSH were skewing status.

Changelog for mysql-table-checksum:

2007-09-01: version 1.1.14

   * Added documentation about the storage engine for the checksum table.
   * The --replcheck option now checks the server and its slaves.

Changelog for mysql-visual-explain:

2007-09-01: version 1.0.2

   * FULLTEXT indexes were not recognized (type=fulltext in EXPLAIN).
   * Temporary/filesort were not added to the right node in the tree.
   * Added a new node type for "Range checked for each record."