MaxScale for the rest of us - Part 1

MaxScale for the rest of us - Part 1

SkySQL released MaxScale as Open Source on github a few weeks ago. For some instructions on how to install and configure it, Ivan Zoratti has written a blog about that, and Mark Riddoch has written about why we need MaxScale here, but what I will try in a series of blogs is to describe what MaxScale is, in terms of what you can do with it, and to put it into context, so that you have an idea why you would want to install it in the first place. I will also show how to configure and manage MaxScale. This first post is a quick introduction to MaxScale.

So what is MaxScale then? Mark, and many others, call it a Proxy, and that is partly true, if you download MaxScale as it stands and start using it, it sure is a proxy, but that's underestimating the abilities of MaxScale a bit. The power of MaxScale largely lies in that is is extensible and in how effective the core of MaxScale is.

 There are 5 different types of plugins, with different properties. Some of these are always required, some are optional, but they all have some common properties:

• The MaxScale core is written in C and the Plugin API is a C API. This doesn't mean a plugin is limited to C, but the interface to MaxScale is.
• MaxScale is multi-threaded, so plugins need to be threadsafe. This is a good thing, not a restriction!
• They depend on eachother, to a large extent.
• The MaxScale core handles a lot of the basics and the routing between modules.

This means that there are a lot of things that MaxScale can do, all you need is to develop a module or two, right? Well, actually it's easier than that as there are some Modules that are already included with MaxScale.

Current MaxScale modules

The currently available modules are of two types, the ones used for development, debugging and monitoring of MaxScale and the ones that can be used for real database work.

Protocol modules

Protocol modules are key to MaxScale, these are the nodules that enable MaxScale to talk to just about anything. Currently the most important modes are the ones that implement the MySQL client and server protocol (yes, this is two separate modules). For testing and managing MaxScale there are also telnet and http protocol modules.

Authentication modules

These modules do authentication, but if you look at the current sourcecode, you realize there are no authentication modules. But authentication still takes place, as part of the MySQL protocol modules.

Monitor modules

These modules monitor the servers and keep MaxScale informed about the status of these, like which one is a master, which is running, which is a slave etc.. This information is then used by the routing modules to know to to route client traffic. There are 2 monitor modules available right now, one that monitors the master/slave status of servers and one that monitors the status of the nodes in a MariaDB Galera Cluster setup.

Filter modules

The purpose of this kind of plugin should be obvious. This module API is currently not yet implemented.

Router modules

This is the most interesting type of module, and this is where the logic of routing database traffic takes place. There are currently 3 router modules included with MaxScale, one that does read/write splitting on a cluster master/slave servers, using the monitor to keep track of which is master and slave, one that does simple load balancing and one that handles a MariaDB Galera Cluster.

MaxScale uses

As can be seen from above, MaxScale can be used for many thing potentially. What determines what it can be used for depends largely on what modules are available, potentially it can be used for protocol conversion, query filtering, high availability and load balancing, for example. As it stands with the currently available modules though, is the MySQL protocol and for query routing we can do a couple of different variations on load balancing and HA using Galera.

To be continued

The next post in this series of blogs on MaxScale will show an actual use-case and how we can solve a real-world problem using MaxScale.

/Karlsson

Some myths on Open Source, the way I see it

The Open Source movement is full of myths, there are different myths from inside the movement (i.e. those who live and breath Open Source or at least thinks it's a good thing) and outside (i.e. those who do not think Open Source really is a good idea to those who thinks Open Source is the work of the devil in a bad way (as I think some people think that something that is the work of the devil has to be a good thing. And they have a point, but let's not get into that right now).

I want to cover some of those myths here and how I look at them here. Also, I am aware that I might be stomping on one or another sensitive Open Source toe here.

Open Source software creates better code

No, I don't think this is so. But I do think that using an Open Source model properly can potentially help us create better code. But this is not same as saying that OSS always means better code. I am old enough to have been a developer when C was a "hot" programming language, and one of the virtues of C was that it made it possible to create portable code (yes, this is true, this was a supposedly big advantage). But I am also old enough to have been developing when nearly all code you wrote was for a specific machine.

But I still believe that Open Source has the potential to create better code, but then the project needs to be managed properly. I am sorry that I don't fully understand the wonders of the Internet, and I still don't fully see why taking some half-good piece of code and publishing it will suddenly make it better. Good code is good code, be it closed or open, and bad code is just plain bad, both in closed and open shape. Admittedly, there are advantages to code being open, for example that you can see that it is bad, but that assumes that you or someone else knows the code and the domain and the application well enough to be able tell that this is the case: If some code is a stinker, that's what it is, closed or open source!

