Why MariaDB Scores Over MySQL

This article gives readers an insight into MariaDB as well as MySQL. Both are analysed and the differences between them are discussed. There is also a section on why MySQL users should switch to MariaDB.

Today, organisations have a range of database options to choose from—whether commercial or open source. There was a time when most database management systems (DBMSs) were commercial. But now, with a number of open source databases available, industry professionals prefer to use them. In fact, open source databases have gained a significant share of the market today, because users have the freedom to implement these systems as per requirements and get the best out of them.

Open source database systems are available from various vendors like Oracle, Microsoft, Google, Amazon and Rackspace. The history of open source DBMSs is not very old. The first operational open source DBMS was MySQL, which was launched in 1995 and till date, lots of significant improvements have been incorporated in it, which has made it a strong competitor even to commercial database software.

Open source DBMSs are gaining popularity not only because they are feature-rich and low cost, but also due to their operational efficiency. The freedom users enjoy in customising the source code as per individual needs, and with little programming knowledge, is a plus point. Gartner reports that open source DBMSs are growing by 42 per cent each year, and this industry will be worth US$ 8 billion by 2020. About 80 per cent of the users polled for the report use some sort of open source software, including DBMSs. Various commercial vendors like Microsoft and IBM are also offering ‘Express Editions’ of their DBMS software.

The most widely adopted open source DBMSs are MySQL and MariaDB. This article discusses their similarities, differences and relative merits, before going on to suggest why users ought to migrate to MariaDB.

Databases

Databases are specific structures that hold data in the form of tables. The software that enables the flow of data through a table-based structure is called a DBMS. The most widely used DBMS is a relational database management system (RDBMS). A DBMS is a computer software application that interacts with end users, other applications and the database itself to capture data. A general-purpose DBMS allows definition, creation, querying, updation and the administration of databases.

The functions of a database and its data are:

Data definition: Creation, modification and removal of definitions that lay the foundation for data organisation

Data updation: Insertion, modification and deletion of data

Retrieval: Retrieval of data from the entire database by making specific queries

Administration: User creation and modification, data security, performance monitoring, data integrity, concurrency control and data recovery

So, a database system is a combination of a database model, a DBMS and a database.

MySQL

MySQL is an open source, multi-threaded, relational database management system (RDBMS) written in C and C++. MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Micheal ‘Monty’ Widenius, and development started in 1994. The first version was launched on May 23, 1995. MySQL is named after co-founder Monty Widenius’s daughter, My. The name of the MySQL dolphin logo, Sakila, was chosen from a huge list of names suggested by users in a ‘Name the Dolphin’ contest.

MySQL server is suitable for mission-critical, heavy-load production systems as well as for embedding into mass deployed software. It is regarded as a central component of the LAMP stack (Linux, Apache, MySQL and PHP). The MySQL database is available for various operating system platforms like Linux, Windows, MAC, BSD UNIX, etc. Popular websites like Wikipedia, Facebook and YouTube use MySQL.

MySQL is now a trademark owned by Oracle Corporation and/or its affiliates, and is dual licensed. Users can choose to use the software as open source under the GNU licence and even purchase a commercial

licence from Oracle.

MySQL architecture

The MySQL server is based on a tiered architecture and consists of both primary sub-systems and support components that interact with each other to read, parse and execute queries to return the results.

The five types of primary sub-systems of MySQL are:

• Query engine
• Storage manager
• Buffer manager
• Transaction manager
• Recovery manager

Query engine: This contains three main interrelated components—the syntax parser, the query optimiser and the execution component. The syntax parser decomposes the SQL commands it receives from calling programs in a form that is understood by the MySQL engine. The ‘query optimiser’ streamlines the syntax for use by the execution component and then prepares the most efficient plan for query execution. The execution component interprets the execution plan, based on the information it receives and makes requests to other components to retrieve the records.

The storage manager: This interfaces with the operating system to write data to the disk in the form of user tables, indices and logs as well as internal system data.

The query cache: The MySQL engine uses an extremely efficient result set caching mechanism called query cache, which dramatically enhances the response time for queries that are called upon to retrieve the same data as the previous query.

The buffer manager: This handles all memory management issues between requests for data by the query engine and the storage manager. MySQL makes use of memory to cache result sets that can be returned, and the cache is maintained in the buffer manager.

The transaction manager: This sub-system provides a locking facility to ensure that multiple users access the data in a consistent manner without corrupting or damaging the data.

The recovery manager: This keeps copies of data for the purpose of retrieval in case of any sort of data loss.

The two support components of MySQL are:

• Process manager
• Function libraries

Process manager: This performs two main functions —managing user connections via modules for network connection management, and synchronisation of tasks and processes via modules for multi-threading, thread locking and performing thread-safe operations.

Function libraries: This contains general-purpose routines like string manipulation, sorting operations and performing OS-specific functions like memory management and file I/O.

