Wednesday, June 6, 2018

MariaDB is powerful and scalable

MariaDB
MariaDB is a forked version of MySQL. It was primarily developed due to concerns that arose when MySQL was acquired by Oracle Inc. MariaDB is a general-purpose DBMS engineered with extensible architecture to support a broad set of use cases via pluggable storage engines. It uses different storage engines to support different use cases.
MariaDB is an open source, multi-threaded, relational database management system, released under the GNU Public License (GPL). Its lead developer is Michael Monty Widenius, who is also one of the founders of MySQL AB. Many features contribute to MariaDB’s popularity as a database system. Its speed is one of its most prominent features. MariaDB is also remarkably scalable, and is able to handle tens of thousands of tables and billions of rows of data. It can also manage small amounts of data quickly and smoothly, making it convenient for small businesses or personal projects. Another feature that sets it apart from its predecessors is a focus on security. MariaDB’s built-in functions include those for manipulating and formatting text, business and statistical calculations, recording chronological information, as well as speciality features such as those related to GPS mapping.
MariaDB Server is one of the most popular open source databases in the world. It is available in Debian and Ubuntu, and is now the default database on Arch Linux, Manjaro, openSUSE, Red Hat Enterprise Linux, CentOS, Fedora and SUSE Linux Enterprise. And as one of the most broadly adopted and widely deployed in the world, MariaDB Server receives contributions from companies like Alibaba, Facebook and Google. Recently, Microsoft has also joined hands to support the MariaDB community.
Features of MariaDB
  • MariaDB is available for use under GPL, LGPL and BSD.
  • It includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources.
  • It uses a standard and popular querying language.
  • MariaDB runs on a number of operating systems and supports a wide variety of programming languages.
  • It offers support for PHP, one of the most popular Web development languages.
  • It offers Galera cluster technology.
  • MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features that impact performance negatively.
Other features include multi-source replication, fusion IO optimisations, table discovery and online alter table.
A comparative analysis of MariaDB and MySQL
MariaDB has a significantly high number of new features, which makes it better in terms of performance and user-orientation. Let’s evaluate both MariaDB and MySQL to determine which one is the better. The choice will ultimately depend on the IT managers and their familiarity with open source databases.
Some significant differences between MySQL and MariaDB
1. Usage of the database: Since 1995, MySQL has been regarded as the most implemented and widely used open source database till date. Many IT giants like Twitter, YouTube, Netflix and PayPal, as well as NASA, the US defence forces and Walmart, make use of this database.
MariaDB, being a more recent arrival, is also taking strong root as back-end software in various IT giant organisations, such as Google, Red Hat, CentOS and Fedora.
2. Structure of the database and the index: MySQL is a pure relational database integrated with an ANSI-standard information schema, and consists of tables, columns, views, procedures, triggers, cursors, etc. The SQL of MySQL is a subset of ANSI SQL 99.
MariaDB, on the other hand, is a fork of MySQL and, hence, has the same database structure and index. This facility makes MariaDB a strong choice for users who want to switch or upgrade their back-end directly without any issues of upgrading the database and data structures.
Everything—from the data, table definitions, structures and APIs—remains identical when upgrading from MySQL to MariaDB.
3. Binaries and implementation: MySQL was developed using C and C++ and is fully compatible to run with almost every operating system like Microsoft Windows, MAC OS X, Linux, FreeBSD, UNIX, NetBSD, Novell Netware and many others.
MariaDB was developed using C, C++, Bash and Perl. It is compatible with various operating systems like Microsoft Windows, Linux, MAC OS X, FreeBSD, Solaris, etc.
4. Replication and clustering: MySQL provides strong replication and clustering through master-master and master-slave replication, and makes use of the Galera Cluster for multi-master clustering.
MariaDB provides almost the same replication and clustering facilities to end users as MySQL in terms of master-master and master-slave replication. It also uses Galera Cluster from version 10.1 onwards.
5. Support for databases: MySQL technical support services are provided round-the-clock via Oracle, and the support team consists of professional developers and engineers who provide various facilities like bug fixes, patches and version releases. Oracle offers MySQL premier support, extended support and sustaining support, depending on what users need.
MariaDB provides strong support to users through the open source community, online forums and even via experts. Round-the-clock support is available for MariaDB via enterprise subscription especially for mission-critical production systems.
6. Security: In terms of security, MySQL provides a strong encryption mechanism for tablespace data. It provides strong security parameters in terms of choosing good passwords, not granting unnecessary privileges to users, and ensures application security by preventing SQL injections and data corruption.
MariaDB has had a significant boost in terms of security features like internal security and password check, PAM and LDAP authentication, Kerberos, user roles, and strong encryption over tablespaces, tables and logs.
7. Extensibility: A database supporting an extensible system can be extended by the user in many different ways like adding new data types, functions, operators, aggregate functions, index methods and procedural languages. MySQL has no support for extensibility.
MariaDB is built on a modern architecture that is extensible at every layer—client, cluster, kernel and storage. This extensibility provides two major advantages. It allows for continual community innovation via plugins, which means that a variety of storage engines, like MariaDB ColumnStore or Facebook’s MyRocks, can be integrated through MariaDB’s extensible architecture. Additionally, it makes it easy for customers to configure MariaDB to support a wide variety of use cases, ranging from OLTP to OLAP.
8. JSON support: MySQL supports a native JSON data type that enables efficient access to data in JSON (JavaScript Object Notation) documents. The JSON data type provides the following advantages over storing JSON-format strings in a string column:
  • Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
  • Optimised storage format: JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later has to read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up sub-objects or nested values directly by key or array index without reading all values before or after them in the document.
