MySQL-HA

I've been working on cleanups to the Drizzle autoconf and automake system. Actually, to be fair, I've been doing this work on Drizzle, Gearmand, libdrizzle and libmemcached.

In all of those projects, we have the same set of build ideas:

1. Warnings == Errors
2. As many warnings as we can enable
3. Lots of people will be building directly from bzr

As it turns out, I'm pretty lazy, and I got tired of remember to port the latest m4 macro fix that we made in one project across to the other three. O, hai - code reuse!

So I did some refactoring and split the several macros and large portions of the configure.ac files out into a set of m4 macro files. These now live in their own project, which you can grab from bzr via:

 bzr branch lp:pandora-build

Although each of the macros is useful by itself, the fun part is that you can just put:

 PANDORA_CANONICAL_TARGET

at the top of your configure.ac file right after AC_INIT  and the normal AC_CONFIG macros. It will set up libtool, automake, run some standard tests, and turn on assloads of warnings, etc.. essentially setting your build env up to be just like we're running for all of our projects. There are a few parameters that you can pass to control some optional features (drizzle, for instance, is the only one of our projects that uses gnulib)

To add the macros to your project, you can do one of two things. First, you can just copy all of the m4 files in to your m4 dir, but that will make Robert Collins sad. The better way is that you can do:

 autoreconf

 ./configure

 make install

in the pandora-build tree, which will add all of these macros to /usr/share/aclocal.

I welcome folks to start using them in their projects and to give me feedback. I'm sure feedback #1 will be "better documentation"... 

One of the things we've been working on in Drizzle is having automated performance regressions run when we push new code. Although the fully automated system isn't quite there yet, one of the pieces is, which is a Drizzle-supporting version of sysbench.

I've pushed the code to:

 lp:~drizzle-developers/sysbench/trunk

This new version of sysbench has a libdrizzle driver which can be used to run benchmarks against Drizzle, or, since libdrizzle can also talk to MySQL, to MySQL.

Getting started playing with it is pretty easy, just make sure you've installed a recent copy of libdrizzle first.

  bzr init-repo sysbench

  cd sysbench

  bzr branch  lp:~drizzle-developers/sysbench/trunk

  cd trunk

   ./autogen.sh

  ./configure

  make

  make install

From that point, you should be able to do:

  sysbench --test=oltp help

And see all of the lovely drizzle options.

When I use it against drizzle, I do this. In one shell, I go to the drizzled dir of a recently built drizzle and issue:

  ./drizzled --datadir=.  --innodb-buffer-pool=256M --key-buffer-size=16M --scheduler=multi_thread

Then in another window, I'll do:

  ./client/drizzle 'create database test' 

  sysbench --max-time=60 --max-requests=0 --test=oltp --drizzle-db=test --drizzle-port=4427 --drizzle-host=127.0.0.1 --drizzle-user=root --db-ps-mode=disable --db-driver=drizzle --drizzle-table-engine=innodb --oltp-read-only=on --oltp-table-size=10000 prepare

  sysbench --max-time=60 --max-requests=0 --test=oltp --drizzle-db=test --drizzle-port=4427 --drizzle-host=127.0.0.1 --drizzle-user=root --db-ps-mode=disable --db-driver=drizzle --drizzle-table-engine=innodb --oltp-read-only=on --oltp-table-size=10000 --num-threads=128 run

Which will run 128 client threads against a drizzle database.

This version of sysbench is based on the pre-lua version of mainline sysbench... so I don't know how soon these changes might hit mainline, but this branch is in pretty constant use by us at least.

A couple of days ago, while fighting with removing warnings from the Sun Studio build of Drizzle, I got frustrated and took a break to tackle one of the little things I've been wanting to do for a while.

Delete gen_lex_hash

(Just in case gen_lex_hash means nothing to you, it's a tool in the MySQL and Drizzle source tree that creates hash structures containing the reserved symbols for easy lookup during parsing)

Now, don't get me wrong. It's not that I have anything in particular against the code in gen_lex_hash. It's more of an annoyance about building and using a custom code-generation tool when there is a perfectly good general purpose one out there in the Free Software universe.

I replaced gen_lex_hash with gperf, which is a GNU tool for generating perfect hashes. There are several wins here:

1. We have no more tools that must be built to generate code which is then built as part of the source, making the build process easier to grok. We removed comp_err early on in favor of some header files and gettext.
2. Code that writes code is usually rather obtuse to get in to. Tweaking said code is often times even more odd, and often times people just don't do it. gperf has commandline configuration options to control various qualities of the generated hashes, so we can easily play with optimizing the hashes.
3. The code for looking something up in the generated hash has a clean API, and the method that actually does this went down from a couple of pages to 3 lines. WIN
4. Advances in the field can be incorporated into gperf upstream and we can take advantage of them.
5. The build system has gperf as part of the process now, so if someone wants to pre-generate any more hash structures, the tool is already plugged in and there is an example of its use. Before, gen_lex_hash would have had to have been patched to support generating another hash, so it really wasn't a sensible option.
6. We no longer have weird defines protecting part of a header from being sucked in when it was used for gen_lex_hash, but not in other times.

All in all, I think it's a win. It's also not a hard patch to make, it turns out. I've already mailed it over to the MySQL build team to look at.

It will be interesting to see if there are any perceivable end-user benefits in speed. I'm guessing probably not, but you never know.

Baron was just writing about problems with the Debian init scripts. The basic problem boils down to /etc/mysql/debian-start running mysqlcheck on every table.

Kolbe Kolbe and Harrison Fisk pointed this  out to me last February, and as a result I re-wrote the debian-start.inc script to only operate on MyISAM tables. Additionally, the default config was changed to turn on the myisam-recover option, so even for the MyISAM tables, all we do is touch the table to get MySQL to recover it if needed. (Which I promise you, you really want if you're modifying MyISAM tables on a production server. Don't do that, really, but that's another issue.) The new process essentiall looks like this:

for table in `select TABLE_NAME from information_schema.TABLES where ENGINE='MyISAM'":

  select count(*) into @discard from TABLE_NAME 

If the table is MyISAM and is not damaged, the select count(*) will be very, very quick and not noticable. If even this is too much, then just remove the myisam-recover entry from your /etc/mysql/my.cnf file, and the debian-start script should be essentially a no-op.

So while I obviously agree with Baron here about the root problem, I'd suggest that rather than disabling the debian-start script, you upgrade to a recent version of it.  If it's still a problem, ping me and we can talk about improving the script.

First of all, I'd like to thank everyone who has helped translating messages for Drizzle so far.

Secondly, congratulations to Dutch and French speakers, since you have translations of all translatable strings! (so far, Mark Atwood is working on an overhaul of the error message system, so I imagine we'll find some more strings that need work)

Italian speakers are close - there are only 76 of 1413 messages untranslated.

After the top three, things fall off a bit. German has 744 untranslated, English (UK) has 905 untranslated (although honestly, there probably aren't that many needing translation). Spanish has 1019 untranslated, Norwegian Bokmal has 1049 and Brazillian Portuguese has 1078. Hindi and Polish round out the top ten with 1243 and 1251 untranslated respectively.

So good job everybody, and thanks for all the hard work! For anyone who wants to pitch in who hasn't, you can just head over to http://translations.launchpad.net/drizzle and jump in