MariaDB versus MySQL
From Askmonty.org
See also:
About MariaDB
MariaDB 5.1 is based on MySQL 5.1 and is available under the terms of the GPL v2 license.
MariaDB will be kept up to date with the latest MySQL release from the same branch.
In most respects MariaDB will work exactly as MySQL: all commands, interfaces, libraries and APIs that exist in MySQL also exist in MariaDB.
Currently the main differences between MariaDB 5.1 and MySQL 5.1 are:
- Maria storage engine is included
- PBXT storage engine is included
- XtraDB storage engine is included
- FederatedX storage engine is included
- Faster complex queries
- Pool of Threads
- Fewer warnings and bugs
- Speed improvements
- Extensions (More index parts, new startup options etc)
- Better testing of features
- Table elimination
- Slow Query Log Extended Statistics
- User profiling (SHOW PROFILE) is compiled in by default.
In MariaDB 5.2, the new features are:
- Group commit for the Maria storage engine. (faster)
- userstatv2', which creates several new information_schema tables with useful statistics information. You enable the statistics by setting the 'userstat' variable.
(for a definite and up-to-date list of features in 5.2, see MariaDB 5.2 page).
Features in MariaDB 5.1.38
Maria storage engine is included
The Maria storage engine version 1.5 (the crash-safe version) is included in the source and binaries by default.
If you use the source, you can of course easily disable the Maria storage engine when configuring MariaDB.
The new Maria storage engine specific options can be found here: Manual:Maria storage engine.
XtraDB storage engine is included
Percona XtraDB version 6 replaces InnoDB in the MariaDB 5.1 tree.
XtraDB is a drop in replacement of InnoDB (same table formats, no need to convert any data).
XtraDB gives you similar performance improvements for multi-cpu systems in MariaDB 5.1 that you can expect from using InnoDB in MySQL 5.4.
See also: Manual:XtraDB storage engine
PBXT storage engine is included
The PBXT storage engine is included in the source and binaries by default.
See also: Manual:PBXT storage engine
Faster complex queries
Our use of the Maria storage engine enables faster complex queries (queries which normally use disk-based temporary tables).
The Maria storage engine is used for internal temporary tables, which should give you a speedup when doing complex selects. Maria is usually faster for temporary tables when compared to MyISAM because Maria caches row data in memory and normally doesn't have to write the temporary rows to disk.
Pool of Threads
Limited sets of threads handling all queries.
Fewer warnings and bugs
- Fewer warnings when compiling. We believe that compiler warnings can indicate bugs, and strive toward reduction to 0.
- Fewer bugs. If we see a bug while fixing a warning, cleaning up code or adding more test to test suite, we'll fix it when possible.
- Extended test suite to cover more code and with fewer timing dependencies.
Speed improvements
- There are some improvements to DBUG code to make its execution faster when debug is compiled in but not used.
- CHECKSUM TABLE is faster. See: Manual:CHECKSUM TABLE for details.
- We have eliminated/improved some not needed character set conversions. Overall speed improvements is 1-5 % (according to sql-bench) but can be higher for big results sets with all characters between 0x00-0x7f.
Extensions
- MariaDB can handle up to 32 key segments per key (up from 16)
- Added a new handler function: prepare_index_scan() that is called before a key scan is done.
- Added --abort-source-on-error to the mysql client.
Better testing of features
- Wrong mutex usage detector. This helps us find and fix deadlocks when taking mutex in inconsistent orders. In MariaDB we have removed several deadlocks which exist in the normal MySQL code.
- More tests in the test suite.
- Test builds with different configure options to get better feature testing.
Table elimination
Implementation of MWL:17: Table elimination.
See Manual:Table_Elimination for details.
Slow Query Log Extended Statistics
This is based on the microslow patch from Percona.
See Manual:Slow Query Log Extended Statistics for details.
Microsecond Precision in Processlist
This is based on the microsec_process patch from Percona.
See Manual:Microsecond Precision in Processlist for details.
Features in MariaDB 5.1.39
FederatedX storage engine is included
The FederatedX storage engine replaces the old, not maintained, Federated storage engine.
See also: Manual:FederatedX storage engine
Things to add to MariaDB 5.1 before stable release
See MariaDB_5.1_TODO for the list
Incompatibilities between MariaDB 5.1 and MySQL 5.1
Our aim is to make MariaDB a full drop in replacement for MySQL. This means:
- From the user standpoint, things should be the as before, except that there are some new commands and things should be faster and more reliable.
- Only that package name is different; All filenames, binaries, paths, ports, sockets etc should be the same.
- Client libraries are binary compatible.
- Data and table definition files (.frm) files are binary compatible.
- Command should work the same.
However, in some few cases we have to be incompatible as we want to provide more and better information than MySQL.
Here we will list all the known user level incompatibilities that you may see when using MariaDB 5.1 instead of MySQL 5.1.
- You can't use a binary only storage engine with MariaDB if it's not compiled for exactly the same MariaDB version. (This is because the server internal structure THD is different between MySQL and MariaDB. This is common also between different MySQL versions).
- CHECKSUM TABLE may give different result as MariaDB doesn't ignore NULL's in the columns as MySQL 5.1 does (Future MySQL versions should calculate checksums the same way as MariaDB). You can get the 'old style' checksum in MariaDB by starting mysqld with the --old option. Note however that that the MyISAM and Maria storage engines in MariaDB are using the new checksum internally, so if you are using --old, the CHECKSUM command will be slower as it needs to calculate the checksum row by row.
- The slow query log has more information about the query, which may be a problem if you have a script that is parsing the slow query log.
What kind of patches can be considered for MariaDB 5.2
The idea is that while the Maria storage engine is being tested we will add patches to MariaDB of significant interest to users and developers alike. These include patches both Monty Program Ab and the Maria community feel should have been added to MySQL a long time ago. We'd like your opinions.
If you have a patch that you would like to get into MariaDB, please submit them to the maria-developers email list.