Peer reviews created by opening up your code creates better code

In some cases, this is so, in others it doesn't matter. And again for this to work, the field where the code we are looking at is wide enough so more that a few people know it. As for something like MySQL, Linux or JBoss, this is simple. For something like an Open Source ERP system, things start to get more tricky. There are of course OSS ERP systems, but I have to assume that the community involvement is limited.

MySQL is a great Open Source Project

Again I am sorry, but I don't think so. As far as code, adoption and reaching out to create an SQL-based RDBMS that anyone can afford, MySQL / MariaDB has been immensely successful. But as an Open Source project, something being developed together with the community where everyone work on their end with their skills to create a great combined piece of work, MySQL has failed. This is sad, but on the other hand I'm not so sure that it would have as much influence and as wide adoption if the project would have been a "clean" Open Source project.

Let's try to image something: Someone finds a flaw in the GPL license and suddenly it is not allowed to share this code as we used to, instead it would be acquired by a corporation that owns and drives the project as Closed Source. What would happen to Linux? I think that would be devastating if say, Red Hat or IBM or someone was driving this by themselves. What about MySQL? I don't think much would change. And hence I think that MySQL just isn't that reliant on the community and is not really a community driven project. I think Linux to a much larger extent is community driven, and I also firmly believe that this is not related to the size of the project.

And to be clear, what we are talking about here is the OSS attributes of MySQL. As far as an RDBMS goes, MySQL is a fine piece of software.

Anyone can read your Open Source code and that makes you a better programmer

This I think is largely true, and this is one of the things I like best with Open Source. But even this has it's drawbacks. Compare these two developers for some project:

• A great developer but with limited domain knowledge.
• Someone who know the field we are developing code for really well, better than most, but is not a top notch developer, but still OK.

That someone reads the code that the latter wrote and determines it's n good, and it might even be inferior, doesn't mean that the code in terms of how it works and get's the job done right, maybe even to a greater extent then the former.

Open Source means Software Freedom

Oh yeah, load up with tomatoes and get ready to throw them now. That the code can be seen by anyone sure means more freedom, right? Well, in some ways it does, but I have two issues with this. First, this software freedom assumes that we are all developers. And not only that, it assumes that we are not only application developers, but also, for example, Database Software Developers with knowledge in C, C++ and it helps to know yacc / bison. Well, I don't have a problem with that, I write C on a daily basis and have done my share of C++ as well as yacc, but then I'm not your average user of MySQL. I am not saying that there is something wrong with being a software developer not being proficient in C, just that C is not very useful for an application developer today, although C probably still one of the big technologies for infrastructure (databases, servers of different kinds, operating system etc). For someone not knowing C, C++ etc, MySQL is just a convoluted piece of software as Oracle. And yes, you can learn C or hire someone who does, but why should you do that? The deal is that we need to separate between the Open Source developers, i.e. those who developer OSS, and Open Source Users, who actually don't get that much more "software freedom", but on the other hand, they probably do not ask for it either.

The other issue with "Software Freedom" that I have is that even though we who are somewhat "in" the OSS Developer Community have a few issues with it. When an OSS developer somewhere out there want's to know how a piece of OSS software works, so he can modify or extend it, he can just read the code, that's all there is to it? Read the Source, Luke! Well, honestly, this isn't by far as good as it sounds, to begin with, the source code doesn't tell you much about what a piece of OSS software does, but it tells you how it does it, but not even how it's supposed to be done. In effect, without proper documentation, OSS provides too much information in some cases. In addition, sometimes I get the feeling that as the source is available for anyone to read, some people think that documentation isn't necessary. But documenting how something works often tells you little about how it is supposed to be used, using the sourcecode to figure out how something is supposed to be used is akin to learning how to drive by dissecting an engine.

My conclusion

The reason I wrote this was not that I don't like Open Source as a development model, that I don't appreciate the hard work of all the OSS developers or that I prefer closed source software. But at the same time, even though OSS in my mind, for many, even most, things, is the best way of developing, distributing, licensing and using software, it's far from perfect. But it is sure is the best model I know, although it is not without it's flaws. And there are areas where OSS really isn't a good model, and I think we have to appreciate that.

/Karlsson

MariaDB Database Disk snapshot backups on Windows

Using disk snapshots to perform MariaDB backups has become more and more common, but this seems to have been limited to Linux and Cloud environments. There seems that there has been a notion that Snapshots cannot be done on native Windows, the way we can do snapshots using LVM on Linux for example. This is actually False, if you use the NTFS filesystem snapshoting is built in and has been so for a long time, the base for it is the Volume Snapshot Service (VSS) which was introduced as far back as Windows XP.

