Drizzle

Follow it over on launchpad. After having fixed an incredibly odd compiler warning (and with -Werror that we build with, error) on OSX (die die die) – xtrabackup for Drizzle is ready to be merged. This will bring it into our next milestone: freemont. ... [More] Over the next few weeks you should see some good tests merged in for backup and restore too. While not final final, I’m thinking that the installed binary name will be drizzlebackup.innobase. A simple naming scheme for various backup tools that are Drizzle specific. This casually pre-empts a drizzlebackup tool that can co-ordinate all of these (like the innobackupex script). [Less]

This is a test to see if people will vote this down on Planet MySQL. If you’ll vote down some of the posts that have gotten negative marks recently, like Allan Packer saying that he’s still working on Sparc supercluster, or Drizzle going GA, or ... [More] Percona Server and XtraBackup being available on Solaris, or mk-query-digest filter how-tos, or TokuDB announcing online add of columns, or XtraBackup Manager, or using WordPress on Drizzle, well… Then you’re probably the kind of person who’ll vote negatively about MySQL saving the lives of baby seals. Seriously: is it at all possible that the above posts, which got thumbs-down votes, are actually bad news for anyone? I usually don’t look at the Planet, and only read through my RSS feeds, but for some reason today I actually browsed to it, and I was just amazed at how many posts that are nothing but great steps forward for the MySQL community have negative votes! Who are these people? Who would do such a thing? Get a life! But before you do, please vote this post down — go on, do it! Prove my point! There are two lessons to learn from this: the ability to vote something down brings out the second-worst in people, and the ability to vote anonymously brings out the absolute worst. Both of those “features” should be ripped out of Planet MySQL and thrown away. Related posts:Breaking news: SHOW INNODB STATUS ported to XML Looking back at the MySQL news from last year News on MySQL Cacti Templates News flash: MySQL 5.1 has zero bugs Drizzle is a MySQL Technology Incubator? [Less]

Since Drizzle went GA a couple of weeks ago, i figured i should update rabbitreplication to support it, quite a few changes have been done to the structure of the protobuf messages etc. First hurdle was to get drizzle to actually publish messages to ... [More] rabbitmq, so here is a step-by-step tutorial to get started with that.Install a recent version of rabbitmq Get the latest version of librabbitmq:$ hg clone http://hg.rabbitmq.com/rabbitmq-codegen/$ hg clone http://hg.rabbitmq.com/rabbitmq-c/Build it (in the rabbitmq-c directory):$ autoreconf -f -i$ ./configure$ make$ make installInstall Drizzle, follow these instructions:http://docs.drizzle.org/#compiling-and-installing. (During ./configure you should see that librabbitmq is found and that the plugin is getting built)Start Drizzle like this:$ sbin/drizzled7 --plugin-add rabbitmq,default-replicator --rabbitmq.use-replicator defaultTo verify that it works, you can run a rabbitmq consumer from rabbitmq-c/examples, like this:./amqp_listen localhost 5672 ReplicationExchange ReplicationRoutingKeyThen execute an insert (or something, just not a select :) ) on your drizzle server, and you should see something like this in your console:Result 0Frame type 1, channel 1Method AMQP_BASIC_DELIVER_METHODDelivery 1, exchange ReplicationExchange routingkey ReplicationRoutingKey----00000000: 0A 17 08 01 10 87 36 18 : F0 FA D9 99 FA F1 A7 02 ......6.........00000010: 20 99 81 DA 99 FA F1 A7 : 02 12 40 08 01 10 F2 FA [email protected]: D9 99 FA F1 A7 02 18 FC : FA D9 99 FA F1 A7 02 2A ...............*00000030: 17 0A 06 0A 01 62 12 01 : 61 12 06 08 04 12 02 69 .....b..a......i00000040: 64 12 05 08 01 12 01 74 : 32 11 08 01 10 01 1A 0B d......t2.......00000050: 0A 01 32 0A 02 61 61 10 : 00 10 00 20 01 28 01 ..2..aa.... .(.0000005F:In a couple of days I'll make a new release of rabbitreplication that supports the latest version of drizzle, stay tuned for that! I'll also update the drizzle rabbitmq plugin so that it is actually usable (support reconnect etc) [Less]

