Xaprb

Download Maatkit

Thanks again to all the great sponsors for my week of work on the kit!

This is the long-awaited “Baron worked on table sync” release. Hooray!

I have resolved all of the issues I was facing in getting a release out the door. I now have individual test suites on all the programs in the kit (some of them trivial, some not) as well as a comprehensive unit test suite on the shared code. This is properly integrated into the Makefile, so it won’t let me release when a test is broken. Yay!

I also found and solved a number of other issues, mostly minor, with other tools in the kit. Yippee!

But before we all celebrate too much, I want to say a word of caution: mk-table-sync is rebuilt from the ground up. That means I probably busted a bunch of things. One thing I know I broke: performance. It has two sync algorithms — Stream and Chunk — and Stream is not high performance, but Chunk can’t always be used. I personally advise you to run the tool with the --test option and make sure the table you’re syncing will not use the Stream algorithm if it is large. And if you are doubtful about bugs, as I am, you would do well not to touch the --execute option for critical data. Instead, use --print and save the output in a file, inspect the file, and then feed the file into mysql.

Also, please be aware that I threw away the old tool’s 99 useless, confusing command-line options and started over. Some of them are similar. Some of them are the same but now mean different things. In other words, assuming backwards compatibility is probably not a good idea! Don’t just upgrade and drop this tool in place (in case you had cron jobs running it, for example).

Performance will come back, better than ever. I promise. But for now, please help me find bugs, and report them via the project’s Sourceforge bug tracker. Also, I would like to encourage you to post in the project’s forums and/or mailing lists instead of blog comments (unless you just have comments) so they are easy for others to find. (No one will search my blog for help on this toolkit, I feel sure).

Changelog:

Changelog for mk-archiver:

2007-12-07: version 1.0.4

   * Updated common code.

Changelog for mk-deadlock-logger:

2007-12-07: version 1.0.6

   * Updated common code.

Changelog for mk-duplicate-key-checker:

2007-12-07: version 1.1.3

   * Updated common code.
   * Corrected documentation.
   * Added --engine and --ignoreengine options.

Changelog for mk-find:

2007-12-07: version 0.9.8

   * Updated common code.

Changelog for mk-heartbeat:

2007-12-07: version 1.0.3

   * Updated common code.
   * Added --time, --interval and --skew options.
   * The combination of sleep() and alarm() did not work on some systems.

Changelog for mk-parallel-dump:

2007-12-07: version 1.0.1

   * Updated common code.

Changelog for mk-parallel-restore:

2007-12-07: version 1.0.1

   * Updated common code.

Changelog for mk-query-profiler:

2007-12-07: version 1.1.7

   * Updated common code.
   * Added --session command-line option.
   * Servers without session variables crashed the tool (bug #1840320).
   * The meaning of --innodb was reversed.

Changelog for mk-show-grants:

2007-12-07: version 1.0.6

   * Updated common code.

Changelog for mk-slave-delay:

2007-12-07: version 1.0.3

   * Updated common code.

Changelog for mk-slave-restart:

2007-12-07: version 1.0.3

   * Updated common code.

Changelog for mk-table-checksum:

2007-12-07: version 1.1.21

   * Updated common code.
   * --chunksize was broken when no suffix given (bug #1845018).
   * --replcheck replaces the --recursecheck option (bug #1841407).

Changelog for mk-table-sync:

2007-12-07: version 1.0.0

   * Complete rewrite.
   * Syncs multiple tables and servers
   * Has no top-down or bottom-up algorithms
   * Integrates with mk-table-checksum results
   * Fixes many bugs, probably introduces new ones

Changelog for mk-visual-explain:

2007-12-07: version 1.0.5

   * Updated common code.
   * Queries of the form "... FROM (SELECT 1) AS X" crashed the tool.

This is the last day I’m taking off work to hack on mk-table-sync, and I thought it was time for (yet another) progress report. Here’s what I have done so far:

• All the code, except for a tiny bit of “glue” and “setup” code, is in modules.
• Lots more tests for the modules.
• A new sync algorithm (I still haven’t rewritten the top-down and bottom-up, which are designed for network efficiency more than MySQL efficiency, and are very complicated). This algorithm is called “Chunk” and is based on the chunking module I’m re-using from two of the other tools. This allows syncing the table a bit at a time to avoid locking it so much.
• The tool chooses its own parameters, including choosing the sync algorithm automatically by examining indexes.
• Proper exit codes, as well as several other smaller issues requested via bug reports.
• The tool now syncs entire servers. That is, you don’t have to specify a table. It’ll find all the tables and just sync them.
• The tool can sync many servers. You give it five servers, it will treat the first as the source, and sync every table in the source to each of the four remaining servers in turn.
• It can work via replication. It can discover a master’s slaves via SHOW SLAVE HOSTS and sync each slave to the master. You can also point it at a slave and it’ll discover the master, connect to it, and sync the slave to the master.
• It integrates with mk-table-checksum’s results. If you’ve given the –replicate option to mk-table-checksum, the slave’s results are stored in a table. It can read that table and sync anything marked as different. This can be combined with sync-to-master and auto-discover-slaves functionality.
• Lots of other bugs and problems are gone simply because I’m using the modules I wrote for other tools. This includes issues with table parsing, identifier quoting, etc etc. As an aside, I have to roll my own for almost everything, because I can’t rely on things like DBI’s quote_identifier() function — it does not work in earlier versions, which are amazingly common in the real world.

Whew! So what isn’t done yet?

• Bi-directional syncing.
• The Nibble sync algorithm. It will be preferred over Chunk and can be used in more cases.
• Documentation.
• Full support for wide characters. (This is non-trivial in Perl. I need to research it. A partial solution might not be hard, but I’m worried about the versions included in, for example, RHEL 3, which is very widely used.)
• Updating other tools to work right with the changes to shared code.
• Locking and transaction code. The tool will ultimately use FOR UPDATE/LOCK IN SHARE MODE automatically on InnoDB tables instead of locking them, for example.

Here’s a sample of what it can do, using a replication sandbox I set up with Giuseppe’s MySQL Sandbox. The sandbox contains a copy of the Sakila sample database. I’ll just mangle a few films on the slaves:

baron@kanga:~$ cd rsandbox_5_0_45/
baron@kanga:~/rsandbox_5_0_45$ ./s1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave1 [localhost] {msandbox} ((none)) > update sakila.film set title='academy dinosaur2' limit 12;
Query OK, 12 rows affected, 12 warnings (0.07 sec)
Rows matched: 12  Changed: 12  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > Bye
baron@kanga:~/rsandbox_5_0_45$ ./s2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

slave2 [localhost] {msandbox} ((none)) > update sakila.film set title='academy dinosaur2' limit 1;
Query OK, 1 row affected, 1 warning (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

slave2 [localhost] {msandbox} ((none)) > Bye

OK, now I’ve messed up the first 12 films on one slave, and the first 1 on another. I could just go ahead and sync them right away, but first I’ll do a table checksum to demonstrate that functionality:

baron@kanga:~/rsandbox_5_0_45$ mk-table-checksum --replicate=test.checksum --port=16045 127.0.0.1 -q

And now I’ll tell the sync tool to go fix the differences the checksum revealed:

baron@kanga:~/rsandbox_5_0_45$ mk-table-sync  --replicate=test.checksum h=127.0.0.1,P=16045 -vx
# Syncing P=16046,h=127.0.0.1
# DELETE INSERT UPDATE ALGORITHM DATABASE.TABLE
#      0      0     12 Chunk     sakila.film
#      0      0      0 Chunk     sakila.film_text
# Syncing P=16047,h=127.0.0.1
# DELETE INSERT UPDATE ALGORITHM DATABASE.TABLE
#      0      0      0 Chunk     sakila.film
#      0      0      0 Chunk     sakila.film_text
baron@kanga:~/rsandbox_5_0_45$ 

Pretty easy, huh? Take a look at the output: the first thing it did was fix the 12 films I changed. sakila.film has a trigger that updates sakila.film_text, so that table got changed too. The checksum tool caught this difference, but the differences were gone by the time the sync tool examined them, again due to the trigger. On the second slave, no differences were found at all, because the changes to the first slave were made on the master, automatically fixing the second slave. (This won’t always be the case, but it worked in this example).

While I’d love to continue building the perfect beast, I’m going to have to call it quits around noon today and start cleaning up, writing the documentation, and getting ready to release the code. I’m not sure how much I’ll finish in that time.

By the way, anyone who wants to is welcome to get the code from the Maatkit SVN repository! I never make a big deal out of that because I generally assume people want to run released code, but SVN is there if you want it…

Ironically, the Stream algorithm I wrote as the simplest possible syncing algorithm does what the much more efficient algorithm I wrote some time ago can’t do: sync a table without a primary key, as long as there are no duplicate rows. In fact, it’s so dumb, it will happily sync any table, even if there are no indexes.

The flash of inspiration I had on Friday has turned out to be good insight. It immediately showed me how I can re-use a lot of the hard work I’ve already done for other tools. Chunking and nibbling are the names I’ve given to two algorithms I’ve developed for processing tables a little at a time. Chunking looks for a suitable index and generates an array of WHERE clauses that will divide the table into pieces of approximately a given size (number of rows or number of bytes). I use this on mk-table-checksum and mk-parallel-dump. It requires an indexed column I can treat as a number one way or another. That includes temporal values.

Nibbling is related. It’s an efficient way to do something like LIMIT X, Y without scanning through the first X rows. It also requires a suitable index, but the code I wrote to make it work with mk-archiver is really generous about what “suitable” means. It’ll basically work with any index. It selects some rows with LIMIT Y and uses the last-fetched row to start the next select.

Both algorithms will adapt well to finding and resolving differences in rows, a chunk at a time. The general procedure is to create the WHERE clauses that define boundaries around the chunk of rows, then checksum the whole chunk. The result is a tiny little hash value. If this differs between the source and destination tables, the next step is to checksum the rows individually and fetch their primary or unique key columns. This uses a little more network bandwidth, but it’s still not bad unless the key (or the chunk) is huge. Any rows whose checksums differ can then be fetched by the key and synced.

The more complex algorithms, which were in the original table-sync tool, will come later. They can be potentially much more efficient in terms of network traffic, but they have drawbacks too, such as the granularity of locking. The mk-table-sync tool will soon be able to choose the best algorithm that causes the least locking and just do it without any fuss. For example, if it sees a nice primary key it can use for chunking, and it sees that the table is InnoDB, it’ll just chunk and use SELECT FOR UPDATE. Voila, no table locks, and not much of the table will be locked at a time (it’ll commit between chunks).

Right now I’ve gotten a simple interface for code that finds differences in rows, a plugin-like interface for implementing each of the algorithms uniformly, an interface for resolving differences, and a few other things. I’m about to embark on the Chunk algorithm for syncing.

I don’t think most people will consider this a big deal, but don’t expect the final product to correctly sync tables without a primary key and with duplicate rows. Comparing tables with duplicates is absolutely meaningless. If you can’t write a WHERE clause that uniquely identifies a row, you’re done.