Sunday, June 28, 2009

Remote replication setup with Gearman and MySQL Sandbox

replication via Gearman

A few months ago, Brian Aker invited me to have a look at Gearman, saying that I could find interesting combinations with MySQL Proxy. I did not forget, and I kept thinking about interesting ways of using it. The first idea that I managed to apply is not related to Proxy, but to a practical problem that I have been keeping in reserve for years, i.e. installing replication systems from remote, without effort.

After some fiddling around with the alternatives, I convinced myself that Gearman is the way to go. Before I proceed to show what I did, though, perhaps it's useful if I spend a few words about Gearman itself.

Gearman overview

It's a strange word. If you don't pay close attention, you read "German" and you may think "why should an administration tool be German? Doesn't Open Source apply across country boundaries?" And when you realize that there is an "a" after the "e", you are as baffled as before. What's this? Gearman is an anagram of manager. Because managers, so the explanation goes, don't do anything, but only direct workers towards their jobs. And this could be a good explanation of what Gearman is: it's a system to organize distributed jobs.
how gearman works
This picture explains the concept. You, as a client, need to have a job done. You ask the gearman server which somehow asks all the registered workers for their ability to perform such job. If any of the function names advertised by the workers matches the client request, the server assigns the job to the worker, together with the workload sent by the client, and then returns to the client the results that the worker has provided.
The system is flexible, because you can set as many servers and workers as needed, in several hosts. And it is fault tolerant, because the same worker can register its functions to several servers, and several workers can advertise the same functions to the same or a different server. Depending on the nature of the task, a client can ask several workers for a portion of the task, or it can just ask the first available one to perform the whole job. Since the workers can be located in several servers, you can distribute the load of a heavy job among many hosts. Your imagination is the limit.

Installing a replication system with Gearman

If you are familiar with MySQL Sandbox, you know that its main aim is to install servers quickly and efficiently, and to save valuable time by providing shortcut scripts for each sandbox. You can create a replication sandbox with one master and two slaves in the same server in a handful of seconds. However, they are all in the same server. This means that you can use this sandbox system to test the correctness of your application, but you can't test efficiency and scalability very well.
On one side, we have a flexible method to install single sandboxes. On the other side, we have a general purpose distributed system that can do jobs in remote servers. It is a logical choice to put these two frameworks together.


Before you start, you need to install MySQL Sandbox and Gearman in all the servers that will host a database node.You also need to have downloaded the appropriate tarball in each host. If the boxes are compatible, you can download once and copy from box to box (I am playing with the idea of doing the copy through Gearman), but that is not always a safe assumption. If one of the boxes has a different operating system or architecture (as in my case: two Macs and one Tux), you need a different tarball.
Another task to perform before installing is opening the necessary ports. You will need to open the Gearman port (4730) and the port(s) used by MySQL master and slaves.

Gluing pieces together

Installing the Gearman framework is not trivial. For many users, it is not as difficult as setting up a MySQL replicated system, but it may have some pitfalls.
Unless you are comfortable programming in C, in addition to the Gearman server you may want to install the API for your favorite language. I used Gearman::XS, a Perl module wrapped around the C API. It installs easily on some operating systems, but it requires some manual help in some others.
Anyway, after one hour, I had it installed in six servers with five operating systems and I am confident that I can install it almost anywhere (if that anywhere looks like Unix).
Sandbox replication with Gearman
Unlike the sample framework of the previous picture, I can't use a single Gearman server. Or, actually, I can, but it would require quite a lot of additional traffic and code complexity in the client, and therefore I used one server for each host where I had to install something.
Once that decision was taken, everything went smoothly. Each host has a server and one or two workers that registered the "make_sandbox" function with a remote installer script. The client knows which role is assigned to each host. Although it is not strictly necessary: the IPs could be shuffled around, and the final result would not change. Anyway, using the make_distributed_replication scripts, the client installs (or, rather, asks a worker to install) the master in the first host, and the slaves in the second and third host. At each step, there is a rudimentary error check, to make sure that the installation is being performed as expected. During the operation, the client produces a few scripts that make the administration easy.
At the end of the installation, you can connect to the master and the slaves using the familiar m, s1, and s2 scripts, and do mass operations with use_all and check_slaves.
To the user, the layout looks like the result of make_replication_sandbox in one machine, but you are dealing with a system spread among several hosts.

