MariaDB

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/MariaDB

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

Introduction

What is MariaDB?

MariaDB is a fork of MySQL. MySQL is the world's most popular RDBMS; for information about its history, see the official documentation or the MySQL Wikibook.

In 2008 Sun Microsystems bought MySQL. After the acquisition, the development process has changed. The team has started to release new MySQL versions less frequently, so the new code is less tested. There were also less contributions from the community.

In 2009 Monty Widenius, the founder of MySQL, left the company and created a new one, called The Monty Program. He started a new fork called MariaDB.

When Oracle announced the acquisition of Sun Microsystems (and thus MySQL software), most of the MySQL developers left Sun to join its forks: MariaDB and Drizzle.

The scopes of MariaDB are:

import all the new code that will be added to the main MySQL branch, but enhancing it to make it more stable;
clean the MySQL code;
add contributions from the community (new plugins, new features);
develop the Maria storage engine;
adding new features to the server.

Many of the improvements to the code have been written by third parties. The Monty Programs wants to keep open the development process. Its resources are not competitive with other big companies, but it benefits from the community's work. Most of the improvements are imported from Percona's patches, which are included in OurDelta MySQL builds.

Percona also provides packages for some GNU/Linux distributions.

Differences between MariaDB and MySQL

This section tries to list the most important MariaDB unique features.

Storage engines

The following Storage Engines are included by default in MariaDB and not in MySQL:

XtraDB - A fully-compatible fork of InnoDB, mantained by Percona
Aria - A more modern MyISAM, also used for internal tables
TokuDB - A transactional engine with innovative buffers and high compression
Mroonga - Supports fulltext searches with Chinese, Japanese and Korean languages
SPIDER - Shards tables through multiple servers
OQGRAPH - Used to implement tree structures
Sequence - Returns a sequence of numbers as a resultset
CONNECT - Support several external data sources (data files, DBMS's...)
CassandraSE - A bridge to Apache Cassandra
SphinxSE - A bridge to Sphinx
FederatedX - A richer fork of Federated

Obsolete storage engines:

PBXT - A transactional engine which aimed to be "somewhere between InnoDB and MyISAM"

Others may be added in the future.

Why MariaDB?

Free as in Freedom - Released with GPL version 2 license (inherited from MySQL)
Cost - Free!
Support - MySQL has online tutorials, forums, mailing list (lists.mysql.com), paid support contracts; more MariaDB-specific sites / services are expected to come.
Open - contributions and suggestions from the community are welcome.
Speed - One of the fastest databases available; probably faster than MySQL.
Functionality - support for all the MySQL features, plus others developed by the community.
Ease of use - the syntax is flexable and managing a database is pretty simple.
Portability - easily import / export from CSV and XML.
Scalable - Useful for both small as well as large databases containing billions of records and terabytes of data in hundreds of thousands of tables.
Plugins - Some Storage Engines and other plugins are pre-installed in MariaDB (and not in MySQL); you can get support for them.

Resources

Docs

Official sites and documentation.

Obsolete docs

Blogs

Blogs of some MariaDB developers and blogs about MariaDB.

Monty Says, MySQL founder and actual CTO of MariaDB Corporation AB
Sergey Petrunia
Patrick Galbraith

People

User groups and other people.

MariaDB @ Meetup

Misc

Storage Engines

MariaDB includes all the Storage Engines which are included by MySQL 5.1, except for ClusterDB, IBMDB2I and Example.

In addition, it includes some more Storage Engines.

Aria

The Aria storage engine is compiled in by default in MariaDB 5.1 and it is required to be 'in use' when mysqld is started.

Additionally, internal on-disk tables are in the Aria table format instead of the MyISAM table format. This should speed up some GROUP BY and DISTINCT queries because Aria has better caching than MyISAM. The inclusion of Aria is one of the differences between MariaDB 5.1 and MySQL 5.1.

PBXT

The PBXT storage engine is included in the MariaDB source and binaries by default.

PBXT versions in various releases are:

version 1.0.11 in MariaDB 5.1.47 version 1.0.08d in MariaDB 5.1.44b PBXT is a general purpose transactional storage engine. PBXT is fully "ACID" compliant, which means it can be used as an alternative to other MariaDB transactional engines (such as XtraDB or InnoDB).

PBXT features include the following:

MVCC Support: MVCC stands for Multi-version Concurrency Control. MVCC allows reading the database without locking.
Fully ACID compliant: This means that all transactions are: atomic, consistent, isolated and durable.
Row-level locking: When updating, PBXT uses row-level locking. Row-level locking is also used during SELECT FOR UPDATE.
Fast Rollback and Recovery: PBXT uses a specialized method to identify garbage which makes "undo" unnecessary. This make both rollback of transactions and recovery after restart very fast.
Deadlock Detection: PBXT identifies all kinds of deadlocks immediately.
Write-once: PBXT uses a log-based storage which makes it possible to write transactional data directly to the database, without first being written to the transaction log.
Referential Integrity: PBXT supports foreign key definitions, including cascaded updates and deletes.
BLOB streaming: In combination with the BLOB Streaming engine PBXT can stream binary and media directly in and out of the database.
PBXT will not take any resources (disk space or CPU processing) until you create your first PBXT table.

XtraDB

XtraDB is a fork of InnoDB created and maintained by Percona.

XtraDB is completely compatible with InnoDB. The InnoDB codebase is constantly updated when new InnoDB versions are released. Percona applies to it several patches, which fix known bugs and add new features.

XtraDB's documentation: http://www.percona.com/docs/wiki/percona-xtradb:start

In most builds, InnoDB is disabled and XtraDB is instead installed under the name InnoDB. This means that if you specify InnoDB you actually get XtraDB.

FederatedX

FederatedX is based off of the Federated Storage Engine, which is no longer maintained. It is an attempt to move the Federated Storage Engine forward to fix bugs, add new features and develop new concepts that are easier to achieve as a pluggable storage engine.

FederatedX at Launchpad: https://code.launchpad.net/federatedx

Features

The features that are discussed in this page have been developed for MariaDB and may not be present in MySQL.

Pool of threads

Table elimination

Microseconds precision

SHOW PROCESSLIST, INFORMATION_SCHEMA.PROCESSLIST and the Slow Log now have a microsecond precision.

A microsecond is a unit of time equal to one millionth (10^-6) of a second.

SHOW PROCESSLIST

In MariaDB, an extra column `TIME_MS` has been added to the INFORMATION_SCHEMA.PROCESSLIST table, as well as to the output of SHOW FULL PROCESSLIST. This column shows the same information as the column `TIME`, but in units of milliseconds with microsecond precision (the unit and precision of the `TIME` column is one second).

The value displayed in the `TIME` and `TIME_MS` columns is the period of time that the given thread has been in its current state. Thus it can be used to check for example how long a thread has been executing the current query, or for how long it has been idle.

In MariaDB the `TIME` column and the `TIME_MS` column are not affected by any setting of @TIMESTAMP. This means that it can be reliably used also for threads that change @TIMESTAMP (such as the replication SQL thread).

The `TIME` column of SHOW FULL PROCESSLIST and INFORMATION_SCHEMA.PROCESSLIST can not be used to determine if a slave is lagging behind. For this, use instead the Seconds_Behind_Master column in the output of SHOW SLAVE STATUS.

The addition of the TIME_MS column is based on the microsec_process patch, developed by Percona.

Slow Log

The Slow Log includes details of execution plan and microsecond-precision resolution.

This addition is based on the microslow patch from Percona.

Performance

MariaDB's code has several optimizations which have been made after the fork has been launched.

Some speed enhancements are:

CHECKSUM TABLE is faster;
performance improvements for common cases of character set conversion;
test suite speedups.

The list is not complete.

MariaDB/Printable version

Contents