The MariaDB project is pleased to announce a special preview release of MariaDB 10.0.9 with significant performance gains on FusionIO devices. This is is a beta-quality preview release.

Download MariaDB 10.0.9-FusionIO preview

Background

The latest work between MariaDB and FusionIO has focused on dramatically improving performance of MariaDB on the high-end SSD drives produced by Fusion-IO and at the same time delivering much better endurance for the drives themselves. Furthermore, FusionIO flash memory solutions increase transactional database performance. MariaDB includes specialized improvements for FusionIO devices, leveraging a feature of the NVMFS filesystem on these popular, high performance solid state disks. Using this feature, MariaDB 10 can eliminate some of the overhead within the InnoDB storage engine when used with FusionIO devices.

In below Figure 1 shows a legacy architecture of SSDs on the left and the FusionIO architecture on the right.

fusionioFigure 1: Legacy architecture on left and new FusionIO architecture on right.

Continue reading

On Thursday MySQL technology saw a huge boost. It’s hard for anyone now to argue that MySQL isn’t in the game of extreme scalability and performance, which some NoSQL vendors have been using as a tagline for the last years. To see four of the largest MySQL and MariaDB users come together to bootstrap a branch of MySQL for extreme scaling needs is simply fantastic. The improvements done inside these companies will now be available to the rest of the community. In all fairness Facebook and Twitter, in particular, have been making their improvements publicly available also before. Google has also made some improvements available publicly over the years and have lately been active in the MariaDB project with code reviews, bug fixes and other patches. But broadening the public contributions further and combining it all, is new.

Engineering of MySQL technology happens in many places. Aside from Oracle and the companies behind WebScaleSQL, there are two other entities that have made significant contributions to improving MySQL technology in recent years – Percona and MariaDB. Percona has made many performance-related functionality and tooling improvements. The MariaDB project has made significant engineering efforts by bringing out many new features to MySQL technology and has also become the project for community contributions. Take a look at the list of bigger contributions that have made it into MariaDB 10.0.

MariaDB 5.5 is constantly being merged with MySQL 5.5 community edition. Every time there is a new minor release of MySQL 5.5 a new release of MariaDB 5.5 with exactly the same number comes out shortly afterwards. In MariaDB 10, this dependency is lighter, which the numbering also indicates. MariaDB 10 includes a lot of merged code from MySQL 5.6, but it also includes a big amount of MariaDB specific code and code merged from the wider community. WebScaleSQL will be another important source for merges in the future. Without knowing all the details of WebScaleSQL, it should be safe to say that there are two sorts of patches: the ones that improve MySQL technology in general and the ones that would be specific for the purposes of running MySQL at extreme scale with integration into technologies not commonly used in more normal setups of MySQL or MariaDB.

MariaDB is all about improving and keeping the world’s most installed database, MySQL, available to the masses independent of whether they are private persons with the need for a database for their blog or if the target is a mission critical enterprise application. MariaDB therefore needs to provide all the components needed from database drivers (connectors) to integrated high-availability solutions like MariaDB Galera Cluster.

In addition, the majority of the users and organizations using MariaDB or MySQL don’t have the in-house skills to build and make changes to MariaDB or MySQL. This is why MariaDB has to be supported on a wide variety of platforms and binaries provided for all of them. WebScaleSQL is currently only compatible with GNU/Linux x86_64 platforms and no binaries are produced.

WebScaleSQL confirms the power of community driven development in open source and is a very nice addition to the branches and forks of MySQL!

I’ve continued building on my MariaDB GIS and node.js example application that I wrote about two weeks back, https://blog.mariadb.org/node-js-mariadb-and-gis/. The application shows how to load GPX information into MariaDB, using some MariaDB GIS functionality, and making use of the node.js platform together with MariaDB’s non-blocking client.

With the GPX data converted into GIS points in the MariaDB database, I wanted to further expand a little on both the GIS aspect and also look at how some additional data could be shown in the application by using jQuery’s Ajax calls to update a piece of the web based application UI.

To start with, an interesting thing to do when you have a bunch of GIS points in a database table is to do distance calculation with the end result being to get the complete distance of the track formed by the points. There of course exists a bunch of different formulas for this, but since MariaDB yet doesn’t have the third coordinate in GIS, which is altitude (or elevation), I chose to use the concept of the Great Circle Distance, http://en.wikipedia.org/wiki/Great-circle_distance  and the Haversine approach. The algorithm for counting the distance between two points in this way is:

Image1: Haversine formula from http://en.wikipedia.org/wiki/Great-circle_distance

Since we need to make the distance calculations for all distances between the points it makes sense to create a database function for counting the distance between two points:

With the function in place in the database, we can test it with a query that actually will be the base of the query the application will be using to retrieve the distance for the whole track:

In the SELECT query I’ve given the id of the first point of the track and then done an INNER JOIN over the same table to be able to get the second point and calculating the distance between the points. I’ve made sure when inserting the points into the database that they are ordered in such a way that the next point on a track after the previous one always has the following pointId, so that I for example now in this case can tell the query that the join is done on pointId + 1. The outcome of running the SELECT –query is:

The next step is to do this for every point on the track and sum the distances together to get the full length of the track. This is done by changing the query slightly so that there is no specific pointId restriction and making use of the SUM function to sum all the point distances:

With that in place, let’s move to the application side. For the background of setting the whole application up in node.js, please refer to my previous blog post, https://blog.mariadb.org/node-js-mariadb-and-gis/. On the application side I’ll start with defining a new URL mapping through which the distance information will be retrieved. This happens in app.js where a new row is added to the URL mapping section:

In the URL mapping the common.trackInfo –function is called, which is a really simple function just calling data methods for getting a database connection, querying the distance and closing the database connection. Inside the data method [name of method for retrieving distance] the SELECT query for summing up the distances between the points can be seen. The only parameter being given to the query is trackId, which is read from the URL querystring in the normal node.js way of req.param(“trackId”).

On the UI side, let’s make use of the page that plots the track onto Google Maps. In that web page Google’s implementation of jQuery is used, which can be seen in the source code:

Also notice in the source code how the points that are being plotted onto the map are requested through a $.getJSON function call. I’m going to do something similar for getting the distance and displaying it. First though let’s create a link through which the retrieval of the distance is fired and add a placeholder for the distance information by adding a div –block in the HTML: 

With that in place let’s take a look at the jQuery function that will get us the distance:

The function calls the url /trackinfo URL mapping, which returns a JSON formatted output holding the distance information. The distance is easily picked out from JSON and finally placed in the div –block.

Image2: A part of the web page where the distance is shown

As you can see, it’s very straight forward to use MariaDB with node.js and make use of jQuery. Also there are many interesting things that can be done by using the GIS capabilities of MariaDB. In MariaDB 10.1 the third coordinate will be present, which then means that the height differences could be considered in the distance calculations and the outcome would be more precise. In this particular case we were looking at a GPX track from a half marathon run I did. I had a running watch on me, which gathered the information for the track. It showed the final distance of the run to be 21.23 kilometers, while the distance counted with the Haversine -algorithm approach was 21.15 kilometers. The difference could very well be that the watch actually included the altitude into the calculations, but I’m of course not sure about that since I don’t know what algorithms it uses.

This example application’s source code is available on Github. Try it out if you’re interested in MariaDB GIS or using node.js and jQuery with MariaDB.