MariaDB Recommendations document

October 14, 2015 · by · in MariaDB

  dbversity_MariaDB_db_recommendations_summary_document

 

Product Description:

MariaDB is a drop-in replacement for MySQL.

MariaDB strives to be the logical choice for database professionals looking for a robust, scalable, and reliable SQL server. To accomplish this, the MariaDB Foundation work closely and cooperatively with the larger community of users and developers in the true spirit of Free and open source software, and release software in a manner that balances predictability with reliability.

 

MariaDB versus MySQL – Features

In addition to the standard MyISAM, BLACKHOLE, CSV, MEMORY, ARCHIVE, and MERGE storage engines, the following are also included with MariaDB Source and Binary packages:

Speed improvements

  • A lot of optimizer enhancements inMariaDB 5.3. Subqueries are now finally usable! The complete list and a comparison with MySQL is here. A benchmark can be found here.
  • Faster and safer replication:Group commit for the binary log. This makes many setups that uses replication and lot’s of updates more than 2x times faster.
  • Parallel replication.— new in 10.0
  • Improvementsfor Innodb asynchronous IO subsystem on Windows.
  • Indexes for theMEMORY(HEAP) engine are faster. According to a simple test, 24% faster on INSERT for integer index and 60% faster for index on a CHAR(20) column.
  • Segmented Key Cachefor MyISAM. Can speed up MyISAM tables with up to 4x. — new in 5.2
  • Adjustable hash sizefor 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 TABLEis faster.
  • We 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 results sets with all characters between 0x00-0x7f.
  • Pool of Threads in MariaDB 5.1and even better in MariaDB 5.5. This allows MariaDB to run with 200,000+ connections and with a notable speed improvement when using many connections.
  • 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). TheAria 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

We’ve added a lot of new features to MariaDB. If a patch or feature is useful, safe, and stable — we make every effort to include it in MariaDB. The most notable features are:

For a full list, please see features for each release

Better Testing

  • 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.
  • Allbugs and development plans are public.
  • MariaDB isdeveloped by the community in true open source spirit.

Related links

Differences between the MySQL & MariaDB Query optimizer https://mariadb.com/kb/en/mariadb/differences-between-the-mysql-and-mariadb-query-optimizer/

Optimizer feature comparision Matrix at https://mariadb.com/kb/en/mariadb/optimizer-feature-comparison-matrix/

Different MariaDB releases are at https://mariadb.com/kb/en/mariadb/what-is-in-the-different-mariadb-releases/

Packages downloads at https://mariadb.com/kb/en/mariadb/download/

MariaDB FAQ’s are at https://mariadb.com/kb/en/mariadb/faq/

Supports enhanced MariaDB features

Officially supports MariaDB

 

Client Libraries

MariaDB Connector/C

MariaDB Connector/J

MariaDB Connector/ODBC

C++ Client Library (mariadb++)

LGPL MySQL Client Library 3.23.58

More details are at https://mariadb.com/kb/en/mariadb/client-libraries/

MariaDB Galera Cluster – known Limitations

 

Replication works with only InnoDB Engine

No support for explicit Locking commands

All tables should have a primary key, DELETE operations are unsupported on tables without PK.

The query log cannot be directed to a table when you enable query logging.

 

More limitations and information at

https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

 

 

MariaDB Galera Cluster Architecture :

 

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is available on Linux only, and only supports the XtraDB/InnoDB storage engines.

 

 

Features

 

  • Synchronous replication
  • Active-active multi-master topology
  • Read and write to any cluster node
  • Automatic membership control, failed nodes drop from the cluster
  • Automatic node joining
  • True parallel replication, on row level
  • Direct client connections, native MySQL look & feel

 

Benefits

 

The above features yield several benefits for a DBMS clustering solution, including:

 

  • No slave lag
  • No lost transactions
  • Both read and write scalability
  • Smaller client latencies

 

 

 

 

 

 

 

Licencing :

 

What is the pricing for subscriptions?

Both MariaDB Enterprise and MariaDB Enterprise Cluster are offered on a per-server yearly subscription basis. Because MariaDB Enterprise Cluster is designed to deliver high availability through replication, your database cluster must have a minimum of 3 nodes. For more information about pricing, please contact our sales team

 

MariaDB Enterprise is the certified, fully supported version of MariaDB 10 and 5.5, adding up to 24/7 customer support, certified binaries, advanced features and professional services to improve the management and delivery of enterprise-class web applications and IT infrastructures. Frequently Asked Questions

 

MariaDB Enterprise Cluster makes it easy to provision, deploy, monitor, and manage highly available databases. The enterprise support subscription gives you peace of mind with 24x7x365 coverage from the team that built MySQL, and is building the next generation of MariaDB. Added tools for coordinating the deployment and management of Galera-based multi-master synchronous replication clusters let you use this advanced technology without the complexity and guesswork. Experience the benefits of the most advanced MySQL-compatible high availability solution on the market, without the fuss, and backed by the MariaDB team! Frequently Asked Questions

MariaDB 10 is the community version of a high performance, open source database that helps the world’s busiest websites deliver more content, faster, to its users .Frequently Asked Questions

MariaDB Enterprise on IBM POWER8 is an optimized version of MariaDB. Please note that MaxScale, Visual Query Editor, Query Analyzer, Enterprise Monitoring and Backup and Recovery Manager are not yet compatible with POWER8, though are planned to be. Monitoring can already now be run on a separate x86 computer. Read more about POWER8.

 

More details at https://mariadb.com/products/subscription-plans

 

 

YCSB Benchmarks comparision between MariaDB & OracleDB:

Please refer to setup documents below:

 

MariaDB VM Details:

root @ dbversity: /YCSB-master/logs ] free -g

[ root @ dbversity: /YCSB-master/logs ] cat /proc/cpuinfo | grep processor

 

OracleDB VM Details:

 

[ root @ dbversity : ~ ] free -g

total       used       free     shared    buffers     cached

Mem:             7          1          5          0          0          0

-/+ buffers/cache:          1          6

Swap:           11          0         11

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ] df -h’

> ^C

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ] df -h

Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/rootvg-lv_root

97G   28G   64G  31% /

tmpfs                 3.9G  4.0K  3.9G   1% /dev/shm

/dev/sda1             194M   62M  122M  34% /boot

swdclinx1:/xenv       2.5T  1.4T  960G  60% /xenv

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ] cat /etc/redhat-release

.bash_history                 .pki/

.bash_logout                  rpms

.bash_profile                 .ssh2/

.bashrc                       ssh-copy-id.sh

.cshrc                        .tcshrc

log                           test.sh

mongodb-linux-i686-3.0.4.tgz  .tpm/

.mysql_history                yum.repos.old/

.mysql_secret

[ root @ dbversity : ~ ] cat /etc/redhat-release

Red Hat Enterprise Linux Server release 6.6 (Santiago)

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ] top -c

top – 09:00:31 up 18:37,  2 users,  load average: 0.00, 0.00, 0.00

Tasks: 176 total,   1 running, 175 sleeping,   0 stopped,   0 zombie

Cpu0  :  0.8%us,  0.3%sy,  0.0%ni, 98.9%id,  0.0%wa,  0.0%hi,  0.0%si

Cpu1  :  0.8%us,  0.4%sy,  0.0%ni, 98.8%id,  0.1%wa,  0.0%hi,  0.0%si

Cpu2  :  0.8%us,  0.3%sy,  0.0%ni, 98.8%id,  0.0%wa,  0.0%hi,  0.0%si

Cpu3  :  0.7%us,  0.3%sy,  0.0%ni, 98.9%id,  0.0%wa,  0.0%hi,  0.0%si

Mem:   8061328k total,  1928076k used,  6133252k free,   132132k buff

Swap: 12582904k total,        0k used, 12582904k free,   603104k cach

 

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

6423 root      20   0  498m 165m 7156 S  1.0  2.1  10:41.47 /opt/tiv

1 root      20   0 21424 1588 1236 S  0.0  0.0   0:01.36 /sbin/in

2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 [kthrea]

3 root      RT   0     0    0    0 S  0.0  0.0   0:00.59 [migrat]

4 root      20   0     0    0    0 S  0.0  0.0   0:00.39 [ksofti]

5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 [stoppe]

6 root      RT   0     0    0    0 S  0.0  0.0   0:00.15 [watchd]

7 root      RT   0     0    0    0 S  0.0  0.0   0:00.60 [migrat]

8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 [stoppe]

9 root      20   0     0    0    0 S  0.0  0.0   0:00.46 [ksofti]

10 root      RT   0     0    0    0 S  0.0  0.0   0:00.14 [watchd]

11 root      RT   0     0    0    0 S  0.0  0.0   0:00.65 [migrat]

12 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 [stoppe]

13 root      20   0     0    0    0 S  0.0  0.0   0:00.36 [ksofti]

 

[ root @ dbversity : ~ ] cat /proc/cpuinfo | head

processor       : 0

vendor_id       : GenuineIntel

cpu family      : 6

model           : 47

model name      : Intel(R) Xeon(R) CPU E7- 8870  @ 2.40GHz

stepping        : 2

microcode       : 55

cpu MHz         : 2394.030

cache size      : 30720 KB

physical id     : 0

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ]

[ root @ dbversity : ~ ] cat /proc/cpuinfo | grep processor

processor       : 0

processor       : 1

processor       : 2

processor       : 3

[ root @ dbversity : ~ ]

 

 

MariaDB Runtime (ms) :

OracleDB Runtime (ms) :

 

 

 

 

MariaDB Throughput (ops/sec) :

OracleDB Throughput (ops/sec) :

 

 

 

 

 

 

MariaDB vs MySQL Sysbench

 

http://blog.mariadb.org/sysbench-oltp-mysql-5-6-vs-mariadb-10-0/

 

 

MariaDB & MySQL Optimizer Benchmarks at https://mariadb.com/blog/mariadb-53-optimizer-benchmark

 

 

 

MariaDB is a binary drop in replacement for MySQL

For all practical purposes, MariaDB is a binary drop in replacement of the same MySQL version (for example MySQL 5.1 -> MariaDB 5.1, MariaDB 5.2 & MariaDB 5.3 are compatible. MySQL 5.5 will be compatible with MariaDB 5.5).  That means

  • Data and table definition files (.frm) files are binary compatible.
  • All client APIs, protocols and structs are identical.
  • All filenames, binaries, paths, ports, sockets, and etc… should be the same.
  • All MySQL connectors (PHP, Perl, Python, Java, .NET, MyODBC, Ruby, MySQL C connector etc) work unchanged with MariaDB.
  • There are some installation issues with PHP5 that you should be aware of (a bug in how the old PHP5 client checks library compatibility).
  • The mysql-client package also works with MariaDB server.
  • This means that for most cases, you can just uninstall MySQL and install MariaDB and you are good to go. (No need to convert any datafiles if you use same main version, like 5.1).

 

Galera Use cases :

Galera replication works for a wide variety of use cases. Here are some common use cases we have identified in the open source community:

Read Master Traditional MySQL master-slave topology, but with Galera all “slave” nodes are capable masters at all times – it is just the application that treats them as slaves. Galera replication can guarantee 0 slave lag for such installations and, due to parallel slave applying, much better throughput for the cluster.
Write Scalability Distributing writes across the cluster will harness the CPU power in slave nodes for better use to process client write transactions. Due to the row based replication method, only changes made during a client transaction will be replicated and applying such a transaction in slave applier is much faster than the processing of the original transaction. Therefore the cluster can distribute the heavy client transaction processing across many master nodes and this yields in better write transaction throughput overall.
WAN Clustering Synchronous replication works fine over the WAN network. There will be a delay, which is proportional to the network round trip time (RTT), but it only affects the commit operation.
Disaster Recover Disaster recovery is a sub-class of WAN replication. Here one data center is passive and only receives replication events, but does not process any client transactions. Such a remote data center will be up to date at all times and no data loss can happen. During recovery, the spare site is just nominated as primary and application can continue as normal with a minimal fail over delay.
Latency Eraser With WAN replication topology, cluster nodes can be located close to clients. Therefore all read & write operations will be super-fast with the local node connection. The RTT related delay will be experienced only at commit time, and even then it can be generally accepted by end user, usually the kill-joy for end user experiences is the slow browsing response time, and read operations are as fast as they possibly can be.

 

 

 

 

Executive Summary:-

 

 

Performance:

Functionality highlights:

  • Expert in MySQL and Beyond
  • High availability & scalability

 

  • Scalability, High Availability and Security with MariaDB MaxScale

 

  • Launchable scripts automation framework
  • Client side SSL support
  • Binlog Server for MySQL 5.6 and MariaDB 10
  • Non-root MaxScale Service operations

 

  • Global support & Services
  • Backward compatible with MySQL
  • Forever Open source
  • Maintained by MySQL’s Creator
  • More cutting edge features
  • More storage engines
  • Better performance

 

 

 

Drawbacks:

 

  • MariaDB still have Gap in Data Security with Open Source Database Encryption

However, MariaDB 10.1 Delivers the Triple-Threat CIOs and CISOs Have Been Waiting For: High Performance, Low Maintenance and Cost Effective Data Encryption

 

  • Ask Question
    • Tags: