- MySQL Performance: Identifying Long Queries
- MySQL Performance: MyISAM vs InnoDB
- MySQL Performance: How To Leverage MySQL Database Indexing
- MySQL Performance: MySQL vs. MariaDB
- MySQL Performance: Converting MySQL to MariaDB
- MySQL Performance: System Configuration File & Routine Maintenance
- MySQL Performance: InnoDB Buffers & Directives
We continue our series on MySQL Optimization by focusing on the differences and similarities between MariaDB and MySQL. MariaDB is a popular alternative to using traditional MySQL for database hosting. The free market of hosting allows additional actors to compete with the long-standing database solution, MySQL. MariaDB is the most prominent alternative to MySQL and is a favorite among industry giants such as Google, Craigslist, Wikipedia, Arch Linux, RedHat, CentOS, Fedora, and cPanel. In a direct performance comparison, MariaDB leaps to the head of the pack through performance optimizations, easy of conversion and compatibility.
Why Switch to MariaDB?
MariaDB is mostly a clone of MySQL, but it also provides several improvements over running the standard MySQL instance. MariaDB aims for a drop-in replacement to MySQL, which makes the switch painless and straightforward. Combine the simplicity of switching with a long list of improvements, MariaDB brings both performance and cutting-edge features to the table. Below are some of its selling points over standard MySQL.
More Storage Engines Options: There is 12 new storage engine built into MariaDB. Among these are CONNECT, Spider, and SphinxSE. Visit their Storage Engines page for a complete list of these engines, how they function, and ways to leverage them to optimize your database.
Speed improvements: MariaDB sports many new speed improvements compared directly with standard MySQL. This improved performance makes MariaDB stand apart from the baseline performance of traditional MySQL servers. Like MySQL, MariaDB has dozens of features for speed optimization including disk access, JOIN and EXPLAIN improvements, subquery, derived tables/views, execution control, and optimizer control.
Faster Indexes/Cache: When using the MEMORY storage engine, MariaDB can complete INSERT statements up to 24% faster than traditional MySQL servers, along with CHECKSUM TABLE and MyISAM Segment Key Cache being 4x faster.
Speedier and Larger Connection Pool: MariaDB benefits from an improved pool of threads that run faster and support up to 200,000+ connections where standard MySQL falls short.
Improved Replication: MariaDB sports faster and safer replication with updates being up to 2x faster than with traditional MySQL Replication setups. Now possible, parallel replication allows the existence of Active/Active or Master/Master configurations. MariaDB replication is backward compatible with MySQL servers, so migrating your cluster to MariaDB is possible by utilizing one node at a time.
New Extensions/Features: There are several new extension and features, to name a few, the WITH, JSON and KILL statements. DECIMAL sees an increased from 30 to 38 decimals while KILL ALL queries for a specified user.
List of Features: Available MariaDB’s website is a comprehensive list of improvements and features, located here: MariaDB versus MySQL-Features.
What is the Downside to Using MariaDB?
The MariaDB project is entirely open-source and free, unlike MySQL which uses dual licensing to keep its Enterprise edition specific features proprietary. The MariaDB developers actively update and enhance with cutting-edge features that standard MySQL does not carry.
Missing Features: MySQL uses some proprietary code in its Enterprise Edition. MariaDB does not have access to this propriety content and is a closed source, meaning the features are only available to MySQL Enterprise users.
Mitigation: MariaDB has addressed this concern through allowing viable open-source alternative plugins for MariaDB, providing the same functionality that the MySQL Enterprise Edition offers. This allows the vast majority of standard MySQL and MySQL Enterprise Edition users the option to switch to MariaDB, taking advantage of its robust performance enhancing features.
Delayed MySQL Compatibility: MariaDB originally forked from MySQL 5.5, so this is the base starting point for the MariaDB source code. Meaning that newer features and bug fixes developed for standard MySQL after version 5.5 are not part of the existing MariaDB source code.
Mitigation: MariaDB conducts monthly merges of the standard MySQL source code to ensure both compatibility and feature/bug-fix adoption. This kind of regular code merging, allows MariaDB to maintain its allure as a drop-in replacement for practically any existing versions of standard MySQL. However, there will always be a delay causing MariaDB to lag behind MySQL’s newer features and bug patches.