Time for a database: mariadb vs time-tested mysql. Choose a database between mysql, postgresql, mariadb and mssql? Differences between mariadb and mysql

Which was soon itself purchased by Oracle). These are fairly justifiable doubts, which I will talk about a little later. Besides its role as a "lite version" of MySQL, MariaBD also has several new features that some say make it better than MySQL.

Before going into detail about these features, I want to talk about MariaDB's version numbering scheme. Firstly, its versions are the same as MySQL versions - for example, MariaDB 5.1 uses the same code base as MySQL 5.1. As patches are updated and added to the MySQL source tree, MariaDB will receive the same patches whenever possible (in theory, monthly merges with the MySQL code). But if new and unique features are constantly being added, I imagine maintaining this kind of code parity has become a nightmare.

The MariaDB development team must be aware of this since they decided to use new scheme numbering. The newest version of MariDB (which is still an alpha version) is Maria 10.0, followed by the minor device number:

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, 2013, 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)]>

The people working on MariaDB give a long and rather lengthy explanation of why they did this - which still frustrates some developers - but it is what it is. They can't keep adding new features and constantly claim that this is a faster, fully MySQL compatible version.

So what are the new features? Let's look at a couple of them.

Cassandra engine

One of the unique features of MariaDB is its engine for connecting to the server version of the Cassandra DBMS. The engine itself is simply an intermediary that connects to a Cassandra server running separately. Cassandra is a NoSQL data store that was originally created for Facebook, and later became an Apache project; Although it can be used in clusters without a single point of failure, it is still not ACID compliant. In general, if you are going to use Cassandra as your engine, don't expect the same performance speeds as InnoDB or ExtraBD.

But you can access information using MySQL by adding an SQL-like interface and also allowing selects, inserts, updates, deletes and even joins to some extent. However, the MariaDB team argues that the Cassandra engine is best not used for anything more significant than simply using data.

So this feature might be useful if you... um... let me think... well...

If you write software application which requires data access in Cassandra, then you might be better off using the built-in Cassandra API rather than MySQL. I suppose if you're struggling with the MySQL command line interface and need to grab some data, then Cassandra might be useful - but if you're going to use that, wouldn't it be easier to struggle with the interface? command line Cassandra?

So I'm actually not very sure about this option use, but this is what has dampened the enthusiasm for this feature among some people in the blogosphere.

OQGraph engine

I won’t talk too much about it, since the idea is the same as in Cassandra: the engine is simply an interface to the Open Query Graph computing engine (a repository for organizing complex graphs). This can help in some specialized applications, although adapting graph structures to SQL format is a little strange at first glance.

One major improvement that makes MariaDB more powerful is the use of XtraDB as an accelerated replacement for InnoDB. But XtraDB adds new scalability capabilities that modern applications need—and that's where the real difference lies. Oracle claims that this moment MySQL scales better than ever before. That may be true, but it's only as good as its engine. And if the engine doesn't scale as well as it should in practice, then MySQL won't be able to do it better.

Atomic write mode

One of the main reasons why you should choose a relational database over regular NoSQL is that a relational database is fully ACID compliant. Simply put, if any error occurs, no one wants all the data to disappear. And although errors on developer computers are not a frequent occurrence, they still occur in many IT centers. Currently, the standard InnoDB/XtraDB data writing engine uses double buffering to ensure successful data writing in case of any failure. However, when working with high-speed SSD devices (take this as an example), a double buffer can have a negative impact on performance, preventing you from using the full SSD speed. What solution? You can select one buffer and use Atomic Writes mode. Try it at your own risk and, better yet, not in production.

Again, the feature is interesting, but not enough to convince you to abandon MySQL and switch to MariaDB.

Performance Comparison of MySQL and MariaDB

Now I would like to draw your attention to the comparative tests conducted by the MariaDB team and add some comments. This blog makes an interesting point: if there are fewer than 16 threads, MySQL performs well, and if there are more - although of course, performance continues to improve slightly, but not as good as other versions it has been compared to (including MariaDB- 5.5.28a and MariaDB-10.0.1; see the performance test graph at the beginning of this article). This is a fairly common problem in parallel programming when trying to target multiple cores and threads within a core. If the constructed algorithms are correct, then you will feel the benefits as the cores increase. The problem is that you will have to use 2 methods in your parallel programming: 1) multi-threaded on several cores and 2) vectorization. These techniques are two sides of current multi-core programming, and your code must use them correctly.

One of the most common results of bad coding is that you will see a performance gain for the first 8 or 16 threads, after which there will be no improvement. If you have such a problem, then most likely the problem is in the algorithms. And this will be the case with either hyperthreads or hardware threads. This is exactly what we see in MySQL tests. To me this means that there are scaling issues in MySQL and that's something to think about. In the same test, MariaDB also had some problems because... productivity decreased, but only slightly; I'm guessing this doesn't apply to parallel algorithms.

I also don't know how well some versions matched the computers used to run the test. When compiling Intel code, you want the compiler to generate SIMD code of a size suitable for the target machine. If there is a mismatch, you will not get the expected performance from your vectorization code. To do this correctly, you will need to insert the necessary pragmas into the code, then write the vectorization algorithms correctly, and finally run the appropriate compiler options. I know it sounds stupid, but I've seen programs published with the wrong options more often than you might think. In any case, pure MySQL code was not as optimized to support multi-cores and vectorization as MariaDB.

What I'd most like to see is a flavor of either MySQL or MariaDB compiled specifically for the Intel Xeon Phi coprocessor, where the code offloads the 61-core coprocessor and someone tries to spin up all 244 threads. Unfortunately, I don't have access to such a machine. Also, if you want to learn more about vectorization and parallel coding, check out the latest book by Intel staffers James Jeffers and James Reinders, High Performance Programming for the Intel Xeon Phi Coprocessor.

Should you switch?

Obviously, the new MariaDB features aren't all that magical - you might need to access some Cassandra data, but I doubt you'll use MySQL to do that. The same applies to other engines on this platform. MariaDB performance is slightly better on multi-core computers, however, I believe that MySQL can be configured for this.

So should you switch to MariaDB?

First, think through all the possible risks (senior managers love to hear about risks and benefits). If you move to MariaDB, you may end up using features that are only available in MariaDB (which is unlikely at this time) and then find it impossible to switch back to MySQL without some effort. But I would venture to suggest that this is not such a risk, given some of the larger problems.

Ponder all the questions about Oracle and its MySQL licensing plans. Open source MySQL goes against proprietary and very competitive programs. This alone is a reason to think - will Oracle do anything to somehow interfere with the development of MySQL? Some argue that this is already happening.

What about MySQL and MariaDB compatibility? The MariaDB team is working hard to make the database fully compatible with MySQL, and they continue to work out bugs in the source code. However, the new features (as well as the version numbering scheme) suggest that, despite best efforts, the two platforms will be very different.

If Oracle adds some new features to MySQL that are not supported by MariaDB, then obviously they will not be available to you. And if you use features that MySQL doesn't have, you won't be able to switch back to it, given that you had reasons to switch to another platform. MariaDB shows every sign of being quite for a long time will be in use, which cannot be said about MySQL. In other words, even though MariaDB's new features may not be useful to everyone, in my opinion there are more than enough reasons to ditch MySQL and move to MariaDB entirely.

One little note before I finish. Some bloggers have raised a good point about service agreements. If anyone in your company has bought a service agreement from Oracle to help you with MySQL, you may not want to switch to MariaDB to avoid the financial and legal issues that arise if you break the agreement. Other than that, I don't see any compelling reason to continue using MySQL.

The original version of MySQL was developed by the Finnish-Swedish company MySQL AB, which was founded by Jvid Ahmark, Allan Larsson and Michael Monti. The first version of MySQL appeared in 1995. Initially it was intended for personal use, but after a few years it turned into an enterprise-level database.

In January 2008, Sun Microsystems acquired MySQL AB for $1 billion. Soon after, Oracle bought Sun Microsystems with the permission of the European Commission, which initially feared that such a decision would harm the free MySQL project, since it was a direct competitor to Oracle's database management system. Due to mistrust of MySQL's development strategy, a fork called MariaDB was created.