$ ./
installing master
installing slave 0
initializing slave 0
installing slave 1
initializing slave 1

$ ./check_slaves
slave 1:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
slave 2:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

$ ./m
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.83-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

master> exit

My first test was to load some heavy data, using the employees test database, and I am happy to report that the execution is much faster than a replication in the same host.

TO DO list

This is just a proof of concept. It seems to work well, but there is still much work to do. The error checking must be improved a lot both in the client and in the worker. Before registering the function, the worker must check that the external applications are available. When receiving a request, the worker can check if the desired tarball exists and if there is no conflict with already installed servers.
The client should be able to accept IPs, ports, and tarball versions from the command line. Some functions to cleanup and diagnose the remote servers should be implemented as well.
But I am very pleased about this first step.
Comments are welcome. This is my first usage of Gearman, and any way of improving on the basic concepts of this framework will be welcome.

Sunday, June 21, 2009

Failing by choice. Another bug-vs-feature debate ends

A long standing bug

Among the many outstanding bugs for MySQL, there is one that has sparked a fierce discussion, not only in the bug report itself, but also in blogs, forums, mailing lists.
Bug #19027: MySQL 5.0 starts even with Fatal InnoDB errors was neglected for long time, until finally it got fixed, and it is available in MySQL 5.1.36.
First off, what is it about?
In short, if an engine doesn't initialize correctly, the MySQL server starts anyway, without the offending engine. Depending on how you use the failing engine, this could be either a minor annoyance or a complete disaster.
Annoyance: ARCHIVE fails to initialize, and you create tables with the default engine (usually MyISAM), and after a while you realize that the disk is filling up faster than you expected. You will find out eventually.
Disaster: ARCHIVE fails to initialize, and you copy 300 GB of data into a table that you believed was ARCHIVE and instead is MyISAM, filling the disk and halting your server completely.
Annoyance: InnoDB fails to initialize on your new slave, and you create MyISAM tables instead. After a while you realize that this slave is serving queries way more slowly than the others.
Disaster: InnoDB fails to initialize on your master, which was already using InnoDB tables. None of you application work anymore. You can't read or write from your tables. You are completely stuck.

The same old story: bug or feature?

While I admit that some cases could be classified as annoyances, I am convinced, by personal experience coupled with gut feeling, that disaster is a more frequent outcome of this situation. When the engine fails and the server happily starts without your valuable data you are in trouble. True, you can monitor the error log and have a cron job sending you an alert if this happens, but by then it may be already too late.
So, why did it take this long to fix it? The bug was filed in April 2006.
The reason is that this bug was labeled between low priority and feature request. The behavior was documented and consistent with other similar cases, and many developers felt that by satisfying the request of the bug reporter we were adding a new feature. If this were a philosophical discussion, yes, it would be so. But this is about real life usage of databases, and it's my strong opinion that it is a bug, because it can harm the user, and it feels wrong.
I wrote about this matter before. A bug is not justified by being documented. A documented wrong behavior is a bug nonetheless.

How it works

Let's have a look at the practicalities. In MySQL 5.1.35, you create a InnoDB table and then restart the server with a mistake in your my.cnf. You inadvertently changed the size of the InnoDB file. For example, you set innodb_data_file_path=ibdata1:2000M while the original size was 200 MB.

$ $HOME/sandboxes/msb_5_1_35/restart --innodb_data_file_path=ibdata1:2000M
. sandbox server started
# the server starts
$ $HOME/sandboxes/msb_5_1_35/use -e "show engines" | grep -i innodb
Your query for the InnoDB engine is met by Unix nothingness. InnoDB failed to initialize, but the server started, leaving your data crippled and the DBAs blissfully unaware, unless they were checking for this specific occurrence.
What happens with 5.1.36? By default, it does exactly the same. If you think that this kind of failure could be a minor annoyance, you leave things as they are, and the failure of an engine, from CSV to InnoDB, won't bother you more than usually.
However, if you want to force the server to abort when your engine fails, now you can say so. You need to specify the desired behavior in the options file.
engine_name=OFFThe specified engine will not be included. The server will start without it.innodb=off
engine_name=ONThe specified engine will be included. If it fails, the server will start.innodb=on
engine_name=FORCEThe specified engine will be included. If it fails, the server will NOT start.innodb=force