Hi everyone! Just wanted to announce that Drizzle will stop supporting FreeBSD moving forward. We will continue to build on the platform, but will no longer run our test suite. If someone in the community wants to take over maintenance of Drizzle ... [More] on FreeBSD, we would be delighted! It is simply a matter of it taking too much of our limited time to keep things running on this system. UPDATE:  As you can see below, we've had the FreeBSD port maintainer offer to help us keep things going.  Go open source! : ) [Less]

So, the amazing Mr. Shrewsbury, master of all things Drizzle-replication, has created a beta tree with multi-master replication! As his blog explains, it is still very early days, but as it currently stands, it can provide some long-hoped-for ... [More] functionality for people with particular replication needs.  This feature will definitely be made more robust in the future, but it is still quite useful in its present form. As I think this would be cool to see in trunk, I have started testing the tree.  No progress has been made on the multi-master side of things…yet, but I am happy to report that all of our existing replication tests (including crash and restore) are passing just fine. What this means is that the code could make it into trunk without breaking anything for anyone and also providing access to a very cool new feature.  As time permits, we will be expanding the test cases for this feature, but in the interim, I hope that you will enjoy testing Dave’s awesome work ; ) [Less]

Welcome to this week’s (slightly late) edition of Last Week in Drizzle.  This week sees the kick-off of many new features for the next release of Drizzle codenamed ‘Fremont’ and the mailing list is a hive of activity around Google Summer of Code. ... [More]  I apologise for publishing a few days late this week and will try and stay on-track for future editions. Fremont In the tradition of Drizzle using Seattle road names in alphabetical order for codenames the next release of Drizzle is codenamed ‘Fremont’ (the current GA release is codenamed ‘Elliott’).  Monty Taylor has outlined the merge process going forward as can be seen in his mailing list post. Google Summer of Code We have been accepted for Google Summer of Code 2011 and are getting a lot of interest from potential applicants.  If you are interested in working on the Drizzle project as part of GSoC we have the following recommended instructions: Check out our wiki page on potential projects Email the mailing list with an introduction about yourself and join the Freenode #drizzle IRC channel to chat to us Look at our low-hanging-fruit tasks and try to take one or two on.  This gets you used to the code and launchpad processes as well as gives us an insight into your abilities Xtrabackup Stewart Smith has been working hard on integrating Percona’s Xtrabackup with Drizzle.  Xtrabackup is an online backup tool for InnoDB much like MySQL’s Enterprise Backup.  This is nearly ready for merging into Drizzle and Stewart has written a great blog post on the subject on his blog. Catalogs Stewart has been a real busy guy this last week, another project he has been working on is getting catalogs support working with more than one catalog.  For those not familiar with catalogs they are a way of totally isolating one user’s databases from another, similar to having multiple installations of Drizzle in one box but all running from one daemon.  In the GA release a lot of the framework already existed for catalogs and everything in it runs from a catalog called ‘local’.  More information on the progress Stewart has made can be found on his blog post. Libdrizzle 2.0 In Fremont we are working towards Libdrizzle 2.0.  This is a C++ version of Libdrizzle with a C compatible API.  Eventually it will contain new features such as native sharding (we are still working on filling out a potential features list for it).  For now in the Drizzle trunk you can see libdrizzle has been moved to libdrizzle-1.0 and a new libdrizzle-2.0 directory exists for the new work. Multi-Master Replication David Shrewsbury has been working on multi-master replication in Drizzle with a beta release ready to try.  By multi-master I mean having multiple masters write to a single slave.  For more information on this work take a look at his blog post. Final Thoughts Development is starting to move forward at a rapid pace for our next GA and we have had a lot of branches merged that I haven’t discussed here from people such as Olaf van der Spek who has contributed a lot towards code cleanups. As always if you have any feedback or topics you would like me to cover, please let me know. [Less]

