In-depth comparison between MySQL and MariaDB: Performance and Selection Guide

In the tens of millions of data insertion test, MariaDB took only 1/2 to 1/3 of that of MySQL. This set of data already indicates the fundamental difference in performance architecture between the two.

When companies are faced with database selection decisions, MySQL and MariaDB, a pair of database systems with "same sources and different streams", often become the focus.

The two are closely connected in origin-MariaDB was created by the original developers of MySQL and aims to provide a fully open source alternative. But over time, they have gradually diverged in terms of performance characteristics, functional implementation and development direction.

According to actual test data, MariaDB's performance is about 1 times better than MySQL in a single data insertion scenario, but its performance varies in different query scenarios.

Origin and background

The relationship between MySQL and MariaDB can be compared to father and son and brother. MySQL was originally developed by Swedish company MySQL AB, was later acquired by Sun Microsystems and eventually fell under Oracle.

This series of commercial acquisitions has raised concerns among the open source community about the future development of MySQL, especially concerns that it may gradually close down or be subject to commercial restrictions.

In response, MySQL's original developer Michael Widenius led the creation of MariaDB, named it after his youngest daughter Maria.

MariaDB is maintained by the MariaDB Foundation and uses a fully open source GPLv2 license, a decision that ensures that it is always open to the community and is not subject to business strategies.

From a technical perspective, MariaDB is fully compatible with MySQL at the beginning, and can even directly use MySQL's client APIs and connectors.

But over time, MariaDB began to develop its own features, and the version number started directly from 10.0, marking that it no longer follows the pace of MySQL's version development.

Read and write performance

Performance is a core consideration in database selection. Based on various test results, MySQL and MariaDB have their own advantages in different operation types.

MariaDB performs particularly well in terms of insertion performance. Test data shows that in the scenario where 10,000 pieces of data are inserted, MariaDB takes only 1/2 to 1/3 of that of MySQL. Especially in a single data insertion scenario, MariaDB's performance is about 1 times better than MySQL.

Query performance is more complex. For unindexed queries, MariaDB performs better. For example, in a query test with level = info, MariaDB took 0.006 seconds, while MySQL took 0.049 seconds.''

But when queries involve indexes, the performance difference between the two narrows significantly. In the indexed time field maximum value query, MariaDB takes 0.001 seconds and MySQL takes 0.006 seconds.

Concurrent processing capabilities are another key indicator. According to research, when handling a large number of concurrent connections, MariaDB uses optimization techniques such as thread pools, which can provide better performance and throughput.

MySQL, on the other hand, is stable when processing large data sets with its mature optimizers and caching mechanisms.

High concurrency and cluster performance

As the scale of enterprise applications expands, the high concurrency processing capabilities of databases and cluster performance become crucial. In this regard, MySQL and MariaDB provide different solutions and performance characteristics.

MariaDB's Galera clustering technology provides strong support for it in high-availability scenarios. By checking specific wsrep database variables, you can monitor cluster replication throughput and performance.

Key metrics such as wsrep_local_recv_queue_avg (average local receive queue size) and wsrep_flow_control_paused (proportion of time flow control suspends nodes) can help administrators understand the health of the cluster.

When the wsrep_flow_control_paused value is higher than 0, it means that the flow control mechanism is suspending the node to manage the replication load. For example, if the value is 0.50 after 1 minute, it means that the node has 30 seconds to pause.

MySQL has invested a lot of R & D resources in Group Replication to provide another highly available solution. Different from MariaDB's clustering method, MySQL's Group Replication is based on the Paxos protocol and provides strong consistent data synchronization guarantee.

Tencent Cloud benchmark tests show that by optimizing parameter configuration, MySQL 8.0 can achieve significant performance improvements in high concurrency scenarios.

With an 8-core 32 GB memory configuration, MySQL 8.0 instances using high-performance parameter templates achieved 70,195.37 QPS (queries per second) and 3,509.77 TPS (transactions per second) performance in sysbench tests.

Storage Engine Comparison

