MySQL Data Dumper 0.2.0 released!

A couple of years ago Domas Mituzas created a tool which could basically be thought of as basically a lightweight multi-threaded mysqldump.  By this I mean it can retrieve data from multiple tables simultaneously and can even break a table down into parts for simultaneous retrieval.  Sometime around 2010 I started hacking on mydumper too but stopped whilst working at Rackspace (Drizzle was way more than full-time for me).

Back when Domas first blogged about it he was managing to dump his sample data over 10x faster than mysqldump!

Since the 0.1 series Domas has fixed a lot of problems and since starting at SkySQL I have finished a lot of work that I started over a year ago.

Today sees the first release of the 0.2 series of mydumper, called 0.2.0.  It has many changes over the last 0.1 release:

• Better error handling


• Many bug fixes


• Documentation


• Binary log dumping


• Compression protocol support


• Schema dumps

My personal favourite fetaure I added here is the binary log dumping.  Mydumper can connect to a MySQL server and retrieve all the binary logs in parallel whilst also retrieving the table data.

Many more features are already in development including additional features on the binary log dumping but for those who want to try it now you can obtain the source from Launchpad here.

libeatmydata - Feed me, Seymour!

Whilst supporting customers at SkySQL I often have to load gigabytes of SQL data into MySQL servers to run tests.  This process can be slow especially for InnoDB because in a standard dump file every insert is a transaction and every transaction has to be synchronised to disk for crash safety.  The thing is, most of the time I don't care if the machine I'm using crashes whilst I'm loading this data into the server.

There are of course many ways around this, such as editing the SQL files and wrapping transactions around batches of inserts and editing the configuration files to disable all the syncing involved.  But I don't want one configuration to load in data and then another to play with the data, so this is where libeatmydata comes in.

libeatmydata is a preloaded library that disables disk syncing functionality from doing just that.  The OS will decide when to sync the data to disk.  This is great for loading in an SQL dump file, taking single insert dumps on default configuration down from hours to minutes.  But you wouldn't want to do it during the production running of your server because power failure would certainly lose you some data.

So, how do you use libeatmydata with MySQL?  Simple, this is the command to start it:

LD_PRELOAD=/usr/lib/libeatmydata.so mysqld

Then you can load in your dump file, shutdown mysqld safely and start it up again without libeatmydata.

A great application I could see for this is scripting the startup of slaves, feeding a dump file into the server with libeatmydata and then restarting without this once the slave is ready.

UPDATE

Kristian Nielsen asked in the comments on SkySQL's blog how much faster it is, so I have run a quick benchmark to find out. In this test I am using a 218MB test file of single row inserts I had generated for an old support issue. I am also using a clean MySQL 5.1.51 installation (cleaned on each run) on my i7 based laptop:

Vanilla MySQL 5.1.51

real    166m19.504s
user    0m23.891s
sys     0m6.084s

MySQL 5.1.51 with --sync-binlog=0 --innodb_flush_log_at_trx_commit=0

real    5m33.578s
user    0m11.096s
sys     0m3.215s

MySQL 5.1.51 with libeatmydata

real    3m14.123s
user    0m10.932s
sys     0m3.108s

SkySQL - The Return of the Jedi

The last few weeks have been particularly quiet from me on the blogging front.  Behind the scenes things have been quite the opposite so here is a summary of things past, present and future.

Rackspace and Drizzle

If you have read my last 'Last Week in Drizzle' post you will know that Rackspace are no longer supporting Drizzle.  They have done a fantastic job so far and have decided to pass the baton to other companies.  As for the staff, they wished to redeploy us to other teams which is something I personally was not keen on.  I would rather remain within the MySQL/Drizzle sphere which I would have no longer been able to do effectively inside Rackspace any more.

Drizzle itself will go on to do great things without Rackspace, there are a number of companies that announced support for Drizzle during the O'Reilly MySQL Conference and Expo and Google Summer of Code is still going ahead as planned.

MySQL Conference