Testing the above assumptions with the latest version shows a different result.
$ cd $HOME/sandboxes/msb_5_1_36
./restart --innodb=force --innodb_data_file_path=ibdata1:2000M
................... sandbox server not started yet
With the choice to fail now available, we can decide if an engine is so important that we can't live without, and instruct the server that it's better to fail than starting in crippled mode.
Another usability bug bites the dust.

Tuesday, June 16, 2009

Giving away one copy of MySQL Administration Bible in Madrid

Open Communities Forum

I will be in Madrid on June 18-19, to participate to the Sun Open Communities Forum. I will have two presentations on my own, and I will be a guest speaker during Victor Jimenez's session. There will be some ancillary activities, among which a lunch with the MySQL community and a MySQL workshop.

During one of these activities, I will give away one copy of the MySQL Administrator’s Bible. If you are a MySQL enthusiast and you are in Madrid, that's an extra reason to attend the forum. For the ones who still don't know how good this book is, you can have a look at this Review.
During the forum, there will be an announcement about the MySQL community in Spain.

Friday, June 12, 2009

A quick look at Google Fusion Tables

I was curious about Google Fusion Tables, and gave it a try.
I uploaded the employees table from the employees test database, 16 MB of data, about 300,000 rows. Since the maximum limit per table is 100 MB, I expected interesting results.
However, one of my first tests, with aggregation was quite disappointing.
A simple group by gender was executed in about 30 seconds.

InnoDB on my laptop did a much better job:

select gender , count(*) from employees group by gender;
| gender | count(*) |
| M | 179973 |
| F | 120051 |
2 rows in set (0.32 sec)

Here's the link to my test table, if you want to give it a try. You need to have a Google account to see it.

Thursday, June 11, 2009

MySQL Sandbox and laziness

MySQL Sandbox shortcut
Laziness strikes again.
MySQL Sandbox was created with the intent of avoiding repetitive work when creating and using several servers. Turns out that even the current framework, which many say that is really time saving and enhances productivity, was not enough. So my desire for laziness, which is, as everybody should know a chief virtue for a programmer has made me code a shortcut script, which can joggle sandboxes as never before.

Enter the sb script (available in version 3.0.03). Now I can shorten my typing experience with sandboxes quite a lot:

$ sb 5135
# same as calling
# $SANDBOX_HOME/msb_5_1_35/use

Nice, isn't it? It saves me quite a lot of keys. Not only that. If this sandbox is not active, the script will start it for me. That's yet a few keystrokes more that I am saving.
What if I want a replication sandbox? Easy:

$ sb r5135
# same as calling
# $SANDBOX_HOME/rsandbox_5_1_35/m

For a multiple sandbox instead of a replication one, use "m5135."
Now, for the really amazing stuff. Let's suppose that the sandbox doesn't exist yet.
No problem.

$ sb 5.1.35
# same as calling
# make_sandbox 5.1.35
# and
# $SANDBOX_HOME/msb_5_1_35/use

And if you don't have a pre-expanded directory for 5.1.35 under $SANDBOX_BINARY as I do? Also this is easily done:

$ sb mysql-5.1.35-YOUR_OS.tar.gz
# same as calling
# make_sandbox mysql-5.1.35-YOUR_OS.tar.gz
# and
# $SANDBOX_HOME/msb_5_1_35/use

Creating and using a replication sandbox is really easy as well:

$ sb r5.1.35
# same as calling
# make_replication_sandbox 5.1.35
# and
# $SANDBOX_HOME/rsandbox_5_1_35/m

The manual explains the above cases and some more (Yes: there is actually more).
Go on! get lazy!

Tuesday, June 09, 2009

Speaking at FrOSCon 2009 and getting ready to OpenSQLCamp-Europe