As the years passed, MariaDB began to be used by default in many Linux distributions. It is used to power most Internet sites. In this article we will try to compare MySQL vs MariaDB and figure out why the second is better than the first and when the original MySQL is needed.

Unlike many other open source projects originating from Sun Microsystems, Oracle is still developing MySQL. After many developers resigned, new people were hired. But the development of new versions of MySQL is closed. Source is accessible only to the development team and is uploaded to the public repository only after completion of work. All decisions are discussed within the company

MariaDB is being developed completely openly, all solutions and new ideas regarding development can be freely discussed in the email newsletter, as well as in the bug reporting system. It is very easy to help develop MariaDB; patches from users are accepted as well as from developers. In general, MariaDB is developing more actively.

Due to the branding, MySQL still has a large community, but more and more projects are moving to MariaDB. Well-known enterprise distributions such as REHL 7 and SLES 12 already use MariaDB, which means that in a battle between MySQL and MariaDB, the latter will win.

2. Release frequency

Oracle's policy is to release security updates for all of its products every three months. But the way out new version MySQL is scheduled every two months. This often results in product updates and security updates being out of sync.

Developers do not have time to close all error messages and vulnerabilities, as a result of which the database may remain vulnerable for several months. Another problem with MySQL is that security updates are very vague. If the administrator cannot simply update the program to a new version, then creating a backport is difficult.

MariaDB releases program updates and security updates in a synchronized manner, so all bugs are fixed in time. All fixed CVEs are documented and any user can find out what has changed in the new version.

4. Features and functionality

Overall, MariaDB is developing faster and has more features. These features relate to optimization, improving memory performance, and much more. Typically, over time, these capabilities are migrated to MySQL. For example, the same GIS support appeared in MariaDB earlier than in MySQL. Among other things, MariaDB has many performance improvements to Inodb, MyISAM and the query engine, supports GIS, table liquidation, virtual and dynamic columns, multi-source replication, roles and much more.

But MariaDB also has its downsides; it does not support some features that MySQL has. Namely, MariaDB is not compatible with MySQL's JSON syntax, ngram, MeCab, MySQL X plugins are not supported, as well as tablespaces that allow you to assign data to multiple tables at the same time. But the developers are actively working to correct the shortcomings.

For those interested in MySQL clusters, it will be interesting to know what is used in MariaDB new system Galera replication, its operation differs from the standard master-salve. Galera has been in development since 2007, but has never been included in official version MySQL.

5. Support for storage engines

The MariaDB database management system supports many more data storage engines. Most of these engines are available as plugins for MySQL, but in MariaDB they are included in the official release. This means that the engines are properly integrated and will work well. Here is a list of supported engines:

• Aria;
• XtraDB - improved version of InnoDB;
• FederatedX - improved version of Federated;
• OQGRAPH;
• SphinxSE;
• IBMDB2I;
• TokuDB;
• Cassandra;
• CONNECT;
• SEQUENCE;
• Spider;
• ColumnStore;
• MySIAM.

Let me remind you that the original MySQL supports by default only three types of tables - Aria, MySIAM and InnoDB. This is an important aspect when choosing MySQL or MariaDB.

6. Name and numbering of versions

These differences between MariaDB and MySQL are not so important, but perhaps they will be interesting to someone. The name MySQL was given in honor of the first daughter of one of the developers - Michael Monti, her name is My. The development of MariaDB was continued by the same person, and this time the program was named after his youngest daughter, Maria.

As for versions, initially, until version 5.6, MariaDB versions were numbered synchronously with the MySQL versions on which they were based. But when enough changes had accumulated and MariaDB code began to be taken as a basis, it was decided to change the version numbers to 10. From that moment on, MariaDB numbering is done only this way.

conclusions

In this article we have made a comparison of MySQL vs MariaDB. MariaDB is much better than MySQL in most respects, so it's no wonder that most Linux distributions now use it by default in their repositories. The original version may only be needed in very rare cases.

MySQL has become the property of Oracle, are there any alternatives and how quickly can we move forward?.. Sort of like a general reviewer of “who is who?” hasn't happened yet. So, a reviewer for those who are “not in the know”

Some people, and many simply are not happy that MySQL became owned by Oracle. Fortunately, we already live in a world where information travels at the speed of printing, thoughts and decisions are made at lightning speed.