Using Snapshots to backup on Linux or Windows works well, assuming that the storage engine you use can recover on startup, the backups created this was are physically consistent but not logically, but InnoDB will recover this. Also note that any warnings about using snapshots with MyISAM also applies to the mysql database, i.e. the database metadata. Usually though, this is not an issue, but if you want to protect yourself even more then use a FLUSH TABLES WITH READ LOCK and backup the MyISAM tables separately somehow. But this is no different between Windows and Linux in this case, this is an attribute of any kind of disk snapshot backups.

VSS is a services that run on Windows and to utilize it some programming is needed, but luckily that has already been done for us, there is a simple utility called VSHADOW.EXE available from Microsoft that can be used to access the VSS service. Some version of Windows, such as Windows Server 2008, Windows Server 2012 and also with a version of Windows that is very little used, probably there are more users of Windows 3.10, than of Windows 8, come with DISKSHADOW.EXE, a utility that does the same thing, basically, but has a different interface. In Windows 7, that I tested on, there is VSSADMIN.EXE instead, but as VSHADOW.EXE can be added, I downloaded that one as it is more functional (It is part of the Windows SDK). All this mess (c) Microsoft.

So, how does this work then? As I said, this is different than on Linux (what? Windows being different than Linux? No kidding?). Using the API you can create two types of snapshots, persistent and non-persistent. The difference is that a persistent snapshot is one that remains until specifically removed, whereas a non-persistent one goes away when the program that created it exists. As we are about to take a backup here, I guess you think we want a permanent one, but that is not so, as VSHADOW can run a command file for us, before it exists.

I will use an example here, for that I have placed my MariaDB data on the E-drive in a directory called \data, which means my my.ini has this line in it:
[mysqld]
datadir=E:/Data

Next question then is where the snapshot is placed? Well, the snapshot ends up as a Windows device with a name along the lines of \\?\Volume{5b3610ce-d2cb-11e2-a649-2cd05a8ad0c9}\ which is just as easy to remember and type as the novel War and Peace (Yes, Tolstoy was a Windows guy, I guess you didn't know that!). Yes, again, thank you Microsoft, Windows is SOO user friendly! Anyway, yet again VSHADOW helps us out, as it can create a command file for us that will contain the names of the devices and some other things. It can look something like this and I have called it vss-setvar.cmd:
@echo.
@echo [This script is generated by VSHADOW.EXE for the shadow set {c8fcb322-ce02-409a-b609-7fc04311fad1}]
@echo.

SET SHADOW_SET_ID={c8fcb322-ce02-409a-b609-7fc04311fad1}
SET SHADOW_ID_1={11243eaf-0234-4784-92e1-9bc24668b5b4}
SET SHADOW_DEVICE_1=\\?\GLOBALROOT\Device\HarddiskVolumeShadowCopy8

OK then, so this is generated for us, how do we use that? Well, what we do is create a command script that is called by VSHADOW as I said before, and this calls the generated scripts to get all the details. An example script, that I call e-backup.cmd, can look like this:
call vss-setvar.cmd
@echo off
dosdev T: %SHADOW_DEVICE_1%
robocopy T:\Data C:\Backup /MIR
dosdev /r /d T:

There are still a few missing bits here though. The call command is calling the generated file as above (you can call this anything you want, see the example command line below) and what about the command DOSDEV? What is that, that is a simple little utility you can find on the net (Google is your friend) that allows you to create a DOS device from a device descriptor, and it can also remove them. There are other ways of dealing with this, but I choose this path my my example here. Robocopy you probably know already and the /MIR option is used to tell robocopy to mirror a full directory tree.

So what the command script above will do is create a DOS device called T: for my snapshot, copy that to a directory called C:\Backup and then remove the T: device.

Now we have a command script (not two, the first one I showed was an example of a generated one) and an idea what it does, to do a shadow copy now, we just need to call vshadow, and it will do the work for us. We pass three parameters:

• The name of a script with the device names and things to generate.
• The name of a script to run
• The name of the drive to create a shadow device of.

Using the example above to create a backup of the E-drive, where I have my MariaDB datafiles in the \data directory, this is the command I use:
vshadow -script=vss-setvar.cmd -exec=e-backup.cmd E:
So that's about it, now you know how to create an online snapshot backup of a MariaDB database on Windows.

/Karlsson

Workaround for CURRENT_TIMESTAMP as default for a DATETIME value

Currently you cannot specify the current time as a default value for a DATETIME column. It works for TIMESTAMP but not for DATETIME,and even with TIMESTAMP this only work for the one timestamp column in the table, if you have more than 1 TIMESTAMP column you have to specify a default for all but the first.

This is fixed in MySQL 5.6 and MariaDB 10 which allows you to use a construct like this:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());

So what can you do if you are on MariaDB? Well, you can use a trigger to achieve a similar result:
mysql> CREATE TABLE td1(
  c1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  c2 DATETIME DEFAULT NULL());
delimiter //
mysql> CREATE TRIGGER td1_ins BEFORE INSERT ON td1 FOR EACH ROW
BEGIN
  IF new.c2 IS NULL THEN
    SET new.c2 = now();
  END IF;
END// 
delimiter ;

And then this works:
mysql> INSERT INTO td1 VALUES(NULL, '1960-04-10 16:45');
mysql> INSERT INTO td1 VALUES(NULL, NULL);
mysql> INSERT INTO td1(c1) VALUES(NULL);
mysql> SELECT * FROM td1;
+----+---------------------+
| c1 | c2                  |
+----+---------------------+
|  1 | 1960-01-04 16:45:00 |
|  2 | 2013-11-23 15:57:51 |
|  3 | 2013-11-23 16:00:55 |
+----+---------------------+
4 rows in set (0.00 sec)


Is this perfect? Nope, but it works while we wait for MariaDB 10.

/Karlsson

Bet the company! Just do it!

The term "bet the company" means that a company, large or small, puts everything it has in one big move. Betting the company when the company is small is one thing, this happens every day (you can say that MySQL did this, but they did it when they were small and and there wasn't really much to bet with). It is true that Henry Ford bet the company on the T-Ford, but at that time Ford really wasn't that big when Henry Ford started building the Model T using the Assembly Line which meant that the cars could be made much less expensive.

But how often do you see a major company betting itself on one single product line? Not that often I say, but that might be because I do not know industries outside IT and Cars that well, and also you tend to forget the failures and the associated companies.

One reasonably well-known failure was the Schlitz Brewing Company that went downhill in the early 1980's after betting on new technologies in making beer to produce a less expensive beer to produce and increase margins. In the process though, the beer, even for American beers at that time, went of to taste even less, if that is possible and also the beer looked awful. So Schlitz lost it's customers and went downhill and was acquired a few times. Their most innovative Brewery, as far as technology goes, still exists, is owned by Anheuser-Busch and produces, well, you can guess it by now, Bud Light.

Anyway, enough of interesting things such as cars and beer, let's get back to boring computers again. Some of the biggest bet-the-company project we have seen are in the IT industry, one is when Microsoft decided to go with Windows, skipping out of the development of OS/2. If you look back at it now, and consider that at that time, IBM was still the dominant player and also that IBM had brought Microsoft to the position it was in (by giving them the right to print money, sort of, in the shape of allowing Microsoft the rights to DOS), this was pretty risky bet by Microsoft, but it worked out. There were more IT bet-the-company projects in the 1980's, from Commodore and Atari and so on, but they were largely failed. Also, we have Apple giving us the Mac, but in this case, Apple as a corporate entity never was betting on the Macintosh until it had proven itself in the marketplace.

But I think that one of the most substantial bet-the-company projects ever, in any industry, was when IBM bet on the 360 architecture in the beginning of the 1960's. At the time IBM had a huge array of conflicting and incompatible lines of computers, and this was the case with the computer industry in general at the time, it was largely a custom or small scale design and production industry, but IBM was such a large company and the problems of this was getting obvious: When upgrading from one of the smaller series of IBM computers to a larger one, the effort in doing that transition was so big so you might as well go for a competing product, the effort was actually similar, you had to redo everything. So IBM was getting some serious competition from the "BUNCH" (Burroughs, Univac, NCR, CDC and Honeywell) as they were called later when General Electric and RCA left the field of computing in the late 1960's.

Yes, the IBM 360 was truly revolutionary. It was a whole new range of computers, including software, hardware and peripherals. It had a whole bunch of features we now take for granted, like the 8-bit byte addressing (yes, this was not an obvious feature and the term "byte" itself, although not invented for the 360, was first used by IBM and was made 8-bits and nothing else with the 360-series). The risk that IBM took with the 360 series was enormous, and this is another factor of what bet-the-companies all about: taking a really big risk. Looking at what Xerox did, or rather didn't do, with PARC, explains a big chunk of why PARC was a failure, in terms of turning Xerox around, and why the 360 was not. The 360 really did turn IBM around, and many at IBM was not happy with the way that the 360-series was developing and what it was doing to existing IBM product lines, but the IBM management at that time stood by their decisions. As for Xerox in the 1970, they were much less supportive of PARC (and this is a gross understatement).

Anyway, the IBM management that dared taking the 360-project is to be applauded, and I think we need more of this kind of daring management, stuff is just happening too slow and it's time for a quantum leap. And although there is a risk to betting-the-company, the gains can be enormous and the effects of NOT taking that risk is also betting-the-company (look at those office equipment manufacturers that didn't dare to go into computers).

I think this is only of the issues with many Open Source projects, they are run and supported by people using the project the way it works today, and hence has limited interest in taking a huge risk at some new, unknown technology that currently has limited value for them. As Henry Ford said: "If I had asked people what they wanted, they would have said they wanted a faster horse".

/Karlsson

MariaDB Dynamic Columns client API

I have blogged on using MariaDB Dynamic Columns already, and I hope this was useful and introduction. I have a few more things on this subject though, but one so far little known and used feature is the Client API for Dynamic Columns, see the MariaDB Knowledge Base for details. What this is all about is that Dynamic Columns were originally envisioned as a means of managing the "dynamic columns" used by Cassandra when using the MariaDB Cassandra Storage Engine. But as we realize, this is the server side of things (the Storage Engine) but there is a corresponding client library also, that is part of the MariaDB Client API.

As you have seen if you have read my previous blog on this subject, or whatever is written about MariaDB Dynamic Columns elsewhere, which is not much, MariaDB actually has no external representation of the dynamic column: Either you get the binary representation or you parse the binary representation using one of the supplied functions (like COLUMN_GET to get the value of an attribute in a dynamic column) or you get the binary. The only exception is the function COLUMN_JSON that retrieves the whole dynamic column and converts it to JSON, supporting nested objects also. Regrettably, there is no built-in means of adding a dynamic column in JSON format (Ulf Wendel has provided some JSON User Designed Functions though, but they don't line up with Dynamic Columns on the other hand).

Now, if we assume we are not using the COLUMN_GET function or something like that, can I programmatically parse a dynamic_column on the client? Well, I could sure use the server functions again, calling COLUMN_LIST and functions like that from a SELECT statement or in a Storage Procedure (I'll show this later), but what is real handy (if you are a C-developer like myself) is the DynamicColumns API functions in the MariaDB Client Library.

To begin with, these functions are not as easy to use as it might seem in the Knowledge Base article mentioned above. There are some bugs in what files are required that currently makes it kind of messy to build programs that use the Dynamic Columns API (which brings up another question: How many times can you write Dynamic Columns in one single blogpost?) This will be fixed eventually (it is known, reported bugs we talk about here), but until it is, let me show you some examples, so you get the point of this API.

To begin with, the API assumes that data passed to it is in the form of a DYNAMIC_COLUMN struct, which is a string with a specified length, i.e. it is NOT a null-terminated string but instead it is really, truly binary. This might be silly, but it is just how things are. So the DYNAMIC_COLUMN struct is set up with the data you got from a MariaDB Dynamic Column binary data. Still, this might not be valid Dynamic Column data, so to check that you should call the mariadb_dyncol_check() referencing this data to make sure it is valid Dynamic Column data.

The function that you then want to use is by far the most powerful one, mariadb_dyncol_unpack(). This will unpack a MariaDB dynamic column are retrieve the basic information on it:

• How many attributes there are (how many "Dynamic Columns")
• What the names of those attributes are.
• The the values of those attributes are.

The values are returned as DYNAMIC_COLUMN_VALUE structs and the names as MYSQL_LEX_STRING structs. Most of this is described in the knowledge base article mentioned above.

I spent some time playing with this, most of that time was spent understanding how the API was meant to work and how to build an application on it. One night, after some heavy drinking, I decided that the best way to show this would be to write some code, instead of just blog about it on and on, so still severely drunk I wrote a simple tool for extracting Dynamic Column data from MariaDB and show it in some different ways (JSON, pretty JSON, Indented dynamic columns etc). To allow you to build it yourself I used autotools, but fact is to use that, you have to also copy some includefiles from the MariaDB source distribution (the reason is those bugs I mention above).

So there is a pre-built executable (I built this on an Ubuntu 10.10) that assumes you use the MariaDB 10.x Client Library (I used 10.0.4). If this doesn't work, as I said, you can always build it yourself (which isn't a throughly tested procedure). Also, for any of this to work, you need the Jansson JSON library.

I'll develop some more Dynamic Columns related tools eventually, but for now this is what I have in the dyncoltools toolset, the dyncoldebug tool available on sourceforge.

/Karlsson