MariaDB 10.3 vs MySQL 8.0 Tyler Duzan, Product Manager Percona Who - PowerPoint PPT Presentation

MariaDB 10.3 vs MySQL 8.0 Tyler Duzan, Product Manager Percona

Who Am I? • My name is Tyler Duzan • Formerly an operations engineer for more than 12 years focused on security and automation • Now a Product Manager at Percona • Have used Puppet, Chef, Ansible, Saltstack, and Terraform professionally in the past 2

There is no “best” database • Percona is the unbiased champion of open source databases • We provide services around both MariaDB and MySQL • Both databases have use cases where they are the right choice • Purpose of this talk is to help you understand the differences so you can make the right choice for your application 3

MySQL / MariaDB History • MariaDB first release in 2009 based on MySQL 5.1, following Oracle acquisition of Sun (who previously acquired MySQL AB) • Through MariaDB 5.5, MariaDB versions were based on and maintained compatibility with MySQL versions • Starting with MariaDB 10.0, released in 2015 new feature development and changes created the beginnings of significant divergence • Newest GA major versions are: • MySQL 8.0(.11), released April 19 th , 2018 • MariaDB 10.3(.7), release May 25 th , 2018 4

Disclaimer • My presentation is not an attempt to be exhaustive, I’m discussing only the things I found specifically interesting • Some features were present in MariaDB first but are now available in MySQL, so while they are new in MySQL 8.0 and important they were really relevant for this comparison. Examples: • Windows Functions • Common Table Expressions • I’m primarily focusing on comparing the community releases of both servers, which means some features may not be mentioned as they’re Enterprise only in one and not present in the other. 5

Feature Incompatibilities and Differences

GTID / Replication • ”The big one” • GTIDs introduced in MariaDB Server 10.0.2, introduced in MySQL 5.6.5, but the implementations differ and are incompatible • MariaDB Server maintains partial compatibility with conversion for MySQL GTIDs allowing you to replicate from a MySQL Master to a MariaDB Slave, but you cannot use a MySQL Slave with a MariaDB Master • In MySQL a GTID is Server UUID + Sequence Number • In MariaDB a GTID Domain ID + Server ID + Sequence Number 7

Encryption / Key Management • Both MariaDB 10.3 and MySQL 8.0 offer data-at-rest encryption features, however these implementations differ and are not compatible with one another. • MariaDB 10.3 provides support for encrypting binary logs, temporary tables, and encrypting Aria data, which is absent in MySQL 8.0 • While MySQL 8.0 does offer enterprise versions of these features, MariaDB Server 10.3 provides key management with AWS KMS and support for encrypted file-based keyrings 8

Authentication • Starting with MySQL 8.0, the new authentication method is based on SHA2 passwords • Old MySQL clients, and the current implementation in MariaDB server are based on the prior implementation of MySQL authentication which uses 9

InnoDB Changes • “Because the InnoDB implementation in MariaDB has diverged from MySQL, it is not meaningful to report a MySQL version number for InnoDB any more.” https://jira.mariadb.org/browse/MDEV-16172 • Many features of MySQL InnoDB exist in MariaDB, but often MariaDB implemented them first and the MySQL implementation differs • Some features of MySQL InnoDB were chosen not to be merged into MariaDB. Most 5.7 features are merged, 8.0 are not. 10

MariaDB Features Not in MySQL

Enterprise Features • There are several features that are present in MariaDB Server 10.3 which are only available in enterprise editions of MySQL • Threadpool • Audit Logging • PAM-based Authentication • AWS KMS Keyring Management 12

Invisible Columns • Introduced in MariaDB Server 10.3 • This feature allows you specify columns in your schema which do not show up in SELECT * statements and are not required to have a value present in an INSERT statement • Adds the INVISIBLE attribute available in DDL statements • When specifically referenced in a SELECT, will still behave normally • Useful for making schema changes without breaking legacy applications, as an example • Enables the feature of System Versioned Tables also in 10.3 13

