Colin Charles Agenda

Facebook recently made opensource, osquery. It gives you operating system data via SQL queries! Its very neat, and you can test this even on MacOSX (it works on that platform & Linux). It is by far the project with the most advanced functionality, linked here in this post.

I noticed that rather quickly, there was a PostgreSQL project, called pgosquery, based on Foreign Data Wrappers with a similar idea. (apparently it was written in less than 15 minutes; so a much lower learning curve than the regular MySQL storage engine interface)

I immediately thought about an older MySQL project, by Chip Turner (then at Google, now at Facebook), called mysql-filesystem-engine. This idea was kicking around in 2008. I was intrigued by hearing about this at a talk (probably at the MySQL Conference & Expo); it’s a pity no one took this further.

On a similar tangent, did you also know that there is the option to use MySQL as storage via FUSE (see: mysqlfs)? An article by Ben Martin shows some practical examples.

At its heyday, MySQL had many storage engines (maybe around 50). Wikipedia has an incomplete list. I see some engines on that list, and think that some of these folk are also creating MongoDB backends – competition. At MariaDB we are probably shipping the most storage engines of any MySQL-based distribution, however I think we could be doing an even better job at working with upstream vendors, and figuring out how to support & augment business around it.

In recent time, MariaDB 10 has been getting many new storage engines. We’ve seen TokuDB, CONNECT, SEQUENCE, SPIDER, CassandraSE for various use cases. For a long time, MariaDB shipped OQGRAPH, but it was disabled in MariaDB 5.5. It will make a come back as OQGRAPH v3 has been worked on actively by Andrew McDonnell. Keep track of this via MDEV-5319.

Another engine being worked on by Kentoku Shiba & team is the mroonga engine, which allows you to do full text search. It is optimised for CJK languages, and is supposedly very fast. To track this, follow MDEV-5222.

What this means is that from the start of the MariaDB project, the only engine that we have disabled and don’t include since 5.5 and greater is PBXT. That’s a pretty good record of having many shipping storage engines that have largely come from the community.

Today before Ivan’s tutorial, he told me that in the 10.0.5 virtual machine images he created, he couldn’t find the Cassandra storage engine. I told him it had to be installed separately, and this is true – you have to install some engines separately!

When you do a yum install MariaDB-server MariaDB-client like the installation instructions tell you to do, you don’t get all storage engines (so running SHOW ENGINES might have you wondering what happened to a bunch of engines). This can easily be seen by doing a yum search MariaDB. On a CentOS 6.4 server with the MariaDB 10.0 repository configured, you should see the following:

MariaDB-cassandra-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-connect-engine.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server

So to get Cassandra or CONNECT engine support, don’t forget to install MariaDB-cassandra-engine and MariaDB-connect-engine.

Once you do that, don’t forget to actually load the engines – for example you do something like INSTALL SONAME 'ha_spider.so';.

In fact, why not check out what plugins exist in /usr/lib64/mysql/plugin? You can also see this from the MariaDB monitor: SHOW PLUGINS SONAME;. This shows active and non-installed plugins as well. Read the documentation for SHOW PLUGINS SONAME.

One thing you might have missed from the MariaDB 10.0.4 Alpha release announcement is that SPIDER is finally in-tree, inside MariaDB. I’ve been watching the SPIDER engine for quite some years and fondly remember the time when Kentoku Shiba dressed up as spiderman at a MySQL Conference.

There are many factors that plays to an engines success. Distribution is one of them. Having a company backing it with services is another, so it is with great pleasure that I also show you Spiral Arms Ltd by Kentoku and Goto.

Overall, a good time to celebrate and I’m very happy for Kentoku and Goto. Now to promote its use. There’s a lot of dated resources that we need to get back up to speed with, so I expect that in time the Knowledgebase entry will well populated.

One of the things we (Team MariaDB) talked quite a bit about since we released was PBXT. It was a feature differentiation to MySQL as we shipped another storage engine. It was included in MariaDB 5.1, 5.2, and 5.3; however with our release of MariaDB 5.5, PBXT (docs in the Knowledgebase) has been deprecated and not built by default any longer.

The reason behind it is clear: PBXT is currently not under active development. We still include it in the source releases and if you would like to use it, you just have to build it. If and when development around it comes back to an active state with bugs being fixed and the engine being pushed forward, I’m sure we’ll start building it again. In the meantime, much thanks to Paul McCullagh for developing a great transactional engine.

The NoSQL/relational database debate has been going on for quite some time. MariaDB, like MySQL is relational. And if you read these series of blog posts, you’ll realise that if you use MySQL correctly, you can achieve quite a lot.

1. It all starts with Kellan Elliott-McCrea with his introductory post on Using, Abusing and Scaling MySQL at Flickr. Follow the entire series.
2. He starts of the series with Ticket Servers: Distributed Unique Primary Keys on the Cheap. Flickr scales using shards, and ticket servers give unique integers to serve as PKs.
3. Richard Crowley talks about OpenDNS MySQL abuses. Nothing too out of the ordinary, but it shows MySQL getting the job done.
4. Mikhail Panchenko talks about using The Federated engine for his series.

If you’re using the Federated engine, know that MySQL disables FEDERATED by default. In MariaDB 5.1.42, you get FederatedX, which is a maintained fork of FEDERATED, by the author himself! Bugs are fixed, and this is a supported engine, so if you’re using the FEDERATED engine, it might be wise to try out FederatedX.

I’d also like to bring to attention, an interesting essay by Dennis Forbes: Getting Real about NoSQL and the SQL-Isn’t-Scalable Lie. Monty says: “NoSQL is for very smart people who need a very sharp knife. People who are not capable of mastering SQL should not even attempt to try out NoSQL.”