Michael Widenius, founder of MySQL and founder of MySQL AB (which was acquired by Sun, which was acquired by Oracle)
Petr Zaitsev - MySQL performance expert, former team leader of the High Performance group at MySQL Inc, host of the blog MySQLPerformanceBlog.com

So what are the alternatives?

Percona server is a MySQL build (from Peter Zaitsev and co) with the XtraDB storage engine enabled by default. It differs from the MySQL+InnoDB plugin in better performance/scalability, especially on modern multi-core servers. The functionality has also been improved - more useful for optimizing statistics, etc. It is assembled in versions based on MySQL 5.0 and 5.1. Fully compatible with innodb tables, meaning you can move from innodb to xtradb and back without problems (unless you use some xtradb-specific features, such as smaller page sizes).

The XtraDB storage is based on the InnoDB-plugin code, is fully compatible with it, but has noticeably higher performance thanks to the integration of patches from Google companies and Percona. In particular, XtraDB has improved the memory handling mechanism, improved the operation of the InnoDB I/O subsystem, added support for multiple reading and writing threads, and management support throughput, implementation of forward data sampling (read-ahead), adaptive installation checkpoints (adaptive checkpointing), expanded scaling capabilities for big projects, the locking system has been adapted to work on systems with a large number of CPUs, added additional features for accumulation and analysis of statistics

MariaDB- assembly from Monty, synchronized with the code MySQL database and is fully compatible with it, i.e. can act as a transparent replacement for MySQL 5.1, while having a number of advanced features, including performance optimizations and comes with a set of additional storage engines:

• New data stores:
  • Aria (formerly Maria) - highly reliable storage based on MyISAM, characterized by increased stability and preservation of data integrity after a crash, with full compatibility with MyISAM
  • OQGRAPH (a repository for organizing complex graphs)
  • Sphinx - a repository for building search engines
  • PrimeBase XT - description in Russian
  • The XtraDB engine is used as a replacement for InnoDB
  • FederatedX - allows you to organize access to remote tables as local ones
• MyISAM engine patches - Segmented cache (at high loads it gives a significant increase)
• Eliminating tables - the new kind optimizing queries using JOIN
• Thread pool - now more than one thread can be opened per connection
• Improved

After a year and a half of development and five preliminary releases, the first stable release of the new branch of the MariaDB 10.2 DBMS has been formed, within which a branch from MySQL is being developed, preserving backwards compatible and featuring the integration of additional storage engines and advanced capabilities. MariaDB development is overseen by the independent MariaDB Foundation, following a completely open and transparent development process that is independent of individual vendors. MariaDB is supplied instead of MySQL in many Linux distributions (RHEL 7, SUSE 12, Fedora, openSUSE, Slackware, OpenMandriva, ROSA, Arch Linux, Debian 9) and has been implemented in such large projects as Wikipedia, Google Cloud SQL and Nimbuzz.

Key improvements in MariaDB 10.2:

• Added experimental support for Facebook's MyRocks storage engine, based on the Flash-optimized RocksDB storage system. MyRocks storage uses floating-size data pages to avoid alignment on a fixed block boundary, and a Log Structured Merge Trees data storage model that allows append only (cleanup is done by garbage collection). In the process of executing queries, the number of sequential read/write operations was reduced several times, which led to an increase in performance compared to InnoDB by 20-30% on SDD and up to 6 times on HDD under load with a large number of random write operations. In addition, MyRocks allows you to reduce the size of the database by 50% compared to compressed InnoDB storage and 3.5 times compared to InnoDB without compression. The disadvantages of MyRocks include the lack of support for foreign keys and full-text indexes;
• Added support for window functions specified keyword OVER and allow you to perform a calculation on a set of rows associated with the current row. Similar to aggregate functions, window functions allow you to access other rows while processing a query result, but unlike aggregate functions, they do not group the result into one row;
• Support for common table expressions ("WITH" expression) and recursive common table expressions ("WITH RECURSIVE"). The WITH clause can be used to define subqueries as local temporary tables that can be referenced multiple times throughout the query. “WITH RECURSIVE” allows you to access your own result, for example, you can organize a tree traversal while executing a query;
• Added "CONSTRAINT... CHECK" expression in the "CREATE TABLE" block to set column constraints;
• Implemented the ability to specify expressions in the DEFAULT block, for example “b int DEFAULT (a+1)”. Support for specifying DEFAULT values ​​for BLOB and TEXT fields is provided;
• The InnoDB storage has been updated to the release from MySQL 5.7.18 and is enabled by default (previously, a fork from InnoDB, XtraDB, was offered by default, the meaning of which was lost after most of the main features of XtraDB were implemented in InnoDB). Added support for spatial indexes to InnoDB;
• Added a "SHOW CREATE USER" statement that shows the full "CREATE USER" statement used to create the specified user;
• For the "CREATE USER" expression, options have been implemented to limit resource consumption and configure tls/ssl. For example, you can now limit maximum number requests or connections per hour;
• A new "ALTER USER" expression has been introduced to allow changes to be made to account existing user;
• Many restrictions on virtual calculated columns have been removed;
• Added support for the "EXECUTE IMMEDIATE" expression to run a dynamic SQL statement created on the fly;
• Added the ability to use most expressions in the PREPARE statement;
• Added functions for working with data in JSON format;
• Added an authentication plugin that uses the ed25519 algorithm to store passwords;
• A plugin has been added to the builds for Windows, CentOS, RHEL and Fedora for decrypting keys used in Amazon Web Services (AWS) Key Management Service (KMS), for their subsequent use to encrypt data in the database;
• It is now possible to link several different triggers to one event;
• Added support for delayed replication, in which the state of the slave server lags behind the master server for a specified period of time;
• The implementation of the ANALYZE TABLE expression has been reworked, which now does not lock the table during statistics collection;
• The wsrep library, used to organize synchronous multi-master (active-active) Galera replication, has been updated to release 25.3.20;
• Ensured the formation of packages for Ubuntu 17.04;
• The option “--add-drop-trigger” has been added to mysqldump, reproducing the functionality of MySQL 5.6 to add an expression to the SQL dump to remove a trigger before creating it;
• Added mysqlbinlog script for organizing continuous backup of the binary log;
• Added support for OpenSSL 1.1 and LibreSSL;
• Added variables innodb_deadlock_detect and innodb_stats_include_delete_marked to disable the system for detecting mutual locks and taking into account records marked as deleted when calculating statistics;
• Added the read_binlog_speed_limit variable, which sets the speed limit at which the slave server reads the master server's binary log;
• The old GPL-licensed client library has been removed and replaced by a new LGPL-licensed library.

Are you still using MySQL?
And many people have long switched to the MariaDB database.
Look how beautiful the MariaDB logo with the seal is.

MySQL also has a pretty nice dolphin

A little history of MariaDB from Wikipedia.
MariaDB is a community-developed offshoot of MySQL. The impetus for its creation was the need to ensure the free status of the DBMS (under the GPL license), as opposed to the vague licensing policy of MySQL by Oracle.

The lead developer of MariaDB is Michael Widenius, who is also the author of the original version of MySQL and the founder of Monty Program AB.

The main goal of the MariaDB project is to create a completely binary compatible version of the DBMS with the original MySQL, which will also have a significant number of improvements in the code that affect performance.

MariaDB is designed as a drop-in replacement for MySQL, completely simulating the behavior of MySQL (and it really is).

MariaDB is a promising trend now, look at the comparison table between MySQL and MariaDB (especially the 10.x branch)

I took the comparison from the official MariaDB website, where you can look at the points in more detail. Although even without this it is clear that MariaDB 10.x is clearly in the lead.

Now many Linux distributions install MariaDB by default instead of MySQL and the secret here is simple - MariaDB supports the same data storage formats, tables and even launch commands, etc., i.e. migration to MariaDB is easy and seamless. Installing MariaDB is also easy and simple on FreeBSD.

For example, the Aria table type is optimized for insertion operations into the database (they are noticeably faster than on MyISAM). Aria tables are also used in MariaDB for internal processes; all temporary tables run on Aria, which improves performance on complex queries.

Phrases: comparison of MariaDB and MySQL, which is the better database?, speed of operation, installation of MariaDB