Comparison between MariaDB and MySQL

Now choose to continue using MySQL or discard itThere is enough reason to switch to MariaDB.

Performance tests on the MariaDB blog.

MariaDB is a branch of MySQL source code. After realizing what Oracle will do for MySQL license, MariaDB is separated and MySQL has been acquired by Sun and Oracle ). These concerns are well-founded, and I will discuss them later in this article. In addition to being a "downward alternative" to Mysql, some new features included in MariaDB make it better than MySQL.

Before introducing these features, I 'd like to talk about MariaDB's version number mode. First, the MariaDB version matches the Mysql version-for example, MariaDB 5.1, and MySQL 5.1 uses the same code. Because the updates and fixes are for the MySQL source code tree, MariaDB can adopt these patches. In theory, MariaDB merges with the MySQL source code every month ). However, if new unique features are added on a regular basis, I think code verification will be a nightmare.

The MariaDB team should also be aware that they have begun to develop new numbering schemes. The latest MariaDB version is still in alpha state) is Maria 10.0, followed by a decimal point:

Mysql-P 3406-u root-p Enter password :******** Welcome to the MariaDB monitor. Commands end with; or \ g. Your MariaDB connection id is 1 Server version: 10.0.2-MariaDB mariadb.org binary distribution Copyright (c) 2000,201 3, Oracle, Monty Program AB and others. Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement. MariaDB [(none)]> select version (); + ------ + | Version () | + ------ + | 10.0.2-MariaDB | + ------ + 1 row in set (0.01 sec) MariaDB [(none)]>

MariaDB repeatedly and occasionally explained why they did this-although this made developers feel uncomfortable, it did. If they are still fully compatible with Mysql, they cannot add new features.

As for new features? Let's take a look at several of them.

Cassandra Engine

MariaDB's unique feature is its connection to the Cassandra backend engine. The engine itself is only a separate running intermediary added to the Cassandra server. Cassandra is a NoSQL-type key-value storage, which was created by Facebook and later became an Apache project; it can be used for clusters without spof, and it is not completely ACID ). Generally, if you use the Cassandra engine, you will not be able to get the same speed or performance as InnoDB or extradb.

However, you can access data through MySQL and allow selects, inserts, updates, deletes, or even extended join operations, just like using SQL, however, the MariaDB team said that the Cassandra engine is not suitable for analysis queries with large amounts of data.

So this function may be useful if you ......, Well, let's continue watching ......

If you are writing a data application that needs to access Cassandra, you 'd better directly use the native API of Cassandra instead of using MySQL. If you focus on the MySQL command line and need to capture some data, the Cassandra engine can be used in this case-but if you want to do so, you may just want to try Cassandra's command line interface.

So I really don't know which one is applicable, but it doesn't prevent some people from expressing their excitement about this feature on their blogs.

OQGraph Engine

I will not introduce all of this feature, because it shares the same idea with Cassandra: this engine is an open query interface of the graph computing engine. This may be useful for some specific applications, although it seems odd to map a graphic structure to an SQL format.

An important enhancement of MariaDB is the use of xtradb as a downward replacement of InnoDB. In addition, xtradb adds the scalability required by modern software, which is the core difference. Oracle claims that MySQL is more extensible than before, but the engine is still that engine. If the engine cannot be fully scalable, MySQL does not.

Atomic write

The main reason for choosing a relational database instead of NoSQL is that the former fully supports ACID. Simply put, if a failure occurs, you do not want to lose data. Although faults may not often occur on our development machines, they are common in many IT centers. Currently, the default InnoDB/XtraDB engine uses a dual-buffer method to write data to ensure that data can be written successfully during crash. However, when a high-speed SSD device is used, the dual-Cache has a negative impact on performance, which reduces the SSD access speed advantage. What is the solution? Now you can turn off the double buffer to open the so-called Atomic write. Verify it on your disk before applying it to the production environment.

It is also an interesting feature, but it is not the feature that allows you to switch to MariaDB to discard MySQL.

Performance Comparison Between MySQL and MariaDB

Now let's move our eyes to benchmark. It was actually developed by the MariaDB team and added some additional instructions. This blog mentions an interesting point: the number of MYSQL5.6 threads has been increased to 16, and the performance is very good. However, if the number of MYSQL5.6 threads exceeds 16, although the performance has been improved a little, it is found that, far less than other versions (including MairaDB-5.5.28a and MairaDB-10.0.1; refer to the Performance Testing diagram at the top of the article ). This common problem occurs when a single-core computer tries to achieve multi-core and multi-threaded parallel program. If the algorithm is properly designed, the performance will also improve as the number of CPU cores increases. Of course, the problem is that you must handle two aspects in parallel programs: (1) Cross-core multithreading (2) vectoring. This is also the current multi-core programming oriented direction, you must be able to control these two aspects.

If you do not write the code correctly, you will get a common result, that is, when you start with 8 to 16 threads, you will want to see a good result, however, after these threads run, you will not see the expected results. You will see this problem, which means it may be an algorithm problem. (This is not caused by hyperthreading or hardware threads.) This is where we can see the MySQL Benchmark problem. For me, this is a sign of problems with the scale of MySQL, which is also a worrying reason. MariaDB also has some minor problems in the same benchmark, but it is much more minor than MySQL. It can only be said to be stubborn. I guess this problem may not occur in parallel computing.

I also don't know how to match different compilers according to different machines in the test. When you compile code for Intel, You need to compile the appropriate SIMD code for the target machine. If the Code does not match, you will not get the vector code you expect to execute. To handle the problem correctly, you need to insert the correct Compilation instruction code into the code, write down the correct vector algorithm, and finally select the appropriate compiler. I know it looks stupid, but I have seen the result of a wrong compiler for a release product that you cannot imagine. Obviously, the optimization of MySQL code in multi-core and vectoring is not as good as MariaDB.

(What I really want to see is that a branch of MySQL or MariaDB makes a special compilation for the Intel Xeon Phi processor core and transfers the code to the 61 core coprocessor, and someone can try to speed up all 244 threads. Unfortunately, I have never touched such a machine. Similarly, if you want to learn