MariaDB 10.0 New Features
MariaDB 10.0 is based on MariaDB 5.5 with backported features from MySQL 5.6 and completely new features added to MariaDB 10.0.
MariaDB had been released with a corresponding version number with MySQL, but since 10.0, MariaDB has been released with the independent version numbers as a lot of new unique features were implemented - yet maintaining the compatibility with MySQL. This page introduces the main features added to MariaDB10.0.
MariaDB 10.0 original new features
MariaDB replication is divided broadly into 3 steps.
- Read plural replication events from master side at IO thread and make them wait in relay log
- Each replication event is fetched one by one from relay log by SQL thread
- Each event is applied to slave side, and all the changes of master side are copied
On the 3 steps above, only 1 event was able to be processed in SQL thread at the same time so far, but from MariaDB10 it became possible to process in parallel by plural replication worker threads. Thanks to this, it is expected to improve the replication efficiency.
Global Transaction ID (GTID)
When you copy an event group from master to slave, ID is allocated to each transaction. As this ID is a global unique ID, it becomes possible to specialize the same binlog event existing in plural different servers easily. Thanks to this, the efficiency of the following work is improved: changing the slave server to connect to and master server that is the copy source. Also, the slave situation is to be recorded in crush safe method.
Note: GTID has no compatibility between MariaDB and MySQL due to the different implementation methods
It became possible for 1 slave to have plural masters. You can replicate the data from the plural masters in the slave at the same time.
Each master is processed in slave servers by peculiar instance. Each instance is constructed from different I/O threads, SQL threads, and related setting. Designate instances by using CHANGE MASTER.
By utilizing multi-source replication, it is useful for analyzing distributed data comprehensively and backup.
New Storage Engines
Cassandra, CONNECT, Federated X, Spider, and Toku DB have been newly added.
Engine-independent Table Statistics
Before MariaDB 10.0, the statistical information creation for query optimizer relied on the storage engine, and there were some restrictions on flexibility such as the limitation to target data because statistical information was created from the storage engine interface. However, it has become possible to collect more data by making the engine independent.
It became possible to convert EXISTS subquery into IN subquery. You can use Materialization by rewriting explicit correlative EXISTS into non-correlative IN.
SHOW EXPLAIN Command
SHOW EXPLAIN command was added, and it became possible to refer to the implementation plan of the queries operating in designated threads.
EXPLAIN Output from Slow Query Log
It became possible to output EXPLAIN from slow query logs.
Memory Usage Amount per Thread
It became possible to check the memory usage situation every thread.
- In "information_schema.processlist", "MEMORY_USAGE" column newly added
- In "SHOW STATUS", "Memory_used" variable newly added.
SHOW PLUGINS SONAME Command
It became possible to refer to the information on all the plug-in (including uninstalled ones) in "plugin_dir" directory by using SHOW PUGINS SONAME.
It became possible to shut down servers by using SHUTDOWN. You can plan to make events and shut down the server at a particular time.
KILL [CONNECTION | QUERY [ID] ]
It became possible to finish queries by query ID as well as conventional thread ID./li>
Acceleration of UNIQUE Key Production
In addition to sorting non-unique keys, it became possible to accelerate ALTER TABLE even by unique keys.
Implementation of Asynchronous Commit Check Point
On InnoDB and XtraDB, it became possible to conduct asynchronously.
Atomic Entry Correspondence on DirectFS of FusionIO
On InnoDB and XtraDB, it became possible to enter atomically without using double-byte buffers.
DELETE ... RETURNING
When you DELETE to single tables, it became possible to return the result set.
IF [NOT] EXISTS
Similar to CREATE TABLE, it became possible to use "IF [NOT] EXISTS" section even in ALTER TABLE.
Dynamic Column Enhancement
As NoSQL, dynamic column is the function able to have many different columns in each line of tables, and it was added to MariaDB5.3. In case that the attributed number of stored items is high or unknown in advance, you should save in BLOB type dynamic column, and you can change later.
MariaDB10.0 new support:
- Column Reference by Name
- Check of Column Integrity by Using COLUMN_CHECK
- Making Column Data JSON Object by COLUMN_JSON
- Cooperation with Cassandra (Combination of Cassandra Data and MariaDB one in MariaDB Database)
It became possible to control roles by using CREATE ROLE and DROP ROLE. Thanks to this, you can conduct more delicate authorization management efficiently.
New Features Backported from MySQL 5.6
- New InnoDB
- New Performance schema
- Optimized read only transaction (for InnoDB).
- Filesort optimization for queries using the "ORDER BY ... LIMIT"
- Online ALTER TABLE
- character set related extenstions
- GET DIAGNOSTICS
For further information on MySQL5.6 new features, please refer to the following MySQL page.