Storage engines are the core component of database performance, and MySQL and MariaDB have adopted different strategies and development paths in this regard.

MySQL has long used InnoDB as its default storage engine, a choice that provides a solid guarantee in terms of transaction processing and ACID compatibility. InnoDB's row-level locking, foreign key support and crash recovery capabilities make it the first choice for enterprise-level applications.

MariaDB has adopted a more diversified storage engine strategy. It not only includes MySQL's InnoDB (provided under the name XtraDB), but also introduces a variety of dedicated storage engines to meet the needs of different scenarios.

Among them, the ColumnStore column storage engine is specially designed for analytical workloads and can significantly improve the performance of aggregated queries with large data volumes.

The Aria storage engine supports both transactional and non-transactional operations, providing more flexibility for specific use scenarios.

This diversification strategy allows MariaDB to better adapt to different types of workloads, but also increases learning and maintenance complexity.

Performance in different scenarios

Practical application scenarios vary widely, and MySQL and MariaDB perform differently under different types of workloads. Understanding these differences is critical to making the right selection decisions.

For OLTP (Online Transaction Processing) workloads, a study compared the performance of MySQL 5.6 and MariaDB 10.0.21 in a virtualized environment.

The test used OLTP-Simple and OLTP-Seats benchmark data, and the results showed that MySQL performed significantly better than MariaDB. In a 1000-thread OLTP-Simple test, MySQL's performance was almost twice that of MariaDB.

In web application scenarios, MariaDB performs even better. Due to its optimized thread pool and query executors, MariaDB tends to provide more stable performance when handling large numbers of simple queries and highly concurrent user requests.

Such scenarios are common in content management systems (such as WordPress), e-commerce platforms, and Social networks applications.

Both databases face challenges for complex analytical queries, but the solutions are different. MySQL 8.0 introduces advanced features such as window functions and common table expressions (CTEs) to enhance the ability to handle complex queries.

MariaDB provides specialized optimization for analytical workloads through the ColumnStore storage engine.

Performance in the cloud environment is also worthy of attention. UCloud's benchmark tests show that on MySQL instances with different configurations, performance is closely related to resource configuration. For example, a 16-core and 32 GB memory NVMe instance can achieve 58,015.18 QPS when processing 60 million data volumes.

Choose the most suitable database

Facing MySQL and MariaDB, how should companies make a choice? The answer depends on specific business needs, technical team capabilities and long-term development strategies.

If your application is already built on MySQL and needs maximum compatibility with the existing MySQL ecosystem, continuing to use MySQL may be a safe choice.

Especially when enterprises need enterprise-level support services provided by Oracle, MySQL Enterprise Edition provides additional functions and technical support guarantees.

MariaDB is an option worth considering for organizations that value full open source guarantees, require more storage engine options, and have the ability to handle possible compatibility issues.

The MariaDB community is active and security updates are frequent (usually released regularly every month), which helps quickly fix potential security vulnerabilities.

In actual migration decisions, the widening compatibility gap between the two also needs to be considered.

Although MariaDB was originally designed as an alternative to MySQL, it is no longer guaranteed to be fully compatible with MySQL. Migrating from MySQL to MariaDB requires additional testing, while reverse migration is more complex.

The technical team's expertise is also an important consideration. If the team has an in-depth understanding of MySQL, the learning curve for switching to MariaDB will be relatively smooth. But if the team needs to deal with MariaDB specific features, such as dynamic columns or specific storage engines, additional training investment is needed.

When database systems process tens of millions of concurrent requests, performance differences are no longer just a digital game. MariaDB fine-tunes data flow in the Galera cluster by monitoring the wsrep_flow_control_paused value, while MySQL Enterprise Edition relies on Oracle's commercial support network to ensure uninterrupted global business operations.

Data from cloud service providers reveals a direct correlation between resource configuration and performance-a 16-core and 32GB memory MySQL instance can achieve processing power of more than 58,000 QPS.

The real choice lies not only in technical indicators, but in which path will better carry the future of enterprise data.