System Versioned Tables • Introduced in MariaDB Server 10.3 • Based on a SQL:2011 standard implementation • System Versioned Tables store the history of all changes, not only the current set of data. • This enables auditing or analysis from any point in time, which can be useful for forensic data analysis, meeting compliance requirements, or performing cherry-picked point in time recovery • Extends CREATE TABLE and ALTER TABLE syntax to allow the addition of versioning • Internally implemented on top of Invisible Columns • Additionally supports transaction-precise history in InnoDB 14

Oracle Compatibility • Introduced in MariaDB Server 10.3 • Builds on top of many features in MariaDB Server which correspond to SQL standards, such as Common Table Expressions, Window Functions, and others • Implements a subset of PL/SQL which can be enabled using SQL_MODE=ORACLE • Provides support for native support for parsing Oracle Stored Procedures directly into MariaDB • Materialized Views not yet natively supported, but can be adapted on top of the PL/SQL support with tools like Flexviews • Additionally supports Oracle style packages (CREATE PACKAGE, CREATE PACKAGE BODY, SHOW CREATE PACKAGE) 15

Storage-Engine Independent Column Compression • Introduced in MariaDB 10.3(.2) • Provides a new column attribute COMPRESSED • Supports TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, VARCHAR and VARBINARY column types • Currently only supports zlib • Compressed columns cannot be indexed 16

Flashback • Available since MariaDB 10.2.4 • Contributed by Alibaba • Supports all storage engines within MariaDB via full image binary logs (row logging) • Only supports DML statements (INSERT, UPDATE, DELETE) currently • Allows instances, databases, or tables to be rolled back to previous snapshot • Provides binary-log based point in time recovery mechanism • When using this feature, converted binary log events are stored in memory prior to execution, so may require large amounts of RAM. 17

Additional Storage Engines • MyRocks GA as of 10.3.7 • SpiderSE GA as of 10.3.7 • TokuDB GA as of 10.0.10 18

Semi-Synchronous Replication • Previously was a plugin, introduced in MariaDB Server 10.1(.13) • Plugin is also available in MySQL since 5.7 and can be installed with the appropriate INSTALL PLUGIN statement. • Merged into the server directly in MariaDB 10.3, which eliminates the need to install and eliminates plugin locks. • Improvements and server merge contributed by Alibaba in MDEV-13073 • In semi-sync replication a slave does not send acknowledgement until after an event has been written out to the relay log and then flushed. This combines with a timeout to cause synchronous replication to fail to asynchronous replication until slave lag is eliminated at which point semi- sync resumes. 19

MySQL Features Not in MariaDB

Transactional Data Dictionary • Introduced in MySQL 8.0 • Data dictionary is a collection of metadata about the contents of your database • Prior to 8.0 the data dictionary was stored in a mixture of files and tables, many of which were in MyISAM (non-transactional) • The data dictionary from 8.0 onward is stored in a series of tables (mysql.*) using InnoDB • Conveys numerous benefits, but does make upgrading to MySQL 8.0 a one way change. • InnoDB in 5.7 had internal data dictionary (SYS_*) which was dropped as part of this transition 21

Transactional Data Dictionary • Improves performance for INFORMATION_SCHEMA queries (typical 30x performance improvement over MySQL 5.7) • Improves stability my eliminating inconsistencies that were previously possible in the data dictionary • Enables the existence of atomic DDL operations • Makes DDLs and the data dictionary generally crash-safe • Simplifies replication of DDLs • Enables a single uniform API to access data dictionary information • Provides a simple way to generate serialized dictionary information as JSON to enable simplified data migration processes 22

JSON Data Types, Operators, and Functions • Starting in MySQL 5.7, there is a JSON data_type and operators • In MySQL 8.0, there are many new JSON functions added • MariaDB implemented an alias for the JSON data_type which maps to LONGTEXT allowing import via mysqldump • MariaDB does provide some JSON functions for working with JSON data, but fundamentally treats this data as normal strings • MySQL 8.0 adds capabilities to MySQL to use it as a document store so had significantly advanced JSON support comparatively. • MySQL 8.0 added improvements internally to how JSON is interacted with internally allowing partial updates to JSON objects 23