What is MariaDB TX? How to Manage the New MariaDB MySQL Fork!

You may have heard about MariaDB TX and you may have wondered what it is?

Is there a difference between it and MariaDB Server 10.3?

In this blog post we would like to give you an overview of the MariaDB TX and what it is all about.

In short: MariaDB TX is a subscription in which MariaDB combines multiple offerings building a fully-featured transactional database.

The database used in MariaDB TX is MariaDB Server 10.3, which also includes Galera Cluster for synchronous multi-master replication. For the proxy layer MariaDB TX uses MaxScale.

Let’s focus a bit on those two main offerings, going over their features.

MariaDB Server 10.3

It evolved into a feature-complete fork, which implements new functionalities on top of what Oracle implements in the upstream. MariaDB 10.3 comes with a list of enterprise-focused features.MariaDB Server 10.3 is no longer a drop-in replacement for MySQL.

Oracle Compatibility

MariaDB 10.3 comes with SQL_MODE=ORACLE, which improves compatibility for SQL syntax used in MariaDB 10.3 with Oracle PL/SQL. Following compatibility features are included in the MariaDB TX:

• Stored Procedure Parameters
• Non-ANSI Stored Procedure Construct
• Cursor Syntax
• Loop Syntax
• Variable Declaration
• Data Type inheritance (%TYPE, %ROWTYPE)
• PL/SQL style Exceptions
• Synonyms for Basic SQL Types (VARCHAR2, NUMBER, …)

This allows for easier migration of your applications from Oracle databases into MariaDB TX.

Up to 80% of the Oracle PL/SQL code can now be executed on MariaDB without a need for introducing changes; this seriously impacts the overall learning curve and reduces time needed to rewrite the legacy code to run on MariaDB TX.

What’s also important to keep in mind, MariaDB TX comes with a support package and you will get access to consultants who will be able to share migration best practices with you or even straight help you in the planning process to make the transition even less troublesome.

Improvements in SQL

MariaDB TX brings us also improvements in SQL syntax, including new features that should be very useful for developers like Window functions or Common table expressions. Also a temporal subclauses could be very useful as they allow for accessing multiple versions of a given row based on a specific point in time.

All the features are listed below:

• Temporal subclauses (e.g., AS OF)
• User-defined aggregate functions
• Ordered-set aggregate functions
• INTERSECT/EXCEPT
• Table value constructors
• DDL/SELECT lock timeout
• Common table expressions
• Window functions
• JSON functions

External Storage Engines

The default engine for MariaDB is InnoDB, transactional, all-round storage engine.

It is suitable for most of the workloads and it works great for OLTP (Online Transaction Processing) workload. It’s not the only storage engine available in MariaDB TX, though. You get access to Spider engine, which can be used to partition your data across multiple MariaDB instances while maintaining support for XA transactions.

Another storage engine you can use is MyRocks, an engine optimized for storage and write amplification reduction. Battle-tested in Facebook, LSN-based, it suits perfectly for storing large volumes of data on SSD storage, reducing the costs by implementing strong compression and by reducing the number of writes required for a given workload (and thus minimizing the SSD wear out).

Galera Cluster

MariaDB TX gives you easy access to the Galera Cluster, a virtually synchronous, multi-master replication. Galera Cluster can be used to design highly available, WAN-spanning clusters.

Galera Cluster is built on top of the quorum-aware protocol, which ensures that the network partitioning will not be an issue and that the split brain should no longer be a problem. Galera Cluster provides means of auto-provisioning new or failed nodes, reducing the management footprint.

Operational Features

MariaDB TX also provides some features related to the operational tasks. Instant ADD COLUMN helps to reduce impact of one of the most common schema changes. Invisible columns help to maintain compatibility between old and new code. Indexes on virtual columns will boost the performance.

Mariabackup

MariaDB TX implements data-at-rest encryption which includes encryption of binary logs. To ensure that MariaDB TX users can benefit from lock-free backups, Mariabackup had been created. It is an improved fork of Xtrabackup, which did not work correctly with MariaDB TX encryption features. Now, you can enjoy your hot, physical backups with Mariabackup while having your data safely encrypted.

MariaDB MaxScale

In addition to MariaDB 10.3, MariaDB TX comes with MaxScale 2.3, a SQL-aware proxy which can be used to build highly available environments. It comes with numerous features and we would like to go over the most important of them here.

Automatic Failover

MaxScale can be used to track the health of the master MariaDB node and, should it fail, perform a fast, automatic failover. Automated failover is crucial in building up a highly available solution that can recover promptly from the failure.

Read-Write Splitting

Read-write splitting is critical feature to allow read scaling. It is enough for the application to connect to the MaxScale and it will detect the topology, determine which MariaDB acts as master and which act as slaves. It will then route the traffic accordingly to this. SELECT queries will be sent to the slaves, writes will be sent to the master. Everything happens automatically, topology is monitored all the time and should a failover happened, traffic will be re-routed based on the change.

Transparent Query Routing

MaxScale, being the point of entry of the traffic into MariaDB TX, can be used to do a read-write split. Sometimes this is still not enough and it would be great to have a way of controlling where given query should be sent. This is possible in MaxScale – you can match the queries using regular expressions and then decide should they be sent to the master or to slaves. This can help in some particular cases where SELECT query has to be executed on the master due to read-after-write issues or just because it has to have the most up-to-date view of the dataset.

Query Result Caching

To improve performance, query caching is a must. Query cache available in MariaDB will just not work in highly concurrent environment as it enforces serialization of the queries, seriously reducing the performance even for read-only workloads. Using external solution for cache is not always feasible: after all you will end up with yet another database to maintain, secure and keep healthy. It might be better to use MaxScale as a cache assuming that you already use it for other functionality.

Query Blocking

Sometimes databases suffer from an inefficient query, which creates high load on the system. It could be that rewriting that query would take way too long time (someone would have to rewrite it, test the change on staging and then finally deploy to production), a time you don’t have. MaxScale can help you here with features like query blocking, which basically allow you to stop a given query from hitting the database. This feature can also be used to build a SQL firewall – drop all of the queries which match patterns that point towards SQL inject or other, potentially dangerous and malicious, activities.

As you can see, MariaDB TX comes with a list of features and software that’s designed to work together and build highly available, scalable database for transactional data processing.

Enterprise Monitoring & Management for MariaDB TX

ClusterControl supports MariaDB TX fully. You can easily deploy both MariaDB Server 10.3 and MaxScale 2.3. ClusterControl supports MariaDB replication setups as well as MariaDB Galera Cluster.

As long as you have SSH connectivity from your ClusterControl instance to the nodes you want to deploy MariaDB TX on, you can do so in just a couple of clicks.

First you have to define how ClusterControl will reach the MariaDB TX nodes.

Then, pick MariaDB as the vendor and go for one of the supported versions. Pass the MariaDB root password.

Finally, decide upon the topology. You can deploy MariaDB TX in a master – master, active – standby setup with additional slaves. Replication will use MariaDB GTID.

For MariaDB Galera Cluster first step is exactly the same, then you just have to pick the MariaDB as the vendor, decide on the version and define nodes in the MariaDB Galera Cluster:

For example, adding MaxScale load balancers is just a few clicks away:

Once deployed, you can manage your MaxScale using ClusterControl:

You can scale also scale your Galera cluster by adding new Galera nodes or asynchronous replication slaves. You can add slaves or delayed slaves to your replication setups, or perform topology changes by promoting a slave to master, and reslaving the rest of the setup. ClusterControl will monitor the cluster and attempt to recover failed nodes or clusters should an incident happen. For backups, ClusterControl can help you to define a backup schedule using both mysqldump and Mariabackup so you can easily benefit from the encryption features of MariaDB TX.

For the monitoring and trending part, ClusterControl can be used either in an agentless mode:

Or it can be used along with Prometheus and agents to provide even better insight into what is happening in the MariaDB TX cluster.

We hope this introductory blog post helped you to understand what MariaDB TX is and how ClusterControl can help you manage it.