Thursday, April 29, 2010

While I was presenting my partitioning tutorial at the latest MySQL Conference, I announced a new feature that was, as far as I knew, still in the planning stage. Mattias Jonsson, one of the partitions developers, was in attendance, and corrected me, explaining that the feature was actually available in a prototype.

So, we can have a look at this improvement, which I am sure will make DBAs quite happy. The new feature is an instantaneous exchange between a partition and a table with the same structure. Using this feature, you can transfer the contents of one partition to one table, and vice versa. Since the transition is done only in the attribution of the data, there is no copy involved. The data stays where it is at the moment. What is in the table ends up in the partition and what's in the partition ends up in the table. Let's see an example.With the data in figure 1, where we have a partitioned table t1 and an empty table t2 with the same structure, we can issue the following statement:

ALTER TABLE t1EXCHANGE PARTITION p2WITH TABLE t2

After the exchange, partition p2 is empty, and table t2 contains 4 records.If we repeat the command, the contents will be swapped again, leaving table t2 empty and partition p2 with its original contents.

$ cmake-gui .# add the options you need. For example, enable innodb # or else you will need to load it as a plugin.$ make && ./scripts/make_binary_distribution

You can then use this script to test the new functionality. You may want to change Innodb with MyISAM to test it thoroughly. At the moment, it doesn't work with the archive engine (yet). UPDATE 2010-04-30 Now it does! Mattias has fixed the bug.

Notice that the value for "table_rows" is only approximate with InnoDB, while it is reliable for MyISAM. Anyway, when it says that a partition has 0 records, it's reliable for any engine. Here you see that, after the exchange, partition p04 is empty.The exchange is repeated twice, to make sure that it works both ways.Notice also that, if the table contains data that doesn't fit with the partition, the server throws an error, and the exchange does not happen.

Yesterday I was testing a branch of MySQL 5.5 to help a colleague, and I was set aback at discovering that, with the default build options, the server did not include the Archive engine.In other times, I would have to dig into the build scripts or to examine the output of ./configure --help, but that is no longer necessary. MySQL 5.5 is built using cmake, the cross platform make.

Why does this change make me feel better? Because cmake configuration is more user friendly than the old autoconf/automake/libtools horror syntax. Not only that, but there is a GUI!I am a command line guy, as you probably know, but when the purpose of a GUI is not only to show off but to make difficult choices easy, then I all for it.In my particular case, I enjoyed the idea of setting the options with a contextual help that told me the choices for each item.If you want to know more about the whole process of building MySQL with CMake, there is a comprehensive guide in MySQL Forge.Before I forget, though, there is something that reconciles my command line nature and the need for a good interface. Instead of using cmake-gui, I can get the same results with ccmakeIt is not as pretty as the graphical UI, but it has the advantage of working in a remote terminal, which for me is a must.So, if you want to try it, grab the latest MySQL 5.5 tree and follow the instructions.