It’s time for the weekly roundup of news for Percona Server and XtraBackup. To follow up on my note from last week, we’re still checking and validating the download stats. It appears that Percona Server has been downloaded well over 100,000 times ... [More] , and Percona XtraBackup has been downloaded over 200,000 times. But when we changed the script to separate out the downloads for our 5.5 release series, the overall number came back slightly decreased, which makes no sense. We haven’t had the chance to vet the analysis script yet, but we should be able to do that shortly. For Percona Server, We worked on Solaris builds for the server. Peter proposed a feature for the server to enable more efficient incremental backups with XtraBackup. Peter, Vadim, and Nickolay continued investigating alternative methods of implementing flush in Percona Server. (I am involved, but not as deeply.) The task is complex and there are a great many scenarios to consider. For example, there is fast storage and slow storage, there is the case when the data is larger than the buffer pool and when the data is smaller than the buffer pool (which is actually worse in some ways), there is flush list flushing and LRU flushing, and on and on. We have been benchmarking various types of scenarios for modified neighbor flushing, for example, to investigate how the oldest LSN is advanced in different cases. We still have nothing conclusive, but we have learned some things that gave us ideas about how we could make the flushing algorithm self-tuning in more cases. We will keep you posted. In XtraBackup news, Stewart Smith of the Drizzle project contributed a number of improvements to XtraBackup so that it can be a first-class backup tool for Drizzle. Thanks, Stewart! Continuing with the theme of community contributions, are also delighted that Lachlan Mulcahy is working on software to manage backups with XtraBackup. It is called XtraBackup Manager and is hosted on Google Code. We finished preparing XtraBackup to be used with Percona Server 5.5. We worked on Solaris builds for XtraBackup. We started from scratch and wrote documentation for innobackupex, removing mention of obsolete features and documenting command-line options that were not mentioned in the –help output previously. The docs will be available now with –help, via perldoc, as a man page, and on our documentation wiki. In an unrelated note, we have a new home for the documentation of our InnoDB data recovery tools. The tools moved from Google Code to Launchpad a couple of years ago, but the documentation never got updated, and became stale and inaccurate. It is now hosted on our documentation wiki along with all of our other software documentation. [Less]