Features of MySQL

• Relational database management system: MySQL supports all features, which makes it a complete RDBMS system. It supports full SQL as a standardised language for querying and updating data, and to administer the database.
• Easy and secure: MySQL is very simple and interactive to use, compared to other DBMS software, and is also highly secure with a solid data security layer providing efficient encryption to data.
• Client/server architecture: Its simple client/server architecture helps end users to create a server with connectivity to many clients, in order to communicate with the server for inserting, updating and administrating the database.
• Scalable: MySQL can handle high volumes of data without any hiccups—as much as 50 million rows. It can handle up to 8TB of data without any issues.
• Cross-platform: It is compatible with almost every operating system, like UNIX, Windows, Linux, MAC OS X, etc.
• High-performance, flexible and good productivity: MySQL provides faster, highly reliable, cheap storage solutions and supports a large number of embedded applications. It makes use of triggers, procedures and views to deliver better productivity.

MariaDB

MariaDB is a forked version of MySQL. It was primarily developed due to concerns that arose when MySQL was acquired by Oracle Inc. MariaDB is a general-purpose DBMS engineered with extensible architecture to support a broad set of use cases via pluggable storage engines. It uses different storage engines to support different use cases.

MariaDB is an open source, multi-threaded, relational database management system, released under the GNU Public License (GPL). Its lead developer is Michael Monty Widenius, who is also one of the founders of MySQL AB. Many features contribute to MariaDB’s popularity as a database system. Its speed is one of its most prominent features. MariaDB is also remarkably scalable, and is able to handle tens of thousands of tables and billions of rows of data. It can also manage small amounts of data quickly and smoothly, making it convenient for small businesses or personal projects. Another feature that sets it apart from its predecessors is a focus on security. MariaDB’s built-in functions include those for manipulating and formatting text, business and statistical calculations, recording chronological information, as well as speciality features such as those related to GPS mapping.

MariaDB Server is one of the most popular open source databases in the world. It is available in Debian and Ubuntu, and is now the default database on Arch Linux, Manjaro, openSUSE, Red Hat Enterprise Linux, CentOS, Fedora and SUSE Linux Enterprise. And as one of the most broadly adopted and widely deployed in the world, MariaDB Server receives contributions from companies like Alibaba, Facebook and Google. Recently, Microsoft has also joined hands to support the MariaDB community.

Features of MariaDB

• MariaDB is available for use under GPL, LGPL and BSD.
• It includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources.
• It uses a standard and popular querying language.
• MariaDB runs on a number of operating systems and supports a wide variety of programming languages.
• It offers support for PHP, one of the most popular Web development languages.
• It offers Galera cluster technology.
• MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features that impact performance negatively.

Other features include multi-source replication, fusion IO optimisations, table discovery and online alter table.

A comparative analysis of MariaDB and MySQL

MariaDB has a significantly high number of new features, which makes it better in terms of performance and user-orientation. Let’s evaluate both MariaDB and MySQL to determine which one is the better. The choice will ultimately depend on the IT managers and their familiarity with open source databases.

Some significant differences between MySQL and MariaDB

1. Usage of the database: Since 1995, MySQL has been regarded as the most implemented and widely used open source database till date. Many IT giants like Twitter, YouTube, Netflix and PayPal, as well as NASA, the US defence forces and Walmart, make use of this database.

MariaDB, being a more recent arrival, is also taking strong root as back-end software in various IT giant organisations, such as Google, Red Hat, CentOS and Fedora.

2. Structure of the database and the index: MySQL is a pure relational database integrated with an ANSI-standard information schema, and consists of tables, columns, views, procedures, triggers, cursors, etc. The SQL of MySQL is a subset of ANSI SQL 99.

MariaDB, on the other hand, is a fork of MySQL and, hence, has the same database structure and index. This facility makes MariaDB a strong choice for users who want to switch or upgrade their back-end directly without any issues of upgrading the database and data structures.

Everything—from the data, table definitions, structures and APIs—remains identical when upgrading from MySQL to MariaDB.

3. Binaries and implementation: MySQL was developed using C and C++ and is fully compatible to run with almost every operating system like Microsoft Windows, MAC OS X, Linux, FreeBSD, UNIX, NetBSD, Novell Netware and many others.

MariaDB was developed using C, C++, Bash and Perl. It is compatible with various operating systems like Microsoft Windows, Linux, MAC OS X, FreeBSD, Solaris, etc.

4. Replication and clustering: MySQL provides strong replication and clustering through master-master and master-slave replication, and makes use of the Galera Cluster for multi-master clustering.

MariaDB provides almost the same replication and clustering facilities to end users as MySQL in terms of master-master and master-slave replication. It also uses Galera Cluster from version 10.1 onwards.