For me personally it was the busiest conference I have ever attended, this is mostly down to the three talks I had to give on top of booth duty, meetings and Drizzle Developer Day.  I had some fantastic feedback from people whilst there on many subjects such as Drizzle and the MySQL 5.1 Plugins Development book.  It was great to meet up with old friends and make some new ones and I hope that the conference will continue for many years to come.

SkySQL

The day after returning from the conference I started my new role as Senior Sustaining Engineer at SkySQL (very jetlagged and in hindsight I should have given myself a day or two to recover!).  In this role I not only go back into supporting customers but also developing tools around the MySQL/Drizzle sphere.  I feel very honoured to be working with the team (many of whom I am working with for a second time), they have really done a great job of capturing the traditional MySQL spirit.

One of the first things I have been working on is a new version of mydumper, once this is ready I will create a separate blog post about it.  I think it is a fantastic tool and hope that it will be able to help many users in the future.

Google Summer of Code

SkySQL have encouraged me to continue my work on Drizzle which I have also been doing.  As part of this I am a mentor for Google Summer of Code, a student called Olaf van der Spek will be working on improving the libdrizzle client API under my guidance.  Something I am very much looking forward to.

The Return of the Jedi

So, I am back in a support type role whilst also developing useful tools and patches to enhance the usability of MySQL, I will also be blogging more and getting involved in the community/ecosystem in other ways.  This is very similar to what I was doing at Sun/Oracle but for a company designed from the ground up to be much better for the staff and customers.  I am looking forward the the bright future of SkySQL.

Last Week in Drizzle

Welcome to this week's edition of Last Week in Drizzle.  Unfortunately I could not write this at the Drizzle Developer Day because it is was much busier than I expected.  So this one had to wait until I landed in the UK :)

O'Reilly MySQL Conference and Expo

Last week was the MySQL Conference which at this point should probably be called the MySQL & friends or the Open Database Conference.  We had many talks, great exposure and some fantastic questions and feedback of ideas we had never thought of during the week.  I urge anyone who wasn't there to watch Brian Aker's keynote on the State of Drizzle.

Drizzle Developer Day

On the Friday after the conference we had Drizzle Developer Day which contained people from every level, new users to some of the biggest names in MySQL development.  A great many topics were discussed such as the catalogs work, replication and storage engines.  There were also discussions governance and I hope there will be announcements on this in the near future.

Development Goes On!

All last week the Drizzle Developers at Rackspace were giving talks and meeting people at the conference so we didn't get a lot of time for actually writing code, but there have been many branches and merge requests thanks to the fantastic community around Drizzle.  I still haven't caught up with all the work merged in within the last week!

Drizzle Support

A few companies have come out in the last week offering support services for Drizzle such as Blue Gecko and Percona which is fantastic to see.  This is on top of companies such as SkySQL already providing support for Drizzle.  There could also be announcements from more companies in the near future, so watch this space!

Rackspace and Drizzle

If you have watched the keynote from Brian Aker referenced above you will notice near the end he talks briefly about the developers getting hired by other companies.  Rackspace have done a fantastic job in supporting and funding us right up through the GA but unfortunately can no longer go on providing resources for us.  What does that mean for us?  Most of the developers who work for Rackspace are moving to other companies, many of whom will continue to work on Drizzle.  What does the mean for Drizzle?  Not a whole lot really, development will continue as before.  The great thing about Drizzle is the Rackspace developers were actually the minority, there are many other companies as well as community developers involved.  The features that were originally planned will continue to be developed, Google Summer of Code will also go on as before.

My Next Adventure

My last day at Rackspace was last Friday, today I start my new adventure as Senior Sustaining Engineer at SkySQL.  This means I will still get to work on Drizzle as well as providing fantastic support and development resources for MySQL and MariaDB.

Final Thoughts

I'd personally like to thank everyone for their fantastic feedback at the conference and developer day last week.  It is great to hear that people think we are on the right path with the technology and many of the ideas and discussions that came up last week will help shape the future of Drizzle.

As always if you have any feedback or topics you would like me to cover, please let me know.