A relational database management system (RDBMS) is a database management system that is based on the relational model as invented by E. F. Codd. Today’s most databases are based on the relational database model.
The database management systems are responsible for interacting with the user, other applications, and the database itself to capture and analyze data. Accoring to db-engines.com the most popular DBMS systems are: Oracle, MySQL, Microsoft SQL Server, PostgreSQL and MongoDB.
Oracle and MySQL is indisputably the first ones in popularity, in the new Debian (Stretch) MariaDB replaces MySQL as the default database variant. Let’s see the major differences between the two open-source RDBMS’s.
MySQL was created by a Swedish company, MySQL AB, founded by David Axmark, Allan Larsson and Michael “Monty” Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL launched on 23 May 1995. MySQL continued to spread and attract users worldwide.
In 2008 Sun Microsystems acquired MySQL and Oracle bought Sun Microsystems on 27 January 2010. On the same day Michael “Monty” Widenius left MySQL, launching MariaDB and took developers with him. The founders Michael Widenius, David Axmark, and Allan Larsson accnounced the creation of a foundation that would oversee the development of MariaDB. MariaDB’s version numbers follow numbering scheme up to version 5.5.
They offered the same features in those versions for better intercompatibility. After version 5.5 MariaDB released version 10.0 to make it clear that version 5.6 will not import features from MySQL 5.6. They will still include backported features from MySQL, and include entirely new features not found anywhere else, but does not necessarily include all MySQL features.
Overview (see at)
|Description||MySQL application compatible open source RDBMS, enhanced with high availability, security, interoperability and performance capabilities||Widely used open source RDBMS|
|Database model||Relational DBMS||Relational DBMS|
|Developer||MariaDB Corporation Ab (MariaDB Enterprise), |
MariaDB Foundation (community MariaDB Server)
|Current release||10.2.6, May 2017||5.7.18, April 2017|
|License||Open Source||Open Source|
|Implementation language||C and C++||C and C++|
|Server operating systems||FreeBSD|
|APIs and other access methods||ADO.NET|
|Supported programming languages||Ada|
|Partitioning methods||Horizontal partitioning, sharding with Spider storage engine or Galera cluster||Horizontal partitioning, sharding with Spider storage engine or Galera cluster|
|Replication methods||Master-master replication|
|User concepts||Users with fine-grained authorization concept, user roles and pluggable authentication||Users with fine-grained authorization concept|
More Storage Engines
- ColumnStore, a column oriented storage engine optimized for Data warehousing.
- MyRocks, a storage engine with great compression, in 10.2
- Aria, MyISAM replacement with better caching.
- FederatedX (drop-in replacement for Federated)
- OQGRAPH (In MariaDB 5.2 and later. Disabled in MariaDB 5.5 only.) — new in 5.2
- SphinxSE — new in 5.2
- TokuDB in MariaDB 5.5 and later.
- CONNECT in MariaDB 10.0 and later.
- SEQUENCE in MariaDB 10.0 and later.
- Spider in MariaDB 10.0 and later.
- Cassandra in MariaDB 10.0
Parallel replication. — new in 10.0
Improvements for Innodb asynchronous IO subsystem on Windows.
Segmented Key Cache for MyISAM. Can speed up MyISAM tables with up to 4x. — new in 5.2
Adjustable hash size for MyISAM and Aria. This can greatly improve shutdown time (from hours to minutes) if you are using a lot of MyISAM/Aria tables with delayed keys. — new in 10.0.13
CHECKSUM TABLE is faster.
Improved the performance of character set conversions (and removed conversions when they were not really needed). Overall speed improvement is 1-5 % (according to sql-bench) but can be higher for big result sets with all characters between 0x00-0x7f.
There are some improvements to the DBUG code to make its execution faster when debug is compiled in but not used.
Our use of the Aria storage engine enables faster complex queries (queries which normally use disk-based temporary tables). The Aria storage engine is used for internal temporary tables, which should give a speedup when doing complex selects. Aria is usually faster for temporary tables when compared to MyISAM because Aria caches row data in memory and normally doesn’t have to write the temporary rows to disk.
The test suite has been extended and now runs much faster than before, even though it tests more things.
Extensions & new features
They have added a lot of new features to MariaDB. If a patch or feature is useful, safe, and stable — they make every effort to include it in MariaDB. The most notable features are:
- For a detailed breakdown of system variable differences, see:
- System variable differences between MariaDB 10.2 and MySQL 5.7
- System variable differences between MariaDB 10.1 and MySQL 5.7
- System variable differences between MariaDB 10.1 and MySQL 5.6
- System variable differences between MariaDB 10.0 and MySQL 5.6
- System variable differences between MariaDB 5.5 and MySQL 5.5
- For a detailed breakdown of function differences, see:
- Microseconds in MariaDB — new in 5.3
- Number of supported decimals in DECIMAL has increased from
38. — new in 10.2
- Microsecond Precision in Processlist
- Table Elimination
- Virtual Columns — new in 5.2
- CHECK CONSTRAINT — new in 10.2
- DEFAULT expression, including
TEXT. — new in 10.2
- Extended User Statistics — new in 5.2
- KILL all queries for a user — new in 5.3,
- KILL QUERY ID – terminates the query by query_id, leaving the connection intact — new in 10.0.5,
- Pluggable Authentication — new in 5.2
- Storage-engine-specific CREATE TABLE — new in 5.2
- Enhancements to INFORMATION SCHEMA.PLUGINS table — new in 5.2
- Group commit for the binary log. This makes replication notably faster! — new in 5.3
--rewrite-dbmysqlbinlog option to change the used database — new in 5.2
- Progress reporting for
LOAD DATA INFILE. — new in 5.3
- Faster joins and subqueries. — new in 5.3
- HandlerSocket and faster HANDLER calls. — new in 5.3
- Dynamic Columns support. — new in 5.3
- GIS Functionality — new in 5.3
- Multi-source replication. — new in 10.0
- Global Transaction ID. — new in 10.0
- SHOW EXPLAIN gives the EXPLAIN plan for a query running in another thread. — new in 10.0
- Roles. — new in 10.0
- PCRE Regular Expressions (including
REGEXP_REPLACE()). — new in 10.0
- DELETE … RETURNING. — new in 10.0
For a full list, please see features for each release
More tests in the test suite.
Bugs in tests fixed.
Test builds with different configure options to get better feature testing.
Remove invalid tests. (e.g. don’t test feature ”X” if that feature is not in the tested build)
Fewer warnings and bugs
Bugs are bad. Fix as many bugs as possible and try to not introduce new ones.
Compiler warnings are also bad. Eliminate as many compiler warnings as possible.
Truly Open Source
All code in MariaDB is released under GPL, LGPL or BSD.
MariaDB does not have closed source modules like the ones that can be found in MySQL Enterprise Edition. In fact, all the closed source features in MySQL 5.5 Enterprise Edition are found in the MariaDB open source version.
MariaDB client libraries (for C, for Java (JDBC), for Windows (ODBC)) are released under LGPL to allow linking with closed source software. MySQL client libraries are released under GPL that does not allow linking with closed source software.
MariaDB includes test cases for all fixed bugs. Oracle doesn’t provide test cases for new bugs fixed in MySQL 5.5.
MariaDB is developed by the community in true open source spirit.
We found two benchmarks comparing MariaDB 10.1 versus MySQL 5.7. While both parties claiming awesome performance let’s have a look on the numbers before you can decide. The first benchmark is from MariaDB, so observe carefully the internal testing results.
They used an Intel XEON E5-2690 @ 2.9 GHz with 32 cores, Linux as the operating system with 132 GB main memory. The database is stored on a Fusion-IO ioDrive2 Duo 2.41TB Firmware v7.2.5, rev 110646, using Driver 3.3.4 build 5833069. The database file system is using NVMFS and all test logs and outputs are stored on EXT4.
They chose LinkBench and TPC Benchmarks for comparing the two RMDBS systems. The first one provides a test for persistent storage of social and web service data, while the second simulates a warehouse application. They set up the tests so that the database will not fit in the main memory. For more details and settings visit the full post here.
The first results (source: mariadb.org)
In the second benchmark they are using an IBM Power 8 S822LC system with dual 10 core processors running at 2.92 GHz running in SMT8 mode (160 threads), with 256 GB memory, where the whole database can fit in the main memory. The operating system they used was Ubuntu 15.10. They chose to mesure the performance of MySQL and MariaDB on Prestashop 220.127.116.11 which includes their optimizations to improve both the single-threaded and multi-threaded performances. You can check their page for more details and settings.
The fact, that MariaDB replaces MySQL in the new Debian release is interesting. Maybe some developers will prefer it due to the more open, more community based development. The separation of feature implementing can bring some really interesting things in the future as the two RMDBS evolves. The banchmarks are there, you can choose your preference based on your needs.