On the other hand, MariaDB Server 10.2 introduces a comprehensive set of 24 functions for reading and writing of JSON documents. In addition, the JSON_VALID function can be used with a check constraint for validation, while functions like JSON_VALUE can be used with dynamic columns to index specific fields.
9. Licensing: MySQL offers its code as open source under the GPL, and provides the option of non-GPL commercial distribution in the form of MySQL Enterprise.
MariaDB can only use the GPL, because its work is derived from the MySQL source code under the terms of that licence.
10. Performance: MariaDB achieves best-in-class performance with numerous innovations that are absent for MySQL. These include thread pool management to maximise processing efficiency and extensive optimisation features such as defragmentation within the InnoDB data store. So, when rows are deleted from an InnoDB table, the free space immediately becomes available to the operating system. There’s no need to copy data from old tables to new ones, and there are no gaps in the tablespace. MariaDB also offers engine-independent table statistics to improve the optimiser’s performance, speeding up query processing and data analysis on the size and structure of the tables.
Without these enhancements, MySQL falls short in terms of performance. Thread utilisation in MySQL is sub-optimal and InnoDB tables become fragmented over time, compromising performance.
MariaDB vs MySQL
The following points highlight the pros and cons of MariaDB.
Pros
  • MariaDB has been optimised for performance and is much more powerful than MySQL for large data sets. Elegant migration from other database systems to MariaDB is yet another benefit.
  • Switching from MySQL to MariaDB is relatively easy and is a piece of cake for systems administrators.
  • MariaDB provides better monitoring through the introduction of micro-second precision and extended user statistics.
  • MariaDB enhances the KILL command to allow you to kill all queries from a user (KILL USER username) or to kill a query ID (KILL QUERY ID query_id). MariaDB has also switched to Perl-compatible regular expressions (PCRE), which offer more powerful and precise queries than standard MySQL regex support.
  • MariaDB has applied a number of query optimisations for queries connected with disk access, join operations, sub-queries, derived tables and views, execution control, and even explain statements.
  • MariaDB is purely open source, instead of the dual-licensing model that MySQL uses. Some plugins that are available only for MySQL Enterprise customers have equivalent open source implementations in MariaDB.
  • MariaDB supports significantly more engines (SphinxSE, Aria, FederatedX, TokuDB, Spider, ScaleDB, etc) compared to MySQL.
  • MariaDB offers a cluster database for commercial use, which also enables multi-master replication. Anyone can use it freely and reliance on a MySQL Enterprise system is not required.
Cons
  • Migration of MariaDB back to MySQL has not been possible since release 5.5.36.
  • For new releases of MariaDB, the appropriate libraries (for Debian) will not be deployed in time, which will result in a required upgrade to a newer version due to dependencies.
  • Cluster version of MariaDB is not very stable.
Top reasons to migrate to MariaDB
  • First and foremost, MariaDB offers more and better storage engines. NoSQL support, provided by Cassandra, allows you to run SQL and NoSQL in a single database system. MariaDB also supports TokuDB, which can handle Big Data for large organisations and corporate users.
  • MySQL’s usual (and slow) database engines MyISAM and InnoDB have been replaced in MariaDB by Aria and XtraDB respectively. Aria offers better caching, which makes a difference when it comes to disk-intensive operations.
  • MariaDB provides better monitoring through the introduction of micro-second precision and extended user statistics.
  • The cutting-edge features of MariaDB like GIS, dynamic column support, etc, make it a better choice.
  • MariaDB follows good industry standards by releasing security announcements and upgrades at the same time, and handling the pre-release secrecy and post-release transparency in a proper way. Courtesy Original Posting at https://opensourceforu.com/2018/04/why-mariadb-scores-over-mysql/

No comments: