Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

Question

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL?

I understand that where platform interoperability and/or backwards compatibility may be an issue then sticking with the tried and trusted MySQL is best. But for a stand-alone DB on a stand-alone web site/application are there any benefits to be had by using Maria?

Will Maria work with common web platforms such as WordPress, Drupal, Joomla etc.?

I expect that some of this is going to come down to choice/preference of storage engines, but to be honest I still don't know half the time if/when I should use MyISAM, InnoDB or any of the others! Which is better or faster or whatever?

The only thing I get is that if I want 'true' table relationships (i.e. foreign keys etc.) I use InnoDB.

Thanks for any help or clarity people can offer me.

Answer 1

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3

Examples on improvements/features are "Index Condition Pushdown", "Cost-based choice of range vs. index_merge", "Subquery Materialization", "Subquery Cache" and new joins methods, like "Hash Join". Several of the features affect performance of (statements with) subqueries, derived tables and views.

Details can be found at the Optimizer Feature Comparison Matrix page.

Other additional features like microsecond support for time datatypes and improvements on the replication and binary logging.

---

The previous versions (5.1 and 5.2) also have some features that are not found in (mainstream) MySQL, like [PERSISTENT] VIRTUAL columns.

---

Of course, any migration of your server from MySQL to MariaDB should be examined and tested by you as whether these features are useful in your databases/applications and whether the difference in performance (if any) is big enough for the troubles you may have (as with any migration).

Answer 2

In general, MariaDB is a drop-in replacement for MySQL. It says so on their home page. :-) Your applications won't notice a difference.

For specialized applications and edge cases, there may be difference. But these are going to be tuned to use special features of the newer versions of MySQL.

You may also want to look at Percona Server as it tracks the MySQL (GA) releases but also includes performance optimizations built-in.

The necessity for InnoDB will be revealed by your applications behaviors.

Answer 3

MariaDB developers claim that it's a drop-in replacement, and it's true until version 5.5.

• MariaDB 5.1-5.2-5.3 can replace MySQL 5.1.
• MariaDB 5.5 can replace MySQL 5.5

The small "incompatibility" issues usually don't apply, however, they are documented here: https://kb.askmonty.org/en/mariadb-vs-mysql-compatibility/

There are also a lot of bug fixes and new features, the most important being listed here: https://kb.askmonty.org/en/mariadb-vs-mysql-features/

The default storage engine, XtraDB, is a Percona's fork of Oracle InnoDB, which includes several bug fixes and some extra features. And you can also replace MyISAM with Aria and Federated with FederatedX. However, Oracle InnoDB can be installed if you experience compatibility problems, as well as Federated, and MyISAM is already there.

MariaDB 10.0 can replace MySQL 5.5 too, and has some features from MySQL 5.6 (some of which have been reimplemented by Monty's team because the code quality was too low). However, some features from 5.6 are not in MariaDB, at the moment. For example, they don't have GET DIAGNOSTICS in stored procedures, and the JSON format for EXPLAIN output.

All APIs and clients which work with MySQL should work with MariaDB. If something doesn't work, it's a bug and will be solved. If you use Windows, probably you want to use the HeidiSQL GUI, because it supports some MariaDB specific features like Virtual Columns.