Over the past few years, the open source relational database management systems MySQL and MariaDB have undergone tremendous changes: new and improved features, fixes for long-standing problems, better performance across the board.
With all that’s changed, it’s easy to miss some of the best features MySQL and MariaDB have added in that time. In this article we’ll run through seven of the biggest new capabilities added to MySQL, MariaDB, or both—and why you would want to use them.
JSON support
When NoSQL databases appeared, with their promises of developer ease and elastic scalability, many wondered if relational databases were on the way out. Short answer: Not at all. NoSQL systems are handy and flexible, but schemas and tables will always have their place.
What’s more, many old-school relational databases, MySQL and MariaDB among them, took a page from the NoSQL book and added JSON support as a standard feature. The net effect is NoSQL when you need it, side by side with conventional SQL in the same database.
JSON support in MySQL and MariaDB lets you insert JSON documents in a specially designated table column. The inserted JSON data can be automatically validated using the same kind of constraints used for other data columns. You can retrieve the data either as JSON documents or simple scalars, and you can use generated or virtual columns to produce an effect similar to JSON indexes.
Two important points are worth keeping in mind here. First, while the sets of JSON processing functions in MySQL and MariaDB are similar, they are not drop-in replacements for each other. Second, the MySQL and MariaDB implementations of the native JSON column data type are also different. This makes for slight incompatibilities that you will need to navigate if you’re migrating or syncing data between the two databases.
Resource groups (MySQL only)
All database jobs are important, but some are more urgent than others. For instance, you might want to run jobs like archiving or scheduled batch jobs in the background, while ensuring that business-critical work is executed as snappily as possible. MySQL’s resource groups make this possible.
With resource groups, you can designate a type (“system” or “user”), a CPU affinity, and a thread priority to all of the database jobs assigned to the group. You can select a resource group for a session, or select one for a single statement by using an optimizer hint.
Note that resource groups are implemented differently across MySQL platforms, and that you can’t use resource groups in conjunction with the enterprise thread pool plug-in. Also, while there is a feature request to implement a similar feature in MariaDB, there are no plans yet to implement it.
OQGRAPH storage engine (MariaDB only)
Graph databases let you store and explore relationships among data far more efficiently than you can with a relational database. While dedicated graph databases like Neo4j or Amazon Neptune focus exclusively on graph storage and processing, MariaDB allows you to perform graph processing side-by-side with conventional SQL queries by way of the OQGRAPH storage engine.
Most graph databases use their own custom query language. With OQGRAPH, you load data and construct graph queries using conventional SQL. Results are returned in MariaDB’s conventional query format, so they can be joined or combined with the results of conventional SQL table queries.
Oracle compatibility features (MariaDB only)
Oracle’s database products remain among the most widely used in all of IT, but its licensing costs and contractual restrictions have many users eyeing an exit strategy. What’s more, many applications built on Oracle make heavy use of features exclusive to Oracle PL/SQL and its syntax.
Over the last few versions, MariaDB has rolled in a swath of new features designed to emulate the behavior of Oracle databases, especially Oracle’s PL/SQL language. In theory this allows much existing PL/SQL code to run as-is, or with only minor modifications, in MariaDB. The MariaDB team estimates some 80 percent of legacy Oracle PL/SQL can be run as-is using the compatibility features.
Note that the MariaDB command to use Oracle PL/SQL mode takes effect on a per-client basis. You do not need to change MariaDB’s behaviors globally to use this feature.
System versioned tables (MariaDB)
The 2011 version of the SQL standard added versioned tables, the ability for a database to track versions of table rows. MariaDB added system versioned tables as a native feature in version 10.3.4.
With MariaDB’s system versioned tables, you can run a query using a given temporal range, and the delivered results will appear as they were during that period of time. You can also modify or delete rows that fall within a date range, add or remove time periods to track, and use time periods specified either at the application level, the system level, or both. In theory you could do this with any database that supports time values, but it’s difficult to roll it yourself; MariaDB does it under the hood.
Although system versioned tables are supported in MariaDB for any database engine, some of the features—for instance, transaction-precise history, which shows records in the middle of a given transaction—are available only with the InnoDB engine.
ColumnStore storage engine / InfiniDB (MariaDB)
The pluggable storage engine technology in MariaDB and MySQL allows both databases to greatly expand their native functionality. One such storage engine, ColumnStore, turns MariaDB into a columnar-storage database. (ColumnStore isn’t available for MySQL, but the project ColumnStore was derived from, InfiniDB, uses MySQL to execute queries.)
Column storage is ideal for high-speed querying of large volumes of data. OLAP systems use column storage, and so ColumnStore works as a way to provide OLAP-style functionality inside MariaDB, without relying on an external (and typically commercial) product like Teradata or Greenplum. ColumnStore doesn’t provide the full range of out-of-the-box analytics or data-marshaling features that come with those products, but it can provide the data layer for an in-house analytics solution.
Spider storage engine
The more powerful the feature, the more difficult it is to deploy in production. One such feature is database sharding, or splitting a database across multiple servers to improve performance, which typically requires a lot of tinkering and tweaking.
MariaDB 10.3.4 (and later) smooth the way with Spider, a storage engine with built-in sharding and data partitioning features. Spider supports several different modes: simple federation, high availability, sharding, and sharding plus high availability.
Spider does have some feature overlap with MaxScale, MariaDB’s system for load balancing, proxying, fail-over, and high availability. MaxScale covers a wider and more ambitious set of use cases than Spider, but Spider is useful if you want to take advantage of sharding in more modest deployments.
Spider was originally developed as a MySQL plug-in, and it is still available in that form for MySQL users. The vast majority of the features in both versions are the same, with a few exceptions.