So, back when we released our first beta in September, one of the many responses was this The comments about the reliability / durability of the log definitely struck me as testing we needed. It’s taken a while (we had this GA thing we were working ... [More] on…), but we finally have crash and recover testing of the innodb transaction log and the slave plugin. Here is what happens for the innodb-based log: Set up the test servers and start the randgen with the trx_log grammars.  I’ll point you to my superhuman colleague, Andrew Hutchings for a summary of what they do. Some time into the test (after several rounds of queries have run), `kill -9 $pid` is issued against the master server The master server is restarted The transaction_reader utility is called to generate SQL from the contents of the log A validator server is populated with the log’s SQL Drizzledump is called against the master and validation servers A diff is taken of the dump files – if all is well, they should match For the slave plugin, everything is basically the same except that we wait and make sure the slave and master are synched, then dumpfiles are compared. With this testing we can say that: * The innodb-based rpl log will provide an accurate representation of the database’s state even after a crash. * The slave plugin will provide an accurate representation of the master server even after a crash and restart. Many iterations of these tests have been run so far, using the standard randgen data and queries as well as making use of –seed=time.  When we do this, we randomize the data and queries generated so it can cover more ground than simply running the same 1000 transactions over and over.  As it is a well designed tool, any runs can easily be repeated as the same seed *always* produces the same data and queries…repeatability is one of a qa engineer’s favorite words : ) So without further ado, here is some output from the tests.  They are located in the innodb_trx_log and slave_plugin suites for the randgen, executable via dbqp: To run them: ./dbqp –mode=randgen –randgen-path=/path/to/randgen –suite=innodb_trx_log,slave_plugin multi_thread1_crash_recover NOTE that the output doesn’t normally include the ps output, just putting it in here to show off the magic ; ) <snip> # 2011-03-24T17:00:03 Query:  SELECT * FROM `C` AS X WHERE X . `col_bigint_key` BETWEEN 211 AND 2673999872 LIMIT 5 FOR UPDATE /*Generated by THREAD_ID 1*/  failed: 1213 Deadlock found when trying to get lock; try restarting transaction # 2011-03-24T17:00:03 Query:  ROLLBACK TO SAVEPOINT A /*Generated by THREAD_ID 1*/  failed: 1305 SAVEPOINT %s does not exist # 2011-03-24T17:00:03 Query:  ROLLBACK TO SAVEPOINT A /*Generated by THREAD_ID 1*/  failed: 1305 SAVEPOINT %s does not exist F S   UID   PID  PPID  C PRI  NI ADDR SZ WCHAN  TTY          TIME CMD 0 S  1000 13090   491  0  80   0 -  9317 -      pts/6    00:00:00 python 0 S  1000 13229     1 99  80   0 - 164210 -     pts/6    00:01:31 lt-drizzled 0 S  1000 13260     1  0  80   0 - 104483 -     pts/6    00:00:00 lt-drizzled 0 S  1000 13290 13090  0  80   0 -  1038 -      pts/6    00:00:00 sh 0 S  1000 13291 13290  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13298 13291  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13299 13291  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13300 13291  5  80   0 - 20378 -      pts/6    00:00:02 gentest.pl 1 S  1000 13302 13291  5  80   0 - 20355 -      pts/6    00:00:02 gentest.pl 1 S  1000 13304 13291  5  80   0 - 20324 -      pts/6    00:00:02 gentest.pl 1 S  1000 13306 13291  5  80   0 - 20406 -      pts/6    00:00:02 gentest.pl 0 R  1000 13343 13299  0  80   0 -  1651 -      pts/6    00:00:00 ps # 2011-03-24T17:00:03 0 # 2011-03-24T17:00:03 Sending kill -9 to server pid 13229 in order to force a recovery. F S   UID   PID  PPID  C PRI  NI ADDR SZ WCHAN  TTY          TIME CMD 0 S  1000 13090   491  0  80   0 -  9317 -      pts/6    00:00:00 python 0 Z  1000 13229     1 99  80   0 -     0 ?      pts/6    00:01:31 lt-drizzled <defunct> 0 S  1000 13260     1  0  80   0 - 104483 -     pts/6    00:00:00 lt-drizzled 0 S  1000 13290 13090  0  80   0 -  1038 -      pts/6    00:00:00 sh 0 S  1000 13291 13290  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13298 13291  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13299 13291  0  80   0 - 18502 -      pts/6    00:00:00 gentest.pl 1 S  1000 13300 13291  5  80   0 - 20378 -      pts/6    00:00:02 gentest.pl 1 S  1000 13302 13291  5  80   0 - 20355 -      pts/6    00:00:02 gentest.pl 1 S  1000 13304 13291  5  80   0 - 20324 -      pts/6    00:00:02 gentest.pl 1 S  1000 13306 13291  5  80   0 - 20406 -      pts/6    00:00:02 gentest.pl 0 R  1000 13344 13299  0  80   0 -  1651 -      pts/6    00:00:00 ps # 2011-03-24T17:00:03 0 # 2011-03-24T17:00:03 Killing child process with pid 13300... # 2011-03-24T17:00:03 Killing child process with pid 13306... # 2011-03-24T17:00:03 Killing child process with pid 13302... # 2011-03-24T17:00:03 Killing child process with pid 13304... # 2011-03-24T17:00:03 Kill GenTest::ErrorFilter(13298) # 2011-03-24T17:00:03 Attempting database recovery using the server ... # 2011-03-24T17:00:03 Executing drizzle/drizzled/drizzled --no-defaults --core-file --datadir="drizzle/tests/workdir/bot0/s0/var/master-data" --basedir="drizzle" --plugin-add=shutdown_function --mysql-protocol.port=9306 2>&1 . # 2011-03-24T17:00:03 13345 # 2011-03-24T17:00:08 transaction_log output file:  /tmp//translog_13291_.sql # 2011-03-24T17:00:08 drizzle/plugin/transaction_log/utilities/transaction_reader -uroot --use-innodb-replication-log -p 9306 --ignore-events > /tmp//translog_13291_.sql # 2011-03-24T17:00:09 Replicating from transaction_log output... # 2011-03-24T17:00:09 drizzle/client/drizzle --host=127.0.0.1 --port=9311 --user=root test <  /tmp//translog_13291_.sql # 2011-03-24T17:00:16 Validating replication via dumpfile compare... # 2011-03-24T17:00:16 /tmp//translog_rpl_dump_13291_9306.sql # 2011-03-24T17:00:16 drizzle/client/drizzledump --compact --skip-extended-insert --host=127.0.0.1 --port=9306 --user=root test >/tmp//translog_rpl_dump_13291_9306.sql # 2011-03-24T17:00:17 /tmp//translog_rpl_dump_13291_9311.sql # 2011-03-24T17:00:17 drizzle/client/drizzledump --compact --skip-extended-insert --host=127.0.0.1 --port=9311 --user=root test >/tmp//translog_rpl_dump_13291_9311.sql # 2011-03-24T17:00:17 Executing diff --unified /tmp//translog_rpl_dump_13291_9306.sql /tmp//translog_rpl_dump_13291_9311.sql # 2011-03-24T17:00:17 Cleaning up validation server... # 2011-03-24T17:00:17 Resetting validation server... # 2011-03-24T17:00:18 0 # 2011-03-24T17:00:18 Test completed successfully. The randgen code is in lp:randgen and the updated dbqp tests should be merged to trunk very soon.  Docs on running the randgen are here, dbqp are here. At this point, I’d also like to shine the spotlight on David Shrewsbury for all of his hard work on the replication system.  He’s shepherded this code from a file-based log with limited testing all the way to a functional (and highly flexible) replication solution.  It was Dave who helped me with the often frustrating task of rooting out early bugs so that subsequent code could have a good foundation.  Big props should also go to Jay Pipes (master of the fu!) for his design work on the initial transaction log code.  Good design, good coding, and lots of love = some pretty cool stuff.  Of course, plenty of other people have helped…I just wanted to personally thank Dave for not trying to kill me when I was bombarding him with painful bugs early on ; ) As always, we hope to hear from you guys via IRC, emails and launchpad.  Also Drizzle Developer Day !  Sign up for it ; )  Hope to see you guys at the 2011 MySQL User’s Conference [Less]

For backups, historically in the MySQL world you’ve had mysqldump (a SQL dump, means on restore you have to rebuild indexes), InnoDB Hot Backup (proprietary, but takes a copy of the InnoDB data files, so restore is much quicker), LVM snapshots ... [More] (various scripts exist, does have larger IO impact, requires LVM) and more recently xtrabackup. Xtrabackup essentially does the same thing as InnoDB hot backup except that it’s free and open source software. Many people have been using xtrabackup successfully for quite a while now. In Drizzle7, our default storage engine is InnoDB. There have been a few changes, but it is totally InnoDB. This leaves us with the question of backup solutions. We have drizzledump (the Drizzle equivalent to MySQL dump – although with fewer gotchas), you could always use LVM snapshots and the probability of Oracle releasing InnoDB Hot Backup for Drizzle is rather minimal. So enter xtrabackup as a possible solution… I had though of porting xtrabackup across for a while. Last weekend, while waiting for one of my iterations of catalog support to compile, I decided to give it a go. I wanted to see how far I could get with it also in that weekend. I was successful – there’s a tree up at lp:~stewart/drizzle/xtrabackup thatproduces an xtrabackup binary that’s built for Drizzle (it’s not quite ready for merging yet, there are some obivous bugs around command line option parsing… but a backup and restore did work). I wanted the following: build to be integrated with Drizzle, using the same innobase build that we use to build the server build with strict compiler warnings and -Werror (which we do forDrizzle) build with a C++ compiler (as we do with innobase in Drizzle) not re-add parts of mysys into the Drizzle build just for xtrabackup I’ve already submitted merge requests to upstream xtrabackup containing the compiler fixes and added compiler warnings (they’ve also by now been merged into xtrabackup). Already my work has improved the quality of xtrabackup for everyone. Some of the warnings were fixed slightly differently in xtrabackup than in my Drizzle tree, but I plan to merge. One issue was that the command line parsing library that xtrabackup uses – my_getopt which is part of mysys (the portability library inside MySQL) is long since gone from Drizzle. We currently use Boost::program_options. Thanks to the heroic efforts of Andrew Hutchings, xtrabackp in Drizzle is also using boost::program_options. This was a brilliant “hey, can you have a look at this conversion” followed by handing him a tree that did not even remotely compile, followed by a “I have to take the kids somewhere, here’s a tree – it may compile”. Amazingly enough, it pretty much did compile once I fixed the other issues. An unresolved issue is how to deal with this going forward – my guess is that upstream xtrabackup doesn’t want to require Boost. One solution could be just to factor out command line options into a sepfile that we can ignore for Drizzle and replace with our own. The other option could be to use a differnt command line option parsing library (perhaps from CCAN, as it’s then maintained by somebody else and doesn’t require heaps and heaps of other stuff). Another issue I had to tackle is the patch to innobase that’s required to build xtrabackup. I took a very minimal approach for the Drizzle patch. We are currently based on innobase 1.1.4 from MySQL 5.5 – so I mostly looked at the xtradb55 patch. I think it would be great if these were instead of one giant patch a series of patches to apply (a-la quilt) to a) make iteasier maintain and b) easier for myself to work out the exact reasoning of each bit (also, generating the patches with -p would help a fair bit too). So how did I do it? Step 0 was removing support for old innobase – we totally don’t need it for Drizzle. Step 1 was creating a srv_read_only option for Drizzle’s innobase. This was fairly easy. The one thing I did have to change was adding a checkin os_file_lock() so that we don’t attempt to write lock the ibdatafiles when in read only (otherwise backups can’t be taken while drizzledis running). I’m a little surprised that this wasn’t hit in 5.5 at all. Step 2 was implementing srv_fake_write. I’m pretty sure I’ve gotten this right in the Drizzle implementation, but the patch wasn’t as easy toread as I’d really like. I probably need to do a bit more of a code audit that this is actually correct (I may try and come up with anLD_PRELOAD library that will scream loudly if writes are made to files matching a pattern). Step 3 was implemnting srv_apply_log_only. Pretty sure I have this right, again, more testing will be required. Why? Because I’m that paranoid about getting things very, very right. Step 4 was to go through all the functions that xtrabackup needed to not be static. Instead of having prototypes for them inxtrabackup.cc, I instead added a xtrabackup_api.h header to Innobase and included it where needed (including in xtrabackup). I’d recommend this way going forward for xtrabackup too as it could be a lot less problematic to maintain (and makes xtrabackup source a bit easier to read) Step 5 was fixing up a few skeleton functions that were needed to make our innobase happy. It may not be a bad idea to split out the skeleton functions into a sep source file so it’s a bit easier to track (and some #ifdefs around those not needed for certain releases). I’m hoping to work with the upstream xtrabackup devs on the various points I’ve made above. Another thought of mine is to port xtrabackup into HailDB where we can use much more neat API functions to create good tests for xtrabackup. Thanks go out to all who’ve worked on xtrabackup. It honestly wasn’t too hardgetting it ported across to Drizzle – and with a bit of collaboration I think we can make it easy to keep up to date. What’s the future for Xtrabackup in Drizzle? It’ll likely end up being a binary named drizzlebackup-innobase or similar (this means that there is a clear difference between xtrabackup for MySQL and what we have in Drizzle – which is more accurately defined as based on xtrabackup). We’ll also probably want a nice wrapper or integration with a backup tool to deal with everything Drizzle related. We shall also introduce a lot of testing; backups are important. Xtrabackup is topical, check out the latest OurSQL podcast and the the Percona Xtrabackup website for more info! [Less]

I ‘ve been doing some reading on Stored Procedures and how they are being defined and executed. These are some of the points which I think should be covered in our Stored Procedure Interface and some of my suggestions. I’m open to suggestions and criticism. According to what we had discussed in the channel we [...]