October 29, 2014

Why should you migrate from MySQL to MariaDB?

Anatoliy Dimitrov gives his take on technical reasons to migrate from MySQL to MariaDB: MariaDB offers several advantages in terms of performance and features.

First and foremost, MariaDB offers more and better storage engines. NoSQL support, provided by Cassandra, allows you to run SQL and NoSQL in a single database system. MariaDB also supports TokuDB, which can handle big data for large organizations and corporate users.

MySQL's usual (and slow) database engines MyISAM and InnoDB are replaced in MariaDB by Aria and XtraDB respectively. Aria offers better caching, which makes a difference when it comes to disk-intensive operations. Temporary tables also use Aria, which speeds up complex queries, such as those involving GROUP BY and DISTINCT. Percona's XtraDB gets rid of all of the InnoDB problems with slow performance and stability, especially in high load environments.

Additional, unmatched features in MariaDB provide better monitoring through the introduction of microsecond precision and extended user statistics. MariaDB also enhances the KILL command to allow you to kill all queries for a user (KILL USER username) or to kill a query ID (KILL QUERY ID query_id). MariaDB also switched to Perl-compatible regular expressions (PCRE), which offer more powerful and precise queries than standard MySQL regex support.

In addition to more features, MariaDB has also applied a number of query optimizations for queries connected with disk access, join operations, subqueries, derived tables and views, execution control, and even explain statements. To see what these mean for database performance, visit the MariaDB optimizer benchmark page.

In addition, in the latest Red Hat 7 release, MariaDB replaces MySQL in the default software repository. This means automatic migration to MariaDB for most people who use the default distribution packages. Corporate users' migration to MariaDB will be facilitated with additional support from Red Hat.

Read each MariaDB version's the release notes to learn about all the new features and improvements. Naturally, the latest version (currently 10.0) has the most new features and improvements, but if you are focused on stability, you may want to deploy an older, time-tested version such as 5.5.

For all of the reasons cited here, a large number of former MySQL users, including Wikipedia, have migrated to MariaDB, and in the process have gained a modern, stable, powerful, and fast database system.

Further reading

About Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.

Read all posts by Anatoliy Dimitrov