5. Support for databases: MySQL technical support services are provided round-the-clock via Oracle, and the support team consists of professional developers and engineers who provide various facilities like bug fixes, patches and version releases. Oracle offers MySQL premier support, extended support and sustaining support, depending on what users need.

MariaDB provides strong support to users through the open source community, online forums and even via experts. Round-the-clock support is available for MariaDB via enterprise subscription especially for mission-critical production systems.

6. Security: In terms of security, MySQL provides a strong encryption mechanism for tablespace data. It provides strong security parameters in terms of choosing good passwords, not granting unnecessary privileges to users, and ensures application security by preventing SQL injections and data corruption.

MariaDB has had a significant boost in terms of security features like internal security and password check, PAM and LDAP authentication, Kerberos, user roles, and strong encryption over tablespaces, tables and logs.

7. Extensibility: A database supporting an extensible system can be extended by the user in many different ways like adding new data types, functions, operators, aggregate functions, index methods and procedural languages. MySQL has no support for extensibility.

MariaDB is built on a modern architecture that is extensible at every layer—client, cluster, kernel and storage. This extensibility provides two major advantages. It allows for continual community innovation via plugins, which means that a variety of storage engines, like MariaDB ColumnStore or Facebook’s MyRocks, can be integrated through MariaDB’s extensible architecture. Additionally, it makes it easy for customers to configure MariaDB to support a wide variety of use cases, ranging from OLTP to OLAP.

8. JSON support: MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides the following advantages over storing JSON-format strings in a string column:

• Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
• Optimised storage format: JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later has to read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up sub-objects or nested values directly by key or array index without reading all values before or after them in the document.

On the other hand, MariaDB Server 10.2 introduces a comprehensive set of 24 functions for reading and writing of JSON documents. In addition, the JSON_VALID function can be used with a check constraint for validation, while functions like JSON_VALUE can be used with dynamic columns to index specific fields.

9. Licensing: MySQL offers its code as open source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise.

MariaDB can only use the GPL, because its work is derived from the MySQL source code under the terms of that licence.

10. Performance: MariaDB achieves best-in-class performance with numerous innovations that are absent for MySQL. These include thread pool management to maximise processing efficiency and extensive optimisation features such as defragmentation within the InnoDB data store. So, when rows are deleted from an InnoDB table, the free space immediately becomes available to the operating system. There’s no need to copy data from old tables to new ones, and there are no gaps in the tablespace. MariaDB also offers engine-independent table statistics to improve the optimiser’s performance, speeding up query processing and data analysis on the size and structure of the tables.

Without these enhancements, MySQL falls short in terms of performance. Thread utilisation in MySQL is sub-optimal and InnoDB tables become fragmented over time, compromising performance.

MariaDB vs MySQL

The following points highlight the pros and cons of MariaDB.

Pros

• MariaDB has been optimised for performance and is much more powerful than MySQL for large data sets. Elegant migration from other database systems to MariaDB is yet another benefit.
• Switching from MySQL to MariaDB is relatively easy and is a piece of cake for systems administrators.
• MariaDB provides better monitoring through the introduction of micro-second precision and extended user statistics.
• MariaDB enhances the KILL command to allow you to kill all queries from a user (KILL USER username) or to kill a query ID (KILL QUERY ID query_id). MariaDB has also switched to Perl-compatible regular expressions (PCRE), which offer more powerful and precise queries than standard MySQL regex support.
• MariaDB has applied a number of query optimisations for queries connected with disk access, join operations, sub-queries, derived tables and views, execution control, and even explain statements.
• MariaDB is purely open source, instead of the dual-licensing model that MySQL uses. Some plugins that are available only for MySQL Enterprise customers have equivalent open source implementations in MariaDB.
• MariaDB supports significantly more engines (SphinxSE, Aria, FederatedX, TokuDB, Spider, ScaleDB, etc) compared to MySQL.
• MariaDB offers a cluster database for commercial use, which also enables multi-master replication. Anyone can use it freely and reliance on a MySQL Enterprise system is not required.

Cons

• Migration of MariaDB back to MySQL has not been possible since release 5.5.36.
• For new releases of MariaDB, the appropriate libraries (for Debian) will not be deployed in time, which will result in a required upgrade to a newer version due to dependencies.
• Cluster version of MariaDB is not very stable.

Top reasons to migrate to MariaDB

• 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 organisations and corporate users.
• MySQL’s usual (and slow) database engines MyISAM and InnoDB have been replaced in MariaDB by Aria and XtraDB respectively. Aria offers better caching, which makes a difference when it comes to disk-intensive operations.
• MariaDB provides better monitoring through the introduction of micro-second precision and extended user statistics.
• The cutting-edge features of MariaDB like GIS, dynamic column support, etc, make it a better choice.
• MariaDB follows good industry standards by releasing security announcements and upgrades at the same time, and handling the pre-release secrecy and post-release transparency in a proper way.