FrOSCon 2009
For the fourth time in a row, I will be speaking at FrOSCon, one of the most charming open source events in Europe.
Hosted in the bright environment of the Department of Computer Science of the University of Applied Sciences Bonn-Rhein-Sieg, this event will get you hooked from the beginning. The organization is done by volunteers, who have always done an amazing job, with even better results than more expensive and famous conferences.
This year, there will be some more action than ever before. In addition to the main event, the organizers have given away a few developers rooms, to let some projects build their own event within the main one. There will be a Java subconference, and, closer to my interests, the European edition of the OpenSQLCamp 2009, which applies to all open database related projects, such as MySQL, PostgreSQL, JavaDB, and many others that are listed in the announcements page. The principal organizer is Lenz Grimmer, who is looking for volunteers to share the burden.
The organizers have also launched a creative contest, which most geeks will find irresistible. Check it out!
FrOSCon started as one of the many LAMP events, and in its fourth year it has emerged as an example for others. I warmly recommend it to all people interested in open source.

Joining the PHP BBQ in Hamburg and Kiel

Australian barbecue and countryside

There must be something that gets coders and barbecue together. In Australia, I was invited to a hackers barbecue, while at FrOSCon, the Saturday evening meal is a grill fest.
It seems just right that the PHP coders in Germany are getting together with a BBQ Tour, starting on Monday, June 15, and touching seven German towns in one week.

I will join the happy campers on Saturday, June 20, in Hamburg, and finish off the tour in Kiel the next day.
The event is open. However, for logistic reasons, a registration is required in some of them. Check the schedule for more details.
Being informal events, we may or may not have presentations and demos, but being inter-geek meetings, there will be some fun and mutual learning for sure. I look forward for it. Most of my "aha" technical moments came during serendipitous social gatherings. Beer and food are great science conductors!
Follow FrOSCon on Twitter and OpenSQLCamp on Twitter for more updates.

Saturday, June 06, 2009

Unreliable androids

There is an article with a cute title that is gaining popularity:
Do Androids Count Electric Sheep with DB2 or MySQL?
Allegedly, DB2 is demonstrated as incredibly faster than MySQL, with a benchmark that repeats the same COUNT query 100 times.

This is a naive (at best) benchmark that doesn’t tell me nothing about the database potential. But anyway, if you enable the query cache in MySQL, the repetition of 100 queries is at least three times faster than DB2.

set global query_cache_size=1024*1024;
and repeat that test.

Moreover: the table structure doesn’t correspond to the data from the freebase project.

CREATE TABLE `people` (
`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
`name` varchar(255) NOT NULL,
`fbid` varchar(255) NOT NULL,
`gender` varchar(255),
`profession` varchar(255)

Too bad there is no integer ID in that table, and there are 23 fields instead of 5.
These androids don’t strike me as extremely reliable.

A cross-system benchmark is reliable only if you either (a) know both systems very well or (b) you invite one expert of the systems involved to make sure you are using both of them in a fair way.
Oh, and if you can make a table that has smarter fields than VARCHAR(255), your benchmark may look more credible.

Wednesday, June 03, 2009

MySQL Sandbox has a dedicated home

MySQL Sandbox

MySQL Sandbox is a mature project (recently released version 3.0), and as such I thought that it deserved a dedicated home.
Thus, I went shopping, and I bought a few domains, all pointing to the same place:

Web design is not (to say it mildly) my strongest ability, and thus the result is nothing that would trouble Facebook or eBay's sleep. But I will work at it in my spare time to improve its features and possibly its looks.

This is my 300th post in this blog!

Open Communities in Madrid - June 18-19

I am going to Madrid, Spain, to participate in the Open Communities Forum on June 18-19.
As usual with Sun open events, the agenda includes several actors in the open source arena, such as MySQL, Java, Open Solaris, with topics ranging from web development to mobile integration.
It looks promising, and I look forward to this event, which is my first one in Spain.
On the first day, I will do an introductory talk about MySQL, and a more technical workshop for advanced users. The second day will be mainly dedicated to meeting users in the area, but I will also do a guest appearance in a talk about database testing.

Monday, June 01, 2009

MySQL University - Boosting performance with partitions

MySQL University

Mark your calendars: A MySQL University session about Boosting performance with MySQL 5.1 will take place on Thursday, June 4th at 13:00 UTC ( 8am CDT (Central) / 9am EDT (Eastern) / 14:00 BST / 15:00 CET / 17:00 MDT (Moscow) / 18:30 IST (India))
The session will be conducted through DimDim, a system that allows you to follow the audio and visuals of a presentation from your browser, without any additional settings.

Attendance is free. Please follow the instructions given in the MySQL University main page.