MySQL-HA

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

Vadim announced Percona's XtraDB a couple of days ago, and then followed up with some really fun benchmarks. Of course, the first thought I had was "I need another cup of coffee," which was shortly followed by "I should merge that in to Drizzle!"

First step was merging it in to a branch of the innodb-plugin-upstream branch, making an

lp:~mordred/drizzle/xtradb-upstream

Merged it up to drizzle trunk, and now we have:

lp:~mordred/drizzle/xtradb

But what I really want is for our changes to InnoDB/XtraDB to get merged in. So I also make a branch of XtraDB and applied our stuff to it:

lp:~mordred/percona-xtradb/drizzle

My goal there would be a chunk of code that will compile for either MySQL or Drizzle. I've done what I can for that - with the exception of actually attempting to compile it under MySQL, so it might need a new patch or two.

That has been proposed for merge into the percona-xtradb trunk. 

As far as I can tell, that's just about all of the permutations of the code that could possibly be interesting to anyone. Have fun!